ÈÐ Wïgê† Ðð† ñåmê

If you have worked with mysql/mariadb/galera …. sooner or later you are going to have to do a restore.  Or if you are setting up a new master – slave, the size of the database can greatly affect how long it takes.  mysdqldump at one time was all that was available and for it for to be accurate, you need to lock tables which can affect production environments, do the dump in another shell, record the master log and position, transfer the files to another server, import the database, change master too ….. very very very time consuming.  So here is a way I have found that doesn’t lock the tables, doesn’t need to record the master log file or position, and does the dump and import in parallel greatly speeding things up.

I used this method after importing 3 different corrupt backups of a very large >250G database that was innodb.  The imports took hours.  I had a working non-corrupt master and the process outlined below was faster start to finish than just the old way of doing the import.  So here we go … and if you notice something that looks out of order, just remember this came from bash history with a little bit more details added for clarity.

1 make sure latest percona-xtrabackup is installed on each server
https://www.percona.com/downloads/XtraBackup/LATEST/

2 make sure you have the config added to /etc/my.cnf.d/server.cnf or similar at the bottom:

[xtrabackup]
parallel = 4
compress
compress-threads = 4

3 This creates a hot backup inside of /backups/mysql/  It will create another folder using date-time for the files (obviously change the path to match yours)
# innobackupex /backups/mysql/

4 you can now rsync to slave/new server
# rsync -avP src_ip:/backups/mysql/2013-01-01_14-02-12/ /backups/mysql/2013-01-01_14-02-12/

5 you must decompress the files on the slave/new server
xtrabackup –decompress –parallel –target-dir=/backups/mysql/2013-01-01_14-02-12/

6 it does not remove the qb files either so once the decompress is done, use this on the slave/new server
xtrabackup –remove-original –target-dir=/backups/mysql/2013-01-01_14-02-12/

7 you must also prepare the files on the slave/new server, which basically applies the logfiles and brings the backup up to date with the live server to the point where the backup was completed
xtrabackup –prepare –target-dir=/backups/mysql/2013-01-01_14-02-12/

8 on the slave, follow these steps
service mysqld stop
# make sure all of /var/lib/mysql is empty
mv /var/lib/mysql /var/lib/mysql.old
mkdir /var/lib/mysql
chown mysql:mysql /var/lib/mysql
mount -o remount /var/lib/mysql
# on the slave, we now use –move-back to restore the backup
xtrabackup –move-back –target-dir=/backups/mysql/2013-01-01_14-02-12/
chown -R mysql:mysql /var/lib/mysql
service mysqld start

9 find out the replication stuff needed to restart replication
cat /var/lib/mysql/xtrabackup_binlog_pos_innodb
./mysql-bin.001911 26158935

10 finish setting up the new slave with the required info

mysql> CHANGE MASTER TO MASTER_HOST=’ip.of.master’, MASTER_USER=’user’, MASTER_PASSWORD=’password’, MASTER_LOG_FILE=’mysql-bin.001911′, MASTER_LOG_POS=26158935;
mysql> START SLAVE;
mysql> show slave status \G;