Backup and restore MySQL database

Posted on Edited on In , Views:

Backup a database

mysqldump is a command-line utility which can be used to generate backups of MySQL database.

$ mysqldump -u root --password=<db_password> mydb > mydb_dump_`date +"%Y%m%d_%H%M%S"`.sql
$  ls -ltr | grep mydb
-rw-r--r--.   1 root root 4834575 Sep 28 21:11 mydb_dump_20210928_144610.sql

Restore a database

Create an empty database before restore as below:

$ mysql -u root -p

mysql> create database mydb;
mysql> show databases;
mysql> exit

Restore the database:

$ mysql -u root -p mydb < mydb_dump_20210928_144610.sql

Check the database size as below:

mysql> SELECT table_schema "DB Name", ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"  FROM information_schema.tables  GROUP BY table_schema;
+--------------------+---------------+
| DB Name            | DB Size in MB |
+--------------------+---------------+
| mydb               |           8.1 |
+--------------------+---------------+