A MySQL Backup Strategy

The two common tools of Backup in MySQL are the following: 

1.        mysqldump

2.        mysqlhotcopy

If we are using innodb database engine, it is possible to perform online backup with only mysqldump. It is possible for you to use mysqlhotcopy, in case you are using MyISAM database engine.

Assume that we have scheduled the full database backup daily at 3 am. As the database goes into production, we may need to revise this plan. We may still continue to do a full database backup daily. In addition, there could be incremental backups on an hourly basis.

C:\Program Files\MySQL\MySQL Server 5.0\bin> mysqldump –u root –p – – all-databases – – single-transaction > backup.sql


In order to make the incremental backups, we need to start MySQL server with the option --log-bin. If you have implemented Replication, it creates the binary log. It is stored in, for example, E:\mysqlBinlog, as shown below:

Name Size Type Date Modified Attributes
A
Mysql-bin.000080 6,733 KB 000080 File 6/25/2007 2:56PM A
Mysql-bin.000081 1,027 KB 000081 File 6/25/2007 3:54 PM A

The last file ‘mysql-bin.000081’ is being updated currently.  These binary logs form the incremental backups.  Hence while we conduct a full backup, ensure that the cache is flushed so that a new binary log is created. The full backup plus all the binary logs created after the full backup constitute the data at present.

C:\Program Files\MySQL\MySQL Server 5.0\bin> mysqldump –u root –p – -all-databases – -single-transaction – -flush-logs – -master-data=2 > e:\Backup\2007-06-25_10213136.sql 

It adds the following lines to the file 2007-06-25_10213136.sql, which will be useful at the time of Recovery:

— Position to start replication or point-in-time recovery from 

— CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000082′, MASTER_LOG_POS=98; 

The listing in E:\mysqlBinlog currently will be as shown below: 

Name Size Type Date Modified Attributes
A
Mysql-bin.000080 6,733 KB 000080 File 6/25/2007 2:56PM A
Mysql-bin.000081 1,949 KB 000081 File 6/25/2007 4:39 PM A
Mysql-bin.000082 253 KB 000082 File 6/25/2007 4:54 PM A

Essentially, 2007-06-25_10213136.sql and mysql-bin.000082 together form the most up to date database at any point of time. We may copy this binary file and the later ones to a safe location on every hour.The following is a backup script with name “backup.bat” created on the server in the folder C:\Program Files\MySQL\MySQL Server 5.0\data. It creates backup file once daily at 3 am and stores it in the folder E:\Backup. The script file contains the password of root user. So we need to control access to it.

echo off

echo Starting Backup of Databases:

for /f “tokens=2-4 delims=/ ” %%a in (‘date /t’) do (set dt=%%c-%%a-%%b)

for /f “tokens=1-4 delims=:.” %%a in (‘echo %time%’) do (set tm=%%a%%b%%c%%d)

set backupfile=%dt%_%tm%.sql

echo Backing up to file: %backupfile%

“C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqldump” -u root -p”password” – -all-databases – -single-transaction – -flush-logs – -master-data=2 > e:\Backup\%backupfile%

CD \Program Files\MySQL\MySQL Server 5.0\data

echo on

 This script is scheduled to run at 3 am everyday, by Windows Scheduler.    


 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s