6-27 MySQL 主从复制

上节回顾：

1.  mysqldump

-u -p  -S  -h  -P   

-A ， -B  ， 库  表

-R  --triggers  -E 

--master-data=2

--single-transaction

--set-gtid-purged=OFF 构建主从的备份千万不能加OFF，可以是ON或者AUTO 

--max-allowed-packet=

2. xtrabackup

innobackupex   

全备：

innobackupex --user --password --no-timestamp /data/backup/full

增量：

innobackupex --user --password --no-timestamp --incremental --incremental-basedir=/data/backup/full /data/backup/inc1

整理备份：

全备：

innobackupex --apply-log /data/backup/full

增量：

只有最后一次合并的增量不需要--redo-only,过程中所有备份整理都要加--redo-only。

innobackupex --apply-log --redo-only --incremental-dir=/data/backup/inc1  /data/backup/full

恢复备份：

innobackupex --copy-back /data/backup/full

3. 备份工具如何配合binlog应用

binlog截取最重要的是找到 起点和终点

周三下午两点 ，数据损坏，截取日志的思路

mysqldump :

起点：找到周二晚上全备脚本，找到change master to

终点：通过看events 或者 文件内容 找到故障点的位置

xtrabackup ：

起点：找到周二晚上增量备份。备份路径下xtrabackup_binlog_info

终点：通过看events 或者 文件内容 找到故障点的位置

4. 异构

操作系统版本不一样

逻辑备份

数据库软件不一样

MySQL  ---CSV->  MongoDB  , ES

MySQL  ---JSON->  MongoDB  , ES

========================================

主从复制

1. 介绍

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

2. 主从复制的前提

2.1 至少2个实例

2.2 不同的server_id

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

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

2.5 主库数据备份

2.6 开启专用复制线程

3. 搭建主从复制

3.1 准备多实例

略

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(主库)的二进制日志情况

mysql -S /data/3307/mysql.sock -e "show variables like '%log_bin%'"

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 ~]# mysql -S /data/3308/mysql.sock

mysql> set sql_log_bin=0;

mysql> source /tmp/full.sql;

3.7 告诉从库复制的信息

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.000001',

  MASTER_LOG_POS=444,

  MASTER_CONNECT_RETRY=10;

vim /tmp/full.sql

22 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=444;

3.8 启动复制线程

mysql> start slave;

3.9 如果 change master to 信息输入错误，咋办？

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=444,

  MASTER_CONNECT_RETRY=10;

mysql> start slave;

4. 主从复制工作过程（原理）

4.1  名词认识

文件：

主库：binlog

从库：

relay-log      中继日志

master.info    主库信息文件

relay-log.info  中继日志应用信息

线程：

主库：

binlog_dump_thread 二进制日志投递线程

mysql -S /data/3307/mysql.sock -e "show processlist"

从库:

IO_Thread :  从库IO线程 ：    请求和接收binlog

SQL_Thread:  从库的SQL线程 ： 回放日志

4.2 工作原理

（1）从库执行 change master to 语句，会立即将主库信息记录到master.info中

（2）从库执行 start slave语句，会立即生成IO_T和SQL_T

（3）IO_T 读取master.info文件，获取到主库信息

（4）IO_T 连接主库，主库会立即分配一个DUMP_T,进行交互

（5）IO_T 根据master.info binlog信息，向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按照位置点往下执行relaylog日志

（12）SQL_T执行完成后，重新更新relay-log.info

（13）relaylog定期自动清理的功能。

细节：

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

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

5.1 主从监控

主库：

show processlist;

Master has sent all binlog to slave; waiting for more updates

从库：

show slave status \G

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.000001

          Read_Master_Log_Pos: 444

              Relay_Log_File: db01-relay-bin.000002

                Relay_Log_Pos: 320

        Relay_Master_Log_File: mysql-bin.000001

            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: dd822ce8-9878-11e9-b99b-000c29099eb6

            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:


主库的信息（master.info）：  

Master_Host: 10.0.0.51                主库的IP

Master_User: repl   复制用户名

Master_Port: 3307   主库的端口

Connect_Retry: 10   断连之后重试次数

Master_Log_File: mysql-bin.000001    已经获取得到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

链接数上限

处理思路：

[root@db01 ~]# mysql -urepl -p123 -h 10.0.0.51 -P 3308  --->端口问题

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1130 (HY000): Host 'db01' is not allowed to connect to this MySQL server

[root@db01 ~]# mysql -urepl -p123 -h 10.0.0.52 -P 3307 

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.52' (110)

[root@db01 ~]# mysql -urepl -p1234 -h 10.0.0.51 -P 3307

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'repl'@'db01' (using password: YES)

[root@db01 ~]# mysql -urepl1 -p123 -h 10.0.0.51 -P 3307

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'repl1'@'db01' (using password: YES)

[root@db01 ~]#

如何处理？

stop slave;

reset slave all ;

change master to

start slave;

（2）请求新的binlog

IO线程No的状态分析：

原因一： 日志名不对

从库信息：

Master_Log_File: mysql-bin.000001

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> create database dd;

Query OK, 1 row affected (0.01 sec)

mysql> create database dd1;

Query OK, 1 row affected (0.00 sec)

mysql> create database dd2;

Query OK, 1 row affected (0.00 sec)

从库：       

Slave_IO_Running: No

Slave_SQL_Running: Yes

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'could not find next log; the first event 'mysql-bin.000001' at 154, the last event read from '/data/3307/mysql-bin.000006' at 154, the last byte read from '/data/3307/mysql-bin.000006' at 154.'

处理：

mysql -S /data/3308/mysql.sock

stop slave;

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

方法二：万全的解决

设置从库只读，防止写入

使用中间件做成读写分离的架构

6. 主从延时原因分析

从库延时主库的时间:

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

6.1 主库方面：

日志写入不及时

sync_binlog=1;

主库并发业务较高

“分布式”架构

从库太多

级联主从

对于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: 476

终点：

mysql> show relaylog events in 'db01-relay-bin.000002'

| db01-relay-bin.000002 | 1149 | Query          |        7 |        2036 | drop database delay 

3. 截取日志

[root@db01 ~]# mysqlbinlog --start-position=476 --stop-position=1149 /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阅读 212,332评论 6赞 493
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,508评论 3赞 385
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 157,812评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,607评论 1赞 284
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,728评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,919评论 1赞 290
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,071评论 3赞 410
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,802评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,256评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,576评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,712评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,389评论 4赞 332
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,032评论 3赞 316
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,798评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,026评论 1赞 266
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,473评论 2赞 360
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,606评论 2赞 350

推荐阅读更多精彩内容