How to backup and restore MySQL database?

Last updated on September 14th, 2020 at 05:05 am

When working with MySQL, you may need to backup and restore MySQL database regularly. It helps recover in case of accidents. Here’s how you can backup and restore MySQL database.

mysqldump is an easy way to backup MySQL database. It comes bundled along with MySQL setup files. It is installed on your computer when you install MySQL. It creates a *.sql file with DROP table, CREATE table and INSERT into SQL statements of your database. To restore MySQL database, execute the *.sql file on destination database.

You can use mysqldump to backup single or multiple databases. You can even backup specific tables in a database.

Here’s the syntax of commands to backup and restore MySQL database:

backup: # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

restore:# mysql -u root -p[root_password] [database_name] < dumpfilename.sql

How To Backup MySQL database

1. Backup a single database:

Let’s say you want a backup of sample_db database. E.g, your root password is ‘passwd’

# mysqldump -u root -ppasswd sample_db > sample_db.sql

This command takes backup and dumps output of database in sample_db.sql. The sales_db.sql will contain drop table, create table and insert command for all the tables in the sales_db database. Following is a partial output of sales_db.sql, showing the dump information:

--
-- Table structure for table `user_table`
--
DROP TABLE IF EXISTS `user_table`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(75) DEFAULT NULL,
`email` varchar(75) NOT NULL,
`password` varchar(128) NOT NULL,
`date_joined` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `user_table`
--

LOCK TABLES `user_table` WRITE;
/*!40000 ALTER TABLE `user_table` DISABLE KEYS */;
INSERT INTO `user_table` VALUES (1,'test_user','test@test.com','sha1$96e28$effdf3bfe8d0477','2012-12-12 23:17:10'),(7,'vipul@gmail.com','vipul@gmail.com','sha1$5e05960cede8','2013-02-05 14:56:04'),(8,'amar@gmail.com','amar@gmail.com','sha1$c2497b6420379ac76','2013-02-05 14:57:01');
/*!40000 ALTER TABLE `user_table` ENABLE KEYS */;
UNLOCK TABLES;

2. Backup multiple databases:

Select the databases you want to backup. Here’s how you can get a list of all databases:

# mysql -u root -ppasswd

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| sample_db          |
| mysql              |
| sample_sales_db    |
+--------------------+
4 rows in set (0.00 sec)

Let’s say, you want to take backup of both sample_db and sample_sales_db database. Execute mysqldump as shown:

# mysqldump -u root -ppasswd --databases sample_db sample_sales_db > multi_databases.sql

3. Backup all the databases:

Here’s how you backup all the databases of your MySQL instance.

# mysqldump -u root -ppasswd --all-databases > all-database.sql

4. Backup a specific table:

Let’s say we want to backup only the user_table table from sample_db database.

# mysqldump -u root -ppasswd sample_db user_table > sample_db_user_table.sql

How To Restore MySQL database

To restore the sample_db database, execute mysql with < as shown below. Ensure the database into which you restore your .sql file exists on your computer. Else you can create a new database and then perform the restore.

# mysql -u root -ppasswd

mysql> create database sample_db;
Query OK, 1 row affected (0.02 sec)

# mysql -u root -ppasswd sample_db < sample_db.sql