Slave Failure

If a slave has failed, we perform a restore-from-backups from the master.

In this example, we use mysql-104 as the example slave to recover, and mysql-102 as the example master.

The backup-machine listed below is linux.cscf.

The ${MYSQL_DATA_DIR} is /data/mysql

0.0 On the slave to recover, stop mysql.

root@mysql-104:~# service mysql stop

0.1 Verify MySQL is actually stopped. The following should return nothing.

root@mysql-104:~# ps -ef | grep '[m]ysql'

1. Clean up default databases/tables created by MySQL package installation.

root@mysql-104:~# rm -rf ${MYSQL_DATA_DIR}; mkdir -p ${MYSQL_DATA_DIR}

2. Create local space for storing backups in /tmp.

root@mysql-104:~# mkdir -p /tmp/mysql-restore
root@mysql-104:~# chmod 700 /tmp/mysql-restore

3. Verify that last backup of the node configuration data is suitable. Simple sanity checks include checking the filesize, creation time etc.

root@backup-machine:/backup/mysql/mysql-104.cs# ls -ld | grep 'etcmysql.tar' | tail -n 1

4. Copy remote slave configuration backup into local directory.

root@backup-machine:/backup/mysql/mysql-104.cs# rsync -a $(ls | grep 'etcmysql.tar' | tail -n 1) root@mysql-104:/tmp/mysql-restore/

5. Verify that last backup of the CURRENT MASTER NODE is the backup you want. Simple sanity checks include checking the filesize, creation time etc.

root@backup-machine:/backup/mysql/mysql-master# ls -ld *-innobackupex | tail -n 1

6. Copy remote master backup into local directory.

root@backup-machine:/backup/mysql/mysql-master# rsync -a $(ls | grep 'innobackupex' | tail -n 1) root@mysql-104:/tmp/mysql-restore/

7. Create temporary holding directory for configuration data.

root@mysql-104:~# mkdir /tmp/mysql-restore/config
root@mysql-104:~# chmod 700 /tmp/mysql-restore/config

8. Unpack slave configuration data.

root@mysql-104:~# tar -xf /tmp/mysql-restore/*-etcmysql.tar -C /tmp/mysql-restore/config/

9. Remove any existing MySQL configuration on the system.

root@mysql-104:~# rm -rf /etc/mysql/{.*,*}

10. Restore slave configuration data.

root@mysql-104:~# mv /tmp/mysql-restore/config/etc/mysql/{.*,*} /etc/mysql/

11. Prepare percona innobackup. This is "destructive" and will modify the backups you've copied over.

root@mysql-104:~# innobackupex --use-memory=2G --apply-log /tmp/mysql-restore/*-innobackupex

Should look more or less as follows:

root@mysql-104:/tmp/mysql-restore/config/etc/mysql# innobackupex --use-memory=2G --apply-log /tmp/mysql-restore/*-innobackupex
160315 19:09:17 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".

innobackupex version 2.3.3 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 525ca7d)
xtrabackup: cd to /tmp/mysql-restore/mysql-102-1458034021-innobackupex
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(320319246)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 2147483648 bytes for buffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.8
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 2.0G
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence numbers 319143451 and 319143451 in ibdata files do not match the log sequence number 320319246 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages
InnoDB: from the doublewrite buffer...
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.24 started; log sequence number 320319246
xtrabackup: Last MySQL binlog file position 838665, file name /var/log/mysql/binlog.000018

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 320320828
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.8
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 2.0G
InnoDB: Completed initialization of buffer pool
InnoDB: Setting log file ./ib_logfile101 size to 48 MB
InnoDB: Setting log file ./ib_logfile1 size to 48 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=320320828
InnoDB: Highest supported file format is Barracuda.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.24 started; log sequence number 320321036
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 320326703
160315 19:09:21 completed OK!
root@mysql-104:/tmp/mysql-restore/config/etc/mysql#

12. Restore the prepared backup.

root@mysql-104:~# innobackupex --copy-back /tmp/mysql-restore/*-innobackupex

Should start and end as follows:

root@mysql-104:/etc/mysql# innobackupex --copy-back /tmp/mysql-restore/*-innobackupex
160315 19:11:16 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex
           prints "completed OK!".

innobackupex version 2.3.3 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 525ca7d)
160315 19:11:16 [01] Copying ib_logfile0 to /data/mysql/ib_logfile0
160315 19:11:16 [01]        ...done
160315 19:11:16 [01] Copying ib_logfile1 to /data/mysql/ib_logfile1
160315 19:11:16 [01]        ...done
160315 19:11:16 [01] Copying ibdata1 to /data/mysql/ibdata1
160315 19:11:17 [01]        ...done
160315 19:11:17 [01] Copying ./blah/db.opt to /data/mysql/blah/db.opt
160315 19:11:17 [01]        ...done
160315 19:11:17 [01] Copying ./blah/tags.MYD to /data/mysql/blah/tags.MYD
160315 19:11:17 [01]        ...done
160315 19:11:17 [01] Copying ./blah/tags.MYI to /data/mysql/blah/tags.MYI
160315 19:11:17 [01]        ...done
160315 19:11:17 [01] Copying ./blah/tags.frm to /data/mysql/blah/tags.frm 
[...]
160315 19:11:17 [01] Copying ./sbtest/db.opt to /data/mysql/sbtest/db.opt
160315 19:11:17 [01]        ...done
160315 19:11:17 [01] Copying ./sbtest/sbtest1.frm to /data/mysql/sbtest/sbtest1.frm
160315 19:11:17 [01]        ...done
160315 19:11:17 [01] Copying ./xtrabackup_info to /data/mysql/xtrabackup_info
160315 19:11:17 [01]        ...done
160315 19:11:17 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/mysql/xtrabackup_binlog_pos_innodb
160315 19:11:17 [01]        ...done
160315 19:11:17 completed OK!
root@mysql-104:/etc/mysql# 

13. Fix database file ownership and permissions.

root@mysql-104:~# chown -R mysql:mysql ${MYSQL_DATA_DIR}
root@mysql-104:~# chmod 700 ${MYSQL_DATA_DIR}

14. Remove spurious innodb logfiles.

root@mysql-104:~# rm ${MYSQL_DATA_DIR}/ib_logfile*

15. Restart MySQL service

root@mysql-104:~# service mysql restart

16. Retrieve master log position to start slaving from.

root@mysql-104:~# cat /tmp/mysql-restore/*-innobackupex/xtrabackup_binlog_info

17. Issue CHANGE MASTER command.

root@mysql-104:~# mysql -e 'CHANGE MASTER TO MASTER_HOST="${CURRENT_MASTER_FQDN}",MASTER_USER="mysql-104_slave", MASTER_PASSWORD="the_password", MASTER_LOG_FILE="${LOG_FILE_NAME}", MASTER_LOG_POS=${LOG_FILE_POS;'

18. Begin slaving.

root@mysql-104:~# mysql -e 'SLAVE START;'

19. Verify slaving.

root@mysql-104:~# mysql -e 'SHOW SLAVE STATUS\G;'

...should return: Slave_IO_Running: Yes, Slave_SQL_Running: Yes, Last_Error: (blank)

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql-102.cs.uwaterloo.ca
                  Master_User: mysql-104_slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000024
          Read_Master_Log_Pos: 49711
               Relay_Log_File: mysqld-relay-bin.000009
                Relay_Log_Pos: 49854
        Relay_Master_Log_File: binlog.000024
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 49711
              Relay_Log_Space: 50154
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 102
1 row in set (0.00 sec)

If Seconds_Behind_Master: is not 0, wait a few seconds and re-run the command until it is 0, to ensure that it catches up with the master properly.