Tag: server admin

I’m trying this new idea for backing up our production MySQL servers. I have a backup server that basically runs rdiff-backup in the morning across several servers, but then does nothing for the rest of the day. It’s a pretty decent machine, so I’d like to utilize some resources. Databases are a tough cookie to backup. You can’t just copy the data files and then expect to copy them back over and have them just work. Especially if your databases have a mixture of InnoDB and MyISAM tables. In order to do a clean and accurate database backup, you need to stop the MySQL server, then copy the files, then restart MySQL.

If you have a live production MySQL server, stopping it to make a backup is not really an option. Fortunately there are a few options. Before you decide on which option to choose, here is a list of things to keep in mind when choosing a backup solution (from the MySQL gurus at Percona):

WHAT TO LOOK FOR

http://www.mysqlperformanceblog.com/2009/03/03/10-things-you-need-to-know-about-backup-solutions-for-mysql/

1. Does the backup require shutting down MySQL? If not, what is the impact on the running server? Blocking, I/O load, cache pollution, etc?
2. What technique is used for the backup? Is it mysqldump or a custom product that does something similar? Is it a filesystem copy?
3. Does the backup system understand that you cannot back up InnoDB by simply copying its files?
4. Does the backup use FLUSH TABLES, LOCK TABLES, or FLUSH TABLES WITH READ LOCK? These all interrupt processing.
5. What other effects are there on MySQL? I’ve seen systems that do a RESET MASTER, which immediately breaks replication. Are there any FLUSH commands at all, like FLUSH LOGS?
6. How does the system guarantee that you can perform point-in-time recovery?
7. How does the system guarantee consistency with the binary log, InnoDB logs, and replication?
8. Can you use the system to set up new MySQL replication slaves? How?
9. Does the system verify that the backup is restorable, e.g. does it run InnoDB recovery before declaring success?
10. Does anyone stand behind it with support, and guarantee working, recoverable backups? How strong is the legal guarantee of this and how much insurance do they have?

BACKUP PROGRAMS

There are a few MySQL backup products out there as well. I have used the first two on this list.

• AutoMySQLBackup script (handy for making a rotating incremental backup of your MySQL databases).
• Percona XtraBackup (nice way to ensure InnoDB and MyISAM tables are backed up properly, also does it incrementally)
• Zmanda (seems to be similar to Percona’s set up)

There’s probably a gazillion more out there. Google’s your friend in finding things you need.

HOW TO DO IT

There are several options. You could use a script above, or create a slave of the database (basically an exact copy of the production MySQL server – all changes that occur in the master are sent to the slave), or some combination. I’ll use a combination. I’ll replicate the production server onto the backup server, then run the incremental backups from there. This first part will walk through the process of setting up MySQL replication.

To give proper credit, here are several other how to’s I found helpful.

• http://www.badllama.com/content/mysql-backups-using-lvm-snapshots
• http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/
• http://www.softwareprojects.com/resources/programming/t-how-to-move-copy-a-live-mysql-database-and-what-1257.html
• http://www.clusterdb.com/mysql-cluster/get-mysql-replication-up-and-running-in-5-minutes/

On the master server

Step 1. Edit the my.cnf file to include at least the following (if needed) lines. Note: you will have to restart MySQL for these changes to take affect.

[mysqld]
server_id=1
innodb_flush_log_at_trx_commit=1
log_bin=mysql-bin.log
sync_binlog=1

Step 2. Make a MySQL user for the slave to use.

GRANT REPLICATION SLAVE ON *.* TO 'rep_user'@'localhost' IDENTIFIED BY 'passwordhere';

Step 3. Open a terminal session and log in to a MySQL prompt. Type the following command and hit enter.

FLUSH TABLES WITH READ LOCK

Step 4. After the FLUSH TABLES command finishes, run the following command and press enter.

SHOW MASTER STATUS

Step 5.  Make a copy of the database files.

lvcreate -L10G -s -n mysql-backup /dev/mapper/dbases

mkdir -p /mnt/mysql-backup

mount -o nouuid /dev/mapper/mysql-backup /mnt/mysql-backup

rsync -avz -e "ssh -c blowfish" /mnt/mysql-backup user@remote.host:/backup/location

rsync -avz -e "ssh -c blowfish" /dbases/mysql user@remote.host:/backup/location

mysqldump -uuser -p --all-databases > mysql-backup.sql

scp mysql-backup.sql user@remote.host:/backup/location

Step 6. Once the lvcreate command has finished, you can unlock the database.

UNLOCK TABLES

Step 7. If you haven’t already, copy the copy of the database files to the backup server.

On the slave server

Step 1. Edit the my.cnf file to include at least the following (if needed) lines. Note: you will have to restart MySQL for these changes to take affect.

[mysqld]
server_id=2

Step 2. Start MySQL and run the following commands in a mysql session to start the MySQL slave.

CHANGE MASTER TO
MASTER_HOST = "master.server.com",
MASTER_USER = "rep_user",
MASTER_PASSWORD = "passwordhere",
MASTER_LOG_FILE = "mysql-bin.log",
MASTER_LOG_POS = 2341234;

START SLAVE;