Linux云盘算 |【第四阶段】RDBMS2-DAY1

重要内容：

MySQL主从同步（概述、原理、构建主从同步）、主从同步结构范例（主多从、主从从、主主）、复制模式（异步、半同步）、启用半同步复制

一、MySQL主从同步

MySQL 主从同步（Master-Slave Replication）是一种常见的数据库复制技术，用于进步数据库的可用性、扩展性和数据安全性。通过主从同步，可以将一个 MySQL 数据库（主库）的数据复制到一个或多个 MySQL 数据库（从库），从而实现数据的冗余备份和读写分离。

1、主从同步概述

1）实现同步的服务结构

• ① 主服务器（主库）：接收从服务器访问毗连；
  
• ② 从服务器（从库）：自动同步主服务器数据；
  

2）主从同步的工作原理

   主库（Master）：
  

• 主库是数据的源头，负责处理所有的写操作（INSERT、UPDATE、DELETE）。
  
• 主库将所有的写操作记录在二进制日志（Binary Log）中。
  

  从库（Slave）：
  

• 从库是主库的副本，负责处理读操作（SELECT）。
  
• 从库通过读取主库的二进制日志，将主库的写操作应用到自身，从而保持与主库的数据同等性。
  

  二进制日志（Binary Log）：
  

• 二进制日志记录了主库的所有写操作，包括事务的开始和结束。
  
• 从库通过毗连到主库，读取并应用这些二进制日志，实现数据的同步。
  

  3）主从同步的设置步骤

① 设置主库（Master）

• 启用二进制日志：在主库的设置文件（通常是 my.cnf 或 my.ini）中，添加以下设置：
  

1. [mysqld]
2. log_bin = /var/log/mysql/mysql-bin.log
3. server-id = 1
4. binlog_do_db = your_database_name

复制代码

  log_bin：指定二进制日志文件的路径和名称。
  server-id：指定主库的唯一标识符。
  binlog_do_db：指定需要同步的数据库名称（可选）。
  

• 重启 MySQL 服务：重启 MySQL 服务以应用设置更改：
  

1. sudo systemctl restart mysql

复制代码

• 创建用于复制的用户：在主库上创建一个用于复制的用户，并授予复制权限：
  

1. CREATE USER 'repl_user'@'slave_ip' IDENTIFIED BY 'password';
2. GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'slave_ip';
3. FLUSH PRIVILEGES;

复制代码

• 获取主库的二进制日志位置：在主库上执行以下下令，获取当前的二进制日志文件和位置：
  

1. FLUSH TABLES WITH READ LOCK;
2. SHOW MASTER STATUS;

复制代码

  记录 File 和 Position 的值，这些值将用于从库的设置。
  ② 设置从库（Slave）

• 设置从库：在从库的设置文件中，添加以下设置：
  

1. [mysqld]
2. server-id = 2
3. relay_log = /var/log/mysql/mysql-relay-bin.log
4. log_bin = /var/log/mysql/mysql-bin.log
5. binlog_do_db = your_database_name

复制代码

  server-id：指定从库的唯一标识符。
  relay_log：指定中继日志文件的路径和名称。
  log_bin：指定二进制日志文件的路径和名称（可选）。
  binlog_do_db：指定需要同步的数据库名称（可选）。
  

• 重启 MySQL 服务：重启 MySQL 服务以应用设置更改：
  

1. sudo systemctl restart mysql

复制代码

• 设置从库毗连主库：在从库上执行以下下令，设置从库毗连到主库：
  

1. CHANGE MASTER TO
2. MASTER_HOST='master_ip',
3. MASTER_USER='repl_user',
4. MASTER_PASSWORD='password',
5. MASTER_LOG_FILE='mysql-bin.000001',
6. MASTER_LOG_POS=12345;

复制代码

  MASTER_HOST：主库的 IP 地址或主机名。
  MASTER_USER：用于复制的用户名。
  MASTER_PASSWORD：用于复制的用户暗码。
  MASTER_LOG_FILE：主库的二进制日志文件名。
  MASTER_LOG_POS：主库的二进制日志位置。
  

• 启动从库复制：在从库上执行以下下令，启动复制历程：
  

1. START SLAVE;

复制代码

• 检查从库状态：在从库上执行以下下令，检查复制状态：
  

1. SHOW SLAVE STATUS\G

复制代码

  检查 Slave_IO_Running 和 Slave_SQL_Running 是否为 Yes，表现复制正常运行。
  2、主从同步的优点

   进步可用性：通过数据冗余，进步数据库的可用性，淘汰单点故障的风险
  读写分离：将读操作分配到从库，减轻主库的负载，进步体系性能
  数据备份：从库可以作为主库的备份，提供数据的安全性和可靠性
  扩展性：通过增加从库，可以扩展体系的读能力，支持更多的并发读操作
  3、主从同步的缺点

   延迟问题：从库的数据同步大概存在延迟，特别是在主库写操作频仍的情况下
  单点写入：主库仍旧是单点写入，如果主库故障，需要手动切换到从库
  设置复杂：主从同步的设置和管理相对复杂，需要仔细规划和维护
  

---

构建主从同步示例：

实验拓扑结构：

• 服务器角色：node10为主(Master），node11为从(Slave)；
  
• node10设置：ip地址为192.168.2.10，安装mysql，初始化暗码为NSD2021@tedu.cn
  
• node11设置：ip地址为192.168.2.11，安装mysql，初始化暗码为NSD2021@tedu.cn
  

   提示：防火墙与SELinux提前关闭；
  步骤1：准备初始化情况（node10、node11操作）
① 准备MySQL数据库，并初始化暗码（以node10为例）

1. [root@node10 ~]# yum -y install mysql-community-*   //检查Yum源，并安装MySQL
2. [root@node10 ~]# systemctl start mysqld     //启动服务
3. [root@node10 ~]# systemctl enable mysqld    //设置开机自启
4. [root@node10 ~]# grep -i password /var/log/mysqld.log    //查看生成的随机密码
5. 2021-06-10T13:44:26.945514Z 1 [Note] A temporary password is generated for root@localhost: O-,Ya=5qpkpW
6. [root@node10 ~]# mysqladmin -uroot -p' O-,Ya=5qpkpW ' password 'NSD2021@tedu.cn'

复制代码

② 导入sql数据（仅node10操作）
   参考：# git clone https://gitee.com/mrzhangzhg/tedu_nsd.git

1. [root@localhost ~]# scp -r /var/lib/mygit/tedu_nsd/dbs/mysql_scripts/ 192.168.2.10:/root
2. [root@node10 ~]# ls
3. anaconda-ks.cfg  mysql_scripts
4. [root@node10 ~]# mysql -uroot -pNSD2021@tedu.cn < mysql_scripts/nsd2021_data.sql
5. [root@node10 ~]# mysql -uroot -pNSD2021@tedu.cn -e 'show databases;'
6. +--------------------+
7. | Database           |
8. +--------------------+
9. | information_schema |
10. | mysql              |
11. | nsd2021            |
12. | performance_schema |
13. | sys                |
14. +--------------------+

复制代码

 
步骤2：设置主服务器（node10操作）
① 修改设置文件，并重启服务

1. [root@node10 ~]# vim /etc/my.cnf
2. [mysqld]
3. server_id = 10         //服务器身份标识
4. log_bin = master10    //启用binlog日志
5. ...
6. [root@node10 ~]# systemctl restart mysqld   //重启服务

复制代码

② 检察生成的binlog日志文件

1. [root@node10 ~]# ls /var/lib/mysql/master10.*
2. /var/lib/mysql/master10.000001  /var/lib/mysql/master10.index
3. [root@node10 ~]# mysql -uroot -pNSD2021@tedu.cn
4. mysql> show master status;
5. +-----------------+----------+--------------+------------------+-------------------+
6. | File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
7. +-----------------+----------+--------------+------------------+-------------------+
8. | master10.000001 |      154 |              |                  |                   |
9. +-----------------+----------+--------------+------------------+-------------------+
10. 1 row in set (0.00 sec)

复制代码

③ 授权Slave从服务器，可通过repluser用户同步数据

1. mysql> grant replication slave on *.* to repluser@'%' identified by 'NSD2021@tedu.cn';
2. mysql> show grants for repluser@'%';
3. +--------------------------------------------------+
4. | Grants for repluser@%                            |
5. +--------------------------------------------------+
6. | GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' |
7. +--------------------------------------------------+
8. 1 row in set (0.00 sec)

复制代码

步骤3：设置从服务器（node11操作）
① 修改设置文件，并重启服务

1. [root@node11 ~]# vim /etc/my.cnf
2. [mysqld]
3. server_id = 11    //服务器身份标识
4. ...
5. [root@node11 ~]# systemctl restart mysqld

复制代码

② 通过mysqldump手动同步主服务数据库数据
   注意：从服务器与主服务器进行自动同步前提，需保证之前已有的数据库数据同等；
  # 主服务器备份数据，并拷贝备份文件到从服务器

1. [root@node10 ~]# mysqldump -uroot -pNSD2021@tedu.cn --master-data nsd2021 > /root/fullbackup.sql
2. [root@node10 ~]# scp /root/fullbackup.sql root@192.168.2.11:/root

复制代码

# 从服务器恢复数据

1. [root@node11 ~]# mysql -uroot -pNSD2021@tedu.cn
2. mysql> create database nsd2021 default charset utf8mb4;
3. Query OK, 1 row affected (0.00 sec)
4. [root@node11 ~]# mysql -uroot -pNSD2021@tedu.cn nsd2021 < /root/fullbackup.sql

复制代码

  【--master-data】作用：如果完全备份完成后，又有新的数据产生，它可记录备份时的数据状态信息。
  
步骤4：设置从服务器与主服务器同步
① 在同步的备份文件中，检察binlog日志名(master_log_file)和偏移量(master_log_pos)
   注意：没有过滤出日志名和偏移量，需检查主服务器的日志状态

1. [root@node11 ~]# grep master10 /root/fullbackup.sql
2. CHANGE MASTER TO MASTER_LOG_FILE='master10.000001', MASTER_LOG_POS=441;

复制代码

② 从服务器指定主服务器同步信息

1. [root@node11 ~]# mysql -uroot -pNSD2021@tedu.cn
2. mysql> change master to
3.     -> master_host='192.168.2.10',    //指定连接主库地址
4.     -> master_user='repluser',        //指定连接已授权用户
5.     -> master_password='NSD2021@tedu.cn',     //授权用户密码
6.     -> master_log_file='master10.000001',     //binlog日志
7.     -> master_log_pos=441;      //偏移量
8. Query OK, 0 rows affected, 2 warnings (0.00 sec)

复制代码

③ 启动slave服务器历程

1. mysql> start slave;
2. Query OK, 0 rows affected (0.01 sec)

复制代码

④ 验证：检察slave状态信息

1. mysql> show slave status\G
2. *************************** 1. row ***************************
3.                Slave_IO_State: Waiting for master to send event
4.                   Master_Host: 192.168.2.10
5.                   Master_User: repluser
6.                   Master_Port: 3306
7.                 Connect_Retry: 60
8.               Master_Log_File: master10.000001
9.           Read_Master_Log_Pos: 441
10.                Relay_Log_File: node11-relay-bin.000004
11.                 Relay_Log_Pos: 319
12.         Relay_Master_Log_File: master10.000001
13.              Slave_IO_Running: Yes
14.             Slave_SQL_Running: Yes
15. …

复制代码

⑤ 验证：检察从服务器上的相关文件

1. [root@node11 ~]# ls /var/lib/mysql

复制代码

文件名	阐明
master.info	主库信息（端口、IP、日志、用户账户暗码、UUID等）
relay-log.info	中继日志信息（中继日志、端口、偏移量等）
主机名-relay-bin.编号	中继日志（binlog日志记录的SQL下令）
主机名-relay-bin.index	索引日志（记录已有的中继日志）

   补充：将目录中的四类从库文件全部删除，再重启服务，可还原为独立的、非从服务器；
  

---

排错思路：
① 通过`show slave status`检察Error错误阐明；
② 如果设置主库信息输入错误，需先关闭slave功能，设置完成后再开启slave功能

1. mysql> stop slave;   //停止slave
2. mysql> start slave;   //开启slave
3. mysql> show slave status\G

复制代码

③ master.info文件和relay-log.info文件有设置记录，可用于排错；

常见报错：Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

• 原因分析：主从架构中使用了相同的UUID，原因是克隆了虚拟机，而只改server_id不行
  
• 解决办法：将从服务器上的/var/lib/mysql/auto.cnf重定名，重启Mysqld服务生成新的auto.cnf文件，即新的UUID；或者直接修改UUID，不重复即可；
  

1. [root@node11 mysql]# mv auto.cnf auto.cnf.bak
2. [root@node11 mysql]# systemctl restart mysqld
3. [root@node11 mysql]# vim auto.cnf
4. [auto]
5. server-uuid=fd780a70-ca14-11eb-9562-000c29da9126

复制代码

---

步骤5：验证主从架构同步效果
① 在主服务器上的数据库中新增数据

1. [root@node10 ~]# mysql -uroot -pNSD2021@tedu.cn
2. mysql> insert into nsd2021.departments(dept_name) values('行政部');
3. Query OK, 1 row affected (0.01 sec)
4. mysql> select * from nsd2021.departments where dept_name='行政部';
5. +---------+-----------+
6. | dept_id | dept_name |
7. +---------+-----------+
8. |       9 | 行政部    |
9. +---------+-----------+
10. 1 row in set (0.00 sec)

复制代码

② 从服务器验证同步数据

1. [root@node11 ~]# mysql -uroot -pNSD2021@tedu.cn
2. mysql> select * from nsd2021.departments;
3. +---------+-----------+
4. | dept_id | dept_name |
5. +---------+-----------+
6. |       1 | 人事部    |
7. |       2 | 财务部    |
8. |       3 | 运维部    |
9. |       4 | 开发部    |
10. |       5 | 测试部    |
11. |       6 | 市场部    |
12. |       7 | 销售部    |
13. |       8 | 法务部    |
14. |       9 | 行政部    |
15. +---------+-----------+
16. 9 rows in set (0.00 sec)

复制代码

二、主从同步架构

1、常见的架构范例

① 单主单从架构，一主一从

   架构描述：一个主库和一个从库，主库负责所有的写操作，从库负责读操作
优点：
  - 简单易设置
- 适用于读多写少的场景
缺点：
  - 主库仍旧是单点写入，存在单点故障风险
- 从库的数据同步大概存在延迟
适用场景：
  - 小型应用或初创企业
- 读操作远多于写操作的场景
  ② 单主多从架构，一主多从

 
   架构描述：一个主库和多个从库。主库负责所有的写操作，多个从库负责读操作
优点：
  - 进步了读操作的扩展性
- 通过增加从库，可以分担主库的读负载
缺点：
  - 主库仍旧是单点写入，存在单点故障风险
- 从库的数据同步大概存在延迟
适用场景：
  - 读操作远多于写操作的场景
- 需要扩展读能力的应用
  ③ 级联复制架构，主从从（主->从主->从）

   架构描述：一个主库和多个从库，此中一个从库作为中心节点，再毗连到其他从库；
主库负责所有的写操作，中心节点从库负责部分读操作，并作为其他从库的主库
优点：
  - 减轻主库的复制负载
- 进步了读操作的扩展性
缺点：
  - 增加了复制的复杂性
- 中心节点的故障会影响到卑鄙从库
适用场景：
  - 主库的复制负载较高，需要减轻主库压力的场景
- 需要扩展读能力的应用
  ④ 环形复制架构，主主结构、双主结构（互为主从）

   架构描述：多个主库之间形成一个环形复制链。每个主库既是主库也是从库，数据在环形链中循环复制
优点：
  - 进步了写操作的可用性和扩展性
- 避免了单点写入的故障风险
缺点：
  - 设置和管理非常复杂
- 存在数据同等性问题，需要解决冲突
适用场景：
  - 需要极高可用性和扩展性的写操作场景
- 对数据同等性要求不高的应用
  

---

构建一主多从同步示例：（基于主从架构，额外添加一个从服务器）

实验拓扑结构：

• 服务器角色：node10为主(Master），node11为从(Slave1) ，node12为从(Slave2)；
  
• node10设置：ip地址为192.168.2.10，安装mysql，初始化暗码为NSD2021@tedu.cn
  
• node11设置：ip地址为192.168.2.11，安装mysql，初始化暗码为NSD2021@tedu.cn
  
• node12设置：ip地址为192.168.2.12，安装mysql，初始化暗码为NSD2021@tedu.cn
  

   提示：防火墙与SELinux提前关闭；
  步骤1：准备初始化情况（node12操作）
① 准备MySQL数据库，并初始化暗码

1. [root@node12 ~]# yum -y install mysql-community-*   //检查Yum源，并安装MySQL
2. [root@node12 ~]# systemctl start mysqld     //启动服务
3. [root@node12 ~]# systemctl enable mysqld    //设置开机自启
4. [root@node12 ~]# grep -i password /var/log/mysqld.log    //查看生成的随机密码
5. 2021-06-10T13:44:26.945514Z 1 [Note] A temporary password is generated for root@localhost: O-,Ya=5qpkpW
6. [root@node12 ~]# mysqladmin -uroot -p' O-,Ya=5qpkpW ' password 'NSD2021@tedu.cn'

复制代码

步骤2：主服务器数据备份（node10操作）
① 检查设置文件

1. [root@node10 ~]# cat /etc/my.cnf
2. [mysqld]
3. server_id = 10         //服务器身份标识
4. log_bin = master10    //启用binlog日志
5. ...

复制代码

② 检查授权用户

1. mysql> show grants for repluser@'%';
2. +--------------------------------------------------+
3. | Grants for repluser@%                            |
4. +--------------------------------------------------+
5. | GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' |
6. +--------------------------------------------------+
7. 1 row in set (0.00 sec)

复制代码

③ 主服务器完全备份数据，并拷贝到从服务器

1. [root@node10 ~]# mysqldump -uroot -pNSD2021@tedu.cn --master-data nsd2021 > /root/fullbackup.sql
2. [root@node10 ~]# scp /root/fullbackup.sql root@192.168.2.12:/root

复制代码

步骤3：设置从服务器（node12操作）
① 修改设置文件，并重启服务

1. [root@node12 ~]# vim /etc/my.cnf
2. [mysqld]
3. server_id = 12    //服务器身份标识
4. ...
5. [root@node12 ~]# systemctl restart mysqld

复制代码

② 从服务器手动同步主服务数据库数据

1. [root@node12 ~]# mysql -uroot -pNSD2021@tedu.cn
2. mysql> create database nsd2021 default charset utf8mb4;
3. Query OK, 1 row affected (0.00 sec)
4. [root@node12 ~]# mysql -uroot -pNSD2021@tedu.cn nsd2021 < /root/fullbackup.sql

复制代码

步骤4：设置从服务器同步主服务器
① 获取日志文件名和偏移量

1. [root@node12 ~]# grep master10 /root/fullbackup.sql
2. CHANGE MASTER TO MASTER_LOG_FILE='master10.000001', MASTER_LOG_POS=717;

复制代码

② 从服务器指定主服务器同步信息

1. [root@node12 ~]# mysql -uroot -pNSD2021@tedu.cn
2. mysql> change master to
3.     -> master_host='192.168.2.10',
4.     -> master_user='repluser',
5.     -> master_password='NSD2021@tedu.cn',
6.     -> master_log_file='master10.000001',
7.     -> master_log_pos=717;
8. Query OK, 0 rows affected, 2 warnings (0.00 sec)

复制代码

③ 启动slave服务器历程

1. mysql> start slave;
2. Query OK, 0 rows affected (0.01 sec)

复制代码

④ 验证：检察slave状态信息

1. mysql> show slave status\G
2. *************************** 1. row ***************************
3.                Slave_IO_State:
4.                   Master_Host: 192.168.2.10
5.                   Master_User: repluser
6.                   Master_Port: 3306
7.                 Connect_Retry: 60
8.               Master_Log_File: master10.000001
9.           Read_Master_Log_Pos: 717
10.                Relay_Log_File: node12-relay-bin.000001
11.                 Relay_Log_Pos: 4
12.         Relay_Master_Log_File: master10.000001
13.              Slave_IO_Running: Yes
14.             Slave_SQL_Running: Yes
15. …

复制代码

步骤5：验证一主多从架构同步效果
① 在主服务器上的数据库中新增数据（node10操作）

1. mysql> insert into nsd2021.departments(dept_name) values('风控部');

复制代码

② 从服务器验证同步数据（node11、node12操作）

1. mysql> select * from nsd2021.departments where dept_name='风控部';
2. +---------+-----------+
3. | dept_id | dept_name |
4. +---------+-----------+
5. |      10 | 风控部    |
6. +---------+-----------+
7. 1 row in set (0.00 sec)

复制代码

---

构建主从从同步示例：

实验拓扑结构：

• 服务器角色：node12为主(Master），node13为从/主(Slave1，Master2) ，node14为从(Slave2)；
  
• node12设置：ip地址为192.168.2.12，将角色Slave还原并做成Master（nsd2021保存）
  
• node13设置：ip地址为192.168.2.13，安装mysql，初始化暗码为NSD2021@tedu.cn
  
• node14设置：ip地址为192.168.2.14，安装mysql，初始化暗码为NSD2021@tedu.cn
  

   提示：防火墙与SELinux提前关闭；
  步骤1：准备初始化情况（node13、node14操作）
① 准备MySQL数据库，并初始化暗码（以node13为例）

1. [root@node13 ~]# yum -y install mysql-community-*   //检查Yum源，并安装MySQL
2. [root@node13 ~]# systemctl start mysqld     //启动服务
3. [root@node13 ~]# systemctl enable mysqld    //设置开机自启
4. [root@node13 ~]# grep -i password /var/log/mysqld.log    //查看生成的随机密码
5. 2021-06-10T13:44:26.945514Z 1 [Note] A temporary password is generated for root@localhost: O-,Ya=5qpkpW
6. [root@node13 ~]# mysqladmin -uroot -p' O-,Ya=5qpkpW ' password 'NSD2021@tedu.cn'

复制代码

② 将一主多从设置中的从服务器192.168.2.12改为主服务器（node12操作）

1. [root@node12 ~]# cd /var/lib/mysql
2. [root@node12 mysql]# rm -rf master.info relay-log.info node12-relay-bin.*
3. [root@node12 ~]# systemctl restart mysqld     //重启服务
4. [root@node12 ~]# mysql -uroot -pNSD2021@tedu.cn
5. mysql> show slave status;   //检查slave状态（为空则恢复成功）
6. Empty set (0.00 sec)

复制代码

  补充：将从服务器/var/lib/mysql中的四类从库文件删除并重启服务，即可还原独立数据库；
  
步骤2：设置主服务器（node12操作）
① 修改设置文件，并重启服务

1. [root@node12 ~]# vim /etc/my.cnf
2. [mysqld]
3. server_id=12
4. log_bin=master12
5. ...
6. [root@node12 ~]# systemctl restart mysqld

复制代码

② 检察生成的binlog日志文件

1. [root@node12 ~]# ls /var/lib/mysql/master12.*
2. /var/lib/mysql/master12.000001  /var/lib/mysql/master12.index
3. [root@node12 ~]# mysql -uroot -pNSD2021@tedu.cn
4. mysql> show master status;
5. +-----------------+----------+--------------+------------------+-------------------+
6. | File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
7. +-----------------+----------+--------------+------------------+-------------------+
8. | master12.000001 |      154 |              |                  |                   |
9. +-----------------+----------+--------------+------------------+-------------------+
10. 1 row in set (0.00 sec)

复制代码

③ 授权Slave1从服务器，可通过repluser用户同步数据

1. mysql> grant replication slave on *.* to repluser@'%' identified by 'NSD2021@tedu.cn';
2. mysql> show grants for repluser@'%';
3. +--------------------------------------------------+
4. | Grants for repluser@%                            |
5. +--------------------------------------------------+
6. | GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' |
7. +--------------------------------------------------+
8. 1 row in set (0.00 sec)

复制代码

步骤3：设置从-主服务器（node13操作）
   提示：node13作为node12的Slave服务器，同时也作为node14的Master服务器
  ① 修改设置文件，并重启服务

1. [root@node13 ~]# vim /etc/my.cnf
2. [mysqld]
3. server_id=13
4. log_bin=master13     //开启binlog日志功能
5. log_slave_updates     //让从库从主库复制数据时可以写入到binlog日志
6. …
7. [root@node13 ~]# systemctl restart mysqld

复制代码

  补充：从库开启log_bin参数，如果直接往从库写数据，是可以记录binlog日志的；但从库通过I0线程读取主库二进制日志文件，然后通过SQL线程写入的数据，是不会记录在binlog日志的；也就是说从库从主库上复制的数据，是不写入从库的binlog日志的。所以从库做为其他从库的主库时，需要在设置文件中添加 log-slave-updates 参数；
  ② 检察生成的binlog日志文件

1. [root@node13 ~]# ls /var/lib/mysql/master13.*
2. /var/lib/mysql/master13.000001  /var/lib/mysql/master13.index
3. [root@node13 ~]# mysql -uroot -pNSD2021@tedu.cn
4. mysql> show master status;
5. +-----------------+----------+--------------+------------------+-------------------+
6. | File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
7. +-----------------+----------+--------------+------------------+-------------------+
8. | master13.000001 |      154 |              |                  |                   |
9. +-----------------+----------+--------------+------------------+-------------------+
10. 1 row in set (0.00 sec)

复制代码

③ 授权Slave2服务器，可通过repluser用户同步数据

1. mysql> grant replication slave on *.* to repluser@'%' identified by 'NSD2021@tedu.cn';
2. Query OK, 0 rows affected, 1 warning (0.00 sec):

复制代码

④ 通过mysqldump手动同步主服务数据库数据
   注意：自动同步前，需保证主从服务器的数据库数据同等；
  # 主服务器备份数据，并拷贝备份文件到slave1从服务器

1. [root@node12 ~]# mysqldump -uroot -pNSD2021@tedu.cn --master-data nsd2021 > /root/fullbackup.sql
2. [root@node12 ~]# scp /root/fullbackup.sql root@192.168.2.13:/root

复制代码

# 从服务器恢复数据

1. [root@node13 ~]# mysql -uroot -pNSD2021@tedu.cn
2. mysql> create database nsd2021 default charset utf8mb4;
3. Query OK, 1 row affected (0.00 sec)
4. [root@node13 ~]# mysql -uroot -pNSD2021@tedu.cn nsd2021 < /root/fullbackup.sql

复制代码

步骤4：设置Slave1从服务器同步主服务器
① 获取日志文件名和偏移量

1. [root@node13 ~]# grep master /root/fullbackup.sql
2. CHANGE MASTER TO MASTER_LOG_FILE='master12.000001', MASTER_LOG_POS=441;

复制代码

② 从服务器指定主服务器同步信息

1. [root@node13 ~]# mysql -uroot -pNSD2021@tedu.cn
2. mysql> change master to
3.     -> master_host='192.168.2.12',
4.     -> master_user='repluser',
5.     -> master_password='NSD2021@tedu.cn',
6.     -> master_log_file='master12.000001',
7.     -> master_log_pos=441;
8. Query OK, 0 rows affected, 2 warnings (0.00 sec)

复制代码

③ 启动slave服务器历程

1. mysql> start slave;
2. Query OK, 0 rows affected (0.01 sec)

复制代码

④ 验证：检察slave状态信息

1. mysql> show slave status\G
2. *************************** 1. row ***************************
3.                Slave_IO_State: Waiting for master to send event
4.                   Master_Host: 192.168.2.12
5.                   Master_User: repluser
6.                   Master_Port: 3306
7.                 Connect_Retry: 60
8.               Master_Log_File: master12.000001
9.           Read_Master_Log_Pos: 441
10.                Relay_Log_File: node13-relay-bin.000002
11.                 Relay_Log_Pos: 319
12.         Relay_Master_Log_File: master12.000001
13.              Slave_IO_Running: Yes
14.             Slave_SQL_Running: Yes…
15. …

复制代码

⑤ 验证：检察master状态信息

1. mysql> show master status;
2. +-----------------+----------+--------------+------------------+-------------------+
3. | File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
4. +-----------------+----------+--------------+------------------+-------------------+
5. | master13.000001 |   173726 |              |                  |                   |
6. +-----------------+----------+--------------+------------------+-------------------+
7. 1 row in set (0.00 sec)

复制代码

步骤5：设置从服务器（node14操作）
① 修改设置文件，并重启服务

1. [root@node14 ~]# vim /etc/my.cnf
2. [mysqld]
3. server_id = 14
4. ...
5. [root@node14 ~]# systemctl restart mysqld

复制代码

② 通过mysqldump手动同步Slave1从-主服务数据库数据
# 从-主服务器备份数据，并拷贝备份文件到slave2从服务器

1. [root@node13 ~]# mysqldump -uroot -pNSD2021@tedu.cn --master-data nsd2021 > /root/fullbackup.sql
2. [root@node13 ~]# scp /root/fullbackup.sql root@192.168.2.14:/root

复制代码

# 从服务器恢复数据

1. [root@node14 ~]# mysql -uroot -pNSD2021@tedu.cn
2. mysql> create database nsd2021 default charset utf8mb4;
3. Query OK, 1 row affected (0.00 sec)
4. [root@node14 ~]# mysql -uroot -pNSD2021@tedu.cn nsd2021 < /root/fullbackup.sql

复制代码

步骤6：设置Slave2从服务器同步Slave1主服务器
① 获取日志文件名和偏移量

1. [root@node14 ~]# grep master /root/fullbackup.sql
2. CHANGE MASTER TO MASTER_LOG_FILE='master13.000001', MASTER_LOG_POS=173726;

复制代码

② 从服务器指定主服务器同步信息

1. mysql> change master to
2.     -> master_host='192.168.2.13',
3.     -> master_user='repluser',
4.     -> master_password='NSD2021@tedu.cn',
5.     -> master_log_file='master13.000001',
6.     -> master_log_pos=173726;
7. Query OK, 0 rows affected, 2 warnings (0.00 sec)

复制代码

③ 启动slave服务器历程

1. mysql> start slave;
2. Query OK, 0 rows affected (0.00 sec)
3. ④ 验证：查看slave状态信息
4. mysql> show slave status\G
5. *************************** 1. row ***************************
6.                Slave_IO_State: Waiting for master to send event
7.                   Master_Host: 192.168.2.13
8.                   Master_User: repluser
9.                   Master_Port: 3306
10.                 Connect_Retry: 60
11.               Master_Log_File: master13.000001
12.           Read_Master_Log_Pos: 173726
13.                Relay_Log_File: node14-relay-bin.000002
14.                 Relay_Log_Pos: 319
15.         Relay_Master_Log_File: master13.000001
16.              Slave_IO_Running: Yes
17.             Slave_SQL_Running: Yes
18. …

复制代码

步骤7：验证主从从架构同步效果
① 在主服务器上的数据库中新增数据（node12操作）

1. mysql> insert into nsd2021.departments(dept_name) values('企划部');

复制代码

② 从服务器验证同步数据（node13、node14操作）

1. mysql> select * from nsd2021.departments where dept_name='企划部';
2. +---------+-----------+
3. | dept_id | dept_name |
4. +---------+-----------+
5. |      11 | 企划部    |
6. +---------+-----------+
7. 1 row in set (0.00 sec)

复制代码

③ 在Slave1从服务器上的数据库中新增数据（node13操作）

1. mysql> insert into nsd2021.departments(dept_name) values('创业部');

复制代码

④ 从服务器验证同步数据（node14操作）

1. mysql> select * from nsd2021.departments where dept_name='创业部';
2. +---------+-----------+
3. | dept_id | dept_name |
4. +---------+-----------+
5. |      12 | 创业部    |
6. +---------+-----------+
7. 1 row in set (0.00 sec)

复制代码

---

构建主主（互为主从）同步示例：

实验拓扑结构：

• 服务器角色：node15为主-从(Master1/Slave2），node16为主-从(Master2/Slave1)；
  
• node15设置：ip地址为192.168.2.15，安装mysql，初始化暗码为NSD2021@tedu.cn
  
• node16设置：ip地址为192.168.2.16，安装mysql，初始化暗码为NSD2021@tedu.cn
  

   补充：node15和node16互为主库和从库，若有数据库数据更新，相互进行同步
  步骤1：准备初始化情况（node15、node16操作）
① 准备MySQL数据库，并初始化暗码（以node15为例）

1. [root@node15 ~]# yum -y install mysql-community-*   //检查Yum源，并安装MySQL
2. [root@node15 ~]# systemctl start mysqld     //启动服务
3. [root@node15 ~]# systemctl enable mysqld    //设置开机自启
4. [root@node15 ~]# grep -i password /var/log/mysqld.log    //查看生成的随机密码
5. 2021-06-10T13:44:26.945514Z 1 [Note] A temporary password is generated for root@localhost: O-,Ya=5qpkpW
6. [root@node15 ~]# mysqladmin -uroot -p' O-,Ya=5qpkpW ' password 'NSD2021@tedu.cn'

复制代码

步骤2：设置主服务器（node15操作）—> “将192.168.2.15作为主服务器”
① 修改设置文件，并重启服务

1. [root@node15 ~]# vim /etc/my.cnf
2. [mysqld]
3. server_id=15
4. log_bin=master15
5. ...
6. [root@node15 ~]# systemctl restart mysqld

复制代码

② 检察生成的binlog日志文件

1. [root@node15 ~]# ls /var/lib/mysql/master15.*
2. /var/lib/mysql/master15.000001  /var/lib/mysql/master15.index

复制代码

③ 授权从服务器(node16)，可通过repluser用户同步数据

1. [root@node15 ~]# mysql -uroot -pNSD2021@tedu.cn
2. mysql> grant replication slave on *.* to repluser@'%' identified by 'NSD2021@tedu.cn';
3. Query OK, 0 rows affected, 1 warning (0.00 sec)
4. mysql> show master status;     //查看日志文件和偏移量
5. +-----------------+----------+--------------+------------------+-------------------+
6. | File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
7. +-----------------+----------+--------------+------------------+-------------------+
8. | master15.000001 |      441 |              |                  |                   |
9. +-----------------+----------+--------------+------------------+-------------------+
10. 1 row in set (0.00 sec)

复制代码

步骤3：设置从服务器（node16操作）—> “将192.168.2.16作为从服务器”
① 修改设置文件

1. [root@node16 ~]# vim /etc/my.cnf
2. [mysqld]
3. server_id = 16
4. ...
5. [root@node16 ~]# systemctl restart mysqld

复制代码

② 从服务器指定主服务器同步信息（通过主服务器show master status获取信息）

1. [root@node16 ~]# mysql -uroot -pNSD2021@tedu.cn
2. mysql> change master to
3.     -> master_host='192.168.2.15',
4.     -> master_user='repluser',
5.     -> master_password='NSD2021@tedu.cn',
6.     -> master_log_file='master15.000001',
7.     -> master_log_pos=441;
8. Query OK, 0 rows affected, 2 warnings (0.01 sec)
10. mysql> start slave;     //启动slave程序
11. Query OK, 0 rows affected (0.01 sec)
13. mysql> show slave status\G      //查看slave状态
14. *************************** 1. row ***************************
15.                Slave_IO_State: Waiting for master to send event
16.                   Master_Host: 192.168.2.15
17.                   Master_User: repluser
18.                   Master_Port: 3306
19.                 Connect_Retry: 60
20.               Master_Log_File: master15.000001
21.           Read_Master_Log_Pos: 441
22.                Relay_Log_File: node16-relay-bin.000002
23.                 Relay_Log_Pos: 319
24.         Relay_Master_Log_File: master15.000001
25.              Slave_IO_Running: Yes
26.             Slave_SQL_Running: Yes
27. …

复制代码

步骤4：设置主服务器（node16操作）—> “将192.168.2.16作为主服务器”
① 修改设置文件，并重启服务

1. [root@node16 ~]# vim /etc/my.cnf
2. [mysqld]
3. server_id=16
4. log_bin=master16    //启用binlog功能（只需新增此项）
5. ...
6. [root@node16 ~]# systemctl restart mysqld

复制代码

② 检察生成的binlog日志文件

1. [root@node16 ~]# ls /var/lib/mysql/master16.*
2. /var/lib/mysql/master16.000001  /var/lib/mysql/master16.index

复制代码

③ 授权从服务器(node15)，可通过repluser用户同步数据

1. [root@node16 ~]# mysql -uroot -pNSD2021@tedu.cn
2. mysql> grant replication slave on *.* to repluser@'%' identified by 'NSD2021@tedu.cn';
3. Query OK, 0 rows affected, 1 warning (0.00 sec)
4. mysql> show master status;     //查看日志文件和偏移量
5. +-----------------+----------+--------------+------------------+-------------------+
6. | File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
7. +-----------------+----------+--------------+------------------+-------------------+
8. | master16.000001 |      441 |              |                  |                   |
9. +-----------------+----------+--------------+------------------+-------------------+
10. 1 row in set (0.00 sec)

复制代码

步骤5：设置从服务器（node15操作）—> “将192.168.2.15作为从服务器”
① 从服务器指定主服务器同步信息（通过主服务器show master status获取信息）

1. [root@node15 ~]# mysql -uroot -pNSD2021@tedu.cn
2. mysql> change master to
3.     -> master_host='192.168.2.16',
4.     -> master_user='repluser',
5.     -> master_password='NSD2021@tedu.cn',
6.     -> master_log_file='master16.000001',
7.     -> master_log_pos=441;
8. Query OK, 0 rows affected, 2 warnings (0.00 sec)
10. mysql> start slave;    //启动slave程序
11. Query OK, 0 rows affected (0.00 sec)
13. mysql> show slave status\G    //查看slave状态
14. *************************** 1. row ***************************
15.                Slave_IO_State: Waiting for master to send event
16.                   Master_Host: 192.168.2.16
17.                   Master_User: repluser
18.                   Master_Port: 3306
19.                 Connect_Retry: 60
20.               Master_Log_File: master16.000001
21.           Read_Master_Log_Pos: 441
22.                Relay_Log_File: node15-relay-bin.000002
23.                 Relay_Log_Pos: 319
24.         Relay_Master_Log_File: master16.000001
25.              Slave_IO_Running: Yes
26.             Slave_SQL_Running: Yes
27. …

复制代码

步骤6：验证主主架构同步效果
① 以node15作为主服务器进行验证
# 主服务器上创建数据库和表（node15操作）

1. [root@node15 ~]# mysql -uroot -pNSD2021@tedu.cn
2. mysql> create database mydb;
3. mysql> use mydb;
4. mysql> create table students(id int primary key, name varchar(20));

复制代码

# 从服务器上检察（node16操作）

1. [root@node16 ~]# mysql -uroot -pNSD2021@tedu.cn
2. mysql> show databases;
3. +--------------------+
4. | Database           |
5. +--------------------+
6. | information_schema |
7. | mydb               |
8. | mysql              |
9. | performance_schema |
10. | sys                |
11. +--------------------+
12. 5 rows in set (0.00 sec)
13.  
14. mysql> use mydb;
15. mysql> show tables;
16. +----------------+
17. | Tables_in_mydb |
18. +----------------+
19. | students       |
20. +----------------+
21. 1 row in set (0.00 sec)

复制代码

② 以node16作为主服务器进行验证
# 主服务器上添加用户（node16操作）

1. [root@node16 ~]# mysql -uroot -pNSD2021@tedu.cn
2. mysql> insert into mydb.students values(1,'tom');

复制代码

# 从服务器上检察（node15操作）

1. mysql> select * from mydb.students;
2. +----+------+
3. | id | name |
4. +----+------+
5. |  1 | tom  |
6. +----+------+
7. 1 row in set (0.00 sec)

复制代码

三、复制模式

MySQL 复制模式是指主库（Master）和从库（Slave）之间数据同步的方式。常见的复制模式包括异步复制、全同步复制和半同步复制。每种复制模式都有其特点和适用场景。
1、异步复制（Asynchronous replication）

工作原理：

• 主库将写操作记录到二进制日志（Binary Log）中，但不等待从库确认接收和应用这些日志
  
• 从库通过毗连到主库，读取并应用这些二进制日志，实现数据的同步
  

   优点：
  - 性能高：主库不需要等待从库的确认，写操作的延迟较低。
- 设置简单：默认的复制模式，无需额外设置。
缺点：
  - 数据同等性较差：从库的数据同步大概存在延迟，主库故障时大概丢失部分数据。
- 单点故障风险：主库仍旧是单点写入，存在单点故障风险。
  适用场景：
  - 对性能要求较高，但对数据同等性要求不严格的场景。
- 读操作远多于写操作的场景。
   
2、全同步复制（Fully syncchronous replication）

工作原理：

• 主库在执行写操作后，必须等待所有从库确认接收并应用了二进制日志，才能返回乐成。
  
• 所有从库的数据都是实时同步的，确保数据的同等性。
  

   优点：
  - 数据同等性高：所有从库的数据都是实时同步的，确保数据的同等性。
- 高可用性：从库可以作为主库的备份，提供数据的安全性和可靠性。
缺点：
  - 性能较低：主库需要等待所有从库的确认，写操作的延迟较高。
- 设置复杂：需要确保所有从库的网络毗连稳定，设置和管理较为复杂。
适用场景：
  - 对数据同等性要求极高的场景。
- 金融、买卖业务等对数据可靠性要求极高的应用。
   
3、半同步复制（Semi-synchronous replication）

工作原理：

• 主库在执行写操作后，必须等待至少一个从库确认接收了二进制日志，但不等待从库应用这些日志。
  
• 从库在接收到二进制日志后，异步地应用这些日志，实现数据的同步。
  

   优点：
  - 性能和同等性的平衡：主库不需要等待所有从库的确认，写操作的延迟较低，同时确保至少一个从库接收到数据。
- 设置相对简单：只需设置半同步插件，无需复杂的网络设置。
缺点：
  - 数据同等性介于异步复制和全同步复制之间：从库的数据同步大概存在延迟，但比异步复制更可靠。
- 单点故障风险：主库仍旧是单点写入，存在单点故障风险。
适用场景：
  - 对性能和数据同等性都有肯定要求的场景。
- 需要肯定水平的数据冗余和高可用性的应用。
   
4、设置半同步复制

1. 安装半同步插件

在主库和从库上安装半同步插件：

1. INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
2. INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

复制代码

2. 启用半同步复制

在主库上启用半同步复制：

1. SET GLOBAL rpl_semi_sync_master_enabled = 1;

复制代码

在从库上启用半同步复制：

1. SET GLOBAL rpl_semi_sync_slave_enabled = 1;

复制代码

3. 重启从库的复制线程

在从库上重启复制线程以应用半同步复制设置：

1. STOP SLAVE IO_THREAD;
2. START SLAVE IO_THREAD;

复制代码

4. 检查半同步复制状态

在主库上检查半同步复制状态：

1. SHOW STATUS LIKE 'Rpl_semi_sync_master_status';

复制代码

在从库上检查半同步复制状态：

1. SHOW STATUS LIKE 'Rpl_semi_sync_slave_status';

复制代码

---

半同步复制模式设置示例：

   实验基于主主同步结构，启用半同步复制模式，以便切换角色后能继续使用半同步复制
  

• 服务器角色：node15为主-从(Master1/Slave2），node16为主-从(Master2/Slave1)；
  

   补充：如果主从模式，则对应角色加载相应的模块和启用模式；
  步骤1：检察MySQL是否允许动态加载模块（have_dynamic_loading）

1. mysql> show variables like 'have_dynamic_loading';
2. +----------------------+-------+
3. | Variable_name        | Value |
4. +----------------------+-------+
5. | have_dynamic_loading | YES   |
6. +----------------------+-------+
7. 1 row in set (0.00 sec)

复制代码

步骤2：启用半同步复制模式
方法1：修改设置文件（永久设置）（node15、node16操作）

1. [root@node15 ~]# vim /etc/my.cnf
2. [mysqld]
3. server_id=15
4. log_bin=master15
5. plugin-load ="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"  //加载半同步模块
6. rpl_semi_sync_master_enabled=1    //主库-启动半同步模式
7. rpl_semi_sync_slave_enabled=1     //从库-启动半同步模式
8. ...
9. [root@node15 ~]# systemctl restart mysqld   //重启服务
12. [root@node16 ~]# vim /etc/my.cnf
13. [mysqld]
14. server_id=16
15. log_bin=master16
16. plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
17. rpl_semi_sync_master_enabled=1
18. rpl_semi_sync_slave_enabled=1
19. ...
20. [root@node16 ~]# systemctl restart mysqld   //重启服务

复制代码

方法2：MySQL下令行下设置（重启MySQL服务失效）
① 下载对应的半同步模块

1. mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
2. mysql> install plugin rpl_semi_sync_master soname 'semisync_slave.so';

复制代码

② 启用半同步复制，默认是关闭的

1. mysql> set global rpl_semi_sync_master_enabled = 1;
2. mysql> set global rpl_semi_sync_slave_enabled = 1;

复制代码

步骤3：验证，检察半同步模块是否安装，半同步模式是否启用
① 检察 information_schema.plugins 表，模块是否安装乐成

1. mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like '%semi%';
2. +----------------------+---------------+
3. | plugin_name          | plugin_status |
4. +----------------------+---------------+
5. | rpl_semi_sync_master | ACTIVE        |
6. | rpl_semi_sync_slave  | ACTIVE        |
7. +----------------------+---------------+
8. 2 rows in set (0.00 sec)

复制代码

② 检察 "rpl_semi_sync_%_enabled" 是否启用半同步模式

1. mysql> show variables like "rpl_semi_sync_%_enabled";
2. +------------------------------+-------+
3. | Variable_name                | Value |
4. +------------------------------+-------+
5. | rpl_semi_sync_master_enabled | ON    |
6. | rpl_semi_sync_slave_enabled  | ON    |
7. +------------------------------+-------+
8. 2 rows in set (0.00 sec)

复制代码

---

总结：
MySQL 复制模式包括异步复制、全同步复制和半同步复制。每种复制模式都有其优缺点和适用场景。异步复制性能高但数据同等性较差，全同步复制数据同等性高但性能较低，半同步复制在性能和数据同等性之间取得平衡。选择符合的复制模式需要根据实际需求、体系规模和业务特点进行综合考虑。通过合理的设置和管理，可以充分发挥差别复制模式的优势，进步数据库的可用性、扩展性和数据安全性。

思维导图：

小结：

本篇章节为【第四阶段】RDBMS2-DAY1 的学习笔记，这篇笔记可以初步相识到 MySQL主从同步（概述、原理、构建主从同步）、主从同步结构范例（主多从、主从从、主主）、复制模式（异步、半同步）、启用半同步复制，除此之外推荐参考相关学习网址：

•  参考：mysql数据库主从同步复制模式_mysl的主从同步的方式-CSDN博客
  

---

Tip：毕竟两个人的智慧大于一个人的智慧，如果你不明白本章节的内容或需要相关笔记、视频，可私信小安，请不要害羞和回避，可以向他人讨教，花点时间直到你真正的明白。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。