2019-06-27 Day11~手撕MySQL主从复制

徒手搭建主从复制

1. 介绍

依赖于二进制日志的，“实时”备份的一个多节点架构。

2. 主从复制的前提（搭建主从复制）

2.1 至少两个实例

2.2 不同的server_id

2.3 主库需要开启二进制日志

2.4 主库需要授权一个专用复制用户

2.5 主库数据备份

2.6 开启专用复制线程

3. 搭建主从复制

3.1 启动多实例

多实例的环境准备请参考Day02文章

systemctl start mysqld3307

systemctl start mysqld3308

systemctl start mysqld3309
3.2 检查server_id
mysql -S /data/3307/mysql.sock -e "select @@server_id;"

mysql -S /data/3308/mysql.sock -e "select @@server_id;"

mysql -S /data/3309/mysql.sock -e "select @@server_id;"
3.3 检查3307（主库）的二进制日志情况
[root@db01 /data]# 
mysql -S /data/3307/mysql.sock -e "show variables like '%log_bin%';"
+---------------------------------+----------------------------+
| Variable_name                   | Value                      |
+---------------------------------+----------------------------+
| log_bin                         | ON                         |
| log_bin_basename                | /data/3307/mysql-bin       |
| log_bin_index                   | /data/3307/mysql-bin.index |
| log_bin_trust_function_creators | OFF                        |
| log_bin_use_v1_row_events       | OFF                        |
| sql_log_bin                     | ON                         |
+---------------------------------+----------------------------+
3.4 主库创建复制用户
[root@db01 ~]# mysql -S /data/3307/mysql.sock

mysql> grant replication slave on *.* to repl@'10.0.0.%' identified by '123';
3.5 进行主库数据备份
[root@db01 ~]# mysqldump -S /data/3307/mysql.sock  -A --master-data=2 -R -E --triggers --single-transaction >/tmp/full.sql
3.6 恢复数据到从库(3308)
[root@db01 /data]# mysql -S /data/3308/mysql.sock

mysql> set sql_log_bin=0;
mysql> source /tmp/full.sql;
3.7 告知从库复制的信息

mysql> help change master to

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

#然后运行
3.8 启动复制线程
mysql> start slave;
3.9 如果 change master to 信息输入错误，怎么办？
#删除主从信息
mysql> stop slave;

mysql> reset slave all;

#重新执行 3.7
3.10 查看主从信息
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.51
                  Master_User: repl
                  Master_Port: 3307
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 444
               Relay_Log_File: db01-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             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: 444
              Relay_Log_Space: 526
              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: 0
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: 7
                  Master_UUID: 01cce940-8e8f-11e9-badd-000c29c21dbb
             Master_Info_File: /data/3308/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

4. 主从复制工作过程

4.1 名词认识

文件

主库：binlog

从库：
relay-log 中继日志
master.info 主库信息文件
relay-log.info 中继日志应用信息

线程

主库： binlog_dump_thread 二进制日志投递线程

[root@db01 /data]# 
mysql -S /data/3307/mysql.sock -e "show processlist"

+----+------+------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host       | db   | Command     | Time | State                                                         | Info             |
+----+------+------------+------+-------------+------+---------------------------------------------------------------+------------------+
|  7 | repl | db01:39534 | NULL | Binlog Dump | 1894 | Master has sent all binlog to slave; waiting for more updates | NULL             |
|  8 | root | localhost  | NULL | Query       |    0 | starting                                                      | show processlist |
+----+------+------------+------+-------------+------+---------------------------------------------------------------+------------------+

从库：
IO_Thread 从库IO线程，请求和接受binlog
SQL_Thread 从库的SQL线程，回放日志

4.2 工作原理
image.png

（1）从库执行 change master to 语句，会立即将主库信息（ip，port，user，password等）记录到master.info中。
（2）从库执行 start slave 语句，会立即生成IO_T和SQL_T。
（3）IO_T 读取 master.info 文件，获取主库信息
（4）IO_T 连接主库，主库会立即分配一个 DUMP_T，进行交互
（5）IO_T 根据 master.info 的二进制日志信息，向主库的DUMP_T进行请求最新的binlog
（6）主库DUMP_T 经过查询，如果发现有新的，截取并返回给从库的IO_T
（7）从库IO_T会收到binlog，存储在TCP/IP缓存中，在网络底层返回ACK
（8）从库IO_T会更新master.info，重置binlog位置点信息
（9）从库IO_T会将binlog写入到relay-log中
（10）从库SQL_T，读取relay-log.info文件，获取上次执行过的位置点
（11）SQL_T按照位置点往下执行relay-log日志
（12）执行完成后，重新更新relay-log.info
（13）relay-log定期自动清理的功能

细节：
主库发生了新的信息修改，更新二进制日志完成后，会发送一个“信号”给Dump_T，Dump_T会通知给IO_T线程。

5. 主从复制监控及故障处理

5.1 主从监控

主库：

mysql> mysql> show processlist;
+----+------+------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host       | db   | Command     | Time | State                                                         | Info             |
+----+------+------------+------+-------------+------+---------------------------------------------------------------+------------------+
|  7 | repl | db01:39534 | NULL | Binlog Dump | 5831 | Master has sent all binlog to slave; waiting for more updates | NULL             |
|  9 | root | localhost  | NULL | Query       |    0 | starting                                                      | show processlist |
+----+------+------------+------+-------------+------+---------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

从库：

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.51
                  Master_User: repl
                  Master_Port: 3307
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 444
               Relay_Log_File: db01-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             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: 444
              Relay_Log_Space: 526
              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: 0
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: 7
                  Master_UUID: 01cce940-8e8f-11e9-badd-000c29c21dbb
             Master_Info_File: /data/3308/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

主库的信息：master.info

Master_Host: 10.0.0.51               #主库的IP
Master_User: repl                    #复制用户名
Master_Port: 3307                    #主库的端口
Connect_Retry: 10                    #断连之后重试次数 
Master_Log_File: mysql-bin.000002    #已经获取到的binlog的文件名
Read_Master_Log_Pos: 444             #已经获取到的binlog的位置号

从库的relaylog的信息：relay-log.info

Relay_Log_File: db01-relay-bin.000002    
#从库已经运行过的relaylog的文件名

Relay_Log_Pos: 320   
 #从库已经运行过的relaylog的位置号

从库复制线程工作状态：

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:

从库延时主库的时间：

Seconds_Behind_Master: 0    #从库延时主库的时间（秒为单位）

从库线程报错详细信息：

Last_IO_Errno: 0     #IO报错的号码
Last_IO_Error:       #IO报错的具体信息
Last_SQL_Errno: 0    #SQL报错的号码
Last_SQL_Error:      #SQL线程报错的具体原因

延时从库

SQL_Delay: 0                #延时从库设定的时间，防止误操作
SQL_Remaining_Delay: NULL   #延时操作的剩余时间

GTID复制信息

Retrieved_Gtid_Set:     #接收到的GTID的个数
Executed_Gtid_Set:     #执行的GTID的个数

5.2 主从故障的分析及处理

查看相关的状态：

从库复制线程的工作状态

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

从库线程报错详细信息：

Last_IO_Errno: 0     #IO报错的号码
Last_IO_Error:       #IO报错的具体信息
Last_SQL_Errno: 0    #SQL报错的号码
Last_SQL_Error:      #SQL线程报错的具体原因
5.2.1 IO线程故障

（1）连接主库连接不上

connecting 相关报错信息

###### 原因：
网络不通
防火墙
IP不对
port不对
用户不对
密码不对
skip_name_resolve
连接数上限

##### 处理思路：
手工连接查看报错信息：
mysql -urepl -pxxx -h10.0.0.51 -P3307

如何处理？
stop slave;
reset slave all;
change master to
start slave;

（2）请求新的binlog

IO  线程  No 的状态分析

原因一：日志名不对
    从库信息：
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 444
    对比备份的位置号。

原因二：日志损坏，日志不连续
演示：
主库：
mysql -S /data/3307/mysql.sock
mysql> flush logs;
mysql> flush logs;
mysql> flush logs;
mysql> flush logs;
mysql> flush logs;
mysql> reset master;

mysql  -S /data/3307/mysql.sock
mysql> create database dd;
mysql> create database dd1;
mysql> create database dd2;

从库处理：
mysql  -S /data/3308/mysql.sock 
mysql> stop slave;
mysql> reset slave all ;
CHANGE MASTER TO
  MASTER_HOST='10.0.0.51',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154,
  MASTER_CONNECT_RETRY=10;
start slave;

（3）写relaylog
（4）更新master.info
（5）server_id重复

5.2.2 SQL线程故障
原因一：

读 relay-log.info
读 relay-log，并执行日志
更新 relay-log.info

以上文件损坏，最好是重新构建主从


原因二：

为什么一条SQL语句执行不成功？
1. 主从数据库版本差异较大
2. 主从数据库配置参数不一致（例如：sql_mode等）
3. 想要创建的对象已经存在
4. 想要删除或修改的对象不存在
5. 主键冲突
6. DML语句不符合表定义及约束时
归根结底是从库进行了写入。


解决方法一：（不推荐使用）
stop slave; 
set global sql_slave_skip_counter = 1;

/etc/my.cnf
slave-skip-errors = 1032,1062,1007

解决方法二：万全的解决

1. 做成从库只读
2. 通过中间件，做成读写分离的架构

6. 主从延时原因分析

从库延时主库的时间：

Seconds_Behind_Master: 0    #从库延时主库的时间（秒为单位）
6.1 主库方面：

日志写入不及时

mysql> select @@sync_binlog;
+---------------+
| @@sync_binlog |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

主库并发业务较高

"分布式" 架构
从库太多
级联主从

对于Classic Replication：
主库是有能力并发运行事务的，但是在Dump_T传输日志的时候，是以事件为单元传输日志的，
所以导致事务的传输工作是串行方式的，这时在主库TPS很高时，会产生比较大的主从延时。

怎么处理：
group commit
从 5.6 开始加入了GTID，在复制时，可以将原来串行的传输模式变成并行的。
除了GTID支持，还需要双一保证。

6.2 从库方面

Classic Replication
SQL 线程只有一个，所以说只能串行执行relay的事务。
怎么解决？
多加几个SQL线程
再5.6中出现了database级别的多线程SQL
只能针对不同库下的事务，才能并发
到5.7版本加入了MTS，才真正实现了事务级别的并发SQL线程

7. 延时从库

7.1 数据损坏

物理损坏
逻辑损坏

对于传统的主从复制，比较擅长处理物理损坏。

7.2 设计理念

对SQL线程进行延时设置

7.3 延时时间

生产环境一般是 3 - 6 小时

7.4 如何设置从库延时

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

7.5 如何使用延时从库

7.5.1 思路

模拟故障：

mysql -S /data/3307/mysql.sock
create database delay charset utf8mb4;
use delay;
create table t1(id int);
insert into t1 values (1),(2),(3);
commit;
drop database delay;

发现问题：

1. 停止SQL线程，停止主库业务
2. 模拟SQL线程手工恢复relaylog到drop之前的位置点
3. 截取relaylog日志，找到起点（relay-log.info）和终点（drop 操作）
4. 恢复截取的日志，验证数据可用性

开始处理：

1. 停用从库的SQL线程

mysql -S /data/3308/mysql.sock 
mysql> stop slave sql_thread;

2. 找 relaylog 的起点和终点

Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 320

3. 截取日志 

[root@db01 ~]# 
mysqlbinlog --start-position=385 --stop-position=992 /data/3308/data/db01-relay-bin.000002 >/tmp/relay.sql


4. 恢复 

mysql -S /data/3308/mysql.sock 
set sql_log_bin=0;
source /tmp/relay.sql
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 204,921评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 87,635评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,393评论 0赞 338
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,836评论 1赞 277
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,833评论 5赞 368
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,685评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,043评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,694评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 42,671评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,670评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,779评论 1赞 332
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,424评论 4赞 321
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,027评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,984评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,214评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,108评论 2赞 351
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,517评论 2赞 343