:dango:为什么做主从复制

1
2
3
4
1.为了做高可用
2.为了解决MySQL的单点故障
3.分摊主库的压力
4.备份（延时从库）

主从复制原理

三个线程四个文件配合

QQ_1724397104506

1） Slave 上面的IO 线程连接上 Master，并请求从指定日志文件的指定位置(或者从最开始的 日志)之后的日志内容。（请求有没有新的数据）
2） Master 接收到来自 Slave 的 IO 线程的请求后，通过负责复制的 IO 线程根据请求信息读 取指定日志指定位置之后的日志信息，返回给 Slave 端的 IO 线程。

3）Slave 的 IO 线程接收到信息后，将接收到的日志内容依次写入到 Slave 端的RelayLog（中继日志文件）文件(MySQL-relay-bin.xxxxxx)的最末端，并将读取到的Master 端的bin-log 的文件名和位置记录到master-info 文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log 的哪个位置开始往后的日志内容，请发给我” 。
**4）**Slave 的 SQL 线程检测到 Relay Log 中新增加了内容后，会马上解析该 Log 文件中的内容成为在 Master 端真实执行时候的那些可执行的 Query 语句，并在自身执行这些 Query。同时将执行relay log 名字和位置点记录到relay-log.info里

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
## 三个线程
# 从库
IO线程：和主库建立连接接收主库新数据
SQL线程：将IO线程接收到的新数据在数据库中执行

# 主库
dump线程：接收IO线程的请求，从binlog中取出新数据返回给IO线程

## 四个文件
# 从库
master.info：记录IO线程上一次取出主库上binlog的名字和位置点（主库信息）
relay-log：存储binlog中新增的数据（不是一直存在的）复制SQL线程在执行完文件中的所有事件并且不再需要它后，会自动删除每个中继日志文件
relay-log.info：记录SQL语句上一回执行到中继日志的哪个位置点了
# 主库
bin-log：二进制日志记录主库所有对表数据发生变化的语句

搭建主从复制

:dango:环境一览 一主三从

主机名 WanIP LanIP 说明
db01 10.0.0.51 172.16.1.51 master
db02 10.0.0.52 172.16.1.52 slave
db03 10.0.0.53 172.16.1.53 slave
db04 10.0.0.54 172.16.1.54 slave 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
#主库
1.修改配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
basedir=/app/mysql
datadir=/app/mysql/data
server_id=1
log-bin=mysql-bin
skip_name_resolve

2.重启
[root@db01 ~]# systemctl restart mysqld

3.创建主从复制用户
mysql> grant replication slave on *.* to rep@'172.16.1.5%' identified by '123';

4.查看binlog（master）信息  记录binlog文件名和点
mysql> show master status;
+------------------+----------+
| File             | Position |
+------------------+----------+
| mysql-bin.000001 |  1324392 |
+------------------+----------+

#从库操作   （ db02,db03,db04 ）
1.修改从库配置文件
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
basedir=/app/mysql
datadir=/app/mysql/data
server_id=2
skip_name_resolve

2.重启数据库
[root@db01 ~]# systemctl restart mysqld

3.登录MySQL执行change master 语句
mysql > change master to
master_host='172.16.1.51',
master_user='rep',
master_password='123',
master_log_file='mysql-bin.000001',
master_log_pos=1324392,
master_port=3306;


############
master_host='172.16.1.51',    		 #主库ip
master_user='rep',							  #主从复制的用户
master_password='123',					   #rep用户的密码
master_log_file='mysql-bin.000001',	#主库在用的binlog文件
master_log_pos=1324392,						 #主库插入点位置
master_port=3306;

4.开启主从复制
mysql> start slave;

5.查看主从复制状态   (保证两个线程都是OK的)
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

主从故障处理:dango:

mysql里执行show slave status\G 查看是哪个线程出错

:dango:IO线程故障

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
#1.检查连接是否有问题
change master to
master_host='172.16.1.51',
master_user='rep',
master_password='123',
master_log_file='mysql-bin.000001',
master_log_pos=1324392,
master_port=3306;

#2.检查IP是否有问题
ping 172.16.1.51   ping 主机ip

#3.检查端口是否有问题
telnet 172.16.1.51 3306
端口不通：
	- 防火墙 selinux
	- 服务没起
	- 没有创建远程连接的用户

#4.检查用户名和密码能否登录
[root@db02 ~]# mysql -urep -p123 -h172.16.1.51

#5.  主库  检查binlog名字和位置点
mysql> show master status;
+------------------+----------+
| File             | Position |
+------------------+----------+
| mysql-bin.000001 |  1324392 |
+------------------+----------+

#6.检查主库和从库配置文件的server_id是否相同
#如果相同则报错如下：
Last_IO_Error:Fatal error:The slave I/0 thread stops because master and slave have equal MySQL server ids;these ids must be different for replication to work (or the --repLicate-same-server-id option must be used on slave but this does not always make sense;please c

#7.检查uuid是否一样（如果你是直接克隆虚拟机）
[root@db01 ~]# cat /app/mysql/data/auto.cnf 
[auto]
server-uuid=80a4722e-60ef-11ef-baf4-000c29bff80f

#如果uuid一直则报错如下
error:The slave I/0 thread stops because master and slave have equal MySQL server UUIDs;these UUIDs must be different for replication to work.

:dango:SQL线程故障

1
2
3
4
5
6
7
8
9
10
11
12
# 根本原因：主库和从库数据不一致
# 1.主库有从库没有的数据
出现原因：新加的从库主从复制点位是主库已有数据状态

# 2.主库没有从库有的数据
`出现原因`:直接在从库中操作数据库
`解决`:
解决方案：从库设置为只读（读写分离）
# 在命令行临时设置
set global read_only=1;
# 在配置文件中永久生效
read_only=1

:dango:生成环境如何添加新从库 保持新主数据一致 避免SQL线程故障

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# 1.从库初始化环境
[root@db04 ~]# systemctl stop mysqld
[root@db04 ~]# rm -fr /app/mysql/data
[root@db04 ~]# /app/mysql/scripts/mysql_install_db --user=mysql --basedir=/app/mysql --datadir=/app/mysql/data

`mysql5.7`
/app/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/app/mysq --datadir=/app/mysql/data

[root@db04 ~]# systemctl start mysqld

#2.主库打点全备
[root@db01 ~]# mysqldump -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/full.sql.gz

#3.从库恢复数据
[root@db01 ~]# scp /tmp/full.sql.gz 172.16.1.54:/root
[root@db04 ~]# zcat /root/full.sql.gz |mysql

# 4..查看主库全备的点位
[root@db04 ~]# zcat /root/full.sql.gz |head -25
--MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1843126;

# 5.从库执行change master语句
change master to
master_host='172.16.1.51',
master_user='rep',
master_password='123',
master_log_file='mysql-bin.000001',
master_log_pos=1843126;

#6.开启主从复制
mysql> start slave;

:dango:取消主从复制

1
2
3
4
5
6
mysql>stop slave;

mysql>reset slave all;

mysql> show slave status\G

数据库的延时从库

:dango:传统主从复制缺陷

1
2
3
4
5
6
1.传统住从复制无法做备份
2.传统主从复制无法过滤复制
3.传统主从复制速度慢
4.传统主从复制是异步复制，从库数据延迟

`延时从库应运而生`
1
2
3
4
5
6
7
8
9
10
一般企业中会延时3-6小时（开不开启从库延时   看企业需求，优弊端都有）

`优点：`
1.做延时从库的服务器，无法在生产中继续提供服务
2.备份
3.恢复数据速度更快

`弊端:`
企业使用从库延时做备份需求，需要一台服务器做备胎，浪费资源

:dango:延时从库原理

如下主从基础上，IO线程继续拿数据，SQL线程在指定时间后，延时执行，当主库出现删除库操作，那么从库将在设置的延时之后才会删除库，这就是延时从库实现原理

QQ_1724397104506

:dango:如何配置延时从库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
# 1.停止主从复制
mysql> stop slave;

#2. 设置延时180s
mysql> change master to master_delay=180;

#3.开启主从
mysql> start slave;

#4.查看状态
mysql> show slave status \G


#关闭延时从库
#停止主从复制
mysql> stop slave;

#设置延时为0关闭
mysql> change master to master_delay=0;

#开启主从
mysql> start slave;



#使用延时从库恢复数据
`思路：
`IO线程不停，读取relay-log里SQL线程最后拿取的点位为起始点，过滤relay-binlog文件读取drop之前的点为结束点，这一段为第一次新增数据`

`drop 之后结束点为起点，停止数据库写入，此时读取relay-binlog文件最后点（IO不停），截取为第二段新增数据`

# 1.停止延时从库SQL线程
mysql> stop slave SQL_thread;

# 2准备新环境
[root@db03 ~]# systemctl stop mysqld
[root@db03 ~]# rm -fr /app/mysql/data
[root@db03 ~]# /app/mysql/bin/mysqld --user=mysql --basedir=/app/mysql --datadir=/app/mysql/data
[root@db03 ~]# systemctl start mysqld

# 3.全备延时从库
[root@db04 ~]# mysqldump -A -R --triggers > /tmp/full_new.sql

# 3.截取relay-log
cat /app/mysql/data/relay-log.info 
./db04-relay-bin.000002
12656
mysql-bin.000003
390768
起始位置点：12656

[root@db04 data]# mysqlbinlog --base64-output=decode-rows db04-relay-bin.000002 |grep -C 10 drop    #drop之前
结束位置点：24110

#导出第一段新增数据
mysqlbinlog --start-position=12656 --stop-position=24110 /app/mysql/data/db04-relay-bin.000002 >/tmp/inc1.sql


#停止主库写入

#截取第二段新增数据
[root@db04 data]# mysqlbinlog --base64-output=decode-rows db04-relay-bin.000002 |grep -C 10 drop    #drop之后
起始位置点：24202
mysqlbinlog --base64-output=decode-rows db04-relay-bin.000002
结束位置点：220265

#导出第二段新僧数据
mysqlbinlog --start-position=24202 --stop-position=240826 /app/mysql/data/db04-relay-bin.000002 >/tmp/inc2.sql

prod：1051

#导入数据
导入延时从库全备
[root@db04 data]# scp /tmp/full.sql 172.16.1.53:/root
[root@db03 ~]# mysql <full.sql 

#导入第一次新增数据
[root@db04 data]# scp /tmp/inc1.sql  172.16.1.53:/root   
[root@db03 ~]# mysql <inc1.sql 

#导入第二次新增数据
[root@db04 data]# scp /tmp/inc2.sql  172.16.1.53:/root
[root@db03 ~]# mysql <inc2.sql 

#应用割接
开发改代码
运维将新环境全备到旧环境

数据库的半同步复制

1
从MYSQL5.5开始，支持半自动复制。之前版本的MySQL Replication都是异（asynchronous）的，主库在执行完一些事务后，是不会管备库的进度的。如果备库不幸落后，而更不幸的是主库此时又出现Crash（例如宕机），这时备库中的数据就是不完整的。简而言之，在主库发生故障的时候，我们无法使用备库来继续提供数据一致的服务了。
1
2
3
4
5
6
7
8
半同步复制出现原因：`为了保证主库和从库的数据一致性`
从库的IO线程在没有接收到ACK之前，会阻塞主库写入操作

`半同步缺点`：阻塞主库写入数据，影响主库性能，降低用户体验
`半同步优点`：主库和从库数据保证了一致性，不会丢数据

半同步复制（Semi synchronous Replication）则一定程度上保证提交的事务已经传给了至少一个备库。出发点是保证主从数据一致性问题，安全的考虑。

:dango:注意

1
2
3
4
5
1)在半同步复制中，Master节点等待Slave节点返回确认信息的过程中，如果发生异常，或者一直等待直到超过rpl_semi_sync_master_timeout配置的时间点，同步复制将退化为异步复制。

2）在退化为异步复制后，如果Slave节点追上了Master节点，那么Master节点又会重新转为半同步复制。

看企业选择

:dango:MySQL的半同步复制原理

IO线程取出数据之后，先放入tcp缓存，直到SQL线程完成再给IO线程返回ack

半同步插件

1
2
3
[root@db01 ~]# ll /app/mysql/lib/plugin/
-rwxr-xr-x 1 mysql mysql 515584 Aug  7 23:04 semisync_master.so
-rwxr-xr-x 1 mysql mysql 276296 Aug  7 23:04 semisync_slave.so

:dango:配置半同步

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
## 主库操作
# 1.主库查看是否支持半同步
mysql> show global variables like 'have_dynamic_loading';
+----------------------+-------+
| Variable_name 			| Value	|
+----------------------+-------+
| have_dynamic_loading | YES	 |
+----------------------+-------+

# 2.安装主库半同步插件
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME'semisync_master.so';

# 3.启动插件
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;

# 4.设置超时时间(毫秒)
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;

# 5.永久启动+超时时间
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout = 1000

db01 [(none)]>show variables like'rpl%';
+------------------------------------+----------+
| Variable_name                      | Value    |
+------------------------------------+----------+
| rpl_semi_sync_master_enabled       | ON       |
| rpl_semi_sync_master_timeout       | 1000     |
| rpl_semi_sync_master_trace_level   | 32       |
| rpl_semi_sync_master_wait_no_slave | ON       |
| rpl_stop_slave_timeout             | 31536000 |
+------------------------------------+----------+

db01 [(none)]>show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+


## 从库操作
# 1.安装从库半同步插件
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so';

# 2.启动插件
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;

# 3.重启IO线程
mysql> stop slave io_thread;
mysql> start slave io_thread;

# 4.永久生效
[root@db02 data]# vim /etc/my.cnf
[mysqld]
rpl_semi_sync_slave_enabled = 1


mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 6     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 1363  |
| Rpl_semi_sync_master_tx_wait_time          | 8178  |
| Rpl_semi_sync_master_tx_waits              | 6     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 6     |
+--------------------------------------------+-------+

MySQL过滤复制

:dango:主库配置过滤复制

  • binlog-do-db：白名单
1
2
3
4
5
6
7
8
9
10
11
12
13
[root@db01 ~]# cat /etc/my.cnf
[mysqld]
binlog-do-db=prod


db01 [(none)]>show master status;
+------------------+----------+--------------+
| File             | Position | Binlog_Do_DB |
+------------------+----------+--------------+
| mysql-bin.000006 |   515446 |     prod     |
+------------------+----------+--------------+

binlog：只记录prod库的数据
  • binlog-ignore-db：黑名单
1
2
3
4
5
6
7
8
9
10
11
12
[root@db01 ~]# cat /etc/my.cnf
[mysqld]
binlog-ignore-db=prod
binlog：不记录prod库的数据

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 |   515446 |              |      prod        |
+------------------+----------+--------------+------------------+

:dango:从库配置过滤复制

白名单

  • replicate-do-db=prod #从库SQL线程只执行prod库的SQL语句
  • replicate-do-table=prod.prod #从库SQL线程只执行prod库里prod表的SQL语句
  • replicate-wild-do-table=prod.p* #从库SQL线程只执行prod库里所有p开头表的SQL语句

SQL线程只执行 Replicat_Do_DB指定的库的SQL语句

黑名单

  • replicate-ignore-db
  • replicate-ignore-table
  • replicate-wild-ignore-table

SQL线程不执行 Replicat_Ignore_DB指定的库的SQL语句

基于GTID的主从复制

:dango:什么是GTID

1
2
3
GTID：事务提交做的主从复制（全局事务标识符）
GTID=UUID：主库的身份证号 + TID：事务提交号
3E11FA47-71CA-11E1-9E33-C80AA9429562:23

:dango:GTID特性

1
2
3
4
5
(1).支持多线程复制:事实上是针对每个database开启相应的独立线程,即每个库有一个单独的(sqlthread).
(2).支持启用GTID,在配置主从复制,传统的方式里,你需要找到binlog和POS点,然后change master to指向.在mysql5.6里,无须再知道binlog和POS点,只需要知道master的IP/端口/账号密码即可,因为同步复制是自动的,MySQL通过内部机制GTID自动找点同步.
(3).基于Row复制只保存改变的列,大大节省Disk Space/Network resources和Memory usage.
(4).支持把Master 和Slave的相关信息记录在Table中原来是记录在文件里,记录在表里,增强可用性
(5).支持延迟复制

:dango:GTID主从配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
主库172.16.1.51
从库172.16.1.52、172.16.1.53、172.16.1.54

# 1.修改主库配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log-bin=mysql-bin
gtid_mode=ON
enforce_gtid_consistency
#msyql5.6需要再加下面一个（5.7不需要）
log_slave_updates


# 2.修改从库配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=2
log-bin=mysql-bin
gtid_mode=ON
enforce_gtid_consistency
#msyql5.6需要再加下面一个（5.7不需要）
log_slave_updates



[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=3
log-bin=mysql-bin
gtid_mode=ON
enforce_gtid_consistency
#msyql5.6需要再加下面一个（5.7不需要）
log_slave_updates


[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=4
log-bin=mysql-bin
gtid_mode=ON
enforce_gtid_consistency
#msyql5.6需要再加下面一个（5.7不需要）
log_slave_updates

# 3.在主库上创建主从复制用户
mysql> grant replication slave on *.* to rep@'172.16.1.5%' identified by '123';

# 4.所有从库上执行change master
change master to
master_host='172.16.1.53',
master_user='rep',
master_password='123',
master_auto_position=1;

# 5.所有从库开启主从复制
start slave;


#查看GTID是否开启
db01 [(none)]>show variables like '%gtid%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| binlog_gtid_simple_recovery     | OFF       |
| enforce_gtid_consistency        | ON        |
| gtid_executed                   |           |
| gtid_mode                       | ON        |
| gtid_next                       | AUTOMATIC |
| gtid_owned                      |           |
| gtid_purged                     |           |
| simplified_binlog_gtid_recovery | OFF       |
+---------------------------------+-----------+

`注意`
## MySQL5.6开启GTID
gtid_mode=ON 						// 开启GTID模块
enforce_gtid_consistency // 保证GITD数据强一致性
log-slave-updates 			// 从库日志更新（1.基于GTID的主从复制 2.双主+keepalived 3.级联复制）
log-bin=mysql-bin 			// 开启binlog

## MySQL5.7开启GTID
gtid_mode=ON
enforce_gtid_consistency