MySQL 5.7配置主从复制和读写分离

友儿

发布于 2024-03-15 09:08:49

1050

发布于 2024-03-15 09:08:49

文章被收录于专栏：友儿

安装mysql

CentOS7 单机配置mysql多实例运行

环境说明

CentOS7、mysql5.7
mysql服务器

- master  `host`:127.0.0.1  `port`:3306
- slave 1 `host`:127.0.0.1  `port`:3307
- slave 2 `host`:127.0.0.1  `port`:3308

主从配置

原理

① 当master节点接收到一个用户写请求时，这个写请求可能是增删改操作，此时会把写请求的操作都记录到binlog日志中。

② 首先slave节点通过配置信息连接到master节点上，当slave节点连接到master节点上时，master节点会为每一个slave节点分别创建一个binlog dump线程，用于向每个slave节点发送binlog日志。

③ 此时，binlog dump线程会读取master节点上的binlog日志，然后将binlog日志发送给slave节点上的I/O线程。

④ slave几点上的I/O线程接收到binlog日之后，会将文件名、路径、密码等信息保存到master.info中，会将binlog日志先写入到本地的relaylog中，relaylog中就保存了master的binlog日志。

⑤ 最后，slave节点上的SQL线程会读取relaylog中的biinlog日志，将其解析成具体的增删改操作，把这些在master节点上进行过的操作，重新在slave节点上也重做一遍，打到数据还原的效果，这样就可以保证master节点和slave节点的数据一致性了。

master配置

# 查看当前的二进制日志记录状态

root@(localhost) 01:23:33>SHOW VARIABLES like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

# 创建用于从服务器连接的用户

# 刷新权限
FLUSH PRIVILEGES;

# 创建用户
CREATE USER 'synchronous'@'%' IDENTIFIED by 'synchronous';

# 授予'synchronous'用户复制从服务器的权限
GRANT REPLICATION SLAVE ON *.* TO 'synchronous'@'%' IDENTIFIED WITH mysql_native_password BY 'synchronous';

# 刷新权限
FLUSH PRIVILEGES;

# 查看主服务器的状态，包括二进制日志文件名和位置

root@(localhost) 01:23:44>show MASTER status;
+-----------------------+----------+--------------+------------------+-------------------+
| File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| mysql-3306-bin.000003 |     1757 |              |                  |                   |
+-----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

slave 1 从服务器

# 添加主服务器信息

CHANGE MASTER TO
MASTER_HOST='127.0.0.1',
MASTER_PORT=3306,
MASTER_USER="synchronous",
MASTER_PASSWORD="synchronous",
MASTER_LOG_FILE="mysql-3306-bin.000003",
MASTER_LOG_POS=1757;

# 开启从服务器 

start SLAVE;

# 查看从服务器状态
root@(localhost) 01:41:00>SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: synchronous
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-3306-bin.000003
          Read_Master_Log_Pos: 1757
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 938
        Relay_Master_Log_File: mysql-3306-bin.000003
             Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 1757
              Relay_Log_Space: 1149
.....................................................................................
             Master_Server_Id: 3306
                  Master_UUID: fcbfbd72-e0fc-11ee-8997-5254008afee6
             Master_Info_File: /usr/local/mysql/data/3307/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
.....................................................................................
1 row in set (0.00 sec)

ERROR:
No query specified

slave 2 从服务器

# 添加主服务器信息

CHANGE MASTER TO
MASTER_HOST='127.0.0.1',
MASTER_PORT=3306,
MASTER_USER="synchronous",
MASTER_PASSWORD="synchronous",
MASTER_LOG_FILE="mysql-3306-bin.000003",
MASTER_LOG_POS=1757;

# 开启从服务器 

start SLAVE;

# 查看从服务器状态
root@(localhost) 01:41:00>SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: synchronous
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-3306-bin.000003
          Read_Master_Log_Pos: 1757
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 938
        Relay_Master_Log_File: mysql-3306-bin.000003
             Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 1757
              Relay_Log_Space: 1149
.....................................................................................
             Master_Server_Id: 3306
                  Master_UUID: fcbfbd72-e0fc-11ee-8997-5254008afee6
             Master_Info_File: /usr/local/mysql/data/3308/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
.....................................................................................
1 row in set (0.00 sec)

ERROR:
No query specified

测试主从

# 主服务器创建test库，test表

CREATE DATABASE test;
USE test;
 CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username` char(30) NOT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '昵称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='测试表';

# slave1、slave2 查看

root@(localhost) 01:41:10>SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

root@(none) 01:49:25>USE test;
Database changed
root@test 01:49:44>SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

如有侵权请联系 cloudcommunity@tencent.com 删除

配置