MySQL主从搭建

主从复制

主从复制简介

1
2
3
主从复制时基于二进制日志进行复制的
主库的修改会记录二进制日志
从库请求二进制日志解析SQL语句并执行，从而达到主从数据同步

前提

1
2
3
4
5
6
两台以上mysql实例 ,server_id,server_uuid不同
主库开启二进制日志
主库建立专用同步用户
主从开启时间点，主从数据需要一致 
告诉从库复制IP，user，passwd，以及二进制文件和复制起点
开启复制线程  (start slave;)

搭建环境：

主库 从库 mysql版本
192.168.177.200 192.168.177.201 5.7.41

主库

设置主库server-id并开启主库二进制

1
2
3
4
5
6
7
8
vim /etc/my.cnf
#开启二进制日志指定文件路径
log-bin=/web/logs/mysql/binlog/mysql-bin
#设置serverID
server-id=1

#连接数设置：最多可以两个
#max_connections=1       

1
2
3
主从server-id不能相同
每个 MySQL 服务器在主从复制架构中需要有一个唯一的标识符。server_id用于区分不同的服务器实例。如果多个服务器具有相同的 server_id，那么在复制过程中会产生冲突和混乱。
如果主从服务器的 server_id 相同，可能会导致双主循环复制的问题，可能会使数据库陷入混乱或崩溃。

主库中创建专用复制用户

1
2
3
mysql -u root -p                                                         //登录mysql终端
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.177.%' IDENTIFIED BY '123456'; //创建用户授予权限
flush privileges;                                        			    //刷新权限

查看二进制文件

1
2
3
4
5
6
7
mysql>  show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 1059
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 6533bab9-8a0a-11ef-b891-000c299acf13:1-5

从库

设置从库server-id

1
2
3
vim /etc/my.cnf
#设置serverID
server-id=2

执行同步SQL语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CHANGE MASTER TO
  MASTER_HOST='192.168.177.200',
  MASTER_USER='repl',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=1059,
  MASTER_CONNECT_RETRY=10;
  
  CHANGE MASTER TO            
  MASTER_HOST                 //主库的 IP 地址
  MASTER_USER                 //连接主库进行复制的用户名
  MASTER_PASSWORD             //连接主库的用户密码
  MASTER_PORT                 //主库的端口号
  MASTER_LOG_FILE             //指定主库同步二进制日志文件名
  MASTER_LOG_POS              //二进制文件开始同步位置
  MASTER_CONNECT_RETRY        //连接主库失败后重试时间间隔(s)

开启同步进程

1
start slave;

查看同步状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.177.200
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1059
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

主从复制原理

描述

1
2
3
4
5
6
7
8
9
10
11
12
13
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线程再请求

线程

1
2
3
4
5
主:
Binlog_Dump_Thread
从:
Slave_IO_Thread
Slave_SQL_Thread

文件

1
2
3
4
5
6
主:
mysql-bin.000003             二进制日志
从: 
dbname-relay-bin.000002      中继日志
master.info                  主库信息记录日志
relay-log.info               记录中继应用情况信息

主从故障监控

线程监控

主库：

1
2
3
4
5
6
7
8
9
10
11
mysql> show full processlist\G;
每一个从库都会有一行 Binlog Dump 相关的信息
*************************** 1. row ***************************
     Id: 4
   User: repl
   Host: 192.168.177.201:45880
     db: NULL
Command: Binlog Dump
   Time: 1450
  State: Master has sent all binlog to slave; waiting for more updates
   Info: NULL

从库：

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
mysql> show slave status\G;
*************************** 1. row ***************************
主库相关信息：
                  Master_Host: 192.168.177.200
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1636
中继日志：
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 897
复制线程状态：
             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: 
主从延时相关状态：(非人为)(单位：s)
        Seconds_Behind_Master: 0     
延时从库有关的状态：(人为)
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
GTID 复制有关的状态：
           Retrieved_Gtid_Set: 6533bab9-8a0a-11ef-b891-000c299acf13:6-8
            Executed_Gtid_Set: 6533bab9-8a0a-11ef-b891-000c299acf13:6-8,
                               65c5f12a-8a0a-11ef-8a2e-000c29c3145d:1-2
                Auto_Position: 0

延时从库

介绍

1
是一种特殊的从库，认为配置从库和主库延时N小时

为啥配置延时从库

1
2
3
4
5
6
7
数据库故障?
物理损坏
主从复制非常擅长解决物理损坏.
逻辑损坏
普通主从复制没办法解决逻辑损坏(删库、删表)

***延时从库不提供业务***

配置延时从库

1
2
3
4
5
6
7
8
9
10
11
SQL线程延时:数据已经写入relay-log中了,SQL线程"慢点"运行
一般企业建议3-6小时,具体看公司运维人员对于故障的反应时间


mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_DELAY = 300;
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
                    SQL_Delay: 300                                //设定的延时时间(s)
          SQL_Remaining_Delay: NULL                               //到下条语句执行还剩多少时间(s)

延时从库应用

1
2
3
4
5
6
7
8
1主1从,从库延时5分钟,主库误删除1个库
1. 5分钟之内 侦测到误删除操作
2. 立即停延时从库SQL线程
3. 截取relaylog进行恢复
	起点 :停止SQL线程时,relay最后应用位置
	终点:误删除之前的position
4. 恢复截取的日志到从库
5. 从库替代主库工作

案例

主

1
2
3
4
mysql> create table t1(id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;
mysql> drop table t1;

延时从

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
停止SQL线程：
mysql> stop slave sql_thread;
截取Relay_Log
起点：
mysql>  show slave status\G;
               Relay_Log_File: localhost-relay-bin.000003
                Relay_Log_Pos: 360    
终点：
mysql> show relaylog events in 'localhost-relay-bin.000002';
localhost-relay-bin.000003 | 846 | Query | 1 | 3610 | use `amt`; DROP TABLE `t1` /* generated by server */ 

截取：
[root@localhost data]# mysqlbinlog --start-position=360 --stop-position=846 /web/data/mysql/data/localhost-relay-bin.000003 >/tmp/relay.sql

恢复：
mysql> source /tmp/relay.sql;

验证：
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

从库代替主库：
mysql> stop slave;
mysql> reset slave all;
更换业务数据库

半同步

1
解决主从数据一致性问题

半同步复制工作原理的变化

1
2
3
4
5
6
7
1. 主库执行新的事务,commit时,更新 show master status\G ,触发一个信号给binlog dump
2. binlog dump 接收到主库的 show master status\G信息,通知从库日志更新了
3. 从库IO线程请求新的二进制日志事件
4. 主库会通过dump线程传送新的日志事件,给从库IO线程
5. 从库IO线程接收到binlog日志,当日志写入到磁盘上的relaylog文件时,给主库ACK_receiver线程
6. ACK_receiver线程触发一个事件,告诉主库commit可以成功了
7. 如果ACK达到了我们预设值的超时时间,半同步复制会切换为原始的异步复制.

配置半同步

加载插件

主

1
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

从

1
2
3
4
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

查看是否加载成功:
show plugins;

启动

主

1
SET GLOBAL rpl_semi_sync_master_enabled = 1;

从

1
2
3
4
5
6
SET GLOBAL rpl_semi_sync_master_enabled = 1;


重启从库上的IO线程
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

查看是否在运行

主

1
show status like 'Rpl_semi_sync_master_status';

从

1
show status like 'Rpl_semi_sync_slave_status';

过滤复制

参数

主库参数：

1
2
3
Binlog_Do_DB               //白名单
Binlog_Ignore_DB           //黑名单
控制记录二进制日志记录

从库参数：

1
2
3
4
mysql> show slave status\G;
*************************** 1. row ***************************
              Replicate_Do_DB:               //白名单库 
          Replicate_Ignore_DB:               //黑名单库

配置

1
2
3
4
5
6
7
8
vim /etc/my.cnf
#复制过滤 可以只设置一个(白名单或者黑名单)
#设置白名单
replicate_do_db=amt
#设置黑名单
replicate_ignore_db=old

/etc/init.d/mysql restart

查看

1
2
3
4
mysql> show slave status\G;
*************************** 1. row ***************************
              Replicate_Do_DB: amt
          Replicate_Ignore_DB: old

GTID复制

介绍

1
2
3
4
5
6
GTID(Global Transaction ID)是对于一个已提交事务的唯一编号，并且是一个全局(主从复制)唯一的编号。
它的官方定义如下：
GTID = source_id ：transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
什么是sever_uuid，和Server-id 区别？
核心特性: 全局唯一,具备幂等性

核心参数

1
2
3
4
5
6
7
8
vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1

gtid-mode=on                            //启用gtid类型，否则就是普通的复制架构
enforce-gtid-consistency=true           //强制GTID的一致性
log-slave-updates=1slave                //slave更新是否记入日志

环境

主库 从库1 从库2 MySQL版本
192.168.177.200 192.168.177.201 192.168.177.202 5.7.41

配置

主

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[mysqld]
basedir=/web/soft/mysql/
datadir=/web/data/mysql/data/
socket=/web/soft/mysql/mysql.sock
#开启二进制日志指定文件路径
log-bin=/web/logs/mysql/binlog/mysql-bin
server-id=1
pid-file=/web/soft/mysql/mysql.pid
#配置不需要密码登录
#skip-grant-tables

#开启GTID
gtid-mode=on
#强制 GTID 一致性
enforce-gtid-consistency=true
#从服务器将其执行的更新操作记录到自己的二进制日志中
log-slave-updates=1

从1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[mysqld]
basedir=/web/soft/mysql/
datadir=/web/data/mysql/data/
socket=/web//soft/mysql/mysql.sock
#开启二进制日志指定文件路径
log-bin=/web/logs/mysql/binlog/mysql-bin
server-id=2
pid-file=/web/soft/mysql/mysql.pid
#配置不需要密码登录
#skip-grant-tables

#GTID
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1

从2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[mysqld]
basedir=/web/soft/mysql/
datadir=/web/data/mysql/data/
socket=/web//soft/mysql/mysql.sock
#开启二进制日志指定文件路径
log-bin=/web/logs/mysql/binlog/mysql-bin
server-id=3
pid-file=/web/soft/mysql/mysql.pid
#配置不需要密码登录
#skip-grant-tables

#GTID
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1

重启

1
/etc/init.d/mysql restart

构建主从

主

1
2
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.177.%' IDENTIFIED BY '123456'; 	 //创建用户授予权限
flush privileges;                                        			    //刷新权限

从

1
2
3
4
5
6
7
8
9
10
11
清除原来的主从连接
stop slave;
reset slave all;

CHANGE MASTER TO
  MASTER_HOST='192.168.177.200',
  MASTER_USER='repl',
  MASTER_PASSWORD='123456',
  MASTER_AUTO_POSITION=1;
  
  start slave;

GTID 从库误写入操作处理

1
2
3
4
5
6
注入空事物的：(生产禁用)
stop slave;
set gtid_next='6533bab9-8a0a-11ef-b891-000c299acf13:27';
begin;commit;
set gtid_next='AUTOMATIC';
start slave;

GTID复制跟传统复制区别

1
2
3
4
5
6
7
8
9
10
11
1、在启动主从复制时，不需要指定binlog文件名和position号，之间auto即可
2、自动读取最后一个relay，获取到上次已经复制的GTID号，从此号码开始向后复制即可
3、在MHA高可用环境下，主库无法SSH时，从库进行数据补偿，更加便捷了

在主从复制环境中，主库发生过的事务，在全局都是由唯一GTID记录的，更方便Failover
额外功能参数（3个）
change master to 的时候不再需要binlog 文件名和position号,MASTER_AUTO_POSITION=1;
在复制过程中，从库不再依赖master.info文件，而是直接读取最后一个relaylog的 GTID号
mysqldump备份时，默认会将备份中包含的事务操作，以以下方式：
    SET @@GLOBAL.GTID_PURGED='8c49d7ec-7e78-11e8-9638-000c29ca725d:1';
    告诉从库，我的备份中已经有以上事务，你就不用运行了，直接从下一个GTID开始请求binlog就行。
MySQL
#Linux #MySQL
MySQL主从搭建
https://yftxhy.site/2024/10/15/MySQL主从搭建/
作者
Taozi
发布于
2024年10月15日
许可协议