目录

1、主从复制简介

2、搭建主从复制流程

3、主从复制原理

3.1、主从复制过程涉及的文件

3.2、主从复制涉及的线程及原理

4、主从异常分析

4.1、IO线程异常

经典问题现象与解决

4.2、SQL线程异常

4.2.1、SQL功能

4.2.2、SQL线程问题及分析

一劳永逸的方法:

4.3、主从延时大

4.4、从库问题

 5、主从复制高级进阶

5.1、延时从库

5.1.1、延时从库配置

5.1.2、 延时从库应用（故障恢复思路）

主库逻辑损害恢复案例

6、从库过滤复制

1、主从复制简介

1.1. 基于二进制日志复制的
1.2. 主库的修改操作会记录二进制日志
1.3. 从库会请求新的二进制日志并回放,最终达到主从数据同步
1.4. 主从复制核心功能:
辅助备份,处理物理损坏                   
扩展新型的架构:高可用,高性能,分布式架构等

2、搭建主从复制流程

1、两个数据库实例、server_id、server_uuid
2、主库开启二进制日志
3、主库建立专用复制用户（replication slave）
4、主库全备（用于恢复备库）
5、change、master to、（用于指定主库的ip、port、user、password、复制起点（file、position）在全备中可以找到）
6、start slave 、开启复制线程（主库：binlog_dump_T,从库：IO_T、SQL_T）

开始搭建操作：

1、主库开启二进制库和server_id
    cat /etc/my.cnf
    [mysqld]
    server_id=1
    log-bin=/var/log/mysql/mysql-bin
2、主库建立专业复制用户（进入主数据库） 
    mysql> grant replication slave on *.* to master@'192.168.0.%' identified by '123456';
3、主库全备
    mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF >./full.sql
    找到全备结束的点
    -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000025', MASTER_LOG_POS=691237;
4、从库恢复（从库操作主库全备）
    mysql -uroot -p < full.sql
5、change master to （从库操作）
    mysql> help change master to; （查看帮助）
    CHANGE MASTER TO
      MASTER_HOST='source2.example.com',
      MASTER_USER='replication',
      MASTER_PASSWORD='password',
      MASTER_PORT=3306,
      MASTER_LOG_FILE='source2-bin.001',
      MASTER_LOG_POS=4,
      MASTER_CONNECT_RETRY=10;

      修改帮助语句：
     CHANGE MASTER TO
      MASTER_HOST='192.168.0.2',
      MASTER_USER='master',
      MASTER_PASSWORD='123456',
      MASTER_PORT=3306,
      MASTER_LOG_FILE='mysql-bin.000025',
      MASTER_LOG_POS=691237,
      MASTER_CONNECT_RETRY=10; 
6、开启从库复制进程
    mysql> start slave;

主从复制的状态：

1、状态查看命令：
    show slave status \G
1.1、主库相关信息：
    Master_Host: 192.168.0.2         （主库IP）
    Master_User: master              （主库赋予的用户）
    Master_Port: 3306                （主库端口）  
    Connect_Retry: 10                （网络不好时尝试链接主库次数）
    Master_Log_File: mysql-bin.000025 （复制的到二进制文件） 
    Read_Master_Log_Pos: 691532        （复制到的节点）    
1.2、从库的中继日志的状态
    Relay_Log_File: localhost-relay-bin.000002  
    Relay_Log_Pos: 615
1.3、从库复制的线程状态：
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
     
    Last_IO_Errno: 0
    Last_IO_Error:
    Last_SQL_Errno: 0
    Last_SQL_Error:
1.4、过滤复制有关状态
    Replicate_Do_DB:
    Replicate_Ignore_DB:
    Replicate_Do_Table:
    Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
    Replicate_Wild_Ignore_Table:
1.5、主从延时相关状态（非人为）
    Seconds_Behind_Master: 0
1.6、延时从库相关状态（人为）
    SQL_Delay: 0
    SQL_Remaining_Delay: NULL
1.7、GTID复制相关状态：
    Retrieved_Gtid_Set:
    Executed_Gtid_Set:
    Auto_Position: 0

3、主从复制原理

3.1、主从复制过程涉及的文件

库主：
    二进制日志文件：（binlog）
从库：
    中继日志文件（relaylog）
    master.info                 ===》主库信息记录日志
    relay-log.info              ===> 记录中继应用情况信息       

3.2、主从复制涉及的线程及原理

主:
DUMP THREAD
从:
IO  THREAD
SQL THREAD

原理描述

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线程再请求

4、主从异常分析

4.1、IO线程异常

可能性：
1.主库ip连错
2.主库port连错
3.链接主库的user，password错误

上面是初建时可能出现的问题

4.防火墙，网络问题 
5.主库或从库tcp链接上线
6.主库没启动

经典问题现象与解决

1、主库二进制文件缺失或损坏

现象：（上图）
解决：主要在从库中操作（重新搭建主从复制）
    1) mysql>stop slave;  (停slave)
    2) mysql>reset slave all;   (重置从库信息)
    3）mysql>show slave status \G  (查看)
    4) mysql> CHANGE MASTER TO
                MASTER_HOST='192.168.0.2',
                MASTER_USER='master',
                MASTER_PASSWORD='123456',
                MASTER_PORT=3306,
                MASTER_LOG_FILE='mysql-bin.000025',  (主库新的二进制文件名)
                MASTER_LOG_POS=691237,       （新的二进制节点show master status;可在主库看）
                MASTER_CONNECT_RETRY=10;
    
     5）开启从库复制进程
        mysql> start slave;

4.2、SQL线程异常

4.2.1、SQL功能

(1)读写relay-log.info 
(2)relay-log损坏,断节,找不到
(3)接收到的SQL无法执行

4.2.2、SQL线程问题及分析

1. 版本差异，参数设定不同，比如：数据类型的差异，SQL_MODE影响
2.要创建的数据库对象,已经存在
3.要删除或修改的对象不存在  
4.DML语句不符合表定义及约束时.  
归根揭底的原因都是由于从库发生了写入操作.
Last_SQL_Error: Error 'Can't create database 'db'; database exists' on query. Default database: 'db'. Query: 'create database db'


解决：最好重建主从（其他方法有风险）

一劳永逸的方法:

(1) 可以设置从库只读.
    mysql>show variables like '%read_only%';
注意：
只会影响到普通用户，对管理员用户无效。
(2)加中间件
读写分离。

4.3、主从延时大

外在因素：
    网络 
    主从硬件差异较大
    版本差异
    参数因素

主库问题：
    1）二进制文件写入不及时 （ select @@sync_binlog;）
    2）CR的主从复制中,binlog_dump线程,事件为单元,串行传送二进制日志(5.6 5.5)
        主库并发事务量大,主库可以并行,传送时是串行
        主库发生了大事务,由于是串行传送,会产生阻塞后续的事务
    
解决方案:
    1）5.6 开始,开启GTID,实现了GC(group commit)机制,可以并行传输日志给从库IO
    2）5.7 开始,不开启GTID,会自动维护匿名的GTID,也能实现GC,我们建议还是认为开启GTID
    3）大事务拆成多个小事务,可以有效的减少主从延时.

4.4、从库问题

SQL线程导致的主从延时
在CR复制情况下: 从库默认情况下只有一个SQL,只能串行回放事务SQL
1. 主库如果并发事务量较大,从库只能串行回放
2. 主库发生了大事务,会阻塞后续的所有的事务的运行

解决方案:
1. 5.6 版本开启GTID之后,加入了SQL多线程的特性,但是只能针对不同库(database)下的事务进行并发回放.
2. 5.7 版本开始GTID之后,在SQL方面,提供了基于逻辑时钟(logical_clock),binlog加入了seq_no机制,
真正实现了基于事务级别的并发回放,这种技术我们把它称之为MTS(enhanced multi-threaded slave).
3. 大事务拆成多个小事务,可以有效的减少主从延时

 5、主从复制高级进阶

5.1、延时从库

5.1.1、延时从库配置

SQL线程延时:数据已经写入relaylog中了,SQL线程"慢点"运行
一般企业建议3-6小时,具体看公司运维人员对于故障的反应时间（从库配置）

mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 300;  （秒的）
mysql>start slave;
查看：
mysql> show slave status \G
SQL_Delay: 300
SQL_Remaining_Delay: NULL

5.1.2、 延时从库应用（故障恢复思路）

1主1从,从库延时5分钟,主库误删除1个库
1. 5分钟之内 侦测到误删除操作
2. 停从库SQL线程
3. 截取relaylog
起点 :停止SQL线程时,relay最后应用位置
终点:误删除之前的position(GTID)
4. 恢复截取的日志到从库
5. 从库身份解除,替代主库工作

主库逻辑损害恢复案例

1.主库数据操作
    mysql>create database relay charset utf8;
    mysql>use relay
    mysql>create table t1 (id int);
    mysql>insert into t1 values(1);
    mysql>drop database relay;           (误操作)
2. 停止从库SQL线程
    mysql>stop slave sql_thread;
3. 找relaylog的截取起点和终点
起点:
    Relay_Log_File: db01-relay-bin.000002
    Relay_Log_Pos: 482
终点:
    show relaylog events in 'db01-relay-bin.000002'
    | db01-relay-bin.000002 | 1046 | Xid            |         7 |        2489 | COMMIT /* xid=144 */                  |
    | db01-relay-bin.000002 | 1077 | Anonymous_Gtid |         7 |        2554 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
    mysqlbinlog --start-position=482 --stop-position=1077  /data/3308/data/db01-relay-bin.000002>/tmp/relay.sql
4.从库恢复relaylog
    mysql>source /tmp/relay.sql
5.可以从库变主库、可以备份库写到主库中（都需要重新搭建主从节点）

6、从库过滤复制

主库也可以配置过滤但不建议；

从库查看和配置：

查看：
show slave status\G
Replicate_Do_DB: 
Replicate_Ignore_DB: 

配置在my.cnf
白名单配置：
    riplicate_do_db=world
    riplicate_do_db=sk
黑名单配置：
    replicate_ignore_db=mydql
    replicate_ignore_db=sk

重启从数据库