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 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.
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
[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!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
