MySQL-数据库主从复制

0、企业高可用性标准(全年无故障率)

99.9%                 ----> 0.001*365*24*60 = 525.6  min 
99.99%                ----> 0.0001*365*24*60= 52.56  min
99.999%               ----> 0.0001*365*24*60= 5.256  min      金融级别

0.1 企业级高可用方案

负载均衡:有一定的高可用性（LVS、Nginx）
主备系统:有高可用性,但是需要切换,是单活的架构
    KeepAlive,
    MMM,
    MHA*****, 
    TMHA

真正高可用(多活系统): 
    MySQL NDB Cluster 
    Oracle RAC  
    Sysbase cluster 
    PXC,    ***
    MGC,    ***
    InnoDB Cluster（MGR 5.7.17） ****

1、主从复制简介

基于二进制日志复制的
主库的修改操作会记录二进制日志
从库会请求新的二进制日志并回放,最终达到主从数据同步

1.1 主从复制核心功能:

辅助备份,处理物理损坏
扩展新型的架构:高可用,高性能,分布式架构等

2、主从复制的前提(主从复制的规划,实施过程)

1>至少2个数据库实例。
2>主库要开启binlog,不同server_id,server_uuid。
3>主库要有一个专门用作复制的用户(replication slave)。
4>通过备份将源库数据补偿到从库。
5>告知从库,用户名,密码,ip,port,自动复制的起点。
6>需要专门的复制线程(start slave )。

3、主从复制

3.1 准备多实例环境

[root@db01 ~]# systemctl start mysqld3307
[root@db01 ~]# systemctl start mysqld3308
[root@db01 ~]# mysql -S /data/3307/mysql.sock
[root@db01 ~]# mysql -S /data/3308/mysql.sock

3.2 检查 主库binlog,不同server_id,server_uuid

[root@db01 ~]# mysql -S /data/3307/mysql.sock -e "select @@log_bin;select @@server_id"
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "select @@log_bin;select @@server_id"

3.3 主库创建复制用户

[root@db01 ~]# mysql -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123456';"
[root@db01 ~]# mysql -S /data/3307/mysql.sock -e "select user,host from mysql.user where user='repl';"

3.4 通过备份将源库数据补偿到从库

[root@db01 ~]# mysqldump  -S /data/3307/mysql.sock -A  -R -E --triggers --master-data=2 --single-transaction --max-allowed-packet=128M   >/tmp/full.sql
[root@db01 ~]# 
[root@db01 ~]# mysql -S /data/3308/mysql.sock  </tmp/full.sql 

3.5 告知从库,用户名,密码,ip,port,自动复制的起点

  vim /tmp/full.sql
  -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=444;

# change master to 
[root@db01 ~]# mysql -S /data/3308/mysql.sock
oldguo[(none)]>help change master to

CHANGE MASTER TO
  MASTER_HOST='10.0.0.51',     
  MASTER_USER='repl',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000005',
  MASTER_LOG_POS=444,
  MASTER_CONNECT_RETRY=10;

3.6 启动主从状态

[root@db01 ~]# mysql -S /data/3308/mysql.sock

wenjuan[(none)]>start slave;
Query OK, 0 rows affected (0.01 sec)

wenjuan[(none)]>

3.7 检测主从状态

[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep Yes
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[root@db01 ~]# 

3.8 简单排错过程

[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep Last
如果有问题：
[root@db01 ~]# mysql -S /data/3308/mysql.sock
wenjuan[(none)]>stop slave ;
wenjuan[(none)]>reset slave all;
wenjuan[(none)]> CHANGE MASTER TO xxxx
wenjuan[(none)]>start slave;

4、主从复制原理

4.1 主从复制过程中涉及到的文件

主库：binlog日志
    存放路径：/data/3307
    mysql-bin.000001
    mysql-bin.000002
从库：
    relaylog中继日志 ----临时存储日志信息的文件
        存放路径：/data/3308/data
        db01-relay-bin.000001
        db01-relay-bin.000002
    master.info  主库信息文件
    relay-log.info    中继日志信息文件 

4.2 主从复制中涉及到的线程

主库：
    Binlog_Dump_Thread
        查询：mysql -S /data/3307/mysql.sock -e 'show processlist'

从库：
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
        查询：mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep Yes

4.3 主从复制原理

来自oldguo

来自oldguo

主从复制原理描述：

1>change master to 时，ip pot user password binlog position写入到master.info进行记录
2>start slave 时，从库会启动IO线程和SQL线程
3>IO_T，读取master.info信息，获取主库信息连接主库
4>主库会生成一个准备binlog DUMP线程，来响应从库
5>IO_T根据master.info记录的binlog文件名和position号，请求主库DUMP最新日志
6>DUMP线程检查主库的binlog日志，如果有新的，TP(传送)给从从库的IO_T
7>IO_T将收到的日志存储到了TCP/IP 缓存，立即返回ACK给主库 ，主库工作完成
8>IO_T将缓存中的数据，存储到relay-log日志文件,更新master.info文件binlog 文件名和postion，IO_T工作完成
9>SQL_T读取relay-log.info文件，获取到上次执行到的relay-log的位置，作为起点，回放relay-log
10>SQL_T回放完成之后，会更新relay-log.info文件。
11>relay-log会有自动清理的功能。
细节：
1>主库一旦有新的日志生成，会发送“信号”给binlog dump ，IO线程再请求

5、主从复制的监控

从库：
    show slave status\G

5.1 线程状态

[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep "Running:"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

5.2 线程报错具体信息

[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep "Last"
                   Last_Errno: 0
                   Last_Error: 
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
[root@db01 ~]#

5.3 查看主库的链接信息有关

[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep "Master"|grep -v 'SSL'
                  Master_Host: 10.0.0.51
                  Master_User: repl
                  Master_Port: 3307
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 154
        Relay_Master_Log_File: mysql-bin.000006
          Exec_Master_Log_Pos: 154
        Seconds_Behind_Master: 0
             Master_Server_Id: 7
                  Master_UUID: 12a52bf9-b835-11e9-ae87-000c290143b9
             Master_Info_File: /data/3308/data/master.info
           Master_Retry_Count: 86400
                  Master_Bind: 
           Master_TLS_Version: 
[root@db01 ~]# 

5.4 查看从库和主库延时的时间

[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep "Master"|grep 'Seconds_Behind_Master'
        Seconds_Behind_Master: 0
[root@db01 ~]# 

5.5 过滤复制相关状态

[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep 'Replicate'
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
  Replicate_Ignore_Server_Ids: 
         Replicate_Rewrite_DB: 
[root@db01 ~]#

5.6 延时从库的状态信息

[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep 'Delay'
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
[root@db01 ~]# 

5.7 监控GTID复制状态信息

[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep 'Gtid'
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
[root@db01 ~]# 

5.8 查看中继日之间

[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep 'Gtid'
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
[root@db01 ~]# 

6、主从复制故障

[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep "Running:"
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

6.1 IO线程故障

(1) 读取master.info 
    损坏 
    信息错误----change master to信息错误
(2) 连接主库
    网络
    防火墙
    主库没启动
    连接数上限了（默认151个）

    以上问题会暴露以下信息：
        Slave_IO_Running: Connecting
        Last_IO_Error:  xxxxxx

    排查方法: 
        通过复制用户,手工连接主库,看报错信息.

    修复: 
        stop slave 
        reset slave all
        change master to
        start slave
 注意：单独启动IO方法：start slave  io_thread
 
(3) 请求日志 （重要）
    master.info 复制起点
    主库: 损坏,误删除等操作

(4) 接收日志
    relaylog损坏

修复: 
    stop slave 
    reset slave all
    change master to
    start slave

(5) 更新master.info

mysql错误所有代码：https://www.jianshu.com/p/8b5ea28609d2

6.2 SQL线程故障 ※※※※※

6.2.1 relay-log.info

6.2.2 回访relaylog中的日志 ※※※※※

SQL语句为什么会失败？
（1）语法，SQL_Mode

版本不同，sql_mode不一致。

（2）DDL、DML为什么会失败？

create database/table  创建的对象已存在
原因：从库被提前写入
解决：以主库为准，删除从库已存在的对象，然后重启主从：start slave

drop database errdb;   要删除和修改的对象不存在.
alter 
insert
update 
delete 
处理方法(以从库为核心的处理方案)：
方法一：
    stop slave; 
    set global sql_slave_skip_counter = 1;
    #将同步指针向下移动一个，如果多次不同步，可以重复操作。
    start slave;
方法二：
    /etc/my.cnf
    slave-skip-errors = 1032,1062,1007
常见错误代码:
    1007:对象已存在
    1032:无法执行DML
    1062:主键冲突,或约束冲突
但是，以上操作有时是有风险的，最安全的做法就是重新构建主从。把握一个原则,一切以主库为主.

6.3 防止从库写入

（1）在从库设置只读
oldguo[(none)]>show variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.01 sec)
注意：只会影响到普通用户，对管理员用户无效。

（2）加中间件。
读写分离

扩展：
pt-xxx 关于主从复制 的过程
检查主从数据一致性：
实现主从数据同步

7、主从延时 ※※※※※

7.1 什么是主从延时？

主库做的事，从库很久才执行。

7.2 主从延时的现象

（1）最直观：主库做变更，从库看数据状态
（2）Seconds_Behind_Master: 0（只能证明，有或者没有）
（3）计算日志的差异 ※※※※※

7.3 主从延时的原因

7.3.1 外部因素

网络、硬件、版本差异、参数差异cd

7.3.2 内部因素

1>主库:
（1）二进制日志方面

二进制日志落地不及时
解决方案:
    sync_binlog=1
    可以将binlog单独存放高性能存储中

（2）Dump_T(默认是串行工作模式)

主库的事务量大
主库发生大事务
    解决方案:  
    1>GTID 模式
    2>双一的保证

如何监控:

主库: show master status;

从库: show slave status \G
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 484    

2> 从库:
（1） IO 线程方面

relaylog写入 
    解决方案:
    可以将relaylog单独存放高性能存储中
    | relay_log_basename        | /data/3308/data/db01-relay-bin       |
    | relay_log_index           | /data/3308/data/db01-relay-bin.index |

（2）SQL线程方面(只有一个 ,串行回放) *****

默认SQL线程,只能逐条的回放SQL
事务并发高
大事务 
5.6 版本 加入了多SQL复制 
    按照库(database)级别,进行并发回放SQL
    slave_parallel_workers=16
    slave_parallel_type=DATABASE 
    
5.7 版本 进行了多SQL复制加强(MTS)
    真正按照事务级别,实现了多SQL线程回放
    slave_parallel_workers=·
    slave_parallel_type=logical_clock 


注意: 必须依赖于GTID复制，并且binlog_format=row 

如何监控:

（1）监控取了多少日志 
    show slave status \G
    Master_Log_File: mysql-bin.000001
    Read_Master_Log_Pos: 1084
    
（2）回放了多少日志
    [root@db01 /data/3308/data]# cat relay-log.info 
    7
    ./db01-relay-bin.000003
    920
    mysql-bin.000001
    1084