Xtrabackup backup mysql database

Xtrabackup is provided by percona

Percona Xtrabackup is a free, open source and complete online backup tool that supports mysql, perconna server and mariadb.

Download the installation package from the official website https://www.percona.com/ and configure the epel source installation. You need to rely on the libev package.

[root@localhost ~] # wget [root@localhost ~] # vim / etc/yum.repos.d/ali-epel.repo [epel] name=ali-epelbaseurl=gpgcheck=0enabled=1 [root@localhost ~] # yum install percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm-y

The backup of Xtrabackup is achieved by log serial number (log sequence number).

Backup needs to create its own backup user and give the backup user some corresponding permissions (reload;lock tables;replication client;create tablespace;process;super;create;insert;select)

Create a backup recovery user:

MariaDB [(none)] > create user 'backup'@'localhost' identified by' xtrabackup123';Query OK, 0 rows affected (0 sec) MariaDB [(none)] > grant reload,lock tables,replication client,insert,select,process,super,create,create tablespace on *. * to 'backup'@'localhost';Query OK, 0 rows affected (0 sec) MariaDB [(none)] > flush privileges;Query OK, 0 rows affected (0 sec)

Xtrabackup only supports hot backup for InnoDB. View database information:

MariaDB [(none)] > show databases;+-+ | Database | +-+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +-+ 5 rows in set (0.00 sec) MariaDB [(none)] > use hellodb Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedMariaDB [hellodb] > show table status\ gateway * 1. Row * * Name: classes Engine: InnoDB Version: 10 Row_format: Compact Rows: 8 Avg_row_length: 2048 Data_length: 16384Max_data_length: 0 Index_length: 0 Data_free: 9437184 Auto_increment: 9 Create_time: 2016-07-05 08:16:44 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: * * 2. Row * * Name: coc Engine: InnoDB Version: 10 Row_format: Compact Rows: 14 Avg_row_length: 1170 Data_length: 16384Max_data_length: 0 Index_length: 0 Data_free: 9437184 Auto_increment: 15 Create_time: 2016-07-05 08:16 : 44 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: * * 3. Row * * Name: courses Engine: InnoDB Version: 10 Row_format: Compact Rows: 7 Avg_row_length: 2340 Data_length: 16384Max_data_length: 0 Index_length: 0 Data_free: 9437184 Auto_increment: 8 Create_time: 2016-07-05 08:16:44 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: * * 4. Row * * Name: scores Engine: InnoDB Version: 10 Row_format: Compact Rows: 15 Avg_row_length: 1092 Data_length: 16384Max_data_length: 0 Index_length: 0 Data_free: 9437184 Auto_increment: 16 Create_time: 2016-07-05 08:16:44 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: * * 5. Row * * Name: students Engine: InnoDB Version: 10 Row_format: Compact Rows: 25 Avg_row_length: 655 Data_length: 16384Max_data_length: 0 Index_length: 0 Data_free: 9437184 Auto_increment: 26 Create_time: 2016-07-05 08:16:44 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: * * 6. Row * * Name: teachers Engine: InnoDB Version: 10 Row_format: Compact Rows: 4 Avg_row_length: 4096 Data_length: 16384Max_data_length: 0 Index_length: 0 Data_free: 9437184 Auto_ Increment: 5 Create_time: 2016-07-05 08:16:44 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: * * 7. Row * * Name : toc Engine: InnoDB Version: 10 Row_format: Compact Rows: 0 Avg_row_length: 0 Data_length: 16384Max_data_length: 0 Index_length: 0 Data_free: 9437184 Auto_increment: 1 Create_time: 2016-07-05 08:16:44 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 7 rows in set (0.00 sec) # are all InnoDB You can do hot backup.

Complete:

[root@localhost ~] # mkdir / backupdir [root@localhost ~] # innobackupex-- user='backup'-- password='xtrabackup123' / backupdir [root@localhost ~] # ls / backupdir/2016-07-05 08-42-50

Full recovery:

[root@localhost ~] # mysql-e 'drop database hellodb;' # Simulation environment first deletes the database to be restored MariaDB [(none)] > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | test | +-+ 4 rows in set (0.00 sec) [root@localhost ~] # innobackupex-- apply-log / backupdir / 2016-07-05 / 08-42-50 / [root@localhost ~] # systemctl stop mariadb [root@localhost ~] # innobackupex-- copy-back / backupdir/2016-07-05 / 08-42-50 pound # verify whether the database has been restored [root@localhost ~] # ls / var/lib/mysql/hellodb ibdata1 ib_logfile0 ib_logfile1 ibtmp1 mysql performance_schema test xtrabackup_infoMariaDB [(none)] > show databases +-+ | Database | +-+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +-+ 5 rows in set (0.00 sec)

Additional equipment:

Complete equipment should be done before additional equipment, because additional equipment is done on the basis of comprehensive changes.

[root@localhost ~] # innobackupex-- user='backup'-- password='xtrabackup123' / backup/ [root@localhost ~] # ls / backup/2016-07-05 / 08-28-54 modify database MariaDB [hellodb] > select * from courses +-+-+ | CourseID | Course | +-+-+ | 1 | Hamo Gong | | 2 | Kuihua Baodian | | 3 | Jinshe Jianfa | 4 | Taiji Quan | | 5 | Daiyu Zanghua | | 6 | Weituo Zhang | 7 | Dagou Bangfa | +-+-+ 7 rows in set (0.00 sec) MariaDB [hellodb] > insert into courses (Course) values ('zhangsan') ('lisi') Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0MariaDB [hellodb] > select * from courses +-+-+ | CourseID | Course | +-+-+ | 1 | Hamo Gong | | 2 | Kuihua Baodian | | 3 | Jinshe Jianfa | 4 | Taiji Quan | | 5 | Daiyu Zanghua | | 6 | Weituo Zhang | 7 | Dagou Bangfa | | 8 | zhangsan | | 9 | lisi | +-+-+ 9 rows in set (0.00 sec) for additional equipment [root@localhost ~] # innobackupex-- user='backup'-- password='xtrabackup123'-- incremental / incbackup/-- incremental-basedir=/backup/2016-07-05 0828-54 / [root @ localhost ~] # cat / incbackup/2016-07-05mm 08-42-06/xtrabackup_checkpoints backup_type = incrementalfrom_lsn = 1628321to_lsn = 1629233last_lsn = 1629233compact = 0recover_binlog_info = 0

Full support + additional backup recovery:

Add the backup and merge it to the full. When you recover the data, you only need to restore the merged one. [root@localhost ~] # innobackupex-- apply-log-- redo-only / backup/2016-07-05 root@localhost 08-28-54 / [root@localhost] # innobackupex-- apply-log-- redo-only / backup/2016-07-05 backup 08-28-54 /-- incremental-dir=/incbackup/2016-07-05 recovery 08-42-06 / [root@localhost ~] # mysql-e 'use hellodb Drop table courses;'[root@localhost ~] # mysql-e 'use hellodb;MariaDB [(none)] > show tables '+-+ | Tables_in_hellodb | +-+ | classes | | coc | | scores | | students | | teachers | | toc | +-+ [root@localhost ~] # Innobackupex-- copy-back / backup/2016-07-05 08-28-54/MariaDB [(none)] > show databases +-+ | Database | +-+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +-+ 5 rows in set (0.00 sec) MariaDB [(none)] > use HellodbReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedMariaDB [hellodb] > show tables +-+ | Tables_in_hellodb | +-+ | classes | | coc | | courses | | scores | | students | | teachers | | toc | +-+ 7 rows in Set (0.00 sec) MariaDB [hellodb] > select * from courses +-+-+ | CourseID | Course | +-+-+ | 1 | Hamo Gong | | 2 | Kuihua Baodian | | 3 | Jinshe Jianfa | 4 | Taiji Quan | | 5 | Daiyu Zanghua | | 6 | Weituo Zhang | 7 | Dagou Bangfa | | 8 | zhangsan | | 9 | lisi | +-+-+ 9 rows in set (0.00 sec)

Some parameters of innobackupex indicate:

-- include: the backup library or table can be selected and regular expressions are supported

-- tables-file: specify all the table names listed in a file

-- databasea: the merger of the above two

-- stream=tar: compress backups as streams

[root@localhost ~] # innobackupex-- user='backup'-- password='xtrabackup123'-- include='hellodb'-- stream=tar / backup/ | gzip > / backup/ `data +% qualified% M% S`.tar.gz