1. 介绍
通过二进制日志同步数据的工作模式
2. 应用场景
辅助备份
读写分离
高可用
分布式数据库
3. 扩展架构中间件
- 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
5. 主从复制前提(搭建过程)
-
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;
-
5.7 开启复制线程
start slave; [root@db01 ~]# mysql -S /tmp/mysql3308.sock -e "show slave status \G"|grep Running:
6. 主从复制工作原理
(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.
7. 主从复制监控
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:
8. 主从复制故障分析及处理
-
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-heartbeat pt-sync pt-checksum
9. 主从延时
-
9.1 延时监控
确认有没有延时 Seconds_Behind_Master: 0 监控延时的日志,造成延时的位置点. Exec_Master_Log_Pos: 485 Relay_Log_Space: 857
-
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模式
10. 延时从库 (人为)
-
作用:
帮助我们解决数据库的逻辑损坏
-
配置原理:
从库方面SQL线程,根据事件的时间戳顺延延时秒数,进行延时回放
配置 [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秒配置: stop slave; CHANGE MASTER TO MASTER_DELAY = 300; start slave; show slave status \G
-
延时从库修复逻辑故障
思路
(1)通过监控发现,drop database 操作
(2)停止从库SQL线程,停止误删除数据业务(挂维护页)
(3)手工模拟SQL线程工作
cat relay-log.info获得起点
分析relaylog日志内容,并drop之前的位置点.
(5) 恢复业务
导出故障库恢复到生产
直接将延时从替代原生产业务库
(1) 准备数据 :
create database relaydb charset=utf8mb4;
use relaydb;
create table t1 (id int);
insert into t1 values(1),(2),(3);
create table t2 (id int);
insert into t2 values(1),(2),(3);
create table t3 (id int);
insert into t3 values(1),(2),(3);
(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;
11. 过滤复制
主库 :
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=
12. 半同步复制(介绍)
解决什么问题?
插件功能.
主从数据一致性问题
和传统复制不同的点是: 从库IO收到日志,并将其写入relaylog文件后,发送一个ACK确认给主库ACK_reciver
如果从库10秒没有回应ACK,主库就会将复制切换为传统复制
PXC percona xtradb cluster
MGR MySQL Group Replication
- 工作原理:
1.主库执行新的事务,commit时,更新 show master status\G ,触发一个信号给
2.binlog dump 接收到主库的 show master status\G信息,通知从库日志更新了
3.从库IO线程请求新的二进制日志事件
4.主库会通过dump线程传送新的日志事件,给从库IO线程
5.从库IO线程接收到binlog日志,当日志写入到磁盘上的relaylog文件时,给主库ACK_receiver线程
6.ACK_receiver线程触发一个事件,告诉主库commit可以成功了
7.如果ACK达到了我们预设值的超时时间,半同步复制会切换为原始的异步复制.
-
配置
加载插件 主: INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; 从: INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; 查看是否加载成功: show plugins; 启动: 主: SET GLOBAL rpl_semi_sync_master_enabled = 1; 从: SET GLOBAL rpl_semi_sync_slave_enabled = 1; 重启从库上的IO线程 STOP SLAVE IO_THREAD; START SLAVE IO_THREAD; 查看是否在运行 主: show status like 'Rpl_semi_sync_master_status'; 从: show status like 'Rpl_semi_sync_slave_status';
13. 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;