MySQL - Backup and Recovery

A MySQL administrator makes database backups to guard against the possibility of system failures / crashes, or hardware failures that may result in data loss or corruption. Backups are also useful when users remove databases or tables by mistake. Another use of backups is to move or copy databases to another server.

Backups can be made by copying database file directly, or by using programs designed for that purpose: mysqldump, mysqlhotcopy, MySQL Administrator, and InnoDB Hot Backup.

Backup is only one of the components needed for data recovery. The other is the binary log. To recover databases, you use the backup to restore them to their state at backup time, and then re-execute statements contained in the binary log that made data changes after the backup was created.

A binary backup is a copy of the files in which database contents are stored. Copying these files preserves the databases in exactly the same format in which MySQL itself stores them on disk. Restoration involves copying the files back to their original locations. Techniques for making binary backups include file copy command (such as cp or tar), mysqlhotcopy, and InnoDB Hot Backup.

A text backup is a dump of database contents into text files. Restoration involves loading the file contents back into databases by processing them through the server. Techniques for making text backups include the SELECT … INTO OUTFILE statement, mysqldump, and MySQL Administrator.

It is faster to make a binary backup because it involves only file copy operations that need to know nothing about the internal structure of the files. However, if the backup is to be used for transferring databases to another machine that uses a different architecture, the files must be binary portable. Binary portability means that the files are machine independent and that you can directly copy them from one MySQL server to another on a different machine and the second server will be able to access their contents with no problems. With binary backup, it is necessary to make sure that the server does not modify the file while the backup is in progress. The procedure for making binary backups depends on which storage engine created the tables, and generally can be used only for local MySQL server.

It is slower to make a text backup because the server must read tables and then write the contents out to disk files itself or send the contents to a client program that writes the tables. Text backups are portable. With text backup methods, the server must be running because it must read the files that are to be backed up. Text backup procedures are more general and can be used for tables created by any storage engine.

Backup and Recovery Principles
Binary Backup for MyISAM
Recovery

The below technique use replication to aid making backup so that making backup does not impact performance. Steps to setup:

1. On the master server, copy the below my.cnf.master file to /etc/my.cnf.
2. Restart the master server
3. Make a backup copy of the master database. This is the replication baseline that sets the slave to known initial state: mysqldump -u root -p —all-databases —master-data=2 > setup_replication.sql
4. Create an account on the master server that the slave can use for connecting to the master and requesting updates. The account must have the global REPLICATION SLAVE privilege
5. Copy the setup_replication.sql to the slave server
6. At this point, I assume that the slave server has just been installed (either from RPMs or from source)
7. Run: mysql -u root -p < setup_replication.sql
8. On the server that is being used to make backup, copy my.cnf.slave to /etc/my.cnf
9. Restart the slave server
10. Open up setup_replication.sql and locate the commented out CHANGE MASTER command
11. Execute this CHANGE MASTER command on the slave
12. On the slave, replication use 2 threads known as "I/O thread" and "SQL thread". Use "SHOW PROCESSLIST" to determine if both of these threads are running. If they are not running, we may need to restart the slave server, and examine the log file /var/log/mysqld.log
13. Schedule the script to run via cron

To create an account on the master server that the slave can use for connecting to the master and requesting updates:

mysql> CREATE USER 'replication_user'@'slave_host' IDENTIFIED BY 'slave_pass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'slave_host';

my.cnf.master:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
max_connections = 200
key_buffer_size = 1G
tmp_table_size = 32M
max_heap_table_size = 32M
thread_cache_size = 16
table_cache = 1024
skip-name-resolve
server-id=1

# We should always enable the binary log
log-bin=/var/lib/mysql/logbin

# The slow query log should only be enabled during the period that we want to do performance tuning
# long_query_time = 1
# log-queries-not-using-indexes
# log-slow-queries

# The general query log should normally be disabled
# log

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

my.cnf.slave:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
max_connections = 200
key_buffer_size = 1G
tmp_table_size = 32M
max_heap_table_size = 32M
thread_cache_size = 16
table_cache = 1024
skip-name-resolve
server-id=2

# We should always enable the binary log
log-bin=/var/lib/mysql/logbin

# By default, slave server does not write to its own binary log any data modifications that are received from the master.
# However, because we are using this slave to take backup of the main server, and we are using the binary log as
# part of our backup strategy, we need this slave to write to its own binary log
log-slave-updates
relay-log=/var/lib/mysql/logrelay

# The slow query log should only be enabled during the period that we want to do performance tuning
# long_query_time = 1
# log-queries-not-using-indexes
# log-slow-queries

# The general query log should normally be disabled
# log

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

The below script do a full backup (copying the data files in /var/lib/mysql) once a week on Sunday. Every other day, it will copy the binlog. To run this script everyday at 2AM, use crontab -e:

0 2 * * * /usr/bin/mysql-backup.sh

/usr/bin/mysql-backup.sh:

#!/bin/sh
backup_dir='/doNotDelete/ClinicalCafe/mysql_backups'
original_dir='/var/lib/mysql'
weekday=`date +'%A'`
/sbin/service mysqld stop
status=$?
if test $status -eq 0
then
    if test $weekday = 'Sunday'
    then
        # This is a full backup (back up the data files, without the binary log files)
        # Rotate previous backups
        cd $backup_dir
        rm -rf allmysqldb.1
        mv allmysqldb allmysqldb.1
        cp -R $original_dir $backup_dir/allmysqldb
        # In the backup directory, we can delete all the files that were
        # part of replication
        find $backup_dir/allmysqldb -regex '.*logbin.[0-9]+' -exec rm {} \;
        find $backup_dir/allmysqldb -regex '.*logbin.index' -exec rm {} \;
        find $backup_dir/allmysqldb -regex '.*logrelay.[0-9]+' -exec rm {} \;
        find $backup_dir/allmysqldb -regex '.*logrelay.index' -exec rm {} \;
        find $backup_dir/allmysqldb -regex '.*relay-log.info' -exec rm {} \;
        find $backup_dir/allmysqldb -regex '.*master.info' -exec rm {} \;
        # In /var/lib/mysql
        #find $original_dir -regex '.*logbin.[0-9]+' -exec rm {} \;
        #find $original_dir -regex '.*logrelay.[0-9]+' -exec rm {} \;
    else
        #  This is a delta back up (only copy the new binary log files)
        find $original_dir -regex '.*logbin.[0-9]+' -newer /tmp/clinicalcafe_mysql_database_backup_successful_compare -print -exec cp {} $backup_dir/allmysqldb \;
    fi
    /sbin/service mysqld start
    status=$?
fi

if test $status -eq 0
then
    date > /tmp/clinicalcafe_mysql_database_backup_successful
    touch /tmp/clinicalcafe_mysql_database_backup_successful
    rm -f /tmp/clinicalcafe_mysql_database_backup_successful_compare
    touch /tmp/clinicalcafe_mysql_database_backup_successful_compare
fi