基于docker搭建的mysql5.6从库，使用innobackup进行异机恢复数据

目的：在不影响主库的业务同时搭建从库。
思路：使用innobackup对主库做备份（因为innobackup采用的是物理热备，这里是不会影响主库的业务）
xtrabackup安装方法，mysql5.7以上需要安装xtrabackup2.4以上的版本
这里贴个安装xtrabackup的教程地址：https://blog.youkuaiyun.com/mr_tia/article/details/81979689

上来就先下载一个docker的mysql5.6版本的镜像

docker pull mysql:5.6  #下载MySQL5.6的镜像

这里贴一个国内docker镜像仓库地址：https://ieevee.com/tech/2016/09/28/docker-mirror.html#选择零网易163-docker镜像
接着就是创建容器了：

[root@o_ce guanli_mysql_sla]# docker run -p 4401:3306 --name guanli_mysql_sla -v /ssd_data/docker/guanli_mysql_sla/conf:/etc/mysql/conf.d -v /ssd_data/docker/guanli_mysql_sla/logs:/logs -v /ssd_data/docker/guanli_mysql_sla/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.6  
f205fa7363142db6bb5f748d6bf9560165d46a8793db50fdfa324af0cdc6b4d1
-p：设置端口，映射到主机上
-v：映射里面的相关目录内容到本机
-e MYSQL_ROOT_PASSWORD：设置数据库密码

再者进入容器：

[root@o_ce guanli_mysql_sla]# docker exec -it guanli_mysql_sla bash

因为上面创建容器的时候已经通过选项-e进行密码的设置：这里我们就知道通过该密码登入上数据库

root@f205fa736314:/# mysql -p123456
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 2
Server version: 5.6.44-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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.

root@(none) 05:32>\q
Bye
root@f205fa736314:/# exit
exit

检查没问题可以登入后，stop数据库，并且情况数据库映射到本机的data文件（为恢复做准备）：

[root@o_ce data]# docker stop guanli_mysql_sla
guanli_mysql_sla
[root@o_ce data]# rm -rf /ssd_data/docker/guanli_mysql_sla/data/*
[root@o_ce data]# ls
[root@o_ce data]#

通过–apply-log选项使数据文件处于一致性状态：

[root@o_ce data]# innobackupex --defaults-file=/ssd_data/docker/guanli_mysql_sla/conf/my.cnf --apply-log /ssd_data/innobackup6.4/
190605 13:35:26 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.8 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 97330f7)
xtrabackup: cd to /ssd_data/innobackup6.4/
xtrabackup: This target seems to be already prepared.
InnoDB: Number of pools: 1
...................略..................
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 4150018550293
190605 13:35:30 completed OK!

接下来就是恢复数据了（因为我这里使用的是docker，所以为了保险起见，我指定了mysql配置文件所在的路径，同时也指定存储目录，加上个端口会更保险）：

[root@o_ce data]# innobackupex --defaults-file=/ssd_data/docker/guanli_mysql_sla/conf/my.cnf --datadir=/ssd_data/docker/guanli_mysql_sla/data  -P4401 --copy-back /ssd_data/innobackup6.4/
190605 13:37:46 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.8 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 97330f7)
190605 13:37:46 [01] Copying ib_logfile0 to /ssd_data/docker/guanli_mysql_sla/data/ib_logfile0
190605 13:37:49 [01]        ...done
190605 13:37:49 [01] Copying ib_logfile1 to /ssd_data/docker/guanli_mysql_sla/data/ib_logfile1
190605 13:37:52 [01]        ...done
190605 13:37:52 [01] Copying ib_logfile2 to /ssd_data/docker/guanli_mysql_sla/data/ib_logfile2
190605 13:37:53 [01]        ...done
190605 13:37:53 [01] Copying ibdata1 to /ssd_data/docker/guanli_mysql_sla/data/ibdata1
.......................................略.............................
190605 13:42:46 [01] Copying ./member/activity_month_statistics.frm to /ssd_data/docker/guanli_mysql_sla/data/member/activity_month_statistics.frm
190605 13:42:46 [01]        ...done
190605 13:42:46 completed OK!

恢复成功之后记得给data目录授权，否则容器起不来的（需要提前留心docker上的所属者跟所属组）：

[root@o_ce data]# chown -R 999.999 /ssd_data/docker/guanli_mysql_sla/data

然后恢复完成之后登入进数据库查看：

[root@o_ce data]# docker exec -it guanli_mysql_sla bash
[root@o_ce conf]# docker exec -it guanli_mysql_sla bash
root@f205fa736314:/# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.44-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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.

root@(none) 05:45>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| analy              |
| camel_trade        |
| erp                |
| hiya               |
| ipos               |
| learn              |
| lottery            |
| member             |
| mysql              |
| mysql_bin          |
| overseas           |
| performance_schema |
| pos                |
| qa                 |
| tradenew           |
| xxb                |
| zentao             |
+--------------------+
18 rows in set (0.00 sec)

root@(none) 05:45>

然后回到主机上查找备份是的bin-log日志及偏移量，位置在备份出来的目录里：

[root@o_ce innobackup6.4]# cd /ssd_data/docker/guanli_mysql_sla/data/
[root@o_ce data]# cat xtrabackup_binlog_pos_innodb 
mysql-bin.000703	561784231

然后指定主库：

root@(none) 05:45>change master to master_host='192.168.17.60',
    -> master_port=33160,
    -> master_user='root',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000703',
    -> master_log_pos=561784231;
Query OK, 0 rows affected, 2 warnings (0.16 sec)

指定完之后开启slave：

root@(none) 05:51>start slave;
Query OK, 0 rows affected (0.01 sec)

root@(none) 05:51>show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log
                  Master_Host: 192.168.17.60
                  Master_User: camel_xtrabackup
                  Master_Port: 33160
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000703
          Read_Master_Log_Pos: 569872132
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 652600
        Relay_Master_Log_File: mysql-bin.000703
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 562436548
              Relay_Log_Space: 8088357
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 98701           ###这时候发现延迟非常的大，但多次查看状态发现正在慢慢的减少
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 577586f0-979d-11e8-ba2d-0242ac110005
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: updating
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

ERROR: 
No query specified

这里可以反复查看两个参数：
一个是Seconds_Behind_Master：如果在不断的减少，证明主从正在缩小延迟。
二个是Exec_Master_Log_Pos：当后面的数值在不断变化的时候说明从库正在追赶主库。
因为当时主库有数据在不断的查询写入，导致了一开始的SBM值非常的大，我的处理方式是——等
（据说也可以通过修改主库的配置文件达到减少延迟的效果，但前面也说了，不能影响主库业务，所以这里我选择保守一些）
反复show slave status\G;查看延迟是否有持续减少。最后减少为零整个过程就结束了。