"MySQL" "Backup" using Xtrabackup to physically back up the MySQL process

Brief introduction:

? Xtrabackup contains two main tools, namely: Xtrabackup and Innobackupex

? Xtrabackup: You can only back up tables of the InnoDB and XTRADB two transaction engines and do not support backing up tables for non-transactional engines.

? Innobackupex: Encapsulates the Xtrabackup perl script, supports non-transactional table backups under global read locks, and supports transaction tables without global read locks.

Installation:

? Recommended to install Percona company source and then Yum install

yum -y install https://www.percona.com/redir/downloads/percona-release/redhat/percona-release-0.1-4.noarch.rpmyum -y install percona-xtrabackup-24

Innobackupex Backup process:

? 1. The backup starts with a background detection process, real-time monitoring of changes in the redo, one but found in the redo in the new log write, immediately log into its background log file (Xtrabackup_log),

? 2. Copy the data file of InnoDB table, system tablespace file Ibdata1, go to next step after completion,

? 3. Perform a global read lock, lock all tables (transactional, non-transactional), copy files of non-transactional tables (. frm. MYI. MYD), get binlog position, unlock all tables

? 4. Stop Xtrabackup_log, end backup

Stage	explain
Generate Xtrabackup log files	The software itself opens a log to save redo
Copy InnoDB related files (. ibd table data files, ibdata1 rollback space)
FTWRL, Global read lock	Non-transactional tables do not support consistency with transactions and require read locks
Copy INNODB table structure file frm, all files of non-transactional tables
Get Binlog Location	Use this location as the global location for the backup
Releasing a global read lock	Backup Preliminary End
Stop the Xtrabackup log work.	End of backup

? Backup statement:

innobackupex --defaults-file=/etc/my.cnf --user=bak --password=bak123 --stream=xbstream .|gzip cat ->xtrabak.xb.gz

?

Innobackupex Recovery process:

? Start the InnoDB instance that comes with the Xtrabackup package, and replay the Xtrabackup_log collected during the Xtrabackup process:

? According to Binlog, the replay Binlog has been registered, but the redo has not yet been committed but has been prepare in the transaction.

? According to Binlog, rollback is not registered in Binlog, but the transaction already prepare in redo

? Copy the applied files back to the data directory where you want to restore the instance, giving the MySQL user the right to do so.

#解压gunzip all.xb.gz -c|xbstream -x -C /data/full#应用xtrabackup_loginnobackupex --apply-log --use-memory=1G /data/full#拷回预备恢复的文件，也可以用手工拷贝代替innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /data/full#将拷回的文件所有权赋给mysql用户chown -R mysql.mysql /data/mysql/3306#启动数据库mysqld --defaults-file=/etc/my.cnf

Report:

1. Reasons for using Xbstream instead of tar:

? In the case of traditional replication, from backing up from the library, you need to obtain replication information from the library, you can use the--slave-info parameter, so that the replication information is appended to the backup file package, but it always truncates a portion of the copied information when used with tar. Conversion to Xbstream circulation is no problem/In addition, if the use of Gtid, regardless of the acquisition of Binlog file and pos, tar or xbstream are acceptable.

2. Reasons for not recommending the use of the add-on:

? 1. Add a tedious recovery step (need to apply redo before the last add-on, apply all xtrabackup_log to the last one, and trouble with the copy back)

? 2. It is not possible to restore data at any point in time between the binlogserver compared to a fully-prepared plus.

3. Overall, Xtrabackup is still a physical backup, supplemented by a logical backup to complete the data consistency of the backup method.

?

?

"MySQL" "Backup" using Xtrabackup to physically back up the MySQL process