Shulou(Shulou.com)06/01 Report--

This article mainly introduces the XtraBackup backup handout in mysql, which involves things, learned from the theoretical knowledge, there are many books, literature for your reference, from a practical point of view, accumulated years of practical experience can be shared with you.

Backup principle of XtraBackup in mysql

The official download address of xtrabackup is:

Http://www.percona.com/software/percona-xtrabackup .

Xtrabackup contains two main tools, xtrabackup and innobackupex, which differ as follows:

1 xtrabackup can only back up tables of innodb and xtradb engines, but table 2 of myisam engine cannot. Innobackupex is a Perl script that encapsulates xtrabackup and supports backing up innodb and myisam at the same time, but a global read lock is needed when backing up myisam. Also, myisam does not support incremental backups.

Schematic diagram of backup process of innobackupex tool

As shown in the picture, at the beginning of the backup

1 will start a xtrabackup_log background detection process to detect mysql redo changes in real time. As soon as new log writes are found in redo, the logs will be written to the log file xtrabackup_log. 2 copy innodb data files and system tablespace files idbdata1 to the corresponding place with the default timestamp as the backup directory 3 after replication Execute flush table with read lock operation 4 to copy .frm .myd .myi file 5 and get the location of binary log at this moment 6 unlock the table unlock tables7 stop the xtrabackup_log process full library recovery process this phase starts the innodb instance embedded in xtrabackup, plays back the xtrabackup log xtrabackup _ Log, applies committed transaction information changes to innodb data or tablespaces, and rolls back uncommitted transactions

Incremental backup

Incremental backup is mainly done by copying pages with changes in innodb (meaning that the LSN is greater than the LSN number in xtrabackup_checkpoints). Incremental backup is based on complete, the data of the first incremental backup is based on the last complete, and then each doubling is based on the previous doubling, and finally achieving a consistent doubling, in the process of doubling, is very similar to complete, the difference is that the second step

Recovery of incremental backup

Similar to full library recovery, it takes two steps.

1 the recovery of data files is divided into three parts: full incremental backup and xtrabackup_log

2 rollback of uncommitted transactions

Use cases for innobackupex

1 create a backup user

Complete the database

Use the following parameters for full library backup

View the corresponding generated file

Several files to pay attention to

Delete a database and restore the whole database

Shut down the database

Restore

The above corresponding directory is the directory created by innobackupex full backup.

Change permissions

[root@MASTER_03 tmp] # chown-R mysql.mysql / data/3306/data/

Start mysqld

[root@MASTER_03 tmp] # / etc/init.d/mysqld start

It is found that the data has been successfully restored.

Have everything in advance

Incremental backup

Insert the data at this time

Incremental backup 2

Recovery of incremental backup

The recovery of incremental backup requires three steps

1 restore full backup

2 restore incremental backup to full backup (add-redo-only parameter for the first incremental backup, and remove-- redo-only for the last incremental backup)

3 restore the overall full backup and roll back the uncommitted data

Simulation test

After the test, the recovery is relatively simple.

Read the above mysql XtraBackup backup handout introduction, I hope it can bring some help to everyone in practical application. Due to the limited space in this article, there will inevitably be deficiencies and areas that need to be supplemented. You can continue to pay attention to the industry information section and will update your industry news and knowledge regularly. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.