目录

一、环境搭建、部署

 1. RHEL7.9、9.3的搭建

二、主从复制

 1. 环境说明

 2. 环境准备

  1）克隆RHEL79_mysql_master

  2）改名为 “RHEL79_mysql_slave” 并修改IP

  3）修改主机名

 3. 部署MySQL主从同步

  1）主库(mysql-master)

  2）从库(mysql-slave)

  3）MySQL主从复制的状况检测

  4）生产环境其他常用设置

三、主从复制进阶

 1. 延时同步

 2. GID复制

  1）配置GID复制(主、从)

  2）检测主从是否能够正常通过GTID同步

四、半同步复制

 1. 配置半同步复制

  1）主服务

  2）从服务

 2. 测试

---

一、环境搭建、部署

 1. RHEL7.9、9.3的搭建

• 具体步骤请看煮波前两篇发文

二、主从复制

 1. 环境说明

主机名	ip地址	操作系统	mysql版本
mysql_master	192.168.248.136	rhel7.9	源码安装mysql8.0.40
mysql_slave	192.168.248.137	rhel7.9	源码安装mysql8.0.40

 2. 环境准备

  1）克隆RHEL79_mysql_master

  2）改名为 “RHEL79_mysql_slave” 并修改IP

  3）修改主机名

[root@mysql-master ~]# hostnamectl set-hostname mysql-salve
[root@mysql-master ~]# reboot

 3. 部署MySQL主从同步

  1）主库(mysql-master)

• IP：192.168.248.136

• port：3306

#1）设置server-id值并开启binlog参数---------------------------------------
[root@mysql-master ~]# cat /etc/my.cnf
修改如下内容：
[mysqld]
datadir=/data/mysql   
socket=/data/mysql/mysql.sock  
server_id=136
log-bin=binlog[root@mysql-master ~]# ll /data/mysql/binlog.*    # 查看日志#重启数据库
[root@mysql-master ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL. SUCCESS!#查看数据库的编号
[root@mysql-master ~]# mysql -uroot -p123 -e 'select @@server_id;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         136 |
+-------------+#2）建立同步账号---------------------------------------------------------
[root@mysql-master ~]# mysql -uroot -p123mysql> create user 'rep'@'%' identified  by 'rep123';
mysql> grant  replication slave on *.* to 'rep'@'%';
#查看权限
mysql>  show grants for 'rep'@'%';
+---------------------------------------------+
| Grants for rep@%                            |
+---------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `rep`@`%` |
+---------------------------------------------+#3）锁表设置只读---------------------------------------------------------
#为后面备份准备，注意生产环境要提前申请停机时间；
mysql> flush tables with read lock;#提示：如果超过设置时间不操作会自动解锁。
mysql> show variables like '%timeout%';#测试锁表后是否可以创建数据库：
mysql> create database test;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock#4）查看主库状态--------------------------------------------------------
#查看主库状态，即当前日志文件名和二进制日志偏移量
mysql> show master status;
+------------+--------+------------+----------------+-----------------+
|    File    |Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+------------+-----+--------------+------------------+----------------+
|binlog.000005|  688  |            |                |                 |
+-------------+-------+------------+----------------+-----------------+
#注意：如果想要重置主库，可以使用reset master;生产环境慎用#5）备份主数据库中数据----------------------------------------------------
# mysqldump -uroot -p -A -B |gzip > /server/backup/mysql_bak.$(date +%F).sql.gz    # 有额外的表，需要了再弄#6）解锁---------------------------------------------------------------
mysql> unlock tables;#7）主库备份数据上传到从库-------------------------------------------------
# scp  /server/backup/mysql_bak.2025-08-07.sql.gz 192.168.168.129:/server/backup     # 有额外的表，需要了再弄

  2）从库(mysql-slave)

• IP：192.168.248.137

• port：3306

[root@mysql-salve ~]# rm -rf /data/mysql/*    # 因为是克隆，所以先删MySQL#1）设置server-id值-----------------------------------------------------
[root@rep1 mysql]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server_id=137      # 自己的主机号[root@mysql-salve ~]# mysqld --initialize --user=mysql   # 初始化
默认密码：/9r7?1n9kw+J#重启数据库
[root@mysql-salve ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/master.err'.SUCCESS! #查看数据库的编号
[root@mysql-salve ~]# mysql -uroot -p123 -e 'select @@server_id;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         137 |
+-------------+[root@mysql-salve ~]# mysqladmin -uroot -p password  '123'
Enter password: /9r7?1n9kw+J[root@mysql-salve ~]# mysql -uroot -p123
mysql> #2）还原从主库备份数据----------------------------------------------------
# cd /server/backup/
# gzip  -d mysql_bak.2025-08-07.sql.gz
# mysql -uroot -p < mysql_bak.2025-08-07.sql.gz
#检查还原：
# mysql -uroot -p -e 'show databases;'#3）设定从库向主库同步----------------------------------------------------
mysql> CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.248.136',     # 主服务器IP
SOURCE_USER='rep',
SOURCE_PASSWORD='rep123',
SOURCE_LOG_FILE='binlog.000005',    # 在主服务器的show master status;中看
SOURCE_LOG_POS=688,
SOURCE_SSL=1; #4）启动从库同步开关------------------------------------------------------
mysql> start replica;#5）检查状态-------------------------------------------------------------
mysql> show replica status\G
Slave_IO_Running: Yes    #IO线程是否打开
Slave_SQL_Running: Yes   #SQL线程是否打开#注意：停止从库可以使用命令：mysql> stop replica;

  3）MySQL主从复制的状况检测

• 在主库上查看该主库有多少从库

# 在主库上查看该主库有多少从库
mysql> show replicas;
+---------+----+-----+---------+--------------------------------------+
|Server_Id|Host| Port|Source_Id| Replica_UUID                         |
+---------+----+-----+---------+--------------------------------------+
|    137  |    | 3306|    136  | 991a13b4-888f-11f0-9cd0-000c29926dc4 |
+--------+-----+------+------+----------------------------------------+# 主库创建一个数据库
[root@mysql-master ~]# mysql -uroot -p -e 'create database test1;'
Enter password:     # 密码：123

• 在从库可以看到主库同步过来的数据库

# 在从库可以看到主库同步过来的数据库
[root@mysql-salve ~]# mysql -uroot -p -e 'show databases;'
Enter password:    # 密码：123
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+

  4）生产环境其他常用设置

1、配置不记录information_schema mysql test等数据库的操作（增、删、改）的二进制日志文件------------------------------------------------------------------
# 在主配置文件中添加配置
binlog_ignore_db="information_schema"
binlog_ignore_db="mysql"
binlog_ignore_db="test"
#重启数据库
[root@mysql-master ~]# /etc/init.d/mysqld  restart
[root@mysql-salve ~]# mysql -uroot -p -e 'show master status\G'
Enter password:    # 密码：123
*************************** 1. row ***************************File: binlog.000002Position: 688Binlog_Do_DB: Binlog_Ignore_DB: 
Executed_Gtid_Set: 2、从库备份开启binlog-----------------------------------------------------
log-slave-updates  #当从库为其它从库的主库时必须要添加该参数
log_bin = mysql-bin  #设置二进制日志文件的文件前缀为mysql-bin
expire_logs_days = 7  #设置二进制日志文件的保留时间
应用场景：级联复制或从库做数据备份。3、从库只读-------------------------------------------------------------
read-only
innodb_read_only = ON或1
注：当用户权限中没有SUPER权限(ALL权限是包括SUPER的)时，从库的read-only生效！

三、主从复制进阶

 1. 延时同步

• 从库

# 配置从库延时同步，设置sql线程延迟300秒后读取relay log
mysql> stop replica;   # 停止同步mysql> CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 300;  #延迟300s，5分钟mysql>start replica;mysql> show replica status\G
SQL_Delay: 300   
SQL_Remaining_Delay: NULLmysql> stop slave sql_thread;    # 停掉slave的线程

• 主库

#主库上创建库和表
mysql> create database relay;
mysql> use relaymysql> create table t1(id int);
mysql> insert into t1 values (1);
mysql> drop database relay;

• 从库

mysql> show relaylog events in 'mysql-salve-relay-bin.000002';mysql> start slave sql_thread;#将relay log进行备份
[root@mysql-salve ~]# mysqlbinlog --start-position=584 --stop-position=1237  /data/mysql/slave1-relay-bin.000002 > /tmp/mysql.backup#恢复误删除的数据库
[root@mysql-salve ~]# mysql -uroot -p -e 'source /tmp/relay.sql'
#查看数据是否恢复
mysql> select * from relay.t1;
+------+
| id   |
+------+
|    1 |
+------+#解除从库身份
mysql> stop replica;
mysql> reset replica all;

 2. GID复制

  1）配置GID复制(主、从)

• 在主master和从slave上执行

mysql> select @@enforce_gtid_consistency;
+----------------------------+
| @@enforce_gtid_consistency |
+----------------------------+
| OFF                        |
+----------------------------+#确定GTID模式复制的兼容性：查看日志中是否有警告，如果有警告说明应用和GTID复制不兼容
mysql> set global enforce_gtid_consistency=warn;#启用GTID强一致性检查，防止GTID不兼容的语句导致复制失败
mysql> set global enforce_gtid_consistency=on;#将主和从的gtid_mode一步步从OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON
mysql> select @@gtid_mode;
+-------------+
| @@gtid_mode |
+-------------+
| OFF         |
+-------------+mysql> set global gtid_mode=OFF_PERMISSIVE;
mysql> set global gtid_mode=ON_PERMISSIVE;#在主库和从库上等待所有匿名事务复制完成（该变量为0）
mysql> show status like 'ongoing_anonymous_transaction_count';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| Ongoing_anonymous_transaction_count | 0     |
+-------------------------------------+-------+#启用GTID模式
mysql> set global gtid_mode=ON;#建议将enforce_gtid_consistency=on和gtid_mode=ON写入配置文件，防止配置丢失；

• 在从slave上重启复制线程

mysql> stop replica;
mysql> change master to master_auto_position=1;
mysql> start replica;
mysql> show replica status \GReplica_IO_Running: YesReplica_SQL_Running: Yes

  2）检测主从是否能够正常通过GTID同步

• 主服务

# 在主上添加表并插入数据
mysql> create database test2;
mysql> use test2;
mysql> create table t1 (id int);
mysql> insert into t1 values(1);
mysql> show master status \G
*************************** 1. row ***************************File: binlog.000009Position: 811Binlog_Do_DB: Binlog_Ignore_DB: 
Executed_Gtid_Set: a3b49872-87d4-11f0-80e9-000c294b1068:1-3#查看日志信息
mysql> show binlog events in "binlog.000009"\G#查看已经执行过的gtid
mysql> select @@gtid_executed;
+------------------------------------------+
| @@gtid_executed                          |
+------------------------------------------+
| a3b49872-87d4-11f0-80e9-000c294b1068:1-3 |
+------------------------------------------+

• 从服务

# 在从上进行查看
mysql> show replica status \G
*************************** 1. row ***************************Replica_IO_State: Waiting for source to send eventSource_Host: 192.168.248.136Source_User: repSource_Port: 3306Connect_Retry: 60Source_Log_File: binlog.000009Read_Source_Log_Pos: 811Relay_Log_File: mysql-salve-relay-bin.000002Relay_Log_Pos: 367Relay_Source_Log_File: binlog.000009Replica_IO_Running: YesReplica_SQL_Running: Yes#查看从上的relaylog日志
mysql> show relaylog events in "mysql-salve-relay-bin.000002"\G#在从库上查看数据是否同步
mysql> select * from test2.t1;
+------+
| id   |
+------+
|    1 |
+------+

四、半同步复制

 1. 配置半同步复制

  1）主服务

#主的配置，加载插件
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';#查看插件是否加载成功
mysql> show plugins;#开启此开关，可写入配置文件
mysql> set global rpl_semi_sync_master_enabled=1;#设置主等待从回复ACK的超时时间为3秒，默认是10秒，可写入配置文件
mysql> set global rpl_semi_sync_master_timeout=3000;mysql> show 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     |
+--------------------------------------------+-------+mysql> show variables like 'rpl_semi_sync_master%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 3000       |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+

  2）从服务

#从的配置
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> set global rpl_semi_sync_slave_enabled=1;
mysql> show status like 'rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+#查看插件是否加载成功
mysql> show plugins;
#重启从的IO线程
mysql> STOP REPLICA IO_THREAD;
Query OK, 0 rows affected (0.01 sec)
mysql> START REPLICA IO_THREAD;

 2. 测试

# 主上创建一张表
mysql> use test2
mysql> create table t2(id int);
mysql> show status like 'Rpl_semi_sync_master_yes_tx';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_yes_tx | 1     |#master成功接收到slave的回复的次数
+-----------------------------+-------+# 模拟从故障
mysql> stop slave io_thread;
mysql> set global rpl_semi_sync_slave_enabled=0;
mysql> show status like 'rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF   |
+----------------------------+-------+# 在主上创建表，可以看到会等待一个超时时间
mysql> insert into t2 values(2);
Query OK, 1 row affected (10.00 sec)# 发现主关闭了半同步复制
mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | OFF   |
+-----------------------------+-------+