Preface:

The encapsulation of xtrabackup by innobackupex is a reliable physical backup tool launched by percona. Official Link Address For mysql, logical backup can use mysqldump, mysqlpump, mysqldumper, and physical backup can use innobackupex directly.

Installation:

For convenience, I use centos7.x yum installation here (if interested, download linux generc version from the official website, you can use it directly by decompressing)

[root@centos7 ~]# yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
[root@centos7 ~]# yum install percona-xtrabackup-24  
[root@centos7 ~]# yum install qpress  ##innobackupex supports compression exports, where compression tools are installed

[root@centos7 ~]# innobackupex -version  ###If the following prompt indicates that the installation was successful, I have 2.4.15 here.
xtrabackup: recognized server arguments: --server-id=11 --datadir=/data/mysql_data --tmpdir=/tmp --log_bin=bin.log --innodb_page_size=8192 --innodb_buffer_pool_size=2G --innodb_io_capacity=4000 --innodb_flush_method=O_DIRECT --innodb_undo_tablespaces=3 --innodb_log_file_size=128M --innodb_log_buffer_size=16777216 
innobackupex version 2.4.15 Linux (x86_64) (revision id: 544842a)
[root@centos7 ~]# 

Backup:

Because my operating environment is multi-instance, I will take 3307 library to operate here, so innobackupex can not use the default / etc/my.cnf configuration file for backup and recovery.

1. Create backup configuration files

[root@centos7 ~]# vim innobackupex.cnf 

//Add the following:

[mysqld]
datadir=/data1
basedir=/use/local/mysql
innodb_page_size=8192
socket=/tmp/mysql.sock1
port=3307

2. Start backup

1.Create a directory to store backups
[root@centos7 /]# mkdir innobackup_dir
[root@centos7 /]# chown mysql.mysql /innobackup_dir/* -R

2.Start backup
[root@centos7 /]# innobackupex --defaults-file=/root/innobackupex.cnf --user=root --password=root --socket=/tmp/mysql.sock1 /innobackup_dir/

3.View Backup
[root@centos7 2019-09-07_17-58-55]# ll
total 45136
-rw-r-----. 1 root root      487 Sep  7 17:59 backup-my.cnf
drwxr-x---. 2 root root     4096 Sep  7 17:58 demo01
drwxr-x---. 2 root root     4096 Sep  7 17:59 employees
-rw-r-----. 1 root root    27956 Sep  7 17:59 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Sep  7 17:58 ibdata1
drwxr-x---. 2 root root     4096 Sep  7 17:59 mysql
drwxr-x---. 2 root root     4096 Sep  7 17:59 performance_schema
drwxr-x---. 2 root root     4096 Sep  7 17:59 sbtest
drwxr-x---. 2 root root    12288 Sep  7 17:58 sys
-rw-r-----. 1 root root  9437184 Sep  7 17:58 undo001
-rw-r-----. 1 root root  7340032 Sep  7 17:58 undo002
-rw-r-----. 1 root root 16777216 Sep  7 17:58 undo003
-rw-r-----. 1 root root       15 Sep  7 17:59 xtrabackup_binlog_info
-rw-r-----. 1 root root      144 Sep  7 17:59 xtrabackup_checkpoints
-rw-r-----. 1 root root      536 Sep  7 17:59 xtrabackup_info
-rw-r-----. 1 root root     2560 Sep  7 17:59 xtrabackup_logfile
//Backup here

Recovery:

Recovery process:

1.Create a directory for storing database recovery
[root@centos7 /]# mkdir /data1_recover_dir
[root@centos7 /]# chown mysql.mysql /data1_recover_dir -R
 
2.Create a configuration file for recovery
[root@centos7 ~]# vim innobackupex2.cnf
###The contents of the configuration file are as follows

[mysqld]
datadir=/data1_recover_dir ## Here points to the directory to be restored
basedir=/use/local/mysql
socket=/tmp/mysql.sock5 ##Make one by yourself.
innodb_page_size=8192  
port=3311
innodb_undo_tablespaces = 3  ##If not configured, undo table spaces will not be restored to the data directory at step 5, resulting in startup errors

3.Application log
[root@centos7 ~]# innobackupex --defaults-file=/root/innobackupex2.cnf --apply-log /innobackup_dir/2019-09-07_17-04-14/


4.Copy backup
[root@centos7 ~]# innobackupex --defaults-file=/root/innobackupex2.cnf --copy-back /innobackup_dir/2019-09-07_17-04-14/
//At this point, you can see the restored database in the restore directory.

5.Add the configuration of the new library to my.cnf Configuration file
    [mysqld5]
    server-id=555
    basedir=/usr/local/mysql
    datadir=/data1_recover_dir
    port=3311
    socket=/tmp/mysql.sock5
6.Boot instance
   [root@centos7 ~]# mysqld_multi start 5

summary

On the first recovery, the boot instance reported an error:

[ERROR] InnoDB: Unable to open undo tablespace './/undo001'.
       [ERROR] InnoDB: Plugin initialization aborted with error Generic error
       [ERROR] Plugin 'InnoDB' init function returned error.
       [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

The prompt could not find undo table spaces because I configure innodb_undo_tablespaces = 3 in the restored configuration file innobackupex2.cnf

After adding the configuration and restoring, the instance can be restarted and the error is resolved. Reference link

 

The above backup is just a simple introduction to innobackupex. We will continue to introduce how to export compression, compress backup recovery, and transfer backup to other places for storage.