MySQL Replication(主从复制)

2021-10-12 02:43:09

职责介绍
搭建主从复制 ***
主从原理熟悉 *****
主从的故障处理 *****比如主从不同步
主从延时 *****
主从的特殊架构的配置使用 ****
主从架构的演变 ****
主从复制介绍

(1) 主从复制基于binlog来实现的

(2) 主库发生新的操作,都会记录binlog

(3) 从库取得主库的binlog进行回放

(4) 主从复制的过程是异步

搭建主从复制需要具备哪些条件

(1) 2个或以上的数据库实例

(2) 主库需要开启二进制日志

(3) server_id要不同,区分不同的节点

(4) 主库需要建立专用的复制用户 (replication slave)

(5) 从库应该通过备份主库,恢复的方法进行"补课"

(6) 人为告诉从库一些复制信息(ip port user pass,二进制日志起点)

(7) 从库应该开启专门的复制线程

主从复制搭建过程(生产)
4.1 准备多实例

[root@db01 data]# systemctl start mysqld3307

[root@db01 data]# systemctl start mysqld3308

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"

查看各个实例的server_id是否不同

[root@db01 data]# mysql -S /data/3308/mysql.sock -e "select @@port"

[root@db01 data]# mysql -uroot -p123 -S /data/3307/mysql.sock -e "select @@port";

mysql -S /data/3309/mysql.sock -e "select @@server_id"

对mysql偶尔有用的另一个选项是-e或--execute选项，可用来将SQL语句传递给服务器。该语句必须用引号引起来(单引号或双引号)。(然而，如果想要在查询中将值引起来，则对于查询应使用双引号，查询中引用的值应使用单引号）。

当使用该选项时，语句被执行，然后mysql立即退出命令外壳。

4.2 检查配置文件

主库: 二进制日志是否开启

两个节点: server_id

[root@db01 data]# cat /data/3308/my.cnf

[mysqld]

basedir=/application/mysql

datadir=/data/3308/data

socket=/data/3308/mysql.sock

log_error=/data/3308/mysql.log

port=3308

server_id=8

log_bin=/data/3308/mysql-bin

[root@db01 data]# cat /data/3307/my.cnf

[mysqld]

basedir=/application/mysql

datadir=/data/3307/data

socket=/data/3307/mysql.sock

log_error=/data/3307/mysql.log

port=3307

server_id=7

log_bin=/data/3307/mysql-bin

也使用以下语句查看主库的二进制日志是否开启

[root@db01 data]# mysql -S /data/3307/mysql.sock -e "select @@log_bin"

4.3 主库创建复制用户

[root@db01 ~]# mysql -uroot -p123 -S /data/3307/mysql.sock -e "grant replication slave on . to repl@'%' identified by '123'"

mysql -uroot -p -S /data/3307/mysql.sock -e "select user,host from mysql.user"

4.4 备份主库数据到从库

[root@db01 ~]# mysqldump -S /data/3307/mysql.sock -A --master-data=2 --single-transaction >/backup/full3307.sql

在从库中执行主库的备份，<这个符号比较奇怪

[root@db01 ~]# mysql -S /data/3308/mysql.sock </backup/full3307.sql

mysql -S /data/3309/mysql.sock </backup/full3307.sql

mysql> set sql_log_bin=0;

mysql> source /backup/full3307.sql

4.5 提交给从库关于主库的信息

help change master to #在mysql中使用这条命令，可以查看 change master to 的帮助信息

从主库的备份文件中查看：从库应该从主库的哪一个日志文件中的哪个位置开始执行主库的操作

方法1：直接打开查找

vim /backup/full3307.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=888;

方法2：文本匹配

[root@localhost backup]# grep "-- CHANGE MASTER TO" /backup/full3307.sql

执行上一条语句，显示一下内容

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=888;

[root@db01 ~]# mysql -S /data/3308/mysql.sock

CHANGE MASTER TO

MASTER_HOST='localhost',

MASTER_USER='repl',

MASTER_PASSWORD='123',

MASTER_PORT=3307,

MASTER_LOG_FILE='mysql-bin.000002',

MASTER_LOG_POS=888,

MASTER_CONNECT_RETRY=10;

4.6 从库开启复制线程(IO,SQL)

[root@db01 ~]# mysql -S /data/3308/mysql.sock

mysql> start slave;

4.7 检查主从复制状态

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

        Slave_IO_Running: Yes

       Slave_SQL_Running: Yes

[root@db01 ~]# mysql -S /data/3308/mysql.sock

mysql> show slave status \G

        Slave_IO_Running: Yes

       Slave_SQL_Running: Yes

主库:

[root@db01 ~]# mysql -S /data/3307/mysql.sock -e "create database fuzhi charset utf8mb4"

从库:

[root@db01 world]# mysql -S /data/3308/mysql.sock -e "show databases"

关闭主从复制

mysql -S /data/3308/mysql.sock -e "stop slave;reset slave all;"

主从复制原理 *****
5.1 主从复制中涉及的文件

主库:

binlog

从库:

relay_log 中继日志，该文件存储主库的二进制日志，位于从库的数据目录下

默认的文件名格式：localhost-relay-bin.000001，localhost-relay-bin是前缀

文件名的格式和文件路径是可以设置的

master.info 该文件存储从库连接主库需要的信息CHANGE MASTER TO ，位于从库的数据目录下

master_info_repository=table时可以提高性能，值为FILE时需要经常和磁盘进行IO

relay-log.info 记录从库执行relay-log到了哪个位置点

relay_log_info_repository = FILE /table，值为table时可以提高性能

5.2 主从复制中涉及的线程

主库:

Binlog_Dump Thread : DUMP_T

接收从库的请求，返回binlog日志给从库

从库:

SLAVE_IO_THREAD : IO_T

请求binlog日志，接收日志

SLAVE_SQL_THREAD : SQL_T，执行relay-log日志

5.3 主从复制工作(过程)原理

1.从库执行change master to 命令(主库的连接信息+复制的起点),

2.从库会将以上信息,记录到master.info文件

3.从库执行 start slave 命令,立即开启IO_T和SQL_T

从库 IO_T,读取master.info文件中的信息

获取到IP,PORT,User,Pass,binlog的位置信息

从库IO_T请求连接主库,主库专门提供一个DUMP_T,负责和IO_T交互
IO_T根据binlog的位置信息(mysql-bin.000004 , 444),请求主库新的binlog
主库通过DUMP_T将最新的binlog,通过网络TP给从库的IO_T
IO_T接收到新的binlog日志,存储到TCP/IP缓存,立即返回ACK给主库,并更新master.info

9.IO_T将TCP/IP缓存中数据,转储到磁盘relaylog中.

SQL_T读取relay.info中的信息,获取到上次已经应用过的relaylog的位置信息
SQL_T会按照上次的位置点回放最新的relaylog,再次更新relay.info信息
从库会自动purge应用过relay进行定期清理relay_log_purge = ON

补充说明:

一旦主从复制构建成功,主库当中发生了新的变化,都会通过dump_T发送信号给IO_T,增强了主从复制的实时性.

5.4 主从复制监控 ****
主要在从库中监控

在主库中使用的命令

show slave hosts;

在从库中使用的命令:

show slave status \G;

主库有关的信息(master.info):

Master_Host: localhost

Master_User: repl

Master_Port: 3307

Connect_Retry: 10

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 609

从库relay应用信息有关的(relay-log.info):

Relay_Log_File: db01-relay-bin.000002

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000004

Exec_Master_Log_Pos: 1050 研究主从延时的关键指标

Exec= Executed

从库的db01-relay-bin.000002文件和主库的mysql-bin.000004是对应的

从库线程运行状态(排错)

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:

从库延时主库的时间(秒):

Seconds_Behind_Master: 0

延时从库:

SQL_Delay: 0

SQL_Remaining_Delay: NULL

GTID复制有关的状态信息

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

5.5 主从复制故障 *****
5.5.1 IO 线程故障

(1) 从库连接主库的状态: connecting/no

网络,连接信息错误或变更了,防火墙,主库达到连接数上限,默认最高连151个并发会话，版本不统一

select @@max_connections;

排查思路:

使用复制用户手工登录

[root@db01 data]# mysql -urepl -p12321321 -h 10.0.0.51 -P 3307

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'repl'@'db01' (using password: YES)

[root@db01 data]# mysql -urep -p123 -h 10.0.0.51 -P 3307

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'rep'@'db01' (using password: YES)

[root@db01 data]# mysql -urepl -p123 -h 10.0.0.52 -P 3307

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.52' (113)

[root@db01 data]# mysql -urepl -p123 -h 10.0.0.51 -P 3309

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.51' (111)

[root@db01 data]#

解决:

stop slave;
reset slave all; #解除主从关系，需要先stop
change master to
start slave

(2) 请求Binlog

binlog 没开

binlog 损坏,不存在

主库 reset master后，日志文件从000001开始，从库的master.info中的记录不会跟着变化，会导致从库无法从主库获得二进制日志，

1一般在主库业务不繁忙期间，业务暂停5分钟

2.如果主从没有太长的延时，一般情况下，从库很快就能和主库同步。如果有延时，需要先处理延时。在从库已经和主库的数据一致的情况下，主库执行reset master

3.重新建立主从关系，即重新设置这两个参数的值MASTER_LOG_FILE='mysql-bin.000002',

MASTER_LOG_POS=1050,

从库

stop slave ;

reset slave all;

CHANGE MASTER TO

MASTER_HOST='localhost',

MASTER_USER='repl',

MASTER_PASSWORD='123',

MASTER_PORT=3307,

MASTER_LOG_FILE='mysql-bin.000002',

MASTER_LOG_POS=1050,

MASTER_CONNECT_RETRY=10;

start slave;

(3) 存储binlog到relaylog

5.5.2 SQL线程故障

relay-log损坏

回放relaylog

研究一条SQL语句为什么执行失败?

insert delete update ---> t1 表不存在

create table oldboy ---> oldboy 已存在

约束冲突(主键,唯一键,非空..)

合理处理方法:

把握一个原则,一切以主库为准进行解决.

如果出现问题,尽量进行反操作

最直接稳妥办法,重新构建主从

从库写入故障模拟：先在从库创建数据库oldguo,然后在主库创建数据库oldguo,再在主库oldguo库中做操作，发现从库oldguo无法同步主库oldguo的数据

解决方法：先在从库中drop database oldguo;

然后重启线程 start slave sql_thread;

(在主从的操作一致的情况下使用，即简单情况使用)

方法一：手工跳过错误

stop slave;

set global sql_slave_skip_counter = 1;

start slave;

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

start slave;

pt 工具：检查主从数据是否一致，修复主从不一致的情况

https://www.jianshu.com/p/c97228b6f60c

方法二：修改配置文件，自动跳过某些类型的错误

/etc/my.cnf

slave-skip-errors = 1032,1062,1007

常见错误代码:

1007:对象已存在

1032:无法执行DML

1062:主键冲突,或约束冲突

但是，以上操作有时是有风险的，最安全的做法就是重新构建主从。把握一个原则,一切以主库为主.

为了很程度的避免SQL线程故障

show binary logs;#查看二进制日志文件

(1)设置从库只读，防止从库写入

select@@read_only

set global read_only=1; #除了root以外的普通用户只读，修改时可逆的

Variable 'read_only' is a GLOBAL variable and should be set with SET GLOBAL

select@@super_read_only #所有用户都只读

(2) 使用读写分离中间件，将用户对数据库的读操作分给从库，写操作分给主库

atlas

mycat

ProxySQL

MaxScale

5.6 主从延时监控及原因 *****
Seconds_Behind_Master=0 不能说明没有延时

评估主从延时比较精确的指标是：从库延迟了多少日志量

5.6.1 主库方面原因

(1) binlog写入不及时，dump_T线程就不会发送信号给IO_T，IO_T就不会请求新的日志

sync_binlog=1

(2) 默认情况下dump_t 是串行传输binlog给IO线程 *****

在并发事务量大时或者大事务,由于dump_t 是串型工作的,导致传送日志较慢

如何解决问题?

5.7+版本，无须手工开启GTID,系统自动生成匿名的GTID信息。使用Group commit方式.有了GTID就可以支持DUMP_T并行传输日志。GTID使得事务在主从的范围内有了唯一标志

(3) 主库极其繁忙

慢语句

锁等待

从库个数

网络延时

5.6.2 从库方面原因

(1) 传统复制(Classic)中 *****

如果主库并发事务量很大,或者出现大事务

由于从库是单SQL线程,导致,不管传的日志有多少,只能一次执行一个事务.

5.6 版本,有了GTID,可以实现多SQL线程,但是只能基于不同库的事务进行并发回放.(database)

5.7 版本中,有了增强的GTID,增加了seq_no,增加了新型的并发SQL线程模式(logical_clock),可以理解为基于事务的并发回放，开启多个SQL线程，同时执行多个事务，MTS(Multi-Threaded Slaves)技术

(2) 主从硬件差异太大

(3) 主从的参数配置

(4) 从库和主库的索引不一致

(5) 版本有差异

5.6.3 主从延时的监控

show slave status\G

Seconds_Behind_Master: 0

在主库中查看当前的二进制日志和位置点

mysql> show master status ;

File: mysql-bin.000001

Position: 1373

1544即为字节

从库 cat /data/3308/data/relay-log.info #在从库中查看relay-log.info的对应指标

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 1373

从库方面原因监控:

拿了多少:

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 691688

执行了多少:

Relay_Log_File: db01-relay-bin.000004

Relay_Log_Pos: 690635

Exec_Master_Log_Pos: 691000

Relay_Log_Space: 690635

码农公寓

执行上一条语句，显示一下内容

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

相关文章