Guidance on how to back up mysql database using xtrabackup

xtrabackup is a mysql backup tool provided by percona. It is a physical backup tool that backs up database data by connecting to the database. For the innodb storage engine, it supports full backup and incremental backup. The myisam storage engine only supports incremental backup. Because xtrabackup's incremental backup of innodb is based on the LSN of the table space. The so-called LSN means: the table space is divided into multiple data blocks. Each data block has a corresponding sequence number. When the data block data changes, the sequence number is updated. . Full backup means backing up all data blocks, while incremental backup backs up based on the serial number of the last data block in the full backup to the latest data block serial number. Because myisam does not support table spaces, incremental backups cannot be performed.

Xtrabackup is a mysql database backup tool provided by percona. According to the official introduction, this is also the only open source tool in the world that can perform hot backup of innodb and xtradb databases.

Xtrabackup mainly contains two tools:

xtrabackup: It is a tool for hot backup of data in innodb and xtradb tables. It cannot back up other types of tables or data table structures;

innobackupex: It is a perl script that encapsulates xtrabackup. It can back up and restore MyISAM tables and data table structures.

The data backed up by xtrabackup includes:

• Table space

• Data file

• Binary log

• Transaction log
  ...

xtrabackup Before performing data recovery, you need to pay attention to the following points:

1. If there is an incremental backup, you need to merge the incremental backup and the full backup before restoring;

2. If it is a backup of a table that supports transactions, the transaction log rollback and commit operations need to be performed before recovery;

3. If it is multiple consecutive backups operation, you only need to submit the transaction log, no rollback is required, and the transaction can only be rolled back after all backup operations are synthesized;

This article demonstrates xtrabackup through detailed experimental steps How to use.
Experimental steps

1. A centos7 virtual machine, install the mariadb-5.5.52 version of the database, first import the hellodb test library through the sql script, which includes A students data table. This experiment is performed in the students table. The command is as follows:
   mysql -uroot < hellodb.sql

2. Install xtrabackup, you can Download the installation package from the official website, and then install it through yum. The download site is:, the installation process will not be demonstrated here;

3. The main command of xtrabackup is innobackupex, through innobackupex --help Or you can get help information from the man manual;

4. The database has been imported. At this time, you can perform the backup operation of the database. First perform the full backup and recovery of the database:

• xtrabackup_binlog_info: Record the contents of the binary log;

• xtrabackup_checkpoints: Record backup information such as backup type, starting and ending lsn numbers;

• xtrabackup_info: records more detailed backup information, such as whether to back up all libraries, whether to compress, the commands executed during backup, etc.;

• xtrabackup_logfile: records transactions The log is a binary file and cannot be viewed;

1. First make sure that the database has enabled binary logs, by show global variables like 'log_bin'; You can see whether it has been enabled by running the command show binary logs;You can see which binary log is currently being used;

2. Back up the data through xtrabackup Back up to the /data/backup directory. After the backup is completed, a folder named after the current time will be generated in the specified directory:
   innobackupex -u root /data/backup

3. The contents of the folder are as follows:
   

4. At this point, the backup is successful, and then the database recovery operation is demonstrated. Note: You need to stop the database service first before recovery:
   innobackupex --apply-log 2017-07-13_21-02-07/ #First commit and rollback the transaction log innobackupex --copy-back 2017- 07-13_21-02-07/ #Perform data recovery and the data will be automatically restored to the mysql data directory

5. After the recovery is successful, all the owners and owners of the recovered data The belonging groups are all root users, and they need to be changed to mysql users to log in to the database;

6. At this time, you can log in to the database and see that all data has been restored.

This time demonstrates incremental backup and recovery operations:

• In production environments, it is recommended that binary logs and data files be kept separately. On different disks, because binary files can be used for point-in-time recovery, less data can be lost as much as possible. If the binary files are also lost, a large amount of data may be lost;

• After the data recovery operation is completed, a full backup of the data must be performed to ensure data security;

1. to the database Insert several new pieces of information, and then perform an incremental backup of the database;

2. Use the following command to perform an incremental backup of the database. After the backup is successful, it will still be generated in the /data/backup directory. A new folder named with time:
   innobackupex -u root -p 1234567a --incremental --incremental-basedir=/data/backup/2017-07-13_21-07-23/ /data/ backup/

3. Then stop the database service, delete all data in the database directory, and perform data recovery operations. The steps are as follows:
   innobackupex --apply- log --redo-only 2017-07-13_22-16-19/ # Submit transactions for full backup, but do not roll back uncommitted transactions, because uncommitted transactions may be submitted in the next incremental backup innobackupex --apply- log --redo-only 2017-07-13_22-16-19/ --incremental-dir=2017-07-13_22-18-34/ #Perform incremental backup and full backup synthesis and transaction submission work innobackupex --apply -log 2017-07-13_22-16-19/ #Rollback operations for transactions that have not been completed in full backup innobackupex --copy-back 2017-07-13_22-16-19/ #At this time, full backup can be used to restore Data no longer needs incremental backup because the backup synthesis operation has been performed

4. It is also necessary to modify the owner and group of the restored data, and then log in to the database. Seeing the complete data, the experimental demonstration is completed.

   Notice:

The above is the detailed content of Guidance on how to back up mysql database using xtrabackup. For more information, please follow other related articles on the PHP Chinese website!