数据库9

第八章 主从复制
1.介绍
通过二进制日志同步数据的工作模式

  1. 应用场景
    辅助备份
    读写分离
    高可用
    分布式数据库

  2. 扩展架构中间件
    3.1 读写分离
    Atlas (360)
    ProxySQL(percona)
    MaxScale (Mariadb)
    mysql router

3.2 高可用
MMM (google)
MHA (facebook taobao TMHA )
PXC,MGC
InnoDB Cluster(mysql 8.0强烈推荐)
MySQL cluster

3.3 分布式
Mycat (DBLE)
DRDS
PolarDB

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

  1. 主从复制前提(搭建过程)
    5.1 两个或以上数据库实例(主库,从库),两台机器不同server_id
    [root@db01 ~]# mysql -S /tmp/mysql3308.sock -e "select @@server_id"
    [root@db01 ~]# mysql -S /tmp/mysql3307.sock -e "select @@server_id"

5.2 主库需要开启二进制日志
[root@db01 /data/3307]# mysql -S /tmp/mysql3307.sock -e "select @@log_bin"

5.3 主库要有专用复制用户(replication slave)
mysql -S /tmp/mysql3307.sock -e "grant replication slave on . to repl@'10.0.0.%' identified by '123'"

5.5 从库需要追数据(mysqldump,xbk)
[root@db01 ~]# mysqldump -A --master-data=2 --single-transaction -S /tmp/mysql3307.sock >/tmp/full.sql
[root@db01 ~]# mysql -S /tmp/mysql3308.sock </tmp/full.sql

5.6 告诉从库复制信息(IP,port,user,password,binlog+pos)
change master to....
mysql -S /tmp/mysql3308.sock
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.000001',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;

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

5.7 干活(开启复制线程)
start slave;
[root@db01 ~]# mysql -S /tmp/mysql3308.sock -e "show slave status \G"|grep Running:

  1. 主从复制工作原理
    (1) change master to 命令 ,指定主库的连接信息和复制起点,会被记录到master.info文件中
    (2) start slave 开启 从库的 IO 和 SQL线程
    (3) IO线程读取master.info,请求连接主库,建立连接后,主库分配一个dump线程和从库IO记性通信.
    (5) 从库IO线程通过binlog位置点记录,向主库DUMP请求最新的binlog
    (6) 主库截取全新二进制日志事件,DUMP线程发送给从库IO线程
    (7) 在网络层面,二进制日志存储到TCP/IP缓存中,从库返回TCP/IP ACK确认
    (8) IO线程将接收到的日志,存储到 db01-relay-bin.000001,更新master.info位置点信息.
    (9) SQL线程读取relay-log.info,获取到上次执行到位置点,向后回放全新的日志
    (10)回放完成后,SQL再次更新relay-log.info
    (11) binlog dump实时监控binlog变化,一旦有新的,通知从库.
    (12) 从库会定期删除应用过的db01-relay-bin.

  2. 主从复制监控

mysql> show slave status \G
*************************** 1. row ***************************
主库有关信息
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

从库线程状态监控
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:

过滤复制有关配置
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:

从库回放的relay和主库binlog的对应关系
Exec_Master_Log_Pos: 444
Relay_Log_Space: 526

主从延时时间
Seconds_Behind_Master: 0

延时从库有关配置
SQL_Delay: 0
SQL_Remaining_Delay: NULL

GTID复制有关信息
Retrieved_Gtid_Set:
Executed_Gtid_Set:

  1. 主从复制故障分析及处理
    8.1 故障监控
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Last_IO_Errno: 0
    Last_IO_Error:
    Last_SQL_Errno: 0
    Last_SQL_Error:

8.2 原因
IO :
(1) 连接主库 (connecting)
连接信息有误
网络不通
防火墙
时间戳
用户没创建
skip_name_resolve

(2) 请求日志
日志位置点不对.
主库日志不完整.
解决办法: 重新搭建主从

reset master ;
停主库业务,等待从完全同步完
执行命令
恢复主库:
stop slave
change master to 000001 154
start slave;

(3) 落地日志
relaylog

处理方法:
停止从库线程
判断并截取缺失部分日志,恢复到从库
启动从库线程

SQL 线程
回放relay : 执行SQL语句
(1) relay无法访问
(2) 主从版本,SQL_Mode,参数不一致,系统配置不一致
(3) 需要创建的对象已经存在 , 要修改的对象不存在
原因1: 主从复制不一致,导致SQL故障
原因2: 从库提前写入
(5) 约束冲突
主键,唯一键

方法一:
stop slave;
set global sql_slave_skip_counter = 1;

将同步指针向下移动一个,如果多次不同步,可以重复操作。

start slave;

方法二:
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
常见错误代码:
1007:对象已存在
1032:无法执行DML
1062:主键冲突,或约束冲突

从库只读:
read_only
super_read_only

读写分离中间件

自己了解一下:
检查一致性
pt-table-sync
pt-table-checksum
检查延时:
pt-heartbeat

  1. 主从延时
    9.1延时监控
    确认有没有延时
    Seconds_Behind_Master: 0
    监控延时的日志,造成延时的位置点.
    Exec_Master_Log_Pos: 485
    Relay_Log_Space: 857

call xxxx() 存储过程 ----> 开发 -----> 做测试呢.

9.2 主从延时原因分析

主库:
(1) 二进制日志书写不及时
sync_binlog=1
(2) 主库IO有问题
binlog和数据分离,尽量ssd存储
(3) Classic replication中 主库可以并发执行事务,但是dump默认是串行工作的
高并发时,大事务多的时候,会延时很高 . 开启GTID+row ,可以并行传输日志.

(5) 业务繁忙时 在线DDL(表结构)
一般是,手工在主和从分别使用PT工具执行DDL.

其他原因:
主库负载过大,从库太多,网络延时抖动等

从:
(1) relay-log写入
最好是单独存储到ssd上
(2) SQL线程回放慢
Classic replication中,SQL线程只有一个,只能串行回放relaylog.
高并发时,大事务多的时候,延时较严重.
5.6 出现了GTID 技术,可以执行多SQL线程,但是只能基于不同database才能.
5.7 开启GTID,出现了真正的并行SQL回放功能,MTS,基于事务级别并发回放.logical_clock模式

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

  1. 延时从库 (人为)
    10.1 作用?
    帮助我们解决数据库的逻辑损坏
    10.2 配置原理
    从库方面SQL线程,根据事件的时间戳顺延延时秒数,进行延时回放
    10.3 配置
    [root@db01 ~]# systemctl start mysqld3309
    [root@db01 ~]# mysqldump -A --master-data=2 --single-transaction -S /tmp/mysql3307.sock >/tmp/full.sql
    [root@db01 ~]# mysql -S /tmp/mysql3309.sock </tmp/full.sql
    vim /tmp/full.sql
    -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=485;
    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;
    start slave;

延时300秒配置:
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

10.5 延时从库修复逻辑故障
10.5.1 思路
(1)通过监控发现,drop database 操作
(2)停止从库SQL线程,停止误删除数据业务(挂维护页)
(3)手工模拟SQL线程工作
cat relay-log.info获得起点
分析relaylog日志内容,并drop之前的位置点.
(5) 恢复业务
导出故障库恢复到生产
直接将延时从替代原生产业务库

10.5.2 干活
(1) 准备数据 :
create database relaydb charset=utf8mb4;
use relaydb;
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
create table t2 (id int);
insert into t2 values(1),(2),(3);
commit;
create table t3 (id int);
insert into t3 values(1),(2),(3);
commit;

(2) 误删除 :
drop database relaydb;

(3) 停SQL线程:
mysql> stop slave sql_thread;

(5) 截取relaylog
起点:
[root@db01 ~]# cat /data/3309/data/relay-log.info
./db01-relay-bin.000002
482

终点:
show relaylog events in 'db01-relay-bin.000002';
db01-relay-bin.000002 | 3180 | Query | 7 | 3446 | drop database relaydb

mysqlbinlog --start-position=482 --stop-position=3180 /data/3309/data/db01-relay-bin.000002 >/tmp/relay.sql

(6) 3309恢复数据
mysql> source /tmp/relay.sql

(7) 解除3309从库身份
mysql> stop slave;
mysql> reset slave all;

  1. 过滤复制
    主库 :
    binlog_do_db
    binlog_ignore_db

从库:
replicate_do_db=world
replicate_do_db=test
replicate_ignore_db=

replicate_do_table=world.t1
replicate_ignore_table=

replicate_wild_do_table=world.t*
replicate_wild_ignore_table=

  1. 半同步复制(介绍)
    解决什么问题?
    插件功能.
    主从数据一致性问题
    和传统复制不同的点是: 从库IO收到日志,并将其写入relaylog文件后,发送一个ACK确认给主库ACK_reciver
    如果从库10秒没有回应ACK,主库就会将复制切换为传统复制

PXC percona xtradb cluster
MGR MySQL Group Replication

  1. GTID复制
    (1) 清理环境
    pkill mysqld
    \rm -rf /data/mysql/data/*
    \rm -rf /data/binlog/*

(2) 准备配置文件
主库db01:
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql/
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=51
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db01 [\d]>
EOF

slave1(db02):
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=52
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db02 [\d]>
EOF

slave2(db03):
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=53
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db03 [\d]>
EOF
(3) 初始化数据
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data
(5) 启动数据库
/etc/init.d/mysqld start
(6) 构建主从:
master:51
slave:52,53

51:
grant replication slave on . to repl@'10.0.0.%' identified by '123';

52\53:
change master to
master_host='10.0.0.51',
master_user='repl',
master_password='123' ,
MASTER_AUTO_POSITION=1;

start slave;

©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 211,884评论 6 492
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 90,347评论 3 385
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 157,435评论 0 348
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 56,509评论 1 284
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 65,611评论 6 386
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 49,837评论 1 290
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 38,987评论 3 408
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 37,730评论 0 267
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 44,194评论 1 303
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,525评论 2 327
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,664评论 1 340
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 34,334评论 4 330
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 39,944评论 3 313
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,764评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,997评论 1 266
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 46,389评论 2 360
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 43,554评论 2 349

推荐阅读更多精彩内容