xtrabackup backup and recovery, working principle

XtraBackup Physical Backup

Percona XtraBackup is the world's only open source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases. With Percona XtraBackup, you can achieve the following benefits:

• Fast and reliable backups

• Uninterrupted transaction processing during backup

• Save disk space and network bandwidth

• Automatic backup verification

• Create a hot InnoDB backup without pausing the database

• Perform incremental backup of MySQL

• Compress compressed MySQL backup to another server

• Move tables between MySQL servers

• Easily create new MySQL master-slave

• Back up MySQL without adding load to the server

Percona XtraBackup performs MySQL hot backup, compressed and incremental MySQL backup for all versions of Percona Server, MySQL and MariaDB. Percona XtraBackup works with MySQL, MariaDB and Percona Server. It supports fully non-blocking backup of InnoDB, XtraDB and HailDB storage engines.

Percona XtraBackup working principle

Percona XtraBackup is based on InnoDB's crash recovery function. Copies InnoDB data files, resulting in internally inconsistent data; but then it performs crash recovery on the files, making them a consistent, usable database again.

This is because InnoDB maintains a redo log, also called a transaction log. This contains a record of every change to InnoDB data. When InnoDB starts, it checks the data files and transaction log and performs two steps. It applies committed transaction log entries to the data files and performs undo operations on any transactions that modified the data but did not commit.

Percona XtraBackup starts by recording the log sequence number (LSN) and then copies the data files. This takes a short time to complete, so if the files are changing, they reflect the state of the database at different points in time. At the same time, Percona XtraBackup runs a background process that monitors transaction log files and copies changes from them. Percona XtraBackup needs to do this constantly because transaction logs are written in a circular fashion and can be reused after a period of time. Percona XtraBackup requires transaction logging for every change since the start of execution of the data files.

Percona XtraBackup will use backup locks as a lightweight alternative. This feature is available in Percona Server 5.6+. Percona XtraBackup uses this to automatically copy non-InnoDB data to avoid blocking DML queries that modify InnoDB tables. When the server supports backup locks, xtrabackup will first copy the InnoDB data, run and copy the MyISAM tables and .frm files. Once completed, the backup of the files will begin. It will backup .frm, .MRG, .MYD, .MYI, .TRG, FLUSH TABLES WITH READ LOCKLOCK TABLES FOR BACKUP.par

NOTE

The lock is only for MyISAM and other non-InnoDB tables , only after Percona XtraBackup has completed backing up all InnoDB/XtraDB data and logs. Percona XtraBackup will use Backup Lock as a lightweight alternative. This feature is available in Percona Server 5.6+. Percona XtraBackup uses this to automatically copy non-InnoDB data to avoid blocking DML queries that modify InnoDB tables. After FLUSH TABLES WITH READ LOCK

, xtrabackup will use to block all operations that may change the binary log location or or report. Then xtrabackup will finish copying the REDO log files and getting the binary log coordinates. After this is completed xtrabackup will unlock the binary log and tables. LOCK BINLOG FOR BACKUPExec_Master_Log_PosExec_Gtid_SetSHOW MASTER/SLAVE STATUS

Finally, the binary log position will be printed to STDERR and xtrabackup will exit returning 0 if everything is OK.

It should be noted that STDERR's xtrabackup is not written in any file. You have to redirect it to a file, for example. xtrabackup OPTIONS2> backupout.log

It will also create the following files in the backed up directory.

During the preparation phase, Percona XtraBackup uses the replicated transaction log files to perform crash recovery on the replicated data files. Once this is done, the database can be restored and used.

The backed-up MyISAM and InnoDB tables will eventually be consistent with each other because, after the preparation (recovery) process, InnoDB's data is rolled forward to the point where the backup completed, rather than rolled back to the point where it started. This point in time matches the position taken so the MyISAM data and the prepared InnoDB data are in sync. FLUSH TABLES WITHREAD LOCK

In short, these tools allow you to perform operations such as streaming and incremental backups by copying data files, copying log files, and applying logs to various combinations of data.

Restore Backup

To restore a backup using xtrabackup, you can use the or option. xtrabackup –copy-backxtrabackup –move-back

xtrabackup will read from the my.cnf variables datadir, innodb_data_home_dir, innodb_data_file_path, innodb_log_group_home_dir, and check whether the directory exists.

This will copy the MyISAM tables, indexes, etc. (.FRM, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV, par and .opt files) first , the next step in InnoDB is tables and indexes, and finally log files. It will preserve the attributes of the files when copying them, mysql may need to change the ownership of the files to before starting the database server as they will be owned by the user who created the backup.

Alternatively, this option can be used to restore the backup. The only difference with this option is that instead of copying the file, it moves it to the destination location. Because this option deletes backup files, it must be used with caution. This feature is useful in situations where there is not enough free disk space to save both data files and their backup copies. xtrabackup –move-backxtrabackup –copy-back

Install XtraBackup

Install XtraBackup source

[root@ZHENGDA ~]# uname -r
3.10.0-327 .el7.x86_64

yum -y install

Installation

yum -y install percona-xtrabackup-24

Create a connection user and authorize

mysql> CREATE USER 'zhengda'@'localhost' IDENTIFIED BY 'goyun.org';
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'zhengda'@'localhost';
mysql> FLUSH PRIVILEGES;

Full backup

(1) xtrabackup can only back up innodb and xtradb tables of other engines, but cannot back up tables of the myisam engine;
(2) innobackupex is a Perl script that encapsulates xtrabackup and supports simultaneous backup of innodb and myisam, but a global read lock needs to be added when backing up myisam. Also, myisam does not support incremental backup.

$ xtrabackup --user=zhengda --password=goyun.org --backup \ 
  --target-dir=/data/backup  
$ innobackupex --user=zhengda --password=goyun.org /path/ to /backup/dir/ 
$ innobackupex --user=zhengda --password=goyun.org --stream=tar ./ | bzip2 -

Back up to /data/backup

备份所有库：$ innobackupex --defaults-file=/etc/my.cnf--user=zhengda \
           --password=goyun.org --socket=/tmp/mysql.sock /data/backup
           
备份两个库：$ innobackupex --databases="db1 db2"--defaults-file=/etc/my.cnf--user=zhengda \
            --password=goyun.org --socket=/tmp/mysql.sock /data/backup

Incremental backup

Note: The first incremental backup must be based on a full backup
–incremental /data/backup1 Specifies the target directory for incremental backup storage
–incremental-basedir=/data/backup Specifies the directory for full backup

$ innobackupex  --defaults-file=etc/my.cnf --user=zhengda --password=goyun.org \
--socket=/tmp/mysql.sock --incremental /data/backuup1 --incremental-basedir=/data/backup/#会根据当天时间生成个目录

To perform the second incremental backup, you need to specify the previous one Incremental backup directory
–incremental /data/backup2 Specify the incremental backup directory
–incremental-basedir=/data/backup1 Specify the last incremental backup directory

$ innobackupex --defaults-file=/etc/my.cnf --user=zhengda --password=goyun.org \
--socket=/tmp/mysql.sock --incremental /data/backup2 --incremental-basedir=/data/backup1/(....)

Restore backup

–apply-log option to prepare to restore backup
–use-memory=8G Set the memory used when preparing to restore data, which can improve the time spent on preparation

$ innobackupex --apply-log --use-memory=8G /data/backup
#还原如下
$ innobackupex  --defaults-file=/etc/my.cnf --user= --password= --copy-back /data/backup
#或
$ xtrabackup --user=zhengda --password=goyun.org --copy-back --target-dir=/data/backup

Use Mysqldump for logic Backup

mysqldump is an important MySQL backup tool and is quite powerful. Backup parameters and recovery strategies need to be studied carefully.

Backup database:

Backup a single database or a specified table in a single database:

mysqldump [OPTIONS] database [tb1] [tb2]…

Back up multiple databases:

mysqldump [OPTIONS] –databases [OPTIONS] DB1 [DB2 DB3…]

Back up all databases:

mysqldump [OPTIONS] –all-databases [OPTIONS]

Add this option to not lock tables when backing up

--single-transaction

Case: Back up all libraries locally

mysqldump -u root -p密码 --all-databases > ./all.sql

Case: Restore all libraries locally

mysql -u root -p密码  < ./all.sql

Case: Back up the two local db1 and db2 libraries

mysqldump -u root -p密码 --databases db1 db2 > ./db1_2.sql

Case: Restore the two local db1 and db2 libraries

mysql -u root -p密码 db1 db2 < ./db1_2.sql

Case: Back up the yun library of host A to B on host B On the host, you need to create a yun library on host B

-C indicates that data transmission between hosts uses data compression

mysqldump --host=192.168.6.108(A) -u root -p密码 --opt yun | mysql --host=localhost(B) -u root -p密码 -C yun

Case: Execute on host B to back up all databases on host A Go to local

mysqldump --host=192.168.6.108（A） -u root -p密码 --all-databases > all.sql（B）

Case: restore execution on host B

The above is the detailed content of xtrabackup backup and recovery, working principle. For more information, please follow other related articles on the PHP Chinese website!