MySQL主从复制
MySQL主从复制基础
高可用架构方案：
负载均衡：有一定的高可用性
LVS Nginx
主备：有高可用性，但是需要切换===》单活
KA（keepalived） MHA MMM
真正高可用（多活系统）：
NDB Cluster Oracle RAC Sysbase cluster InnoDB Cluster（MGR） PXC MGC
主从复制简介
1、基于二进制日志复制的
2、主库的修改操作会记录二进制日志
3、从库会请求新的二进制日志并回放,最终达到主从数据同步
4、主从复制核心功能:
辅助备份,处理物理损坏
扩展新型的架构:高可用,高性能,分布式架构等
主从复制前提：
1、两台以上mysql实例 ,server_id,server_uuid不同
2、主库开启二进制日志
3、专用的复制用户
4、保证主从开启之前的某个时间点,从库数据是和主库一致(补课)
5、告知从库,复制user,passwd,IP port,以及复制起点(change master to)
6、线程(三个):Dump thread IO thread SQL thread 开启(start slave)
实操过程
mkdir -p /data/{3307,3308,3309}/data
mv /etc/my.cnf /etc/my.cnf.bak
初始化数据
mysqld --initialize-insecure --user=mysql --basedir=/app/mysql --datadir=/data/3307/data
mysqld --initialize-insecure --user=mysql --basedir=/app/mysql --datadir=/data/3308/data
mysqld --initialize-insecure --user=mysql --basedir=/app/mysql --datadir=/data/3309/data
cp /etc/systemd/system/mysqld.service /etc/systemd/system/mysqld3307.service
cp /etc/systemd/system/mysqld.service /etc/systemd/system/mysqld3308.service
cp /etc/systemd/system/mysqld.service /etc/systemd/system/mysqld3309.service
vim mysqld3307.service
vim mysqld3308.service
vim mysqld3309.service
ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
开启所有节点
systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309
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;"
主库创建复制用户
grant replication slave on . to repl@'192.168.63.%' identified by '123456';
全备主库
mysqldump -S /data/3307/mysql.sock -A -E --triggers --master-data=2 --single-transaction >/tools/full.sql
恢复到从库
set sql_log_bin=0;
source /tools/full.sql;
从库change master to
help change master to
CHANGE MASTER TO
MASTER_HOST='192.168.157.120',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='oldboy123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1524;
从库开启主从复制
start slave;
监控主从复制状态
show slave status \G
主从复制原理
主从复制涉及到的文件
主库：
二进制日志（binlog）
从库：
中继日志（relaylog）
master.info（主库信息）
relay-log.info（中继日志信息）
主从复制涉及到的线程
主库：
Binlog_Dump_Thread
从库：
Slave_IO_Thread
Slave_SQL_Thread
主从复制原理描述：
1、change master to 时，ip pot user password binlog position写入到master.info进行记录
2、start slave 时，从库会启动IO线程和SQL线程
3、IO_T，读取master.info信息，获取主库信息连接主库
4、主库会生成一个binlog DUMP线程，来响应从库
5、IO_T根据master.info记录的binlog文件名和position号，请求主库DUMP最新日志
6、DUMP线程检查主库的binlog日志，如果有新的，TP(传送)给从从库的IO_T
7、IO_T将收到的日志存储到了TCP/IP 缓存，立即返回ACK给主库 ，主库工作完成
8、IO_T将缓存中的数据，存储到relay-log日志文件,更新master.info文件binlog 文件名和postion，IO_T工作完成
9、SQL_T读取relay-log.info文件，获取到上次执行到的relay-log的位置，作为起点，回放relay-log
10、SQL_T回放完成之后，会更新relay-log.info文件。
11、relay-log会有自动清理的功能。
细节：
1.主库一旦有新的日志生成，会发送“信号”给binlog dump ，IO线程再请求
主从故障分析
IO线程故障：
1、IP、端口、用户、密码、防火墙、网络、连接数上限
查找原因：
模拟从库连接方式查看报错信息
mysql -urepl -p123456 -h 192.168.63.128 -P 3307
2、二进制日志损坏或丢失
处理方法：
stop slave;
reset slave all;
重新搭建从库（生产环境）
然后重新change master to
start slave;
SQL线程故障：
sql线程功能：
1）读写relay-log.info
2）relay-log损坏,断节,找不到
3）接收到的SQL无法执行
导致sql线程故障原因分析
1、版本差异，参数设定不同，比如：数据类型的差异，SQL_MODE影响
2、要创建的数据库对象,已经存在
3、要删除或修改的对象不存在
4、DML语句不符合表定义及约束时.
归根揭底的原因都是由于从库发生了写入操作.
Last_SQL_Error: Error 'Can't create database 'db'; database exists' on query. Default database: 'db'. Query: 'create database db'
处理方法（以从库为核心的处理方案）
方法一:
stop slave;
set global sql_slave_skip_counter = 1;
将同步指针向下移动一个，如果多次不同步，可以重复操作。
start slave;
方法二：
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
常见错误代码:
1007:对象已存在
1032:无法执行DML
1062:主键冲突,或约束冲突
但是，以上操作有时是有风险的，最安全的做法就是重新构建主从。把握一个原则,一切以主库为主.
一劳永逸的方法
1）可以设置从库只读.
show variables like '%read_only%';
注意：
只会影响到普通用户，对管理员用户无效。
2）加中间件
读写分离。
主从延时
主库做了修改，从库比较长时间才能追上
外在因素
网络
主从硬件差异较大
版本差异
参数因素
主库
1）二进制日志写入不及时
select @@sync_binlog;
2）CR的主从复制中,binlog_dump线程,事件为单元,串行传送二进制日志(5.6 5.5)
a、主库并发事务量大,主库可以并行,传送时是串行
b、主库发生了大事务,由于是串行传送,会产生阻塞后续的事务.
解决方案:
1、5.6 开始,开启GTID,实现了GC(group commit)机制,可以并行传输日志给从库IO
2、5.7 开始,不开启GTID,会自动维护匿名的GTID,也能实现GC,我们建议还是人为开启GTID
3、大事务拆成多个小事务,可以有效的减少主从延时.
从库
SQL线程导致的主从延时
在CR复制情况下: 从库默认情况下只有一个SQL,只能串行回放事务SQL
1、主库如果并发事务量较大,从库只能串行回放
2、主库发生了大事务,会阻塞后续的所有的事务的运行
解决方案:
1、5.6 版本开启GTID之后,加入了SQL多线程的特性,但是只能针对不同库(database)下的事务进行并发回放.
2、5.7 版本开始GTID之后,在SQL方面,提供了基于逻辑时钟(logical_clock),binlog加入了seq_no机制,
真正实现了基于事务级别的并发回放,这种技术我们把它称之为MTS(enhanced multi-threaded slave).
3、大事务拆成多个小事务,可以有效的减少主从延时.
[https://dev.mysql.com/worklog/task/?id=6314]
MySQL主从复制高级进阶
延时从库
是我们人为配置的一种特殊从库.人为配置从库和主库延时N小时.
为什么要有延时从库
延时从库是单独的，不对外提供服务，只为了更快的恢复数据
配置延时从库
SQL线程延时:数据已经写入relaylog中了,SQL线程"慢点"运行
一般企业建议3-6小时,具体看公司运维人员对于故障的反应时间
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
延时从库故障恢复思路
1主1从,从库延时5分钟,主库误删除1个库
1、5分钟之内 侦测到误删除操作
2、停从库SQL线程
3、截取relaylog
起点 :停止SQL线程时,relay最后应用位置
终点:误删除之前的position(GTID)
4、恢复截取的日志到从库
5、从库身份解除,替代主库工作
故障模拟及恢复
1.主库数据操作
create database relay charset utf8;
use relay;
create table t1 (id int);
insert into t1 values(1);
drop database relay;
2. 停止从库SQL线程
stop slave sql_thread;
3. 找relaylog的截取起点和终点
起点:
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 482
终点:
show relaylog events in '192-relay-bin.000002'
192-relay-bin.000002 | 1142 | Query | 7 | 1524 | drop database relay
mysqlbinlog --start-position=482 --stop-position=1142 /data/3308/data/192-relay-bin.000002>/tools/relay.sql
从库恢复数据
set sql_log_bin=0;
source /tools/relay.sql
从库身份解除
stop slave;
reset slave all;
还原环境
删从库多出的库
drop database relay;
确认主库二进制日志位置
show master status;
从库
stop slave;
reset slave all;
CHANGE MASTER TO
MASTER_HOST='192.168.63.128',
MASTER_PORT=3307,
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1524;
半同步复制（了解）
工作原理
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';
过滤复制
主库
show master status;
Binlog_Do_DB
Binlog_Ignore_DB
从库
show slave status\G
Replicate_Do_DB:
Replicate_Ignore_DB:
实现过程
mysqldump -S /data/3307/mysql.sock -A --master-data=2 --single-transaction -R --triggers >/backup/full.sql
vim /backup/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;
mysql -S /data/3309/mysql.sock
source /backup/full.sql
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=154,
MASTER_CONNECT_RETRY=10;
start slave;
vim /data/3309/my.cnf
replicate_do_db=ppt
replicate_do_db=word
systemctl restart mysqld3309
主库：
Master [(none)]>create database word;
Query OK, 1 row affected (0.00 sec)
Master [(none)]>create database ppt;
Query OK, 1 row affected (0.00 sec)
Master [(none)]>create database excel;
Query OK, 1 row affected (0.01 sec)
GTID复制
重要参数
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1 --记录主库的二进制日志，多主或级联必加参数
gtid-mode=on --启用gtid类型，否则就是普通的复制架构
enforce-gtid-consistency=true --强制GTID的一致性
log-slave-updates=1 --slave更新是否记入日志
配置过程
1、清理环境
rm -fr /data/mysql/*
mkdir -p /data/mysql/data
mkdir -p /data/mysql/binlog
chown -R mysql.mysql /data/*
2、准备配置文件
mv /etc/my.cnf /etc/my.cnf.bak
主库db01：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/data/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=/data/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=/data/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=/app/mysql --datadir=/data/mysql/data
4、启动mysql
/etc/init.d/mysqld start
5、构建主从
由于是新环境，不需要再备份恢复了
master:128
slave:131,132
128:
grant replication slave on . to repl@'192.168.63.%' identified by '123';
131\132:
change master to
master_host='192.168.63.128',
master_user='repl',
master_password='123' ,
MASTER_AUTO_POSITION=1;
start slave;
GTID复制和普通复制的区别
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_host='10.0.0.51',
master_user='repl',
master_password='123' ,
MASTER_AUTO_POSITION=1;
start slave;
1）在主从复制环境中，主库发生过的事务，在全局都是由唯一GTID记录的，更方便Failover
2）额外功能参数（3个）
3）change master to 的时候不再需要binlog 文件名和position号,MASTER_AUTO_POSITION=1;
4）在复制过程中，从库不再依赖master.info文件，而是直接读取最后一个relaylog的 GTID号
5）mysqldump备份时，默认会将备份中包含的事务操作，以以下方式
SET @@GLOBAL.GTID_PURGED='8c49d7ec-7e78-11e8-9638-000c29ca725d:1';
告诉从库，我的备份中已经有以上事务，你就不用运行了，直接从下一个GTID开始请求binlog就行。
GTID从库误写入操作处理
查看监控信息:
Last_SQL_Error: Error 'Can't create database 'oldboy'; database exists' on query. Default database: 'oldboy'. Query: 'create database oldboy'
Retrieved_Gtid_Set: 71bfa52e-4aae-11e9-ab8c-000c293b577e:1-3
Executed_Gtid_Set: 71bfa52e-4aae-11e9-ab8c-000c293b577e:1-2,
7ca4a2b7-4aae-11e9-859d-000c298720f6:1
注入空事物的方法：
stop slave;
set gtid_next='99279e1e-61b7-11e9-a9fc-000c2928f5dd:3';
begin;commit;
set gtid_next='AUTOMATIC';
start slave;
这里的xxxxx:N 也就是你的slave sql thread报错的GTID，或者说是你想要跳过的GTID。
最好的解决方案：重新构建主从环境
本文来自博客园，作者：霸都运维，转载请注明原文链接：https://www.cnblogs.com/wangjie20200529/p/14414569.html
