第十一讲 MySQL主从复制

最新推荐文章于 2024-08-05 16:11:08 发布

暴走的Mine

最新推荐文章于 2024-08-05 16:11:08 发布

阅读量642

点赞数

分类专栏： # MySQL篇文章标签： mysql 数据库 linux

本文链接：https://blog.csdn.net/u011328843/article/details/125585218

版权

MySQL篇专栏收录该内容

13 篇文章 2 订阅

订阅专栏

1 典型主从架构

一主多从

特点：读写分离，应用于读取压力大的场景，将要求实时性不高的请求分发到多个从库，降低主库的压力

缺陷：存在主从延时问题

如何解决：优化网络环境，MySQL5.5~5.6使用半同步复制，MySQL5.7使用增强半同步复制

多级复制

解决了主库I/O和网络压力大的问题，多级复制可以减小主库的压力，主库只需要向另一个主库发送binlog日志

双主

适用主从切换的场景，通过双主复制架构避免了重复搭建从库的麻烦

主库Master1和Master互为主从，所有Web Client的写请求都访问主库Master1或Master2

多源复制

多源(Multi-Source)复制架构适用于复杂的业务需求，既可以支撑OLTP(联机事务处理),也可以满足OLAP(联机分析处理)

2 主从复制原理

mysql支持两种复制方式，一是基于行的复制，二是基于语句的复制通过主库记录的二进制日志并在从库进行异步复制，可能会产出延时

3 主从复制搭建

3.1 异步复制

逻辑上

MySQL默认的复制操作是异步的，主库在客户端提交的事务会立即返回结果给客户端，不关心从库是否已经被接收并处理，若主库crash，从库也无法收到提交的事务，强行切换导致数据不完整
技术上

主库将事务写入binlog后通知dump线程发送到从库，主库继续处理其他事务，不能保证binlog完全送达所有从库

环境搭建

1）相关主库与从库的配置文件

[mysqld]          # master
datadir=/data
socket=/data/mysql.sock
log-bin=master-bin
sync-binlog=1
server-id=100
#----------------------------------
[mysqld]       # slave
datadir=/data
socket=/data/mysql.sock
relay-log=slave-bin
server-id=111
#------------------------------------

2）开启master与slave的实例

#关闭mysql实例
mysqladmin -uroot -h127.1  -P3306  -p'123456' shutdown;
#开启mysql数据库实例
mysqld_safe --defaults-file=/home/mysql/etc/my.cnf &
#查看进程
ps -ef |grep mysqld

3）创建复制专用用户

mysql> create user 'repl'@'192.169.43.%' identified by '123456';
#create user 'repl'@'%' identified by '123456';
mysql> grant REPLICATION SLAVE on *.* to 'repl'@'192.169.43.%';
mysql> flush privileges;

4）导出master数据

#--master-data值为2会注释change master，值为1或者没有提供值时，这些语句是直接激活的。同时，--master-data会锁定所有表(如果同时使用了--single-transaction，则不是锁所有表
mysqldump -uroot -p --all-databases --master-data=2 > master.sql

5）从master.sql中获取到binlog的坐标

grep -i -m 1 'change master to' master.sql

6）在从库执行导出的master.sql

mysql -uroot -p -h 127.1 -e 'source master.sql'

7）从库连接master

mysql> change master to 
        master_host='192.168.43.42',
        master_port=3306,
        master_user='repl',
        master_password='Password',
        master_log_file='master-bin.000002',
        master_log_pos=771;

8）启动IO线程和SQL线程

#一次性启动
start slave;
#分开执行
start slave io_thread;
start slave sql_thread;

9）在从库查看同步信息

mysql> show slave status\G
Master_Log_File：IO线程正在读取的master binlog
Read_Master_Log_Pos：IO线程已经读取到master binlog的哪个位置
Relay_Log_File：SQL线程正在读取和执行的relay log
Relay_Log_Pos：SQL线程已经读取和执行到relay log的哪个位置
Relay_Master_Log_File：SQL线程最近执行的操作对应的是哪个master binlog
Exec_Master_Log_Pos：SQL线程最近执行的操作对应的是master binlog的哪个位置

10）reset master与reset slave

reset slave会删除master.info/relay-log.info和relay log，然后新生成一个relay log。但是change master to设置的连接参数还在内存中保留着，所以此时可以直接start slave，并根据内存中的change master to连接参数复制日志。

reset slave all除了删除reset slave删除的东西，还删除内存中的change master to设置的连接信息

reset master会删除master上所有的二进制日志，并新建一个日志。在正常运行的主从复制环境中，执行reset master很可能导致异常状况。所以建议使用purge来删除某个时间点之前的日志(应该保证只删除那些已经复制完成的日志)，生产环境慎用

3.2 全同步复制

也称之组复制MGR

逻辑上

主库执行完事务，会等待所有事务分发给从库并执行完才返回客户端，因此会带来性能影响
技术上

主库提交事务后，所有的从库必须收到并提交事务，主库线程收到返回才继续工作，缺点是主库完成事务是时间变长，性能降低
额外的，全同步是主从同步的增强。

因为主从同步虽可以实现一主多从，但它的局限在于只有在主数据库上写的时候从数据库才会做数据备份，而在从数据库做出改变时，主数据库不会记录相应的改变。

然而，全同步出现了，它可以是只要在一个数据库做出改变，所以其它在同组的数据库也会改变，同组的数据库没有等级之分。可以理解为“同组数据库之间数据相等”

部署流程：

主机	ip
Master	192.168.43.43
Slave	192.168.43.42

Master节点设置

1）配置Master节点my.cnf配置文件

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin=slave-bin
binlog_checksum=NONE
sync-binlog=1
#此处需要作出区分
server-id=101
gtid_mode=on
enforce-gtid-consistency=1
# 启用SQL线程回放之后将二进制写入自身的binlog中，在组复制中，依赖于每个成员持久化的binlog来实现一些数据自动平衡>的特性
log_slave_updates=ON
# 启用ROW格式复制，增强数据一致性
binlog_format=ROW
# 启用双TABLE，使用InnoDB引擎表来保存IO和SQL线程的位置信息（复制元数据），以增强复制状态的安全性
master_info_repository=TABLE
relay_log_info_repository=TABLE
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
#组内统一，自行设定值
loose-group_replication_group_name="eba794f9-cfb3-11ec-9b91-000c29058c90"
loose-group_replication_start_on_boot=off
#本机ip，此处各节点需单独配置
loose-group_replication_local_address= "192.168.43.43:33061"
#组内ip
loose-group_replication_group_seeds="192.168.43.42:33061,192.168.43.43:33061"
loose-group_replication_bootstrap_group=off
#白名单
loose-group_replication_ip_whitelist="127.0.0.1,192.168.43.0/24"
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF

2）新建复制用户

#创建用户，需暂时关闭同步，避免同步问题
SET SQL_LOG_BIN=0;
CREATE USER repl@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO repl@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

#设置同步Master
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';

3）配置hosts

#vi /etc/hosts 按实际情况填写 ip 主机名
192.168.43.43 master
192.168.43.42 slave

4）启动组

mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

注意：引导只能由单个服务器完成，即启动组的服务器并且只执行一次。这就是为什么group_replication_bootstrap_group选项的值没有存储在实例的选项文件中的原因。如果它保存在选项文件中，则在重新启动服务器时会自动引导第二个具有相同名称的组。这将导致两个不同的组具有相同的名称

5）查看组内成员

mysql> select * from performance_schema.replication_group_members;

+---------------------------+------------------+------+------+-------+
| CHANNEL_NAME              | MEMBER_ID        | HOST | PORT | STATE |
+---------------------------+------------------+------+------+-------+
| group_replication_applier | 688532-0c296515c |master| 3306 | ONLINE|
+---------------------------+------------------+------+------+-------+

Slave节点设置

1）配置Master节点my.cnf配置文件

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin=slave-bin
binlog_checksum=NONE
sync-binlog=1
#此处需要作出区分
server-id=100
gtid_mode=on
enforce-gtid-consistency=1
# 启用SQL线程回放之后将二进制写入自身的binlog中，在组复制中，依赖于每个成员持久化的binlog来实现一些数据自动平衡>的特性
log_slave_updates=ON
# 启用ROW格式复制，增强数据一致性
binlog_format=ROW
# 启用双TABLE，使用InnoDB引擎表来保存IO和SQL线程的位置信息（复制元数据），以增强复制状态的安全性
master_info_repository=TABLE
relay_log_info_repository=TABLE
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
#组内统一，自行设定值
loose-group_replication_group_name="eba794f9-cfb3-11ec-9b91-000c29058c90"
loose-group_replication_start_on_boot=off
#本机ip，此处各节点需单独配置
loose-group_replication_local_address= "192.168.43.42:33061"
#组内ip
loose-group_replication_group_seeds="192.168.43.42:33061,192.168.43.43:33061"
loose-group_replication_bootstrap_group=off
#白名单
loose-group_replication_ip_whitelist="127.0.0.1,192.168.43.0/24"
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF

2）新建复制用户

#创建用户，需暂时关闭同步，避免同步问题
SET SQL_LOG_BIN=0;
CREATE USER repl@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO repl@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

#设置同步Master
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';

3）配置Slave服务器hosts

#vi /etc/hosts 按实际情况填写 ip 主机名
192.168.43.43 master
192.168.43.42 slave

4）加入组

这里不需要执行SET GLOBAL group_replication_bootstrap_group=ON;来引导组，因为组已经创建，直接加入组即可

mysql> START GROUP_REPLICATION;

5）查看组成员

mysql> select * from performance_schema.replication_group_members;

+---------------------------+------------------+------+------+-------+
| CHANNEL_NAME              | MEMBER_ID        | HOST | PORT | STATE |
+---------------------------+------------------+------+------+-------+
| group_replication_applier | 688532-0c296515c |master| 3306 | ONLINE|
| group_replication_applier | 686783-0c29fd25d | slave| 3306 | ONLINE|
+---------------------------+------------------+------+------+-------+

无论主节点还是从节点均可得到以上的输出结果，证明全同步复制架构搭建完成，可在主或从节点测试同步情况

3.3 半同步复制

逻辑上：一主多从模式下，有一个从节点返回成功，即成功，不必等待多个节点全部返回，半同步复制为了解决数据丢失的问题

include ❤️.1异步复制>

事先构建好主从复制的关系

Master节点

1）安装激活插件

#安装
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
#启用插件
mysql> SET GLOBAL rpl_semi_sync_master_enabled=1;

2）查看主库变量值，查看延迟时间

mysql> SHOW VARIABLES LIKE '%rpl%';

Slave节点

1）安装激活插件

#安装
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
#启用插件
mysql> SET GLOBAL rpl_semi_sync_slave_enabled=1;

2）重启I/O线程

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

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

3）查看从库的状态

mysql> SHOW SLAVE STATUS\G
#查看变量状态
mysql> SHOW STATUS LIKE '%rpl%';
#查看变量的值
mysql> SHOW VARIABLES LIKE '%rpl%';

3.4 GTID复制

include ❤️.1异步复制>

GTID(Global Transaction Identifier)，全局事务ID，保证每一个事务在主服务器提交都可以生成一个唯一ID，对于两主以上的架构优势明显，可以在数据安全的情况下切换新主

1）修改主库、从库的配置文件，新增GTID配置

gtid_mode = ON
enforce-gtid-consistency=1

2）主服务器创建repl复制用户，从服务器的change master to无需指定位置，仅要指定master_auto_position=1

mysql> change master to 
        master_host='10.243.95.3',
        master_port=3306,
        master_user='repl',
        master_password='123456',
        master_auto_position=1;

3）如果进行了reset master的操作，需要主库重新设置GTID的值，使之和从库的GTID值一致

使用show slave status \G;命令找到从库的GTID的值在主库设置进行同步

SET @@GLOBAL.GTID_PURGED='98d34129-d03e-11ec-8956-fa163e30df12:1-4';

4 常见问题处理

4.1 主库日志丢失

现象：主库误执行reset master导致binlog日志被清空，从库读取日志失败

解决思路：在业务低谷期停服，从库reset重做主从配置

#主库操作
mysql > reset master  #清空binlog
#从库操作
mysql > stop slave  #需等待从库把当前日志内容都读取完再做该操作
mysql > reset slave all  #清空从库信息
mysql > change master ...  #重做从库

4.2 主从数据库不一致

现象：从库未设置super_read_only=1，管理员错误的删除了从库的数据，导致主从数据不一致

1）查看从库的同步状态，定位问题报错位置

mysql> show slave status\G;

2）根据Last_Error中的报错信息获取具体出错的SQL

#方法1——通过binlog查询
#找到对应行，该行中Info信息就是1973位置所做操作
mysql> show binlog events in 'mysql-bin.032102' from 730019106 limit 10;
#方法2——通过ps表查询
mysql> select * from performance_schema.replication_applier_status_by_worker where LAST_ERROR_NUMBER=1396

3）执行反向操作恢复数据，重启slave进程

mysql> stop slave
mysql> start slave

4.3 主键冲突

参照4.3主从数据库不一致处理

4.4 从库日志丢失

现象：管理人员误删了从库的relay log，导致部分事务从库未执行

1、在从库上查看当前日志信息，主要观察relay_master_log_file和exec_master_log_pos的值，这两项代表了从库已经处理的中继日志文件和位置所对应的binlog文件和位置

2、在从库上使用上一步看到的位置点重新同步

mysql> stop slave;
mysql> change master to 
        master_host='192.168.43.42',
        master_port=3306,
        master_user='repl',
        master_password='P@ssword1!',
        master_log_file='master-bin.000002',
        master_log_pos=773;
mysql> start slave;

4.5 从库跳过错误事务

1、在从库上执行命令查看出错原因

mysql> show slave status\G;

2、如果确定出错的事务可以忽略不处理，可以跳过该出错事务

# 跳过指定数量的事务，通常为1跳过当前出错事务
mysql > stop slave ;
mysql > set global sql_slave_skip_counter=1
mysql > start slave ;
 
# 跳过指定类型的错误或者所有错误
vi /etc/my.cnf
[mysqld]
slave-skip-errors=1062,1146,2341  #跳过指定错误类型
slave-skip-errors=all  #跳过所有错误，不建议使用
 
# GTID模式下跳过事务
stop slave;
set gtid_next='fb6d07d2-a253-1212-b2fh-29255eg3f3g:23' #show slave status信息中retrieved_gtid_set里的gtid为
fb6d07d2-a253-1212-b2fh-29255eg3f3g:18-23
begin;commit;  #手动指定gtid_next，如果gtid已经存在于实例的GTID集合中，该事务会被忽略；如果没有存在于GTID集合中就将这个gtid分配给接下来要执行的事务，系统不需要给这个事务生成新的GTID
set gtid_next='AUTOMATIC';  #修改回自动获取GTID
start slave;
 
# 使用gtid_purged跳过事务
show slave status \G; #先查看executed_gtid_set的值，该值有两行，看下面那行
show variables like '%gtid_purged%'  #查看主库purged值，假设末尾是1-33
reset master #清空从库binlog和gtid_executed状态
set global gtid_purged='xxxxxxxxxxxxxxxxxxxxxxxxx:1-33'; 跳过1-33的事务
start slave;  #主从状态恢复后需手动补跳过的事务所产生的数据

4.6 从库延迟

由于主从复制原理问题，主从延迟是不能100%避免的

如果必须实现“所有查询都不能是过期读”，比如一些金融类业务。只能放弃读写分离，将所有读写压力都放在主库才行

出现从库延迟后，观察从库事务是否在正常在执行，同时在主从节点排查网络传输是否正常，其它也不能做过多的干涉了

4.7 主从故障通用处理

使用Percona工具自动处理

1、安装percona工具

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install percona-toolkit

2、使用pt-slave-restart忽略错误

mysql > stop slave
pt-slave-restart -uroot -p123456 --error-numbers=1396  #忽略该报错
mysql > start slave

4.8 从库同步状态报错

报错内容：Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most

排查过程：

1、进入数据库查看主从状态 show slave status\G;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fod9ppZl-1656833910806)(https://s2.loli.net/2022/05/12/7s6EDvtr9jgXZRc.jpg)]

2、根据上图的提示，查询到的异常数据出现在opp_strack表中

select * from performance_schema.replication_applier_status_by_worker\G；

确定事务发生在表 opp_strack 上，定位到表,再去排查是哪一条记录

3、去主库的对应的binlog日志中去查找数据库操作记录

mysqlbinlog --no-defaults -v -v --base64-output=decode-rows /zdata/mysql_data/mysql-bin.000063 | grep -A 20 “171661170” --color

4、对比主从库opp_strack表中字段“1683”的数据，我这边得到的数据是不一致的，发现从库opp_strack中被插入进来三条数据，冲掉了主库的id值，然后将从库中的三条数据删除掉

在这里插入图片描述

5、主从数据恢复一致后需要在slave上跳过报错的事务

Stop slave;
Set @@SESSION.GTID_NEXT=’ d393d2e3-9b61-11e5-82bf-141877342ba0:171661170’
Begin;
Commit;
Set @@SESSION.GTID_NEXT = AUTOMATIC;
Start slave;

6、再次查看从库状态，恢复正常即解决

至此OK

附录

两个RESET命令具体干了些啥

RESET MASTER
- 删除所有index file记录的binlog文件，将日志索引文件清空，创建一个新的日志文件
- reset master 将删除日志索引文件中记录的所有binlog文件，创建一个新的日志文件起始值从000001 开始，然而purge binary log 命令并不会修改记录binlog的顺序的数值
- reset master 不能用于有任何slave 正在运行的主从关系的主库
RESET SLAVE
- reset slave 将使slave 忘记主从复制关系的位置信息。该语句将被用于干净的启动, 它删除master.info文件和relay-log.info 文件以及所有的relay log 文件并重新启用一个新的relaylog文件
- 使用reset slave之前必须使用stop slave 命令将复制进程停止
- 所有的relay log将被删除不管他们是否被SQL thread进程完全应用(这种情况发生于备库延迟以及在备库执行了stop slave 命令),存储复制链接信息的master.info文件将被立即清除,如果SQL thread 正在复制临时表的过程中，执行了stop slave ，并且执行了reset slave，这些被复制的临时表将被删除
- reset slave 并不会清理存储于内存中的复制信息比如 master host, master port, master user, or master password,也就是说如果没有使用change master 命令做重新定向，执行start slave 还是会指向旧的master 上面，如果需要清除所有使用RESET SLAVE ALL