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

MySQL Backup Korn Shell script using mysqldump (Backup 1 or ALL databases)

Overview

Note:  MysqlDump cannot backup information_schema by itself
 

How to backup your databases with a simple ksh script

#!/bin/ksh
#########################################################################
# Created by Philippe Courtois                                          #
# date: 2012-08-22                                                      #
#########################################################################
###set -x

if [ "$1" == "-?" ];then
        echo "USAGE: backup_mysql.ksh instance_name database_name|ALL "
        exit
fi

if [ $# -ge 2 ]; then
        export INSTANCE=$1
        echo "INSTANCE=$INSTANCE"
        export DATABASE=$2
        echo "DATABASE=$DATABASE"
else
        echo "USAGE: backup_mysql.ksh instance_name database_name|ALL"
        exit
fi

echo `date` " BACKUP BEGIN"
export TIMESTAMP=`date +%Y%m%d%H%M%S`
export BACKUP_BASE=/dbabackup # Backup folder
export BACKUP_HOME=$BACKUP_BASE/$1
export USER=mysql_user  #mysql root account or account with backup  rights
export HOST=mysql_host  # mysql hostname
export PASSWORD=user_password # mysql account password
export MYSQL=`which mysql`
export MYSQLDUMP=`which mysqldump`
export GREP=`which grep`
#export GZIP=`which gzip`
export PART=_
echo "TIMESTAMP=$TIMESTAMP"
echo "BACKUP_BASE=$BACKUP_BASE"
echo "BACKUP_HOME=$BACKUP_HOME"
echo "USER=$USER"
echo "HOST=$HOST"
echo "PASSWORD=$PASSWORD"
echo "MYSQL=$MYSQL"
echo "MYSQLDUMP=$MYSQLDUMP"
echo "GREP=$GREP"
#echo "GZIP=$GZIP"

if [ -d /dbabackup/$INSTANCE ]; then
        export DBABACKUP=/dbabackup/$INSTANCE
        echo "DBABACKUP=$DBABACKUP"
else
        mkdir -p /dbabackup/$INSTANCE
        export DBABACKUP=/dbabackup/$INSTANCE
        echo "DBABACKUP=$DBABACKUP"
fi

if [ "$DATABASE" == "ALL" ]; then
        for i in `echo 'SHOW DATABASES;' | $MYSQL -u$USER --password=$PASSWORD -h$HOST|$GREP -v '^Database$'|$GREP -v grep|$GREP -v information_schema`

        do
                FILENAME=$BACKUP_HOME/$i$PART$TIMESTAMP.sql
                echo `date` " BACKUP STARTING $i on $FILENAME"
                $MYSQLDUMP -u$USER --password=$PASSWORD --database $i > $FILENAME
                j=`ps -ef | grep  -i mysqldump | grep -v grep| wc -l`
                while [ "$j" -ne "0" ]
                do
                        sleep 5
                        j=`ps -ef | grep -i mysqldump | grep -v grep| wc -l`
                done
                echo `date` " BACKUP FINISHED $i"
        done
else
        export FILENAME=$BACKUP_HOME/$DATABASE$PART$TIMESTAMP.sql
        echo `date` " BACKUP STARTING $DATABASE on $FILENAME"
        $MYSQLDUMP -u$USER --password=$PASSWORD --database $DATABASE > $FILENAME
        j=`ps -ef | grep -i mysqldump | grep -v grep| wc -l`

        while [ "$j" -ne "0" ]
        do
                sleep 5
                j=`ps -ef | grep -i mysqldump | grep -v grep| wc -l`
        done

        /bin/gzip $FILENAME
        echo `date` " BACKUP FINISHED $DATABASE"
fi
echo `date` " BACKUP END"

exit 

Syntax how to execute the script

• Backup all the databases

./backup_mysql.ksh mysql ALL

this command will backup all databases (except  information_schema) from the instance mysql.

• Backup a specific database

./backup_mysql.ksh mysql mysql

This command will backup the database mysql in the instance mysql.


How to add this script in the crontab

#
# min|hour |day  |month|day  | script
#    |     |of mo|     |of wk|
#----|-----|-----|-----|-----|------------------------------------------------
#
#
### Backup ALL mysql instances every day
05 00 * * * /admin/mysql/nossql/backup_mysql.ksh mysql ALL 1>/admin/mysql/noslog/backup_mysql.log 2>&1
### Backup the mysql instance every day
05 00 * * * /admin/mysql/nossql/backup_mysql.ksh mysql mysql 1>/admin/mysql/noslog/backup_mysql.log 2>&1