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

This article mainly gives you a brief introduction to the detailed methods of using xtrabackup tools to achieve MySQL database backup. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope that using xtrabackup tools to achieve MySQL database backup detailed methods this article can bring you some practical help.

At present, there are two mainstream tools to implement physical hot backup: ibbackup and xtrabackup;ibbackup are commercial software that require authorization and are very expensive. Xtrabackup is more powerful than ibbackup, but it is open source. The so-called free version of InnoDB HotBackup.

Official link address: http://www.percona.com/software/percona-xtrabackup; you can download the source code compilation and installation, or you can download the appropriate RPM package or install it using yum or download the binary source package.

Xtrabackup provides two command-line tools:

Xtrabackup: dedicated to backing up data from InnoDB and XtraDB engines

Innobackupex: this is a perl script that invokes the xtrabackup command during execution so that you can back up both InnoDB and MyISAM engine objects.

Xtrabackup is a mysql database backup tool provided by percona with the following features:

(1) the backup process is fast and reliable

(2) the backup process will not interrupt the transaction in progress.

(3) it can save disk space and traffic based on compression and other functions.

(4) automatic backup verification

(5) the reduction speed is fast.

Second, install xtrabackup

1. Download xtrabackup

Wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/tarball/percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz

2. Decompression

Tar zxf percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz

3. Enter the decompressed directory

Cd percona-xtrabackup-2.4.4-Linux-x86_64/

4. Copy all the programs under bin to / usr/bin

Cp bin/* / usr/bin/

Note: there are two main tools in Xtrabackup:

Xtrabackup: a tool for hot backup of data in innodb and xtradb tables. It supports online hot backup. Innodb tables can be backed up without locking, but this tool cannot operate Myisam engine tables.

Innobackupex: a perl script that encapsulates xtrabackup and can handle both Innodb and Myisam, but requires a read lock when dealing with Myisam.

Because read locks are required to operate on Myisam, which blocks writes to online services, and Innodb does not have such restrictions, the greater the proportion of Innodb table types in the database, the more advantageous it is.

5. Install related plug-ins

Yum-y install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-TermReadKey.x86_64 perl-Digest-MD5

6. Download and install percona-toolkit

Wget https://www.percona.com/downloads/percona-toolkit/2.2.19/RPM/percona-toolkit-2.2.19-1.noarch.rpm rpm-vih percona-toolkit-2.2.19-1.noarch.rpm III. Perform xtrabackup full backup + binlog incremental backup

1. Create a backup directory

Mkdir-p / opt/mysqlbackup/ {full,inc}

Note: full: directory for complete storage; inc: directory for incremental backup

2. Open the bin-log log file

Edit the mysql configuration file to add the following:

Vim / etc/my.cnflog-bin = / etc/local/mysql/data/mysql-bin

Restart the mysql service:

Systemctl restart mysqld

3. Create data for testing

Mysql-uroot-p123456mysql > create database test;mysql > use test;mysql > create table tb1 (id int primary key auto_increment,name varchar (20)); mysql > insert into tb1 (name) values ('zhangsan'); mysql > insert into tb1 (name) values (' lisi')

4. Make a full backup

Syntax:

Innobackupex-user=DBUSER-password=DBUSERPASS / path/to/BACKUP-DIR/

Note: description of related options

-- user: specify the user name to connect to the database

-- password: specify the password to connect to the database

/ path/to/BACKUP-DIR/: backup file location

-- defaults-file: specify the configuration file of the database from which innobackupex can obtain information such as datadir.

-- database: specify the database to be backed up. The database specified here is only valid for MyISAM tables and is complete for InnoDB data (InnoDB data in all databases are backed up, not only the specified database is backed up, but also during recovery).

-- no-timestamp: prevents the command from automatically creating a directory named by time; in this way, the innobackupex command will create a BACKUP-DIR directory to store backup data

Make a full library backup:

Innobackupex-user=root-password=123456 / opt/mysqlbackup/full

The following prompt appears to indicate success

At the same time of the backup, the backup data creates a directory under the backup directory with the name of the current date and time to store the backup files:

Note: description of each document

Xtrabackup_checkpoints: backup type (such as full or incremental), backup status (such as whether it is already prepared status), and LSN (log serial number) scope information

Each InnoDB page (usually 16k in size) contains a log sequence number, LSN. LSN is the system version number of the entire database system, and the LSN associated with each page can indicate how the page has changed recently.

The binary log files currently in use on the xtrabackup_binlog_info:mysql CVM and the location of the binary log events up to the moment of backup.

Xtrabackup_binlog_pos_innodb: the current position of the binary log file and the binary log file for the InnoDB or XtraDB table.

Xtrabackup_binary: executable file of xtrabackup used in backup

Backup-my.cnf: configuration option information used by backup commands

Note: the user who backs up the database needs to have the appropriate permissions, and if you want to use a user with minimum privileges for backup, you can create such a user based on the following command:

Mysql > create user' bkpuser'@'localhost' identified by '123456 accountant MySQL > revoke all privileges,grant option from' bkpuser'@'localhost';mysql > grant reload,lock tables,replication client, process on *. * to 'bkpuser'@'localhost';mysql > flush privileges

5. Perform incremental backup

View the binlog log location (position) when viewing a full backup:

Simulate database modification:

Mysql > use testmysql > insert into tb1 (name) values ('wangwu'); mysql > insert into tb1 (name) values (' zhaoliu'); mysql > exit

Incremental backup binaries:

Mysqlbinlog-- start-position=1049 / usr/local/mysql/data/mysql-bin.000001 > / opt/mysqlbackup/inc/ `date-I`.sql

6. Simulate database corruption:

Rm-fr / usr/local/mysql/data/*

7. Restore a full backup:

(1) prepare (prepare) a full backup

In general, after the backup is complete, the data cannot be used for restore operations because the backed up data may contain transactions that have not yet been committed or transactions that have been committed but have not been synchronized to the data file. Therefore, the data file is still in an inconsistent state at this time. The main function of "preparation" is to make the data file consistent by rolling back the uncommitted transaction and synchronizing the committed transaction to the data file. At the end of the prepare process, the InnoDB table data has been rolled forward to the end of the entire backup, rather than to the point where the xtrabackup just started.

The-- apply-log option of the innobakupex command can be used to achieve the above functionality. Such as the following command:

Innobackupex-- apply-log / opt/mysqlbackup/full/2016-09-12 11-29-55 /

-- apply-log indicates that the log is applied to the data file, and then the data in the backup file is restored to the database after completion.

Note: / opt/mysqlbackup/full/2016-09-12 11-29-55 / the name of the directory where the backup file is located

If executed correctly, the last few lines of information output are usually as follows:

In the process of implementing preparation, innobackupex can also use the-- use-memory option to specify the amount of memory it can use, which is usually 100m by default. If enough memory is available, you can allocate more memory to the prepare process to improve its completion speed.

(2) restore the database:

The-- copy-back option of the innobackupex command is used to perform the restore operation, which performs the recovery process by copying all data-related files to the DATADIR directory of the mysql CVM. Innobackupex uses backup-my.cnf to get information about the DATADIR directory.

Innobackupex-- copy-back / opt/mysqlbackup/full/2016-09-12 11-29-55 /

The-- copy-back here indicates that data recovery is performed. After the data recovery is complete, the permissions of the relevant files need to be modified before the mysql database can start normally.

If executed correctly, the last lines of the output information are usually as follows:

Please make sure that "completed OK!" appears on the top line of the above information.

When the data is restored to the DATADIR directory, modify the restored data directory to be root, so you need to change the directory to mysql:

Chown-R mysql:mysql / usr/local/mysql/data/

Restart MySQL:

Systemctl restart mysqld

Verify the restored data:

8. Restore incremental backup

In order to prevent a large number of binary logs from being generated during restore, you can temporarily close the binary logs during restore and then restore:

Mysql > set sql_log_bin=0;mysql > source / opt/mysqlbackup/inc/2017-03-21.sql

Restart the binary log and verify the restore data:

Mysql > set sql_log_bin=1

Verify that the data is restored

Use xtrabackup tools to achieve MySQL database backup detailed methods to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.