欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-10-26 21:06:05
演示环境: 操作系统:Linux-x86-64 数据库版本:5.7.19 本次演示包括:xtrabackup软件的安装, 全量备份,增量备份,恢复 操作 1、安装xtrab...

演示环境:

操作系统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