How to backup and restore MySQL

Overview

This post is to help you backup and restore MySQL.
Concerning the backups, this post will mainly talk about it using either cold backups or mysqldump.

Backup MySQL

Cold Backup

Assuming that your MySQL instance is installed on /var/lib/mysql.
doing a cold backup for mysql is very simple. you first shutdown then you backup your instance the file level.

mkdir /dbabackup
cd /var/lib
cp -Rp mysql /dbabackup/. 

If you want you can then tar the /dbabackup/mysql folder
you can use one of those 2 commands

tar -cvf mysql.tar /dbabackup/mysql

or

tar -cvzf mysql.tar.gz /dbabackup/mysql

Backups using mysqldump

Mysqldump has many parameters and if you want to look at them.

Mysqldump infoMySQL Dev site
Mysqldump description from linuxcomend.org

Here are few basic exemples for mysqldump. Please note that by default the --opt option is ON.

mysqldump -uusername -ppassword my_database > my_database.sql

mysqldump -uusername -ppassword --databases my_d1 my_db2 my_db3 > multiple_db.sql

mysqldump -uusername -ppassword --all_databases > all_databases.sql

On my side here are the options that I like to add when backing up databases.
I add the following options

• --add-drop-database that add the drop database statement for each database.
• --flush-logs 
• --master-data or --master-data=1 if the backup is to create a Slave/Replica
• --master-data=2 if this is mainly a backup of a Master. it still can be used for a Slave/Replica but first make sure you iformation from the show master status; on the master so you can update the Slave/Replica using CHANGE MASTER TO command, then restart the slave.
• --opt I put --opt even if it is ON by default

so if you are doing a backup that will be used for a stand alone instance or just the master of a Master/Slave.

mysqldump -uusername -ppassword --all-databases --add-drop-database --flush-logs --master-data=2 > full_backup.sql

If you want a backup  to create a Slave/Replica then use the following

mysqldump -uusername -ppassword --all-databases --add-drop-database --flush-logs --master-data=2 > full_backup.sql

Restore MySQL

The restore of a database, multiple databases or the full instance is fairly easy. make sure that there is a drop database in the .sql file so you overwrite the full database(s).

If you are restoring a single database:

mysql -uusername -ppassword -D my_database < my_database.sql

If you are restoring multiple databases:

mysql -uusername -ppassword  < multiple_databases.sql

If you are restoring a FULL backup:

mysql -uusername -ppassword  < full_backup.sql

 If you toke a cold backup using cp -Rp :

service mysql stop
mv /dbabackup/mysql.tar /var/lib
cd /var/lib
rm -Rf mysql
tar -xvf mysql.tar
service mysql start