mysql主从复制，从搭建到使用

欢迎来到我的博客，代码的世界里，每一行都是一个故事

前言

数据库世界里有一种奇妙的舞蹈，它叫做主从复制。就像是一场华丽的芭蕾，主数据库和从数据库在节奏的指挥下，优美地协调着动作，完成着数据的同步传递。而今天，我们将一起走进这场舞台，探索MySQL主从复制技术的神奇之处。无论您是观众还是舞者，这都将是一场精彩绝伦的表演！

主从复制原理

MySQL的主从复制是一种数据库复制技术，用于在两个或多个MySQL服务器之间复制数据。它通常用于数据备份、读写分离、提高数据可用性和扩展性。以下是主从复制的工作原理：

Binlog 日志

• 二进制日志（binlog）：主服务器中的一个关键组件是二进制日志。二进制日志记录了所有更改数据的语句（如 INSERT、UPDATE 和 DELETE），以及DDL语句如 CREATE TABLE 和 ALTER TABLE。这些记录是按照它们发生的顺序来写入的，允许服务器在必要时重放这些事件。

复制线程

• IO线程：从服务器上的IO线程连接到主服务器，并请求从当前的二进制日志文件位置开始复制数据。主服务器接收到这个请求后，通过IO线程发送二进制日志的内容到从服务器。
• SQL线程：接收到二进制日志内容后，从服务器将这些内容写入自己的中继日志（relay log）。从服务器上的SQL线程会读取中继日志，并在本地执行这些日志记录的SQL语句，从而使得从服务器的数据库与主服务器的数据库保持同步。

数据同步流程

1. 更改数据：在主服务器上执行写操作（如 INSERT、UPDATE、DELETE）。
2. 写入binlog：主服务器将这些更改写入二进制日志。
3. 从服务器请求数据：从服务器上的IO线程请求并获取主服务器上的二进制日志。
4. 发送binlog到从服务器：主服务器发送二进制日志中的事件给从服务器。
5. 写入中继日志：从服务器上的IO线程将接收到的事件写入中继日志。
6. 执行SQL语句：从服务器上的SQL线程读取中继日志中的事件，并在从服务器上执行这些事件对应的SQL语句。

同步机制

• 异步复制：在标准的主从复制中，数据的复制是异步进行的。这意味着主服务器在写入二进制日志后不会等待从服务器确认就继续处理其他操作，从服务器会在自己的节奏中读取和应用这些更改。
• 半同步复制：MySQL还提供了半同步复制选项。在半同步复制中，主服务器在提交事务之后，会等待至少一个从服务器确认它已经接收到了二进制日志信息，然后才继续执行下一个事务。这可以保证数据在主服务器和至少一个从服务器之间的一致性。

主从复制的这种架构允许从服务器在出现故障时接管主服务器的角色，也允许在不影响主服务器性能的情况下，通过从服务器来处理读取请求，从而实现读写分离和负载均衡。

前置操作

创建一个数据库，一个数据表，这里就是演示，很简单的创建（主节点和从节点都需要创建）

-- 创建数据库 'acowbo'
CREATE DATABASE acowbo;
USE acowbo;

-- 创建表 'sys_user'
CREATE TABLE sys_user (
  Id INT PRIMARY KEY,
  username VARCHAR(255),
  password VARCHAR(255),
  create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  create_by VARCHAR(255),
  deleted TINYINT(1) DEFAULT 0
);

创建一个用户acowbo，具有这个数据库的所有执行权限

-- 创建用户 'acowbo' 并设置密码
CREATE USER 'acowbo'@'%' IDENTIFIED BY 'Test123Ok!';

-- 赋予 'acowbo' 用户对 'acowbo' 数据库的所有权限
GRANT ALL PRIVILEGES ON acowbo.* TO 'acowbo'@'%';

-- 更新权限
FLUSH PRIVILEGES;

配置主服务器

创建主从账号,在master节点

CREATE USER 'replicator'@'%' IDENTIFIED BY 'Test123Ok!';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

删除操作

-- 删除用户及其权限
DROP USER 'username'@'host';

-- 如果需要，取消用户对特定数据库的所有权限
REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';

-- 如果需要，取消用户对特定表的所有权限
REVOKE ALL PRIVILEGES ON database_name.table_name FROM 'username'@'host';

-- 如果需要，取消用户对特定权限的权限
REVOKE privilege_type ON database_name.* FROM 'username'@'host';

修改master节点的配置文件

[mysqld]
server-id = 1                  # 主服务器的唯一ID
log_bin = /var/log/mysql/mysql-bin.log   # 启用二进制日志
binlog_do_db = acowbo          # 指定需要复制的数据库

重启service mysql restart

配置从服务器

修改slave的节点配置文件

[mysqld]
server-id = 2                  # 从服务器的唯一ID，必须与主服务器不同
relay_log = /var/log/mysql/mysql-relay-bin.log   # 启用中继日志
binlog_do_db = acowbo          # 指定需要复制的数据库
read_only = 1                  # 设置为只读模式，以防止直接在从服务器上修改数据

执行主从同步

主节点操作

在主节点执行SHOW MASTER STATUS;,记住file的值与position的值，等等要在slave用到

从节点操作

从节点执行以下命令

CHANGE MASTER TO
  MASTER_HOST='ubtone.local',
  MASTER_USER='replicator',
  MASTER_PASSWORD='Test123Ok!',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=157;    
# 启动从节点
START SLAVE;
# 查看状态
SHOW SLAVE STATUS\G;

配置成功展示

出现下面的即证明成功了

验证，首先我们验证一下slave节点只读，插入数据报错如下

验证主从

主库模拟插入，从库查看是否同步成功

# 主库执行以下命令
INSERT INTO `sys_user` (`Id`, `username`, `password`, `create_time`, `create_by`, `deleted`) 
VALUES 
(1, 'user1', 'password1', '2024-04-24 10:00:00', 'admin', 0),
(2, 'user2', 'password2', '2024-04-24 10:15:00', 'admin', 0),
(3, 'user3', 'password3', '2024-04-24 10:30:00', 'admin', 0);

结果显示

主库

mysql> use acowbo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> INSERT INTO `sys_user` (`Id`, `username`, `password`, `create_time`, `create_by`, `deleted`)  
    -> VALUES 
    -> (1, 'user1', 'password1', '2024-04-24 10:00:00', 'admin', 0),
    -> (2, 'user2', 'password2', '2024-04-24 10:15:00', 'admin', 0),
    -> (3, 'user3', 'password3', '2024-04-24 10:30:00', 'admin', 0);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 

从库

mysql> use acowbo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from sys_user;
+----+----------+-----------+---------------------+-----------+---------+
| Id | username | password  | create_time         | create_by | deleted |
+----+----------+-----------+---------------------+-----------+---------+
|  1 | user1    | password1 | 2024-04-24 10:00:00 | admin     |       0 |
|  2 | user2    | password2 | 2024-04-24 10:15:00 | admin     |       0 |
|  3 | user3    | password3 | 2024-04-24 10:30:00 | admin     |       0 |
+----+----------+-----------+---------------------+-----------+---------+
3 rows in set (0.00 sec)

mysql>