Setting up Master-Slave replication using xtrabackup
Geschrieben von Harald Lapp in Datenbanken um 10:03
In a previous blog entry i described a method of how to setup master-slave replication with mysql. In steps #4 and #5 i used mysqldump and mysql-client for creating a database dump on the master and importing it on the slave. The problem with this approach is, that the database tables are locked, as long as the dump is running. For small databases this might not be a problem, but as data grows, the time to create a dump takes longer and longer. @work we apparently reached some critical level -- mysqldump ran hours and hours and would probably still run, if i had not stopped it already.
Luckily there are more suitable tools for large databases available. Innodb hot backup and xtrabackup. I've decided to go with xtrabackup, because it's open-source, free and actively developed. Innodb hot backup is closed-source and not for free .
The following steps are ment to replace steps #4 and #5 of my previous blog post.
1. building xtrabackup
For Linux i had to build xtrabackup from the source package, because there was no binary package available for my architecture -- it's very easy, though:
harald@master:~/xtrabackup-0.9.5rc$ automake -a -c ... harald@master:~/xtrabackup-0.9.5rc$ ./configure ... harald@master:~/xtrabackup-0.9.5rc$ make ... harald@master:~/xtrabackup-0.9.5rc$ cd innobase/xtrabackup harald@master:~/xtrabackup-0.9.5rc/innobase/xtrabackup$ make ... harald@master:~/xtrabackup-0.9.5rc/innobase/xtrabackup$ sudo cp \ innobackupex-1.5.1 /usr/local/bin harald@master:~/xtrabackup-0.9.5rc/innobase/xtrabackup$ sudo cp \ xtrabackup /usr/local/bin
Needless to say, that xtrabackup needs to be deployed on every database server.
2. creating a database dump
After successfully building and installing xtrabackup, taking a database dump is very easy:
root@master:~# innobackupex-1.5.1 --user=... --password=... \ --defaults-file=... --databases="..." .
The command innobackupex-1.5.1 takes the following parameters:
- username to use for database connection
- password to use for database connection
- --defaults-file this parameter is required, if the my.cnf configuration file is not located at /etc/my.cnf
- space-separated list of databases to backup
- destination directory to save dump to
Dumping the database with xtrabackup is incredible fast compared to mysqldump. With xtrabackup it's just a matter of minutes:
real 4m15.614s user 0m11.710s sys 0m14.960s
If xtrabackup was successful, it should have created a subdirectory which name is the current date/time, with all required files in it. The directory can now be copied to the slave:
root@master:~# scp -r 2010-03-02_15-02-24 firstname.lastname@example.org:~
3. Setting up the slave
The first thing to do on the slave is applying the binary log files to the database dump:
root@dbslave1:~# innobackupex-1.5.1 --apply-log 2010-03-02_15-02-24 ... 100302 14:29:56 innobackupex: innobackup completed OK!
Innobackupex will show the message above, if everything was OK. Next task is to copy the database dump to it's new location on the slave. innobackupex is doing everything for you:
root@dbslave1:~# innobackupex-1.5.1 --copy-back 2010-03-02_15-02-24 ... 100302 14:29:56 innobackupex: innobackup completed OK!
xtrabackup should now have copied the dump to the mysql data directory. It's a good idea to check the user and owner of the copied files and adjust them, when needed.
Last step is to start the replication. All information required to do so ist stored in the file xtrabackup_binlog_info:
root@dbslave1:~# cat 2010-03-02_15-02-24/xtrabackup_binlog_info mysql-bin.000331 54249842
With this information available the replication can be set up as described in step #6 of my previous blog post.