Recently I happen to work for a MySQL database restore, a pretty exciting task for DBA \ud83d\ude1b<\/p>\n
Well, backup server was already configured with Holland (backup framework) & Xtrabackup (Percona’s backup tool) which made our lives easier with steps to restore.<\/p>\n
Extracted backup command from backup server’s holland.log:<\/p>\n
\n\/usr\/bin\/innobackupex-1.5.1 --defaults-file=\/mysql_backups\/xtrabackup\/20141219_013002\/my.cnf --stream=tar --tmpdir=\/mysql_backups\/xtrabackup\/20141219_013002 --slave-info --no-timestamp \/mysql_backups\/xtrabackup\/20141219_013002 > \/mysql_backups\/xtrabackup\/20141219_013002\/backup.tar.gz 2 > \/mysql_backups\/xtrabackup\/20141219_013002\/xtrabackup.log<\/pre>\n<\/blockquote>\n
\nThe task here is as simple as:
\n– Ship & extract the backup to destination,
\n– Apply-logs (innobackupex –defaults-file=\/path\/my.cnf –apply-log \/path\/datadir\/)
\n– Start database (\/etc\/init.d\/mysqld start),
\n– Setup replication (CHANGE MASTER TO) .<\/p>\nMySQL cameup clean after following all the steps and now it’s time to setup replication.<\/p>\n
The option –slave-info to innobackupex script stores master’s binlog coordinates in a file, xtrabackup_slave_info, which can be used to setup the new server as slave.<\/p>\n
The co-ordinates can also be found in the xtrabackup log shown as follows:<\/p>\n
\ninnobackupex-1.5.1: Backup created in directory '\/mysql_backups\/xtrabackup\/20141219_013002'\r\ninnobackupex-1.5.1: MySQL binlog position: filename 'mysql-bin.002573', position 53014559, GTID of the last change '0-10110111-42499073'\r\ninnobackupex-1.5.1: MySQL slave binlog position: master host '10.1.101.11', gtid_slave_pos 0-10110111-42499073\r\n141219 04:55:16\u00a0 innobackupex-1.5.1: Connection to database server closed\r\ninnobackupex-1.5.1: You must use -i (--ignore-zeros) option for extraction of the tar stream.\r\n141219 04:55:16\u00a0 innobackupex-1.5.1: completed OK!<\/em><\/pre>\n<\/blockquote>\nI noted that the backup xtrabackup_slave_info file binlog co-ordinate did not match that of master!! And we found that the binlog co-ordinates we had were of slave itself (show master status) and not of master (relay_Master_Log_File & exec_master_pos). This appears a bug in xtrabackup<\/a>.<\/p>\n
Luckily the xtrabackup log did note the GTID positions and that helped to restore the slave as follows.<\/p>\n
Verified the present gtid_slave_pos matches that of log and started slave. (Check the References for MariaDB documentation page about restoring slave)<\/p>\n
\nMariaDB [(none)]> show global variables like 'gtid_slave_pos';\r\n+----------------+---------------------+\r\n| Variable_name| Value |\r\n+----------------+---------------------+\r\n| gtid_slave_pos | 0-10110111-42499073 |\r\n+----------------+---------------------+\r\n1 row in set (0.00 sec)\r\n\r\nMariaDB [(none)]> show global variables like 'gtid_current_pos';\r\n+------------------+---------------------+\r\n| Variable_name| Value |\r\n+------------------+---------------------+\r\n| gtid_current_pos | 0-10110111-42499073 |\r\n+------------------+---------------------+\r\n\r\nMariaDB [(none)]>CHANGE MASTER TO master_use_gtid=current_pos; START SLAVE;<\/pre>\n<\/blockquote>\nWe’ll look forward to the bug fix to that reports correct (master’s) binary log positions in xtrabackup_slave_info file.<\/p>\n
References:<\/b><\/p>\n
\n
- https:\/\/bugs.launchpad.net\/bugs\/1404484<\/li>\n
- https:\/\/mariadb.com\/kb\/en\/mariadb\/documentation\/replication\/standard-replication\/global-transaction-id\/#setting-up-from-backup<\/li>\n
- http:\/\/www.percona.com\/doc\/percona-xtrabackup\/2.2\/innobackupex\/innobackupex_option_reference.html#cmdoption-innobackupex–slave-info<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"Restoring MariaDB (MySQL) slave using Xtrabackup & GTID.\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,377],"tags":[370,380,379,427,369],"class_list":["post-1988","post","type-post","status-publish","format-standard","category-mysql","category-mysql-articles","tag-bug","tag-gtid-replication","tag-mariadb","tag-mysql","tag-xtrabackup"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t