mysql的xtrabackup备份恢复等基本操作教程

演示环境：

操作系统：Linux-x86-64

数据库版本：5.7.19

本次演示包括：xtrabackup软件的安装， 全量备份，增量备份，恢复 操作

1、安装xtrabackup软件

--查看软件
[root@single-instance oracle_setup]# ll *xtrabackup*
-rw-r--r-- 1 root root 7745224 Mar 19 21:21 percona-xtrabackup-24-2.4.10-1.el7.x86_64.rpm


--安装报错 一些依赖包没有
[root@single-instance oracle_setup]# rpm -ivh percona-xtrabackup-24-2.4.10-1.el7.x86_64.rpm
warning: percona-xtrabackup-24-2.4.10-1.el7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
error: Failed dependencies:
        libev.so.4()(64bit) is needed by percona-xtrabackup-24-2.4.10-1.el7.x86_64
        perl(DBD::mysql) is needed by percona-xtrabackup-24-2.4.10-1.el7.x86_64
        perl(Digest::MD5) is needed by percona-xtrabackup-24-2.4.10-1.el7.x86_64


--安装依赖包
[root@single-instance oracle_setup]# rpm -ivh libev4-4.24-alt1.x86_64.rpm
Preparing...                          ################################# [100%]
Updating / installing...
   1:libev4-4.24-alt1                 ################################# [100%]


[root@single-instance yum.repos.d]# yum -y install perl-Digest-MD5.x86_64
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Resolving Dependencies
--> Running transaction check
---> Package perl-Digest-MD5.x86_64 0:2.52-3.el7 will be installed
--> Processing Dependency: perl(Digest::base) >= 1.00 for package: perl-Digest-MD5-2.52-3.el7.x86_64
--> Running transaction check
---> Package perl-Digest.noarch 0:1.17-245.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================
 Package                                    Arch                              Version                                 Repository                        Size
=============================================================================================================================================================
Installing:
 perl-Digest-MD5                            x86_64                            2.52-3.el7                              rhel7                             30 k
Installing for dependencies:
 perl-Digest                                noarch                            1.17-245.el7                            rhel7                             23 k

Transaction Summary
=============================================================================================================================================================
Install  1 Package (+1 Dependent package)

Total download size: 53 k
Installed size: 82 k
Downloading packages:
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                        6.8 MB/s |  53 kB  00:00:00     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.
** Found 1 pre-existing rpmdb problem(s), 'yum check' output follows:
cyrus-sasl-lib-2.1.26-20.el7_2.x86_64 is a duplicate with cyrus-sasl-lib-2.1.23-15.el6_6.2.x86_64
  Installing : perl-Digest-1.17-245.el7.noarch                                                                                                           1/2 
  Installing : perl-Digest-MD5-2.52-3.el7.x86_64                                                                                                         2/2 
  Verifying  : perl-Digest-1.17-245.el7.noarch                                                                                                           1/2 
  Verifying  : perl-Digest-MD5-2.52-3.el7.x86_64                                                                                                         2/2 

Installed:
  perl-Digest-MD5.x86_64 0:2.52-3.el7                                                                                                                        

Dependency Installed:
  perl-Digest.noarch 0:1.17-245.el7                                                                                                                          

Complete!



[root@single-instance oracle_setup]# rpm -ivh mysql-community-libs-compat-5.7.21-1.el7.x86_64.rpm
warning: mysql-community-libs-compat-5.7.21-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-libs-compat-5.7.2################################# [100%]


--依赖  mysql-community-libs-compat
[root@single-instance yum.repos.d]# yum -y install perl-DBD-MySQL.x86_64
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Resolving Dependencies
--> Running transaction check
---> Package perl-DBD-MySQL.x86_64 0:4.023-5.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================
 Package                                   Arch                              Version                                  Repository                        Size
=============================================================================================================================================================
Installing:
 perl-DBD-MySQL                            x86_64                            4.023-5.el7                              rhel7                            140 k

Transaction Summary
=============================================================================================================================================================
Install  1 Package

Total download size: 140 k
Installed size: 323 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.
** Found 1 pre-existing rpmdb problem(s), 'yum check' output follows:
cyrus-sasl-lib-2.1.26-20.el7_2.x86_64 is a duplicate with cyrus-sasl-lib-2.1.23-15.el6_6.2.x86_64
  Installing : perl-DBD-MySQL-4.023-5.el7.x86_64                                                                                                         1/1 
  Verifying  : perl-DBD-MySQL-4.023-5.el7.x86_64                                                                                                         1/1 

Installed:
  perl-DBD-MySQL.x86_64 0:4.023-5.el7                                                                                                                        

Complete!




--安装xtrabackup
[root@single-instance oracle_setup]# rpm -ivh percona-xtrabackup-24-2.4.10-1.el7.x86_64.rpm
warning: percona-xtrabackup-24-2.4.10-1.el7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:percona-xtrabackup-24-2.4.10-1.el################################# [100%]



--查看安装文件
[root@single-instance xtrabackup_dir]#  rpm -qa |grep  xtrabackup 
percona-xtrabackup-24-2.4.10-1.el7.x86_64

[root@single-instance xtrabackup_dir]# rpm -ql  percona-xtrabackup-24-2.4.10-1.el7.x86_64
/usr/bin/innobackupex
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/share/doc/percona-xtrabackup-24-2.4.10
/usr/share/doc/percona-xtrabackup-24-2.4.10/COPYING
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz

2、基本设置

--创建备份用户
mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 'S3cret2233$';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)


--设置备份路径
[root@single-instance xtrabackup_dir]# mkdir -p /mysql/xtrabackup_dir/
[root@single-instance xtrabackup_dir]# chown mysql:mysql /mysql/xtrabackup_dir/

[root@single-instance ~]# vi /etc/my.cnf
--添加参数
[xtrabackup]
target_dir = /mysql/xtrabackup_dir/

3、xtrabackup全量备份

--xtrabackup 全量备份
[root@single-instance ~]# xtrabackup --backup --user=bkpuser --password=S3cret2233$
180416 17:38:44  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'bkpuser'  (using password: YES).
180416 17:38:44  version_check Connected to MySQL server
180416 17:38:44  version_check Executing a version check against the server...
180416 17:38:44  version_check Done.
180416 17:38:44 Connecting to MySQL server host: localhost, user: bkpuser, password: set, port: not set, socket: not set
Using server version 5.7.19-log
xtrabackup version 2.4.10 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 3198bce)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
180416 17:38:44 >> log scanned up to (2777824)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 54 for flydb/t_list#P#p0, old maximum was 0
180416 17:38:44 [01] Copying ./ibdata1 to /mysql/xtrabackup_dir/ibdata1
180416 17:38:44 [01]        ...done
......
180416 17:38:44 [01] Copying ./mysql/slave_worker_info.ibd to /mysql/xtrabackup_dir/mysql/slave_worker_info.ibd
180416 17:38:44 [01]        ...done
180416 17:38:45 >> log scanned up to (2777824)
180416 17:38:45 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
180416 17:38:45 Executing FLUSH TABLES WITH READ LOCK...
180416 17:38:45 Starting to backup non-InnoDB tables and files
180416 17:38:45 [01] Copying ./flydb/t_list.frm to /mysql/xtrabackup_dir/flydb/t_list.frm
180416 17:38:45 [01]        ...done
......
180416 17:38:47 [01] Copying ./mysql/help_relation.frm to /mysql/xtrabackup_dir/mysql/help_relation.frm
180416 17:38:47 [01]        ...done
180416 17:38:47 Finished backing up non-InnoDB tables and files
180416 17:38:47 [00] Writing /mysql/xtrabackup_dir/xtrabackup_binlog_info
180416 17:38:47 [00]        ...done
180416 17:38:47 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '2777815'
xtrabackup: Stopping log copying thread.
.180416 17:38:47 >> log scanned up to (2777824)

180416 17:38:47 Executing UNLOCK TABLES
180416 17:38:47 All tables unlocked
180416 17:38:47 [00] Copying ib_buffer_pool to /mysql/xtrabackup_dir/ib_buffer_pool
180416 17:38:47 [00]        ...done
180416 17:38:47 Backup created in directory '/mysql/xtrabackup_dir/'
MySQL binlog position: filename 'my-bin.000043', position '800'
180416 17:38:47 [00] Writing /mysql/xtrabackup_dir/backup-my.cnf
180416 17:38:47 [00]        ...done
180416 17:38:47 [00] Writing /mysql/xtrabackup_dir/xtrabackup_info
180416 17:38:47 [00]        ...done
xtrabackup: Transaction log of lsn (2777815) to (2777824) was copied.
180416 17:38:48 completed OK!


--查看备份文件
[root@single-instance xtrabackup_dir]# cd /mysql/xtrabackup_dir/
[root@single-instance xtrabackup_dir]# ll
total 12336
-rw-r----- 1 root root      426 Apr 16 17:38 backup-my.cnf
drwxr-x--- 2 root root     4096 Apr 16 17:38 flydb
-rw-r----- 1 root root      315 Apr 16 17:38 ib_buffer_pool
-rw-r----- 1 root root 12582912 Apr 16 17:38 ibdata1
drwxr-x--- 2 root root     4096 Apr 16 17:38 mysql
drwxr-x--- 2 root root     4096 Apr 16 17:38 performance_schema
drwxr-x--- 2 root root    12288 Apr 16 17:38 sys
-rw-r----- 1 root root       18 Apr 16 17:38 xtrabackup_binlog_info
-rw-r----- 1 root root      113 Apr 16 17:38 xtrabackup_checkpoints
-rw-r----- 1 root root      464 Apr 16 17:38 xtrabackup_info
-rw-r----- 1 root root     2560 Apr 16 17:38 xtrabackup_logfile

4、innobackupex全量备份

--innobackupex 全量备份

--defaults-file=/etc/my.cnf   //my.cnf文件
--user=bkpuser                //user用户
--password                    //password密码
/mysql/innobackupex_dir/      //备份路径


[root@single-instance ~]# innobackupex --defaults-file=/etc/my.cnf   --user=bkpuser --password=S3cret2233$  /mysql/innobackupex_dir/
180416 19:56:45 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

180416 19:56:45  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'bkpuser'  (using password: YES).
180416 19:56:45  version_check Connected to MySQL server
180416 19:56:45  version_check Executing a version check against the server...
180416 19:56:45  version_check Done.
180416 19:56:45 Connecting to MySQL server host: localhost, user: bkpuser, password: set, port: not set, socket: not set
Using server version 5.7.19-log
innobackupex version 2.4.10 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 3198bce)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
180416 19:56:45 >> log scanned up to (2777824)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 54 for flydb/t_list#P#p0, old maximum was 0
180416 19:56:46 [01] Copying ./ibdata1 to /mysql/innobackupex_dir/2018-04-16_19-56-45/ibdata1
180416 19:56:46 [01]        ...done
......
180416 19:56:46 [01] Copying ./mysql/slave_worker_info.ibd to /mysql/innobackupex_dir/2018-04-16_19-56-45/mysql/slave_worker_info.ibd
180416 19:56:46 [01]        ...done
180416 19:56:46 >> log scanned up to (2777824)
180416 19:56:47 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
180416 19:56:47 Executing FLUSH TABLES WITH READ LOCK...
180416 19:56:47 Starting to backup non-InnoDB tables and files
180416 19:56:47 [01] Copying ./flydb/t_list.frm to /mysql/innobackupex_dir/2018-04-16_19-56-45/flydb/t_list.frm
180416 19:56:47 [01]        ...done
......
180416 19:56:49 [01] Copying ./mysql/help_relation.frm to /mysql/innobackupex_dir/2018-04-16_19-56-45/mysql/help_relation.frm
180416 19:56:49 [01]        ...done
180416 19:56:49 Finished backing up non-InnoDB tables and files
180416 19:56:49 [00] Writing /mysql/innobackupex_dir/2018-04-16_19-56-45/xtrabackup_binlog_info
180416 19:56:49 [00]        ...done
180416 19:56:49 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '2777815'
xtrabackup: Stopping log copying thread.
.180416 19:56:49 >> log scanned up to (2777824)

180416 19:56:49 Executing UNLOCK TABLES
180416 19:56:49 All tables unlocked
180416 19:56:49 [00] Copying ib_buffer_pool to /mysql/innobackupex_dir/2018-04-16_19-56-45/ib_buffer_pool
180416 19:56:49 [00]        ...done
180416 19:56:49 Backup created in directory '/mysql/innobackupex_dir/2018-04-16_19-56-45/'
MySQL binlog position: filename 'my-bin.000043', position '800'
180416 19:56:49 [00] Writing /mysql/innobackupex_dir/2018-04-16_19-56-45/backup-my.cnf
180416 19:56:49 [00]        ...done
180416 19:56:49 [00] Writing /mysql/innobackupex_dir/2018-04-16_19-56-45/xtrabackup_info
180416 19:56:49 [00]        ...done
xtrabackup: Transaction log of lsn (2777815) to (2777824) was copied.
180416 19:56:50 completed OK!



--查看备份文件
[root@single-instance innobackupex_dir]# cd /mysql/innobackupex_dir/2018-04-16_19-56-45/
[root@single-instance 2018-04-16_19-56-45]# ll
total 12336
-rw-r----- 1 root root      426 Apr 16 19:56 backup-my.cnf
drwxr-x--- 2 root root     4096 Apr 16 19:56 flydb
-rw-r----- 1 root root      315 Apr 16 19:56 ib_buffer_pool
-rw-r----- 1 root root 12582912 Apr 16 19:56 ibdata1
drwxr-x--- 2 root root     4096 Apr 16 19:56 mysql
drwxr-x--- 2 root root     4096 Apr 16 19:56 performance_schema
drwxr-x--- 2 root root    12288 Apr 16 19:56 sys
-rw-r----- 1 root root       18 Apr 16 19:56 xtrabackup_binlog_info
-rw-r----- 1 root root      113 Apr 16 19:56 xtrabackup_checkpoints
-rw-r----- 1 root root      510 Apr 16 19:56 xtrabackup_info
-rw-r----- 1 root root     2560 Apr 16 19:56 xtrabackup_logfile

5、innobackupex增量备份

--innobackupex 增量备份  
只有innodb 可以， 有lsn号(相当oracle的scn)，可以增量备份； 其他的myisam都是全量再备份一份； memory的只备份了表结构， 数据备份不了。


--备份前新增表 并写入数据
mysql> use flydb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> create table please_recovery_me_t(a int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into please_recovery_me_t values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into please_recovery_me_t values (2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from please_recovery_me_t;
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)





--增量备份

--defaults-file=/etc/my.cnf   //my.cnf文件
--user=bkpuser                //user用户
--password                    //password密码
--incremental                 //增量备份标示
/mysql/innobackupex_dir/      //增量备份路径
--incremental-basedir         //基于的全量备份路径


[root@single-instance 2018-04-16_19-56-45]# innobackupex  --defaults-file=/etc/my.cnf   --user=bkpuser --password=S3cret2233$ --incremental /mysql/innobackupex_dir/  --incremental-basedir=/mysql/innobackupex_dir/2018-04-16_19-56-45/
180416 20:38:06 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

180416 20:38:06  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'bkpuser'  (using password: YES).
180416 20:38:06  version_check Connected to MySQL server
180416 20:38:06  version_check Executing a version check against the server...
180416 20:38:06  version_check Done.
180416 20:38:06 Connecting to MySQL server host: localhost, user: bkpuser, password: set, port: not set, socket: not set
Using server version 5.7.19-log
innobackupex version 2.4.10 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 3198bce)
incremental backup from 2777815 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
180416 20:38:06 >> log scanned up to (2784824)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 54 for flydb/t_list#P#p0, old maximum was 0
xtrabackup: using the full scan for incremental backup
180416 20:38:07 [01] Copying ./ibdata1 to /mysql/innobackupex_dir/2018-04-16_20-38-06/ibdata1.delta
180416 20:38:07 [01]        ...done
......
180416 20:38:11 [01] Copying ./mysql/slave_worker_info.ibd to /mysql/innobackupex_dir/2018-04-16_20-38-06/mysql/slave_worker_info.ibd.delta
180416 20:38:11 [01]        ...done
180416 20:38:11 >> log scanned up to (2784824)
180416 20:38:12 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
180416 20:38:12 Executing FLUSH TABLES WITH READ LOCK...
180416 20:38:12 Starting to backup non-InnoDB tables and files
180416 20:38:12 [01] Copying ./flydb/t_list.frm to /mysql/innobackupex_dir/2018-04-16_20-38-06/flydb/t_list.frm
180416 20:38:12 [01]        ...done
......
180416 20:38:14 [01] Copying ./mysql/help_relation.frm to /mysql/innobackupex_dir/2018-04-16_20-38-06/mysql/help_relation.frm
180416 20:38:14 [01]        ...done
180416 20:38:14 Finished backing up non-InnoDB tables and files
180416 20:38:14 [00] Writing /mysql/innobackupex_dir/2018-04-16_20-38-06/xtrabackup_binlog_info
180416 20:38:14 [00]        ...done
180416 20:38:14 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '2784815'
xtrabackup: Stopping log copying thread.
.180416 20:38:14 >> log scanned up to (2784824)

180416 20:38:15 Executing UNLOCK TABLES
180416 20:38:15 All tables unlocked
180416 20:38:15 [00] Copying ib_buffer_pool to /mysql/innobackupex_dir/2018-04-16_20-38-06/ib_buffer_pool
180416 20:38:15 [00]        ...done
180416 20:38:15 Backup created in directory '/mysql/innobackupex_dir/2018-04-16_20-38-06/'
MySQL binlog position: filename 'my-bin.000043', position '1527'
180416 20:38:15 [00] Writing /mysql/innobackupex_dir/2018-04-16_20-38-06/backup-my.cnf
180416 20:38:15 [00]        ...done
180416 20:38:15 [00] Writing /mysql/innobackupex_dir/2018-04-16_20-38-06/xtrabackup_info
180416 20:38:15 [00]        ...done
xtrabackup: Transaction log of lsn (2784815) to (2784824) was copied.
180416 20:38:15 completed OK!



--查看增量备份文件
[root@single-instance 2018-04-16_20-38-06]# cd /mysql/innobackupex_dir/2018-04-16_20-38-06
[root@single-instance 2018-04-16_20-38-06]# ll
total 596
-rw-r----- 1 root root    426 Apr 16 20:38 backup-my.cnf
drwxr-x--- 2 root root   4096 Apr 16 20:38 flydb
-rw-r----- 1 root root    315 Apr 16 20:38 ib_buffer_pool
-rw-r----- 1 root root 557056 Apr 16 20:38 ibdata1.delta
-rw-r----- 1 root root     44 Apr 16 20:38 ibdata1.meta
drwxr-x--- 2 root root   4096 Apr 16 20:38 mysql
drwxr-x--- 2 root root   4096 Apr 16 20:38 performance_schema
drwxr-x--- 2 root root  12288 Apr 16 20:38 sys
-rw-r----- 1 root root     19 Apr 16 20:38 xtrabackup_binlog_info
-rw-r----- 1 root root    117 Apr 16 20:38 xtrabackup_checkpoints
-rw-r----- 1 root root    598 Apr 16 20:38 xtrabackup_info
-rw-r----- 1 root root   2560 Apr 16 20:38 xtrabackup_logfile

6、innobackupex全量恢复

--删除flydb 数据库 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| backup             |
| flydb              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> drop database flydb;
Query OK, 8 rows affected (0.12 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| backup             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

关闭数据库
[root@single-instance ~]# service mysqld stop
Stopping mysqld (via systemctl):  [  OK  ]

修改数据库目录
[root@single-instance lib]# mv mysql mysqlbak




--准备恢复(全备份)

--apply-log                                      //恢复准备工作 

--redo-only should be used when merging all incrementals except the last one. That’s why the previous
line doesn’t contain the --redo-only option. Even if the --redo-only was used on the last step, backup would
still be consistent but in that case server would perform the rollback phase.

/mysql/innobackupex_dir/2018-04-16_19-56-45/     //全量备份路径

--准备恢复(全备份)
[root@single-instance ~]# innobackupex   --apply-log --redo-only /mysql/innobackupex_dir/2018-04-16_19-56-45/
180416 21:33:44 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".

innobackupex version 2.4.10 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 3198bce)
xtrabackup: cd to /mysql/innobackupex_dir/2018-04-16_19-56-45/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2777815)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 8388608
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 2777815
InnoDB: Doing recovery: scanned up to log sequence number 2777824 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 2492, file name my-bin.000027
InnoDB: xtrabackup: Last MySQL binlog file position 2492, file name my-bin.000027

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2777833
InnoDB: Number of pools: 1
180416 21:33:45 completed OK!




--准备恢复(增量备份)
[root@single-instance ~]# innobackupex   --apply-log  /mysql/innobackupex_dir/2018-04-16_19-56-45/  --incremental-dir=/mysql/innobackupex_dir/2018-04-16_20-38-06/
180416 21:39:13 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".

innobackupex version 2.4.10 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 3198bce)
incremental backup from 2777815 is enabled.
xtrabackup: cd to /mysql/innobackupex_dir/2018-04-16_19-56-45/
xtrabackup: This target seems to be already prepared with --apply-log-only.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2784815)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = /mysql/innobackupex_dir/2018-04-16_20-38-06/
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 8388608
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 54 for flydb/t_list#P#p0, old maximum was 0
xtrabackup: page size for /mysql/innobackupex_dir/2018-04-16_20-38-06//ibdata1.delta is 16384 bytes
Applying /mysql/innobackupex_dir/2018-04-16_20-38-06//ibdata1.delta to ./ibdata1...
......
xtrabackup: page size for /mysql/innobackupex_dir/2018-04-16_20-38-06//mysql/slave_master_info.ibd.delta is 16384 bytes
Applying /mysql/innobackupex_dir/2018-04-16_20-38-06//mysql/slave_master_info.ibd.delta to ./mysql/slave_master_info.ibd...
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = /mysql/innobackupex_dir/2018-04-16_20-38-06/
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 2784815
InnoDB: Doing recovery: scanned up to log sequence number 2784824 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 1527, file name my-bin.000043
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.7.19 started; log sequence number 2784824
InnoDB: xtrabackup: Last MySQL binlog file position 1527, file name my-bin.000043

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2786657
InnoDB: Number of pools: 1
180416 21:39:15 [01] Copying /mysql/innobackupex_dir/2018-04-16_20-38-06/flydb/t_list.frm to ./flydb/t_list.frm
180416 21:39:15 [01]        ...done
......
180416 21:39:17 [00] Copying /mysql/innobackupex_dir/2018-04-16_20-38-06//xtrabackup_info to ./xtrabackup_info
180416 21:39:17 [00]        ...done
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Setting log file ./ib_logfile101 size to 48 MB
InnoDB: Setting log file ./ib_logfile1 size to 48 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=2786657
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 2786828
InnoDB: Doing recovery: scanned up to log sequence number 2786837 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 1527, file name my-bin.000043
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.7.19 started; log sequence number 2786837
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2786856
180416 21:39:19 completed OK!








--全量恢复
[root@single-instance lib]# innobackupex --defaults-file=/etc/my.cnf   --copy-back  /mysql/innobackupex_dir/2018-04-16_19-56-45
180416 21:49:39 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex
           prints "completed OK!".

innobackupex version 2.4.10 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 3198bce)
180416 21:49:39 [01] Copying ib_logfile0 to /var/lib/mysql/ib_logfile0
......
180416 21:49:41 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
180416 21:49:41 [01]        ...done
180416 21:49:41 completed OK!




--打开mysql
[root@single-instance ~]# service mysqld start
Starting mysqld (via systemctl):  [  OK  ]

--登录mysql
[root@single-instance ~]# mysql -uroot -p'Root123$'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


--查看flydb存在，全量备份恢复成功
mysql> use flydb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed


--查看全量备份后，增量备份前的数据在，增量备份恢复成功
mysql> select * from please_recovery_me_t;
+------+
| a |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)


--启动库遇到的问题
[root@single-instance mysql]# chown -R mysql:mysql mysql/
[root@single-instance mysql]# ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock

[root@single-instance mysql]# mkdir -p /var/run/mysqld
[root@single-instance mysql]# chown mysql:mysql /var/run/mysqld

[root@single-instance mysql]# rm /tmp/mysql.sock
rm: remove regular empty file ‘/tmp/mysql.sock’ yes