1、 mysql主从复制

为什么要做主从复制
1、为了做高可用
2、为了解决MySQL的单点故障
3、分摊主库的压力
4、做备份的情况(延迟从库)

Mysql主从复制原理

image-20240823161157907

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
同步：IO线程拿到数据交给SQL线程，sql线程执行完成后，IO线程再去要下一条数据(类似于我给你传文件，我要等你接收完)
异步：IO线程把数据放到缓存里,不管SQL线程是否执行完，IO线程都会去要下一条数据

如果看到SQL显示NO,可能是数据的问题，可能主库和从库数据不一致


master.info 储存主库信息的文件
relay-log  中继日志，也叫差异日志(主库里有，从库里没有，还需要从库执行的语句，也就是主库和从库的数据差异的)


#原理
1）通过change master to语句告诉从库主库的ip，port，user，password，file，pos
2）从库通过start slave命令开启复制必要的IO线程和SQL线程
3）从库通过IO线程拿着change master to用户密码相关信息，连接主库，验证合法性
4）从库连接成功后，会根据binlog的pos问主库，有没有比这个更新的
5）主库接收到从库请求后，比较一下binlog信息，如果有就将最新数据通过dump线程给从库IO线程
6）从库通过IO线程接收到主库发来的binlog事件，存储到TCP/IP缓存中，并返回ACK更新master.info
7）将TCP/IP缓存中的内容存到relay-log中
8）SQL线程读取relay-log.info，读取到上次已经执行过的relay-log位置点，继续执行后续的relay-log日志，执行完成后，更新relay-log.info
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
change master to
master_host='172.16.1.51',
master_user='rep',
master_passwd='123',
master_log_file='mysql-bin.000001',
master_log_pos=446


#MySQL主从复制和3个线程，4个文件有关系
1、#3个线程：
      - 从库上的IO线程：(和主库建立连接，接收主库的数据)
      - 从库上的SQL线程：(将IO线程取出的数据在数据库中执行一遍，他两之前是异步的)
      
      - 主库上的dump线程：(接受IO线程的请求，从binlog中取出数据返回给IO线程)
      
2、#4个文件
      - 从库：master.info(存上一次主库binlog的名字和位置点)：执行start slave会把主库信息记录在master.info,方便下一次取数据
      - 从库：relay-log.info：(记录sql语句上一回执行到中继日志的位置点，下回再执行的时候就从这个位置点往后执行)
      - 从库：relay-log：(存储binlog中新增的数据（不是一直存在的）复制SQL线程在执行完文件中的所有事件并且不再需要它后，会自动删除每个中继日志文件)
      
      - 主库：bin-log(二进制日志记录主库所有对数据表发生变化的语句)
      

#问题：relay-log会一直存在吗,如果不是，会存在多久？
官网给的答案：
https://dev.mysql.com/doc/refman/5.7/en/replica-logs-relaylog.html

The replication SQL thread automatically deletes each relay log file after it has executed all events in the file and no longer needs it. There is no explicit mechanism for deleting relay logs because the replication SQL thread takes care of doing so. However, FLUSH LOGS rotates relay logs, which influences when the replication SQL thread deletes them.
(复制SQL线程在执行完文件中的所有事件并且不再需要它后，会自动删除每个中继日志文件。没有明确的机制来删除中继日志，因为复制SQL线程会负责这样做。但是，FLUSH logs会轮换中继日志，这会影响复制SQL线程何时删除它们。)
也就是说SQL线程执行完relay-log的所有事件之后，会定期的删除relay-log，但是不知道多久删除一次，官方没有给出明确的时间

涉及到3个线程 4个文件
#从：master inifo  
relay.loh  
relay：记录sql语句上一回执行到中继日志的那个位置点了

部署传统主从复制

环境准备

主机名 ip 角色 应用
db01 10.0.0.51 / 172.16.1.51 主库 mysql 5.6
db02 10.0.0.52 / 172.16.1.52 从库 mysql 5.6
db03 10.0.0.53 / 172.16.1.53 从库 mysql 5.6
db04 10.0.0.54 / 172.16.1.54 从库 mysql 5.6

1、主库操作db01

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
1、#主库操作
#修改配置文件
[root@db02 ~]# vim /etc/my.cnf
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
basedir=/app/mysql-5.6.50
datadir=/app/mysql-5.6.50/data
log_bin=mysql-bin
server_id=1
lower_case_table=1
#innodb_data_file_path=ibdata1:12M;ibdata2:50M:autoextend
binlog_format=row
socket=/tmp/mysql.sock
skip-name-resolve
[client]
socket=/tmp/mysql.sock
[mysql]
prompt="\\u@\\h:\\d > "

2、#重启
[root@db04 ~]#  /etc/init.d/mysqld restart

3、#创建一个主从复制的用户
grant replication slave on *.* to rep@'172.16.1.5%' identified by '123';

4、#查看binlog(master)的信息
root@localhost:(none) > show master status;
+------------------+----------+
| File             | Position |
+------------------+----------+
| mysql-bin.000016 |      327 |
+------------------+----------+

2、从库操作 先配置db02 db03

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、#修改配置文件
[root@db03 ~]# vim /etc/my.cnf
[mysqld]
basedir=/app/mysql-5.6.50
datadir=/app/mysql-5.6.50/data
从库binlog不需要开启
#log_bin=mysql-bin
#server_id不一样
server_id=1
lower_case_table=1
#innodb_data_file_path=ibdata1:12M;ibdata2:50M:autoextend
binlog_format=row
socket=/tmp/mysql.sock
skip-name-resolve
[client]
socket=/tmp/mysql.sock
[mysql]
prompt="\\u@\\h:\\d > "

2、#重启
[root@db03 ~]# /etc/init.d/mysqld restart

3、#执行master语句
change master to
master_host='172.16.1.51',
master_user='root',
master_password='123',
master_log_file='mysql-bin.000016',
master_log_pos=327,
master_port=3306;

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

5、#查看主从复制状态
mysql> show slave status\G
        Relay_Master_Log_File: mysql-bin.000016
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            
           #2个yes就OK

主从复制故障处理

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
#大概率是连接出问题
change master to
master_host='172.16.1.51',
master_user='root',
master_password='123',
master_log_file='mysql-bin.000016',
master_log_pos=327,
master_port=3306;

1、#检查IP是否正常(和change master的IP一样，不要写外网IP)
ping 172.16.1.51

2、#检查端口是否正常(和change master的IP一样，不要写外网IP)
telent 172.16.1.51 3306
端口不通：
      - 防火墙 selinux
      - 服务没有起
      - 没有创建远程连接的用户
      
3、#检测用户名和密码  从库用rep用户连接到主库
[root@db03 ~]# mysql -urep -p123 -h172.16.1.51

4、#主库检查binglog名字和位置点是否正确(位置点可以少，不能多，多了找不到那个点)
root@localhost:(none) > show master status;
+------------------+----------+-
| File             | Position | 
+------------------+----------+-
| mysql-bin.000016 |    32858 | 
+------------------+----------+-

image-20240823175441790

1
2
# 报错原因：
主库和从库的server_id相同

image-20240823175520693

1
2
# 报错原因：
主库和从库的UUID相同

SQL线程故障

1
2
3
4
5
#根本原因：主库和从库数据不一致

1、主库的数据比从库多(主库里面有一个jiaoyi表，从库没有，我在主库里面的jiaoyi表插入数据，从库SQL线程就会报错)

2、从库的数据比主库多(从库已经存在一个tabl1表，当主库创建table1表时，从库QL线程就会报错)

1、主库的数据比从库多 报错

image-20240823160331343

2、从库的数据比主库多 报错

image-20240823180522184

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
#解决方法：

方法1：:不推荐  临时的解决方法  
(跳过这个报错的指针)
# 临时停止同步
mysql> stop slave;
# 将同步指针向下移动一个（可重复操作）
mysql> set global sql_slave_skip_counter=1;
# 开启同步
mysql> start slave;


方法2：不推荐  临时的解决方法
(在mysql标签主添加跳过错误代码的配置)
[root@db01 ~]# vim /etc/my.cnf
#在[mysqld]标签下添加以下参数
slave-skip-errors=1032,1062,1007,1146

#但是以上操作都是有风险存在的,导致从库数据缺失，这个数据从库里面永远没有，查不到数据，他跳过这个错误，配置文件里面写了，数据不复制,这个只是表面上看到yes,实际上没有意义，跳过相当于从库少数据


#数据不一致的原因可能如下：
1、在生产中运行已有的数据库的主库，新从库直接从当前位置点复制
解决方法：给主库全备，导入到新从库中，保证数据的一致性

2、直接在从库中操作数据库
解决方法：从库设置不允许写入 ，从库设置为只读(读写分离)
# 在命令行临时设置
set global read_only=1;
# 在配置文件中永久生效
read_only=1
命令行+配置文件 不需要重启就是永久失效
#但是用root用户，对root不生效

3、

image-20240826150832618

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
#解决办法
1、主库停止主从复制
root@localhost:(none) > stop slave;

2、在主库上查看当前位置点
root@localhost:(none) > show master status;
+------------------+----------+--------------+
| File             | Position | Binlog_Do_DB |
+------------------+----------+--------------+
| mysql-bin.000016 |  5235939 |              |
+------------------+----------+--------------+

3、从库打点全备，到从库，
[root@db01 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=1 --single-transaction|gzip > /tmp/full.sql.gz
[root@db01 ~]# scp /tmp/full.sql.gz 172.16.1.52:/tmp

4、从库再从当前位置点开始复制
[root@db02 ~]# zcat /tmp/full.sql.gz |mysql -uroot -p123
[root@db02 ~]# mysql -uroot -p123

5、#从库执行change mater 语句
change master to
master_user='rep',
master_host='172.16.1.51',
master_password='123',
master_log_file='mysql-bin.000016',
master_log_pos=5235939,
master_port=3306;


6、#从库开启主从复制
root@localhost:(none) > start slave;
查看数据是否同步
root@localhost:(none) > show slave status\G
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

生产环境中，如何添加一台从库

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
#新从库环境初始化  (db04)
目前db01主库已经有一些数据了，假设db01是生产环境的主库，还有数据在网里面写入
运行脚本


[root@db02 ~]# /etc/init.d/mysqld stop
[root@db02 ~]# rm -rf /app/mysql-5.6.50/data
配置文件和旧环境大部分保持一致
[root@db02 ~]# vim /etc/my.cnf
[root@db02 ~]# /app/mysql-5.6.50/scripts/mysql_install_db --user=mysql --basedir=/app/mysql-5.6.50 --datadir=/app/mysql-5.6.50/data
[root@db02 ~]# /etc/init.d/mysqld start; mysqladmin -uroot -p password '123'

1、#主库打点全备(热备 不锁表)
[root@db01 ~]#  mysqldump -uroot -p123 -A -R --triggers --master-data=1 --single-transaction|gzip > /tmp/full.sql.gz

但是zcat的文件里面有loca table 为什么锁表呢？
它是用这个文件往里恢复数据的时候锁表，文件显示的是恢复过程，备份的时候不锁表的，不是备份的过程，是在恢复的时候锁住

2、#从库恢复主库全备数据
[root@db01 ~]# scp /tmp/full.sql.gz 172.16.1.54:/tmp

[root@db04 ~]# zcat /tmp/full.sql.gz |mysql -uroot -p123

3、#从查看数据少了，查看点位，直接从打点的位置开始复制
[root@db01 ~]# zcat /tmp/full.sql.gz |head -25
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000016', MASTER_LOG_POS=984578;

5、#从库执行change mater 语句
change master to
master_user='rep',
master_host='172.16.1.51',
master_password='123',
master_log_file='mysql-bin.000016',
master_log_pos=984578,
master_port=3306;

6、#从库开启主从复制
root@localhost:(none) > start slave;
查看数据是否同步
root@localhost:(none) > show slave status\G
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

2、延时复制

传统主从复制的缺陷

1
2
3
4
5
6
7
8
1、传统主从复制无法备份
2、传统主从复制无法过滤复制
3、传统主从复制速度相对来说比较慢
4、传统主从复制是异步复制，从库数据延迟(每台从库数据不是百分百一致，每台从库的数据不一致，但这个是不可避免的，在生产中一定有延迟)

mysql> grant replication slave on prod.* to slave@'%' identified by '123';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
#replication 这是一个全局的权限

延时从库

1
2
3
4
5
6
7
8
9
10
11
#企业一般会延时3~6小时
1、做延时从库的服务器无法在生产中提供服务(这个机器就是一个备机)
2、备份
3、恢复数据速度更快
4、延时从库不影响高可用


延时从库的原理：在SQL线程做手脚，IO线程继续拿数据，SQL线程在指定时间后，延时执行

(怎么做，才能让他延迟3~6小时？)
#会设置SQL线程在指定的时间后再执行

延时从库的原理

image-20240826144421626

配置延时从库

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
#命令：重置主从复制 :他会把所有相关文件重置
需要修改某些参数的时候才用(比如说位置点写错了，密码写错了)
mysql> change master to
master_host='172.16.1.51',master_user='rep',master_password='123',master_log_file='mysql-bin.000003',master_log_pos=154;

reset slave;     会保留之前chang masetr的信息

reset slave all;  (完全重置主从，保留任何信息，取消从库身份的时候)

#停止主从复制
stop slave;  

#就是在SQL-Delay加延迟时间
#将db04变成延时从库

1、#停止主从复制
root@localhost:(none) > stop slave;

2、#重置主从信息
root@localhost:(none) > reset salve;

3、#配置SQL线程延迟复制时间   180 3min
root@localhost:(none) > chang master to master_delay=180;

4、#开启主从复制，并查看状态
root@localhost:(none) > start slave;
root@localhost:(none) > show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
               Master_Host: 172.16.1.51
               ......
               SQL_Delay: 180
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
#配置延时从库，导致数据不一致，当让数据一致的解决办法
1、#从库操作
root@localhost:(none) > stop slave;
root@localhost:(none) > reset slave;
root@localhost:(none) > change master to master_delay=0;

2、主库操作
[root@db01 ~]#  mysqldump -A -uroot -p123 > /tmp/full.sql
[root@db01 ~]# scp /tmp/full.sql.gz 172.16.1.52:/tmp

3、从库恢复数据
root@localhost:world > source /tmp/full.sql;

4、主库查看位置点
root@localhost:(none) > show master status;
+------------------+----------+--------------+
| File             | Position | Binlog_Do_DB |
+------------------+----------+--------------+
| mysql-bin.000016 |  5366820 |              |
+------------------+----------+--------------+

5、#从库执行change mater 语句
change master to
master_user='rep',
master_host='172.16.1.51',
master_password='123',
master_log_file='mysql-bin.000016',
master_log_pos=5366820,
master_port=3306;

root@localhost:world > start slave;
root@localhost:world > show slave status\G

延时从库恢复案例

总数据量级500G，正常备份去恢复需要1.5-2小时
1）配置延时3600秒
mysql>CHANGE MASTER TO MASTER_DELAY = 3600;

2）主库
drop database db;

3）怎么利用延时从库，恢复数据？

1、模拟数据准备

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
1、#主库db01执行写入数据的脚本
[root@db01 ~]# sh insert.sh 

2、先保证其他从库的数据是一致的，主从同步没问题

3、主库创建一个库，写入数据
root@localhost:(none) > create database asm;
root@localhost:(none) > flush logs;
root@localhost:(none) > use asm
root@localhost:(none) > create table asm(id int);
root@localhost:(none) > insert into asm values(1),(2),(3);
root@localhost:(none) > update asm set id=10 where id=1;

4、主库打点全备
[root@db01 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction  > /tmp/full.sql.gz
[root@db01 ~]# scp /tmp/full.sql.gz 172.16.1.52:/tmp

5、从库从库位置点
[root@db02 ~]# head -25 /tmp/full.sql.gz 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000017', MASTER_LOG_POS=99930;
[root@db02 ~]#  mysql -uroot -p123 < /tmp/full.sql.gz 

6、让从库db02变成一个延时从库
root@localhost:world > select * from asm.asm;
+------+
| id   |
+------+
|   10 |
|    2 |
|    3 |
+------+
root@localhost:world > stop slave;
root@localhost:world > reset slave all;

7、从库执行change mater 语句
change master to
master_user='rep',
master_host='172.16.1.51',
master_password='123',
master_log_file='mysql-bin.000017',
master_log_pos=99930,
master_port=3306,
master_delay=180;

root@localhost:world > start slave;
root@localhost:world > show slave status\G
*************************** 1. row ***************************
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
          .........
          SQL_Delay: 180
          SQL_Remaining_Delay: 0
          

8、主库里面再写点数据，从库里面查看现在数据还没有写过来，把主库的库删除
root@localhost:asm > insert into asm values(4),(5),(6);
root@localhost:asm > select * from asm.asm;
+------+
| id   |
+------+
|   10 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+      
root@localhost:(none) > drop database asm;

2、恢复数据

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
94
95
96
97
98
99
100
101
102
103
(企业中的盘)SAS  Flash(最接近内存性能的盘) 
SATA(电脑的盘)
mysq1> change master to master_delay=0;  取消延时复制

#恢复思路    用db02做延时从库
1、#停止db02延时从库的SQL线程
root@localhost:(none) > stop slave SQL_thread;

2、#准备新环境
[root@db02 ~]# /etc/init.d/mysqld stop
[root@db02 ~]# rm -rf /app/mysql-5.6.50/data
配置文件和旧环境大部分保持一致
[root@db02 ~]# vim /etc/my.cnf
[root@db02 ~]# /app/mysql-5.6.50/scripts/mysql_install_db --user=mysql --basedir=/app/mysql-5.6.50 --datadir=/app/mysql-5.6.50/data
[root@db02 ~]# /etc/init.d/mysqld start; mysqladmin -uroot -p password '123'

3、#延时从库做全备  (从库没有开binlog  不用打点全备)
[root@db02 ~]# mysqldump -uroot -p123 -A -R --triggers > /tmp/full_new.sql

4、#截取删库之前relay-log.info数据  (查看从库执行到哪个位置点)
[root@db02 ~]# cat /app/mysql-5.6.50/data/relay-log.info 
7
./db02-relay-bin.000002  #结束位置点的文件
171031  #开始位置点
mysql-bin.000017
270678
180
0
1
开始位置点：171031  

[root@db02 ~]# mysqlbinlog --base64-output=decode-rows -vvv /app/mysql-5.6.50/data/db02-relay-bin.000002 | grep -iC 10 drop
结束位置点：194850

[root@db02 ~]# mysqlbinlog --start-position=171031 --stop-position=194850  /app/mysql-5.6.50/data/db02-relay-bin.000002 > /tmp/inc1.sql

5、截取删库之后的数据 需要停止数据写入
[root@db01 ~]# sh insert.sh ^C
root@localhost:(none) > select count(*) from prod.prod;
+----------+
| count(*) |
+----------+
|     3442 |
+----------+  #记录停库前数据最后写入的状态

6、截取删库之前的位置点~停库的位置点数据
[root@db02 ~]# mysqlbinlog --base64-output=decode-rows -vvv /app/mysql-5.6.50/data/db02-relay-bin.000002 | grep -iC 10 drop
开始位置点：194939 

[root@db02 ~]# ll /app/mysql-5.6.50/data/
-rw-rw---- 1 mysql mysql   489751 Aug 26 17:21 db02-relay-bin.000002
结束位置点：489751

[root@db02 ~]# mysqlbinlog --start-position=194939 --stop-position=489751  /app/mysql-5.6.50/data/db02-relay-bin.000002 > /tmp/inc2.sql

7、准备新环境'db03'
[root@db03 ~]# /etc/init.d/mysqld stop
[root@db03 ~]# rm -rf /app/mysql-5.6.50/data
配置文件和旧环境大部分保持一致
[root@db03 ~]# vim /etc/my.cnf
[root@db03 ~]# /app/mysql-5.6.50/scripts/mysql_install_db --user=mysql --basedir=/app/mysql-5.6.50 --datadir=/app/mysql-5.6.50/data
[root@db03 ~]# /etc/init.d/mysqld start; mysqladmin -uroot -p password '123'

7、将延时从库的全备导入新环境
[root@db02 ~]# scp /tmp/full_new.sql  172.16.1.53:/tmp
[root@db03 ~]#  mysql -uroot -p123 < /tmp/full_new.sql 

查看新环境的数据状态
[root@db03 ~]#  mysql -uroot -p123
root@localhost:(none) > select count(*) from prod.prod;
+----------+
| count(*) |
+----------+
|     1731 |
+----------+  #可以看到数据还不全

8、恢复延迟查看截取的第一段，第二段新增数据到新环境
[root@db02 ~]# scp /tmp/inc1.sql   172.16.1.53:/tmp
[root@db02 ~]# scp /tmp/inc2.sql   172.16.1.53:/tmp
[root@db03 ~]#  mysql -uroot -p123 < /tmp/inc1.sql
[root@db03 ~]#  mysql -uroot -p123 < /tmp/inc2.sql

9、新环境db03查看数据是否恢复
root@localhost:(none) > select count(*) from prod.prod;
+----------+
| count(*) |
+----------+
|     3442 |
+----------+
root@localhost:(none) > select * from asm.asm;
+------+
| id   |
+------+
|   10 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+

10、应用割接

取消延时从库的步骤

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
#将db02延时从库取消配置
root@localhost:(none) > stop slave;

root@localhost:(none) > change master to master_delay=0;

root@localhost:(none) > start slave;

root@localhost:(none) > show slave status\G
#查看线程可能有报错
主库打点全备
[root@db01 ~]#  mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction  > /tmp/full.sql.gz

[root@db01 ~]# scp /tmp/full.sql.gz 172.16.1.52:/tmp 
[root@db01 ~]# head -25 /tmp/full.sql.gz
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000017', MASTER_LOG_POS=589398;

、从库恢复数据
root@localhost:world > source /tmp/full.sql;

、主库查看位置点
root@localhost:(none) > show master status;
+------------------+----------+--------------+
| File             | Position | Binlog_Do_DB |
+------------------+----------+--------------+
| mysql-bin.000017 |  589398  |              |
+------------------+----------+--------------+


、#从库执行change mater 语句
root@localhost:(none) > stop slave;

change master to
master_user='rep',
master_host='172.16.1.51',
master_password='123',
master_log_file='mysql-bin.000017',
master_log_pos=589398,
master_port=3306;

root@localhost:world > start slave;
root@localhost:world > show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.1.51
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000017
          Read_Master_Log_Pos: 589398
               Relay_Log_File: db02-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000017
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

3、半同步复制

半同步复制出现的原因：为了保证主库和从库的数据一致性

从库的IO线程在没有接收到ACK之前，会阻塞主库写入操作
半同步缺点：阻塞主库写入数据，影响主库性能，降低用户体验
半同步优点：主库和从库数据保证了一致性，不会丢数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
从MYSQL5.5开始，支持半自动复制。之前版本的MySQL Replication都是异步（asynchronous）的，主库在执行完一些事务后，是不会管备库的进度的。如果备库不幸落后，而更不幸的是主库此时又出现Crash（例如宕机），这时备库中的数据就是不完整的。简而言之，在主库发生故障的时候，我们无法使用备库来继续提供数据一致的服务了。

半同步复制（Semi synchronous Replication）则一定程度上保证提交的事务已经传给了至少一个备库。
出发点是保证主从数据一致性问题，安全的考虑。
5.5 出现概念，但是不建议使用，性能太差
5.6出现group commit 组提交功能，来提升开启半同步复制的性能
5.7更加完善了，在group commit基础上出现了MGR
5.7的增强半同步复制的新特性：after commit； after sync；

#什么情况下代表数据写完了，而且完整
TCP缓存会返回一个ACK给IO线程

#半同步复制(在IO线程做手脚)
IO线程取出数据后，IO线程不返回ACK，IO线程就不去给Dump线程要数据
只要从库IO线程没有接到ACK之前，会阻塞主库写入操作(为了数据的一致性，影响主库性能导致用户的体验下降)
#开不开半同步取决于公司，但几乎很少开

image-20240826181704110

半同步的插件

1
2
3
4
5
6
7
[root@db01 ~]# ll /app/mysql-5.6.50/lib/plugin/
-rwxr-xr-x 1 mysql mysql 515584 Aug  7 19:52 semisync_master.so
-rwxr-xr-x 1 mysql mysql 276296 Aug  7 19:52 semisync_slave.so

为什么不是自动安装这两个插件？
1、不是每个公司都有开半同步
2、先做主从之后才能开半同步

半同步复制的配置

1、db01主库操作

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
1、主库查看是否支持半同步
root@localhost:(none) > show global variables like 'have_dynamic_loading';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| have_dynamic_loading | YES   |
+----------------------+-------+

2、主库需要安装半同步插件
root@localhost:(none) > install plugin rpl_semi_sync_master soname'semisync_master.so';

3、启动插件+设置超时时间(临时的) 
root@localhost:(none) > SET GLOBAL rpl_semi_sync_master_enabled = 1;
root@localhost:(none) > set global rpl_semi_sync_master_timeout = 1000;

4、#永久启动写在配置文件里面+超时时间  设置超时 1000ms=1s
超过10s还没有写完数据，会停止半同步，恢复成异步复制(如果想要永久失效就写在配置文件里面)
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
....
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout = 1000

5、检查
root@localhost:(none) > show variables like'rpl%';
root@localhost:(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 |#停止从库的超时时间
+------------------------------------+----------+

root@localhost:(none) > show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |#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     |
+--------------------------------------------+-------+

2、db02从库配置

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
#从库配置
1、安装插件
root@localhost:world > INSTALL PLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so';

3、启动插件(临时启动)
root@localhost:world > SET GLOBAL rpl_semi_sync_slave_enabled = 1;
永久生效
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
....
rpl_semi_sync_slave_enabled = 1

4、重启IO线程
root@localhost:world > stop slave io_thread;
root@localhost:world > start slave io_thread;

5、到主库查看是否有从库变成半同步
root@localhost:(none) > 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             | 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     |
+--------------------------------------------+-------+

3、测试

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
1、主库创建一个库
root@localhost:(none) > create database abc111;
Query OK, 1 row affected (0.00 sec)   #配置之后，主库建库时间变快

root@localhost:(none) > create database abc112;
Query OK, 1 row affected (0.01 sec)

root@localhost:(none) > create database abc113;
Query OK, 1 row affected (0.00 sec)

root@localhost:(none) > create database abc114;
Query OK, 1 row affected (0.00 sec)

2、查看状态，会记录平均等待时间  （ms为单位）
root@localhost:(none) > show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 4199  |
| Rpl_semi_sync_master_net_wait_time         | 16799 |
| Rpl_semi_sync_master_net_waits             | 4     |
| 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      | 4286  |#平均等待时间 
| Rpl_semi_sync_master_tx_wait_time          | 17144 |
| Rpl_semi_sync_master_tx_waits              | 4     |#一共4条语句
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 4     |#成功执行4条语句
+--------------------------------------------+-------+

关闭半同步

1
2
3
4
5
6
root@localhost:world > SET GLOBAL rpl_semi_sync_slave_enabled = 0;
配置文件取消
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
....
#rpl_semi_sync_slave_enabled = 1

4、mysql的过滤复制

每个从库只想复制一个库

image-20240826200642230

1
2
3
方法：
可以在主库配置：配置白名单或者黑名单
可以在从库配置：配置白名单或者黑名单

方法一：主库配置(一般不用)

  • binlog-do-db：白名单：只记录白名单中列出的库的二进制日志
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
规范的sql语句先use一个库，再写数据
#在binlog做手脚：binlog只记录prod库的数据，其他的不记录

1、#只复制单库
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
binlog-do-db=prod

2、#重启
[root@db01 ~]# /etc/init.d/mysqld restart

3、#重启之后，从库的主从复制需要重启
root@localhost:world > stop slave;start slave;


4、#主库查看状态：只复制prod这个库的二进制日志
root@localhost:(none) > show master status;
+------------------+----------+--------------+
| File             | Position | Binlog_Do_DB |
+------------------+----------+--------------+
| mysql-bin.000018 |      120 | prod         |#从库只复制prod这个库的二进制日志
+------------------+----------+--------------+



#复制多个库
1、#配置文件写库名字，有几个库就写几行
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
binlog-do-db=prod
binlog-do-db=world


2、#重启
[root@db01 ~]# /etc/init.d/mysqld restart

3、#重启之后，从库的主从复制需要重启
root@localhost:world > stop slave;start slave;

4、#主库查看状态：只复制prod和world这2个库的二进制日志
root@localhost:(none) > show master status;
+------------------+----------+--------------+
| File             | Position | Binlog_Do_DB |
+------------------+----------+--------------+
| mysql-bin.000019 |      120 | prod,world   |
+------------------+----------+--------------+



#测试
1、#主库往prod库里面写数据
root@localhost:(none) > use prod
root@localhost:prod > insert into prod values(77777);

2、#从库查看
root@localhost:world > select * from prod.prod;
+-------+
| 77777 |
+-------+
  • binlog-ignore-db：黑名单：不记录黑名单列出的库的二进制日志
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
#在binlog做手脚：binlog不记录abc库的数据，其他的都记录

1、#不复制单库
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
binlog-ignore-db=abc

2、#重启
[root@db01 ~]# /etc/init.d/mysqld restart

3、#重启之后，从库的主从复制需要重启
root@localhost:world > stop slave;start slave;


4、#主库查看状态：只复制prod这个库的二进制日志
root@localhost:(none) > show master status;
root@localhost:prod > show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+------------------+----------+--------------+------------------+
| mysql-bin.000020 |      120 |              | abc              | 
+------------------+----------+--------------+------------------+
#从库不复制abc这个库的二进制日志




#不复制多个库
1、#配置文件写库名字，有几个库就写几行
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
binlog-ignore-db=abc
binlog-ignore-db=aaa


2、#重启
[root@db01 ~]# /etc/init.d/mysqld restart

3、#重启之后，从库的主从复制需要重启
root@localhost:world > stop slave;start slave;

4、#主库查看状态：只复制prod和world这2个库的二进制日志
root@localhost:(none) > show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+------------------+----------+--------------+------------------+
| mysql-bin.000020 |      120 |              | abc,aaa          | 
+------------------+----------+--------------+------------------+

方法二：从库配置(常用)

白名单：只执行白名单中列出的库或者表的中继日志

  • replicate-do-db=prod (库里所有表都复制)
  • replicate-do-table=prod.prod (库里的某一张表复制)
  • replicate-wild-do-table=prod.p* (库里以p开头的表都复制)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
binlog不记录指定的库，其他的都记录

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

1、#从库配置文件配置
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
replicate-do-db=prod


2、#从库重启
[root@db02 ~]# /etc/init.d/mysqld restart

3、#从库查看状态：只复制prod和world这2个库的二进制日志
root@localhost:(none) > show slave status\G
*************************** 1. row ***************************
.....
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: prod   #只复制prod库

黑名单：不执行黑名单中列出的库或者表的中继日志

  • replicate-ignore-db=abc (库里所有表不复制)
  • replicate-ignore-table=abc.abc (库里的某一张表不复制)
  • replicate-wild-ignore-table=abc.a* (库里以p开头的表都不复制)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL线程不执行 Replicat_Ignore_DB指定的库的SQL语句

1、#从库配置文件配置
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
replicate-ignore-db=abc


2、#从库重启
[root@db02 ~]# /etc/init.d/mysqld restart

3、#从库查看状态：只复制prod和world这2个库的二进制日志
root@localhost:(none) > show slave status\G
*************************** 1. row ***************************
.....
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: abc   #不复制abc库