Mysql Enterprise Backup Restoring a Slave

How do you restore a MEB backup from the slave server for another slave server?


A client I work with has a master and two slave servers.  The slaves are called 'local slave' and 'DR slave'.  Both replicate from the master, so nothing special except, the database is large, currently 1.9TB.  The backup is taken from the 'local slave' to off load resources.  The 'DR slave' need rebuilding (after a crazy D/R test caused an issue regarding a bad shutdown procedure and an ongoing sort at the time of the shutdown). 

After I ftp'ed the nightly backup to the DR server (some 6,000 miles away). I ran a mysqlbackup copy-back command.  The problem was the information regarding the binary log and position to start replication was for the slave and this would mean changing the replication method from the master to the 'local slave', which would change the replication set up and was not desired by the customer.

The solution was simple, ensure you use --slave-info, as part of your slave backup command.  For example:
mysqlbackup -u root --backup-dir=/db/DB1/backup/ --slave-info --with-timestamp=true backup-and-apply-log

It produces the following in the output:
130905 13:15:58 mysqlbackup: INFO: Full Backup operation completed successfully.
130905 13:15:58 mysqlbackup: INFO: Backup created in directory '/db/DB1/backup/2013-09-05_13-14-01'
130905 13:15:58 mysqlbackup: INFO: MySQL binlog position: filename mysql-bin.000013, position 2115
130905 13:15:58 mysqlbackup: INFO: MySQL slave binlog position: master host 172.22.187.194, filename mysql-bin.000057, position 6426

-------------------------------------------------------------
   Parameters Summary
-------------------------------------------------------------
   Start LSN                  : 74539166208
   End LSN                    : 74539169950
-------------------------------------------------------------


It also creates a file in the meta directory of your backup:
/db/DB1/backup/2013-09-05_13-14-01/meta
[mysql@mysqldb2 DB1 /db/DB1/backup/2013-09-05_13-14-01/meta ]$ ls -lrt
total 128
-rw-r--r-- 1 mysql mysql   5545 Sep  5 13:14 backup_create.xml
-rw-r--r-- 1 mysql mysql 108689 Sep  5 13:15 backup_content.xml
-rw-r--r-- 1 mysql mysql     73 Sep  5 13:16 ibbackup_slave_info
-rw-r--r-- 1 mysql mysql    264 Sep  5 13:16 backup_variables.txt
[mysql@mysqldb2 DB1 /db/DB1/backup/2013-09-05_13-14-01/meta ]$ cat ibbackup_slave_info
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000057', MASTER_LOG_POS=6426


You can run this option as part of your MEB backup on your master, except the information is unnecessary (at least I haven't thought of a good use for it).

So  the morale of the story is, if running a backup from your slave, use --slave-info.

For my particular issue I was stuffed.  After racking the few grey cells I have and eventually talking to MySQL Support (excellent response, as always) my fear was confirmed and the 1.9TB database needed another backup and another FTP and another restore.............

1 comment: