Xtrabackup
What is xtrabackup?
Percona XtraBackup is an open-source hot backup utility for MySQL - based servers that doesn’t lock your database during the backup. It is designed to make backups a seamless procedure without disrupting the performance of the server in a production environment.
Default backup lock is enabled in Percona version MySQL 5.6.16+. It is a lightweight alternative to FLUSH TABLES WITH READ LOCK for both physical and logical backups. Three new statements are now available: LOCK TABLES FOR BACKUP, LOCK BINLOG FOR BACKUP and UNLOCK BINLOG. Please find detail in this link Backup Locks.
Special cautions
Backup folder must be dedicated for backup only.
In the xtrabackup folders, there should be no any other files or any folders. Because the file or folder time stamps could impact next incremental backup. The reason is
if ($option_incremental_basedir ne '') {$incremental_basedir = $option_incremental_basedir;} else {my @dirs = `ls -t $backup_root`;my $inc_dir = $dirs[0];chomp($inc_dir);$incremental_basedir = File::Spec->catfile($backup_root, $inc_dir);
ls -t is being used to determinate the latest backup folder based modification time. The oldest folder time stamp could be changed due to mistake, in our case is the purging job starts to work and it does not complete since the folder is big. In this case, this purging folder(the oldest one) could be considered as the latest backup folder and xtrabackup picks up the wrong folder.
information_schema is not backed up.
This means tables create time stamp is lost. The time stamp for all tables are based on when they are restored.
Make backup
Make the backup from master
sudo innobackupex --user=root --password=`sudo keydbgetkey mysqlroot`
The replication coordinates info is recorded in the file /home/y/var/mysql/data/xtrabackup_binlog_pos_innodb
ROW based replication events format allows to maintain only persistent tables state. Thus master log file and position will be the same even if there are changes in temporary tables, https://dev.mysql.com/doc/refman/5.6/en/replication-rbr-usage.html
Make the backup from slaves (BCP master is considered as slave)
We are using row based replication, so there is no temporary table opening issue in BCP master.
sudo innobackupex --user=root --password=`sudo keydbgetkey mysqlroot` --slave-info
If you have a big alter table query running, xtrabackup will wait until "alter query" finishes.
mysql> show processlist;+------+-----------------+-----------+------+---------+--------+----------------------------------+------------------------------------------------+-----------+---------------+| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |+------+-----------------+-----------+------+---------+--------+----------------------------------+------------------------------------------------+-----------+---------------+| 1 | event_scheduler | localhost | NULL | Daemon | 260586 | Waiting on empty queue | NULL | 0 | 0 || 157 | root | localhost | NULL | Sleep | 1 | | NULL | 1 | 4 || 192 | system user | | NULL | Connect | 258744 | Waiting for master to send event | NULL | 0 | 0 || 193 | system user | | yax | Connect | 5923 | altering table | alter table creative_content drop column stacy | 0 | 0 || 5389 | root | localhost | NULL | Query | 4 | Waiting for backup lock | LOCK TABLES FOR BACKUP | 0 | 0 || 5390 | root | localhost | NULL | Query | 0 | init | show processlist | 0 | 0 |+------+-----------------+-----------+------+---------+--------+----------------------------------+------------------------------------------------+-----------+---------------+6 rows in set (0.00 sec)
Make the backup from Multi-Threaded Slave (MTS) without GTID enabled
--slave-info can not be added.
sudo innobackupex --user=root --password=`sudo keydbgetkey mysqlroot` --slave-infoinnobackupex: Error: The --slave-info option requires GTID enabled for a multi-threaded slave. at /home/y/bin/innobackupex line 1928.
--safe-slave-backup is optional for MTS without GTID enabled. It leads different mechanism to perform point-time-recovery.
sudo innobackupex --user=root --password=`sudo keydbgetkey mysqlroot` --safe-slave-backuporsudo innobackupex --user=root --password=`sudo keydbgetkey mysqlroot`
Make the backup from slaves without lock
Use this option to disable backup lock while the backup is taking. Use it only if ALL your tables are InnoDB and you DO NOT CARE about the binary log position of the backup.
If your backup is used for point-time-recovery, no-lock can not be used at all.
sudo innobackupex --user=root --password=`sudo keydbgetkey mysqlroot` --no-lock
Restore backups
Backup option : --compress --nolock
- The database backup is compressed. It could save 2/3 space.
- no-lock means the xtrabackup does not issue "FLUSH TABLES WITH READ LOCK" to temporarily lock tables to get consistent *frm files. This means the backup could be inconsistent if some DDL happen during the *frm is being backed up.
Recovery: /mnt/backup/saca/2016-04-07_05-05-01 is full backup 497 GB (compressed)
/mnt/backup/saca/2016-04-08_05-00-00 is incremental backup 4.7 GB (compressed)
Prepare a empty server, and installed all packages that the original database server owns.
1. copy two backup directories from the backup filer to local server
cp -rp /mnt/backup/saca/2016-04-07_05-05-01 /home/syuan/backupcp -rp /mnt/backup/saca/2016-04-08_05-00-00 /home/syuan/backup
2. Decompress the backups first
sudo innobackupex /home/syuan/backup/2016-04-07_05-05-01 --use-memory=32G --decompress --parallel=16sudo innobackupex /home/syuan/backup/2016-04-08_05-00-00 --use-memory=8G --decompress --parallel=8
3. Apply logs into backups
sudo innobackupex --apply-log --redo-only /home/syuan/backup/2016-04-07_05-05-01 --use-memory=32G --parallel=16sudo innobackupex --apply-log --redo-only /home/syuan/backup/2016-04-07_05-05-01 --incremental-dir=/home/syuan/backup/2016-04-08_05-00-00 --use-memory=8G --parallel=8sudo innobackupex --apply-log /home/syuan/backup/2016-04-07_05-05-01 --use-memory=32G --parallel=16
4. Move the backup into right database directory
sudo innobackupex --move-back /home/syuan/backup/2016-04-07_05-05-01
5. Start database
sudo chown -R mysql:mysql /home/y/var/mysqlyinst start mysql_server
6. Post work, change root password
Go to original database server, get the root password by sudo /home/y/bin/keydbgetkey mysqlroot
$ sudo /home/y/bin/keydbgetkey mysqlrootPassword:A*********.erC8xQDNnPkxSFI9w--bash-4.1$
Login to database by
mysql -u root -ppaste the password here\! sudo /home/y/bin/keydbgetkey mysqlrootset sql_log_bin=0;update mysql.user set password=password('abovepassword') where user='root';flush privileges;
MySQL Point-Time-Recovery
Backup option : --compress --compress-threads=8 --slave-info
The backup is taken in a slave.
slave-info: It prints the binary log position and name of the master server.
The backups
The full backup /mnt/db_backup_nfs/trf_prod/trf_prod_backup/2016-04-12_17-45-01 is 360 GB
Incremental backup /mnt/db_backup_nfs/trf_prod/trf_prod_backup/2016-04-13_16-22-51 is 8.3 GB
Incremental backup /mnt/db_backup_nfs/trf_prod/trf_prod_backup/2016-04-14_09-05-02 is 6.9 GB
Prepare a empty server (bf1-gboothighdb-003.mysql.bf1.yahoo.com), and installed all packages that the original database server owns.
1. Copy three backup directories from the backup filer to local server
cp -rp /mnt/db_backup_nfs/trf_prod/trf_prod_backup/2016-04-12_17-45-01 /home/syuan/backupcp -rp /mnt/db_backup_nfs/trf_prod/trf_prod_backup/2016-04-13_16-22-51 /home/syuan/backupcp -rp /mnt/db_backup_nfs/trf_prod/trf_prod_backup/2016-04-14_09-05-02 /home/syuan/backup
2. Decompress the backups first
sudo innobackupex /home/syuan/backup/2016-04-12_17-45-01 --use-memory=32G --decompress --parallel=16sudo innobackupex /home/syuan/backup/2016-04-13_16-22-51 --use-memory=32G --decompress --parallel=16sudo innobackupex /home/syuan/backup/2016-04-14_09-05-02 --use-memory=32G --decompress --parallel=16
3. Stop MySQL if there is mysqld running and remove all related database files.
yinst stop mysql_serversudo rm -rf /home/y/var/mysql/data/*sudo rm -rf /home/y/var/mysql/ibdata/*sudo rm -rf /home/y/var/mysql/iblog/*
4. Apply the logs.
sudo innobackupex --apply-log --redo-only /home/syuan/backup/2016-04-12_17-45-01 --use-memory=32G --parallel=16sudo innobackupex --apply-log --redo-only /home/syuan/backup/2016-04-12_17-45-01 --incremental-dir=/home/syuan/backup/2016-04-13_16-22-51 --use-memory=32G --parallel=16sudo innobackupex --apply-log --redo-only /home/syuan/backup/2016-04-12_17-45-01 --incremental-dir=/home/syuan/backup/2016-04-14_09-05-02 --use-memory=32G --parallel=16sudo innobackupex --apply-log /home/syuan/backup/2016-04-12_17-45-01 --use-memory=32G --parallel=16
5. Move data files back to database folder and start MySQL
sudo innobackupex --move-back /home/syuan/backup/2016-04-12_17-45-01sudo chown -R mysql:mysql /home/y/var/mysqlyinst start mysql_server
6. Post work, change root password
Go to original database server, get the root password by sudo /home/y/bin/keydbgetkey mysqlroot
$ sudo /home/y/bin/keydbgetkey mysqlrootPassword:A**************w--bash-4.1$Login to database:mysql -u root -ppaste the password here\! sudo /home/y/bin/keydbgetkey mysqlrootset sql_log_bin=0;update mysql.user set password=password('abovepassword') where user='root';flush privileges;
7. Get its master log file and its position
The xtrabackup_slave_info in /home/y/var/mysql/data records the master log file name and its position since slave-info option is used in the xtrabackup process.
# cat /home/y/var/mysql/data/xtrabackup_slave_infoCHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.004464', MASTER_LOG_POS=49402644
8. Set up the replication if the binary logs are still in the master host.
CHANGE MASTER TOMASTER_HOST='master-host-name.yahoo.com',MASTER_USER='replicant',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysqld-bin.004464',MASTER_LOG_POS=49402644;START SLAVE;SHOW SLAVE STATUS\G
9. Set up the replication if the binary logs are NOT in the master host any more.
1. Get binary logs from the backup filer and unzip them.
2. Apply all binary logs into database until the last one
mysqlbinlog --start-position=49402644 mysqld-bin.004464 mysqld-bin.004465 mysqld-bin.004466 .......| mysql -u root -p
3. For example, the last one mysqld-bin.0044670 is in the master host, so connect to it.
CHANGE MASTER TOMASTER_HOST='master-host-name.yahoo.com',MASTER_USER='replicant',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysqld-bin.004470',MASTER_LOG_POS=120; #The first pos is always 120START SLAVE;SHOW SLAVE STATUS\G
10. Handling events
It is fine to use slave backup to rebuild new slave. But if this slave is promoted to a master, we need to be careful!
All events are created in master and they are replicated to all slaves with status SLAVESIDE_DISABLED. If this slave becomes master, we need to enable events.
Alter event event_name enable;
Backup is taken from BCP master
backup option: slave-info
The reason is xtrabackup implemented backup lock instead of using flush table with read lock to cause the replication stop (safe-slave-backup) or no any changes happened during lock time period.
The replication coordinates info is in /home/y/var/mysql/data/xtrabackup_slave_info.
For example
[root@bf1-trfdb-001 data]# cat xtrabackup_slave_info
CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000002', MASTER_LOG_POS=122719339
In the target slave,
CHANGE MASTER TOMASTER_HOST='hostname',MASTER_USER='replicant',MASTER_PASSWORD='****',MASTER_LOG_FILE='mysqld-bin.000002',MASTER_LOG_POS=122719339;start slave;
Everything is fine.
Backup is taken from Multi-Threaded Slave without GTID enabled
Assume we have full backup( 2016-05-10_21-45-02 ) from MTS without GTID in the new server
1. Apply log
sudo innobackupex --apply-log --use-memory=32G /home/syuan/backup/2016-05-10_21-45-02
2. Move backup file to right database folders.
Assume mysql running in this server and we do not this old database
yinst stop mysql_serversudo rm -rf /home/y/var/mysql/data/*sudo rm -rf /home/y/var/mysql/ibdata/*sudo rm -rf /home/y/var/mysql/iblog/*sudo innobackupex --move-back /home/syuan/backup/2016-05-10_21-45-02
3. Start mysql instance
sudo chown -R mysql:mysql /home/y/var/mysqlyinst start mysql_server
Check mysqld.err to make sure everything is fine.
4. Reset root password.
The root password is backup server's root password. We need to change it to its own root password.
- Get password from the backup server
sudo /home/y/bin/keydbgetkey mysqlroot
Save the output
- Change the root password in the new server
Back to the new server, login to mysql
mysql -u root -p
Enter saved password.
mysql> \! sudo /home/y/bin/keydbgetkey mysqlroot -- Get new passwordmysql> set sql_log_bin=0;mysql> update mysql.user set password=password('new-pasword') where user='root';mysql> update mysql.user set host='server-host-name' where user='root' and host='backup-server-host';mysql> flush privileges;
5. Recover database
Since the backup is taken from MTS without GTID enabled, so we need to do some fix before it connects to the master.
Assume this backup is taken with --safe-slave-backup enabled.
- Get all slave worker info.
select ID,Relay_log_name,Relay_log_pos,Master_log_name,Master_log_pos from mysql.slave_worker_info;
mysql> select id,Relay_log_name,Relay_log_pos,Master_log_name,Master_log_pos from mysql.slave_worker_info;+----+------------------------------------------------+---------------+-------------------+----------------+| id | Relay_log_name | Relay_log_pos | Master_log_name | Master_log_pos |+----+------------------------------------------------+---------------+-------------------+----------------+| 1 | /home/y/var/mysql/data/mysqld-relay-bin.000114 | 649715248 | mysqld-bin.000036 | 649715084 || 2 | /home/y/var/mysql/data/mysqld-relay-bin.000114 | 651227473 | mysqld-bin.000036 | 651227309 || 3 | /home/y/var/mysql/data/mysqld-relay-bin.000114 | 651226653 | mysqld-bin.000036 | 651226489 |+----+------------------------------------------------+---------------+-------------------+----------------+3 rows in set (0.00 sec)
- Fill the holes in the replication server.
1. If the slave relay log file still exists in the backup server, such as mysqld-relay-bin.000114, copy/paste it into new server /home/y/var/mysql/data
Add mysqld-relay-bin.000114 in the mysqld-relay-bin.index.
2. If the slave relay file is purged, then we can get mysql binary log file from its master, such as mysqld-bin.000036,
1. copy/paste master binary log file to new server /home/y/var/mysql/data
2. rename this file to mysqld-relay-bin.000001, such as mv mysqld-bin.000036 mysqld-relay-bin.000001
3. modify the content of mysqld-relay-bin.index, make sure it only contains file mysqld-relay-bin.000001.
4. update mysql.slave_worker_info set relay_log_name='/home/y/var/mysql/data/mysqld-relay-bin.000001',relay_log_pos=master_log_pos;
mysql> update mysql.slave_worker_info set relay_log_name='/home/y/var/mysql/data/mysqld-relay-bin.000001',relay_log_pos=master_log_pos;Query OK, 3 rows affected (0.00 sec)Rows matched: 3 Changed: 3 Warnings: 0mysql> select ID,Relay_log_name,Relay_log_pos,Master_log_name,Master_log_pos from mysql.slave_worker_info;+----+------------------------------------------------+---------------+-------------------+----------------+| ID | Relay_log_name | Relay_log_pos | Master_log_name | Master_log_pos |+----+------------------------------------------------+---------------+-------------------+----------------+| 1 | /home/y/var/mysql/data/mysqld-relay-bin.000001 | 649715084 | mysqld-bin.000036 | 649715084 || 2 | /home/y/var/mysql/data/mysqld-relay-bin.000001 | 651227309 | mysqld-bin.000036 | 651227309 || 3 | /home/y/var/mysql/data/mysqld-relay-bin.000001 | 651226489 | mysqld-bin.000036 | 651226489 |+----+------------------------------------------------+---------------+-------------------+----------------+3 rows in set (0.00 sec)
5. Then start slave to fill all holes.
mysql> START SLAVE UNTIL SQL_AFTER_MTS_GAPS;Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> select id,Relay_log_name,Relay_log_pos,Master_log_name,Master_log_pos from mysql.slave_worker_info;+----+----------------+---------------+-----------------+----------------+| id | Relay_log_name | Relay_log_pos | Master_log_name | Master_log_pos |+----+----------------+---------------+-----------------+----------------+| 1 | | 0 | | 0 || 2 | | 0 | | 0 || 3 | | 0 | | 0 |+----+----------------+---------------+-----------------+----------------+3 rows in set (0.00 sec)
- Get the master log file name and its position
Show slave status\G
mysql> show slave status\G*************************** 1. row ***************************Slave_IO_State: Queueing master event to the relay logMaster_Host: gq1-changelogdb-002.mysql.ngd.gq1.yahoo.comMaster_User: replicantMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysqld-bin.000036Read_Master_Log_Pos: 778970368Relay_Log_File: mysqld-relay-bin.000001Relay_Log_Pos: 4Relay_Master_Log_File: mysqld-bin.000036Slave_IO_Running: Yes.........Exec_Master_Log_Pos: 651227309Relay_Log_Space: 127743517............Auto_Position: 01 row in set (0.00 sec)
- Connect to its master
stop slave;change master to master_log_file='mysqld-bin.000036',master_log_pos=651227309;start slave;
Assume this backup is taken without --safe-slave-backup enabled.
- Get all slave worker info.
select ID,Relay_log_name,Relay_log_pos,Master_log_name,Master_log_pos from mysql.slave_worker_info;
mysql> select ID,Relay_log_name,Relay_log_pos,Master_log_name,Master_log_pos from mysql.slave_worker_info;+----+------------------------------------------------+---------------+-------------------+----------------+| ID | Relay_log_name | Relay_log_pos | Master_log_name | Master_log_pos |+----+------------------------------------------------+---------------+-------------------+----------------+| 1 | /home/y/var/mysql/data/mysqld-relay-bin.000187 | 946879583 | mysqld-bin.000059 | 946879419 || 2 | /home/y/var/mysql/data/mysqld-relay-bin.000187 | 947931963 | mysqld-bin.000059 | 947931799 || 3 | /home/y/var/mysql/data/mysqld-relay-bin.000187 | 947515423 | mysqld-bin.000059 | 947515259 |+----+------------------------------------------------+---------------+-------------------+----------------+mysql> show slave status\G*************************** 1. row ***************************Slave_IO_State:Master_Host: gq1-changelogdb-002.mysql.ngd.gq1.yahoo.com..........Master_Log_File: mysqld-bin.000032Read_Master_Log_Pos: 64287Relay_Log_File: mysqld-relay-bin.000187Relay_Log_Pos: 947516238Relay_Master_Log_File: mysqld-bin.000059Slave_IO_Running: NoSlave_SQL_Running: No..........Exec_Master_Log_Pos: 947516074......Master_UUID: 9c95cb9f-0c9b-11e6-a512-d4ae5276557d......Auto_Position: 01 row in set (0.00 sec)
Copy mysqld-relay-bin.000187 relay log from the backup server to the new server.
In the new server
cp mysqld-relay-bin.000187 /home/y/var/mysql/data/.chown mysql:mysql mysqld-relay-bin.000187chmod 660 mysqld-relay-bin.000187Edit the file mysqld-relay-bin.indexadd /home/y/var/mysql/data/mysqld-relay-bin.000187 into mysqld-relay-bin.index
Make sure relay_log_recovery is set to 0 and slave_parallel_worker=3 (needs to be same as backup server setting)
yinst set mysql_config_multi.relay_log_recovery=0yinst set mysql_config_multi.slave_parallel_worker=3yinst restart mysql_server
Login to the MySQL, make sure fill all the holes.
start slave sql_thread until sql_after_mts_gaps;mysql> start slave sql_thread until sql_after_mts_gaps;Query OK, 0 rows affected, 1 warning (0.02 sec)mysql> select ID,Relay_log_name,Relay_log_pos,Master_log_name,Master_log_pos from mysql.slave_worker_info;+----+----------------+---------------+-----------------+----------------+| ID | Relay_log_name | Relay_log_pos | Master_log_name | Master_log_pos |+----+----------------+---------------+-----------------+----------------+| 1 | | 0 | | 0 || 2 | | 0 | | 0 || 3 | | 0 | | 0 |+----+----------------+---------------+-----------------+----------------+3 rows in set (0.00 sec)
Connect to its master, it will successfully connect to its master.
start slave;
show slave status\G
No comments:
Post a Comment