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.
Index
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).sqlCommand 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).sqlCommand 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).sqlCommand 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).sqlCommand for Taking Metadata Backup Only
mysqldump -u root -p -h127.0.0.1 --no-data INFRA > database_name-backup-$(date +%F).sqlMySQL 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.sqlCommand 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.sqlCommand 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.sqlmysql -uroot -pmysql -h127.0.0.1 TEST < database_name-movies-2022-01-03.sqlExport & 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 TESTConclusion
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.