MySQL Backup Strategy

The Story

A few months ago, while at home, my supervisor called me. The incident was that our client’s QR System stops working on a certain module. I learn that the possible cause was a table corruption, so the the story starts.

We went to the client the next day to check what was wrong. I found out that a table is indeed corrupt. We tried to repair the table using myisamchk but it just worsen the problem.

Good thing is that every batch of record on the table has an equivalent CSV file. We restored the latest backup which is from two days ago then restored the rest using the CSV files. So that’s how we started a better backup plan.

Backup Strategy

We are using MyISAM for our table engines. Of course we are using MySQL for Windows. Since most of our operations are more on write (INSERT/UPDATE), we decided to change the table engine to InnoDB to provide better consistency and performance.

This is the summary of the backup plan.

1. Use InnoDB (for transactional databases)
2. Enable database logging
3. Schedule automatic backup
4. Teach the user to backup every night

Since we thought that InnoDB is the right engine for our type of application, we changed all our tables to InnoDB engine. Though phpMyAdmin row count annoyances occurred, we can simply ignore incorrect row count in phpMyAdmin.

To enable database logging for MySQL, here are the simple steps:

1. Choose a location (preferably on a different drive or partition) to store the logs.

2. Edit my.ini (for Windows and my.cnf for Linux). Add this line:

1	log-bin="/path/to_data_dir/prefix"

This will enable logging of binary files for MySQL and the prefix is a string which will be the prefix for your log file name.

3. Schedule automatic backup and schedule manual backup.

Additional Settings

As advised from MySQL Performance Blog, here are some additional settings for tuning up InnoDB performance.

Edit my.ini or my.cnf and modify / add the following contents:

1 2	#innodb_flush_log_at_trx_commit - set the value to 2 innodb_flush_log_at_trx_commit=2

Of course you need to adjust innodb_buffer_pool_size to a higher level.

The Backup Commands

Since we are logging every transactions into a file, we need also to make sure that logs are also flushed so that it will not eat up your precious disk space. The commands below is taken from a sample where replication is used but is also applicable in general on a single MySQL database server.

Grant your user to flush logs and other privileges. (Login to MySQL from the console first)

1	grant RELOAD, SUPER, REPLICATION CLIENT on *.* to 'your_user'@'your_host';

To do a full backup from the command line:

1	mysqldump db_name -u user -pPassword --single-transaction --flush-logs --master-data=2 --delete-master-logs > filename.sql

And to restore from a binary log:

1	mysqlbinlog log_file | mysql -h server_name

Although late, allow me to greet you, HAPPY NEW YEAR!