MySQL主从复制基础day12

一、主从复制基础

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

99.9% 故障率:0.1% 3642460=525.6min
99.99% 故障率:0.01%3642460=52.56min
99.999% 故障率:0.001%36424*60=5.256min

1.企业级高可用方案

负载均衡：有一定的高可用性。
LVS Nginx
主备系统:有高可用性，但是需要切换，是单活的架构
KeepAlive,MHA(日本人研发),MMM，TMHA
真正高可用（多活系统):
MySQL NDB CLuster
Oracle RAC
Sysbase cluster
InnoDB Cluster(MGR 5.7.17),
PXC ***
MGC ***

2.主从复制简介 **

2.1 基于二进制日志复制的

2.2 主库的修改操作会记录二进制日志

2.3 从库会请求新的二进制日志并回收，最终达到主从数据同步

2.4 主从复制核心功能

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

3.主从复制的前提（主从复制的规划,实施过程）

3.1 至少2个数据库实例

3.2 主库要开启binlog,不同server_id,server_uuid

3.3 主库要有一个专门用作复制的用户（replication slave）

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

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

3.6需要专门的复制线程（start slave）

4.搭建主从复制

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

4.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"

4.3 主库创建复制用户

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

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

 mysqldump  -S /data/3307/mysql.sock -A  -R -E --triggers --master-data=2 --single-transaction --max-allowed-packet=128M   >/tmp/full.sql

[root@db01 ~]# mysql -S /data/3308/mysql.sock </tmp/fuul.sql 
[root@db01 ~]#
4.5 告知从库,用户名，密码,IP,port,自动复制的起点

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

# change master to  如果执行错误 记得stop slave

[root@db01 ~]# mysql -S /data/3308/mysql.sock
mysql> CHANGE MASTER TO
    ->   MASTER_HOST='10.0.0.51',
    ->   MASTER_USER='repl',
    ->   MASTER_PASSWORD='123',
    ->   MASTER_PORT=3307,
    ->   MASTER_LOG_FILE='mysql-bin.000003',
    ->   MASTER_LOG_POS=444,
    ->   MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql>   /重试次数
  
vim /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.0000011', MASTER_LOG_POS=444;

4.6 启动主从线程

[root@db01 ~]# mysql -S /data/3308/mysql.sock
oldguo[(none)]>start slave;

4.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 ~]#

4.8 简单排错过程

[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 ~]#

问题:

[root@db01 ~]# mysql -S /data/3308/mysql.sock
oldguo[(none)]>stop slave ;
oldguo[(none)]>reset slave all;
oldguo[(none)]> CHANGE MASTER TO xxxx
oldguo[(none)]>start slave;

5. 主从复制原理

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

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

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

主库:

Binlog_Dump_Thread(二进制日志投递线程)
[root@db01 /data/3308/data]# mysql -S /data/3307/mysql.sock -e "show processlist;"

从库:

[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|egrep "Running:" 
Slave_IO_Thread
Slave_SQL_Thread

6.主从复制的监控

从库
show slave status\G

过滤复制相关状态

mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep "Replicate_"

延时从库的状态信息

mysql -S /data/3308/mysql.sock  -e  "show slave status \G"|grep "Delay:"

6.1 监控Gtid复制状态信息

mysql -S /data/3308/mysql.sock  -e  "show slave status \G"|grep "GTID"

7主从复制故障

7.1 IO线程故障

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

以上问题:
Slave_IO_Running:Connecting
Last_IO_Error:xxxx
排查方法:
通过复制用户,手工连接主库,看报错信息。
修复:
stop slave
reset slave all
change master to
start slave

(3) 请求日志
master.info 复制起点
主库:损坏，误删除等操作
二进制日志满了 清了主库的二进制日志 从库数据异常。
查看从库状态信息，主库信息，然后先停掉从库stop slave;重新清掉从库的信息reset slave all 接着再重新按着主库的起点和二进制日志号重新change master to 然后start slave启动从库。最后查一下状态，发现就好了。

(4)接收日志
relaylog损坏
修复:
stop slave
reset slave all
change master to
start slave
(5)更新master.info

7.2 SQL线程故障 *****

(1)relay.info
(2)回放relaylog中的日志 *****
SQL语句为什么会失败？
(1)语法,SQL_Mode
版本,sql_mode不一致
(2)DDL DML为什么会失败
create database /table 创建的对象已经存在了。
从库被提前写入了

处理方法（以从库为核心的处理方案):
方法一:
stop slave;
set global sql_slave_skip_counter=1
#将同步指针向下移动一个,如果多次不同步，可以重复操作。
start slave;

方法二:

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

常见错误代码:
1007:对象已存在
1032:无法执行DML
1062:主键冲突，或约束冲突
但是，以上操作有时是有风险的，最安全的做法就是重新构建主存，把握一个原则，一切以主库为主。

7.3 防止从库写入

(1)可以设置从库只读参数

show variables like "%read_only%";

mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | OFF | 普通用户
| super_read_only | OFF | 对root用户
| transaction_read_only | OFF |
| tx_read_only | OFF |
+-----------------------+-------+
5 rows in set (0.01 sec)

（2）加中间件
读写分离

自己扩展 pt-xxx关于主从方面的工具
检查主从数据一致性
实现主从数据同步

8主从延时 *****

8.1 什么是主从延时

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

8.2 主从延时的现象

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

8.3 主从延时的原因

8.3.1外部因素

网络
硬件
主库繁忙
版本差异
参数因素

8.3.2 内部因素

主库:
（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

从库

        （1）IO线程方面
                 relaylog写入
                 解决方案:

relay_log_basename        | /data/3307/data/db01-relay-bin       |
| relay_log_index           | /data/3307/data/db01-relay-bin.index
relay_log_purge 自动做清理

SQL线程方面（只有一个,串行回放）
默认SQL线程,只能逐条的回放SQL。
事务并发高
大事务

5.6版本 加入了多SQL复制
按照库（database）级别，进行并发回放SQL。
slave_parallel_workers=16
slave_parallel_type=DTATBASE

5.7版本 进行了多SQL复制加强（MTS）
真正按照事务级别，实现了多SQL线程回放。
slave_parallel_workers=16
slave_parallel_type=logical_clock
注意:必须依赖于GTID复制，并且binlog_format=row

如何监控:
(1)监控取了多少日志

show slave status\G


Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 444

6.主从基础小结

6.1主从前提

6.2主从原理

6.3主从监控

show processlist
show slave status\G
master.info
relay.info

6.4主从故障

IO
连接
binlog
SQL
从库写入
DML,insert,update,delete

6.5主从延时

主
dump 串行:GTID ，双一 (从库和主库执行的gtid号是完全一致的) 并行
show slave status\G
Master_Master_log_Pos:1084
show master status\G
从
SQL 串行 :MTS
已经拿过来的日志:
show slave status\G
Master_Log_File:mysql-bin.000001
Read_Master_Log_Pos:1084
已经执行过多:
./db01-relay-bin.000003
920
mysql-bin.000001
800

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