MySQL Backup & Restore | How to Take Backup & Restore a MySQL Database

It’s very important to possess a homogenous backup of a database so that in case of any unforeseen issues we can recover the data from the backup and run the system. Backups are also essential to safeguard ourselves before upgrading a MySQL database.

MySQL offers different types of backup methods from that you can choose the methods that best suit the necessities for your installation. In this article, we are going to discuss different ways of taking backups of MySQL databases and restore them.

  • Backups: Logical or physical, full or incremental, and so forth.
  • Methods for creating backups.
  • Recovery methods, including point-in-time recovery.
  • Backup scheduling, compression, and encryption.
  • Table maintenance, to enable recovery of corrupt tables.

MySQL Backup Options

General Backup Syntax –

mysqldump utility can dump a database including the required sql statement to rebuild the same.

mysqldump -u [username] -p [databaseName] > [filename]-$(date +%F).sql

Command to Take a Backup of An Entire Database

mysqldump -u root -p -h127.0.0.1 --all-databases --single-transaction --quick --lock-tables=false > full_backup-$(date +%F).sql

Command to Take a Compressed Database Backup

mysqldump -u root -p -h127.0.0.1 --all-databases --single-transaction --quick --lock-tables=false|gzip > full_backup-$(date +%F).sql.gz

–single-transaction: Issue a BEGIN SQL statement before dumping data from the server.

–quick: Enforce dumping tables row by row. This provides added safety for systems with little RAM and/or large databases where storing tables in memory could become problematic.

–lock-tables=false: Do not lock tables for the backup session.

Command to Take Single / Multiple Database Backup

mysqldump -u username -p -h127.0.0.1 -databases database1 database2 --single-transaction --quick --lock-tables=false > database_name-backup-$(date +%F).sql

Command to Take Single table Backup from a Database

mysqldump -u username -p -h127.0.0.1 --single-transaction --quick --lock-tables=false database_name table_name > database_name-table1-$(date +%F).sql

Command for Taking Metadata Backup Only

mysqldump -u root -p -h127.0.0.1 --no-data INFRA > database_name-backup-$(date +%F).sql

MySQL Restoration Options

Command to Restore Full Instance

/mysqldatabase/product/binary/5.7.35/bin/mysql -u root -p -h127.0.0.1 < full-backup-2021-10-03.sql

Command to Restore Single Database from Full Database Backup

mysql --one-database database_name < all_databases.sql

/mysqldatabase/product/binary/5.7.35/bin/mysql -u root -p -h127.0.0.1 --one-database infra2 < full-backup-2022-01-03.sql

Command to Restore a Table

Extract table
sed -n -e '/DROP TABLE.*`table_name`/,/UNLOCK TABLES/p' dump.sql > my_test_table.sql

Import the new table
mysql -u [user] -p'password' database < mytest_table_restored.sql
mysql -uroot -pmysql -h127.0.0.1 TEST < database_name-movies-2022-01-03.sql

Export & Import

mysqldump -u root -p database_name | mysql -h remote_host -u root -p remote_database_name

mysqldump -u root -pmysql -h127.0.0.1 TEST | /mysqldatabase/product/binary/5.7.35/bin/mysql -u root -pmysql -h172.168.190.35 TEST

mysqldump -u root -pmysql -h127.0.0.1 TEST | mysql -u root -pmysql -h172.168.190.35 TEST


The above article has provided you with enough information about how to take backup and restore a MySQL database. The step-by-step guide is specially provided to help beginners understand the process and start testing the different processes in their environment. 

Hope this will be helpful for all. 

Leave a Comment

Your email address will not be published. Required fields are marked *