Technical Recipes

How to backup MySQL database on Linux

There are various ways to do full database backup using tools. What we discuss here is to use mysqldump to do online, logical backup. Assume that data is stored in the InnoDB storage engine, which has support for transactions and automatic crash recovery.

For cases of operating system crashes or power failures, we can assume that MySQL's disk data is available after a restart. The InnoDB data files might not contain consistent data due to the crash, but InnoDB reads its logs and finds in them the list of pending committed and noncommitted transactions that have not been flushed to the data files. InnoDB automatically rolls back those transactions that were not committed, and flushes to its data files those that were committed.

For the cases of file system crashes or hardware problems, we can assume that the MySQL disk data is not available after a restart. This means that MySQL fails to start successfully because some blocks of disk data are no longer readable. Then it is necessary to recover our MySQL data from backups.

Full backups are necessary, but it is not always convenient to create them. They produce large backup files and take time to generate. It is more efficient to make an initial full backup, and then to make incremental backups.

0 Enable binary log

    You can specify binary log location in my.ini
    
	log-bin=<log_path>/file_name

or start database server with option --log-bin=<log_path>/file_name.
    
1. Do an initial full backup weekly

    shell> mysqldump --single-transaction --flush-logs --master-data=2 --all-databases | gzip > backup_sunday_1_PM.sql.gz

    Make sure to include option --flush-logs when you make your full backup, the binary log files created afterward contain all the data changes made since the backup.

    The --master-data option causes mysqldump to write binary log information to its output, so the resulting .sql dump file.

2. Flush the logs daily

    You can create an incremental backup by flushing the logs to begin a new binary log file:
    
    shell> mysqladmin flush-logs

3. Backup the logs files daily

   Keep at least three backups on file, just in case one is corrupted or lost, and store them in different places and on different mediums, like CD, DVD, different hard drives, etc. You can use SSH/SFTP/SCP to copy or sync files between different host or systems. One of the automated way to do this is using WinSCP script as shown in [6] and [7].

4. Use backups for recovery

    4.1 To see a listing of all binary log files
    
    	mysql> SHOW BINARY LOGS;

    4.2 To determine the name of the current binary log file
    
    	mysql> SHOW MASTER STATUS;

    4.3 To restore the full backup from last week
    
    	shell> mysql < backup_sunday_1_PM.sql

    4.4 To use the incremental binary log files up to yesterday
    
        If you have more than one binary log to execute on the MySQL server, make sure to process them all using a single connection to the server like this:
    
    	shell> mysqlbinlog bin.000007 bin.000008 | mysql -u root -p

    4.5 To restore today's data
    
	Today's data is still in the unflushed binary log file. If it's available on the hard disk or some other safe storage, flush it and restore it as well.
    
    	shell> mysqladmin flush-logs
    	shell> mysqlbinlog bin.000009 | mysql -u root -p

    4.6 To view log contents or modify it before restore
    
    	shell> mysqlbinlog binlog_files | more
    	shell> mysqlbinlog binlog_files > tmpfile
    	shell> ... edit tmpfile ...
    
    4.7 To restore using the output of the log viewer
    
    	Saving the output in a file is useful as a preliminary to executing the log contents with certain events removed, such as an accidental DROP DATABASE. You can delete from the file any statements not to be executed before executing its contents. After editing the file, execute the contents as follows: 
    
    	shell> mysql -u root -p < tmpfile

	If you have more than one binary log to execute, you can write all the logs to a single file and then process the file:
		
    	shell> mysqlbinlog binlog.000001 >  /tmp/statements.sql
    	shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql
    	shell> mysql -u root -p -e "source /tmp/statements.sql"

5. Purge old binary logs

    The MySQL binary logs take up disk space. To free up space, purge them from time to time. One way to do this is by deleting the binary logs that are no longer needed, such as when we make a full backup:

    shell> mysqldump --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > backup_sunday_1_PM.sql

	Deleting the MySQL binary logs with --delete-master-logs can be dangerous if your server is a replication master server, because slave servers might not yet fully have processed the contents of the binary log.

    PURGE BINARY LOGS TO 'mysql-bin.010';
    PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

	This statement is safe to run while slaves are replicating. You need not stop them. If you have an active slave that currently is reading one of the log files you are trying to delete, this statement does nothing and fails with an error. However, if a slave is not connected and you happen to purge one of the log files it has yet to read, the slave will be unable to replicate after it reconnects.

    Among others, the solution could be to purge the log files 7 days earlier:
    echo 'PURGE MASTER LOGS BEFORE DATE_SUB(NOW( ), INTERVAL 7 DAY);' | mysql -u root -p
    
6. Create cron job

	If you need to make a cron job, see [8].