MySQL8主从复制读写分离

简单记录一下安装部署、主从复制、添加从节点、读写分离。

一、环境准备

详细安装文档

主机名 ip 用途
mysql101 192.168.10.101 master
mysql102 192.168.10.102 slave01
mysql103 192.168.10.103 slave02
proxysql104 192.168.10.104 用于读写分离的入口

centos7安装mysql8.0.44.

  1. 安装 mysql 官方提供的 yum 源
1
2
wget https://dev.mysql.com/get/mysql80-community-release-el7-11.noarch.rpm
yum localinstall mysql80-community-release-el7-11.noarch.rpm -y
  1. 验证已经添加 yum 源
1
yum repolist enabled | grep mysql
  1. 安装
1
yum install mysql-community-server -y
  1. 开机启动
1
systemctl enable --now mysqld
  1. 获取临时密码
1
grep 'temporary password' /var/log/mysqld.log
  1. 修改默认密码
1
mysql -uroot -p''
1
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Root21..';
  1. 验证新密码
1
mysql -u root -p'Root21..'

二、主从复制

这里简单一点，配置一主一从。

1
egrep -v '^$|^#' /etc/my.cnf

2.1 修改配置

1
vim /etc/my.cnf

mysql101

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[mysqld]
# 1. 基础身份
server-id = 101           # 必须唯一
port = 3306

# 2. GTID 核心配置 (背下来，8.0 标配)
gtid_mode = ON            # 开启 GTID
enforce_gtid_consistency = ON # 强制一致性

# 3. Binlog 配置
log_bin = mysql-bin       # 开启二进制日志
binlog_format = ROW       # 必须是 ROW 模式 (记录行变化，而不是 SQL 语句)
expire_logs_days = 7      # 日志保留 7 天

# 4. 从库更新是否写入 Binlog (级联复制用，建议开启)从 MySQL 8.0.26 开始，该参数已更名为 log_replica_updates。
log_slave_updates = ON
1
2
3
4
5
6
7
8
9
10
11
12
13
14
[root@mysql101 ~]# egrep -v '^$|^#' /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=101
port=3306
gtid_mode=ON
enforce_gtid_consistency=ON
log_bin=mysql-bin
binlog_format=ROW
expire_logs_days=7
log_slave_updates=ON

mysql102

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[mysqld]
server-id = 102           # Node3 改成 103
port = 3306

# GTID 必须开启
gtid_mode = ON
enforce_gtid_consistency = ON

# 开启 Binlog (为了以后它可能晋升为主库，或者是级联架构)
log_bin = mysql-bin
binlog_format = ROW

# 8.0 默认是从库只读，但建议显式配置，防止误操作
read_only = 1
1
2
3
4
5
6
7
8
9
10
11
12
13
[root@mysql102 ~]# egrep -v '^$|^#' /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id = 102
port = 3306
gtid_mode = ON
enforce_gtid_consistency = ON
log_bin = mysql-bin
binlog_format = ROW
read_only = 1

2.2 开启主从复制

  1. 重启数据库服务
1
systemctl restart mysqld
  1. 主数据库（mysql101） 创建主从复制账号
1
mysql -u root -p'Root21..'
1
2
3
4
5
-- 创建复制专用用户
CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'Repl21..';
-- 赋予复制权限
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
  1. 从数据库（mysql102）启动复制，并查看状态（着重看 sql 和 io 线程）
1
mysql -u root -p'Root21..'
1
2
3
4
5
6
7
CHANGE MASTER TO
    MASTER_HOST='192.168.10.101',
    MASTER_USER='repl',
    MASTER_PASSWORD='Repl21..',
    MASTER_PORT=3306,
    MASTER_AUTO_POSITION=1;  -- 【核心】这句话告诉它：用 GTID 自动找位置
START SLAVE;
1
SHOW SLAVE STATUS\G;

新语法

1
2
3
在 MySQL 8.0.23 及更高版本中
CHANGE MASTER TO 已更名为 CHANGE REPLICATION SOURCE TO。
MASTER_HOST 变为 SOURCE_HOST
  1. 验证主从复制

主库操作数据

1
2
3
4
CREATE DATABASE test_db;
USE test_db;
CREATE TABLE user (id INT, name VARCHAR(20));
INSERT INTO user VALUES (1, 'qiankong');

从库验证数据

1
2
3
SHOW DATABASES; -- 看看有没有 test_db
USE test_db;
SELECT * FROM user; -- 看看有没有 'qiankong'

三、添加从节点

备份时刻之后修改的数据，之后会自动同步。

  1. 在主库（mysql101）导出数据
1
mysqldump -uroot -p'Root21..' --all-databases --master-data=2 --single-transaction > full_backup.sql

添加新数据，看之后是否会自动同步

1
2
USE test_db;
INSERT INTO user VALUES (2, 'polish');
  1. 在从库（mysql103）导入数据
1
mysql -uroot -p'Root21..' < full_backup.sql

​ 验证数据，没有备份之后修改的数据

1
2
3
SHOW DATABASES; -- 看看有没有 test_db
USE test_db;
SELECT * FROM user; -- 看看有没有 'polish'
  1. 开启主从复制（参考2.2的配置）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
[root@mysql103 ~]# egrep -v '^$|^#' /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id = 103
port = 3306
gtid_mode = ON
enforce_gtid_consistency = ON
log_bin = mysql-bin
binlog_format = ROW
read_only = 1
[root@mysql103 ~]#
1
systemctl restart mysqld
1
2
3
4
5
6
7
CHANGE MASTER TO
    MASTER_HOST='192.168.10.101',
    MASTER_USER='repl',
    MASTER_PASSWORD='Repl21..',
    MASTER_PORT=3306,
    MASTER_AUTO_POSITION=1;  -- 【核心】这句话告诉它：用 GTID 自动找位置
START SLAVE;
1
SHOW SLAVE STATUS\G;
  1. 验证数据
1
2
3
SHOW DATABASES; -- 看看有没有 test_db
USE test_db;
SELECT * FROM user; -- 看看有没有 'polish'

四、读写分离

实现读写分离有多种方式，代码层、中间件层.

这里使用 ProxySQL

4.1 架构图

flowchart LR
    subgraph Frontend [前端层]
        A[Web/App Server
前端应用]
    end

    subgraph ProxyLayer [读写分离层]
        B[ProxySQL / Mycat
云数据库代理]
    end

    subgraph DBLayer [MySQL数据库层]
        C[(Master
主库-写)]
        D[(Slave1
从库-读)]
        E[(Slave2
从库-读)]
    end

    A --> B
    B -->|写操作| C
    B -->|读操作| D
    B -->|读操作| E

    C -->|主从复制| D
    C -->|主从复制| E

架构图

4.2 介绍

ProxySQL 需要有两个端口

  • 6032 是管理口，通过操作底层的 sqlite 库来进行配置修改
  • 6033 是业务口，进入这个端口的SQL语句会自动分配给上游的数据库

ProxySQL 至少需要两个用户

  • 监控用户 monitor，用于检测上游的状态
  • 业务用户，用于连接上游的数据库，为了安全，不使用默认的 root

4.3 安装启动

官网

  1. 配置 yum 源，centos7 最多支持到 2.7.X 的版本。
1
2
3
4
5
6
7
cat <<EOF | sudo tee /etc/yum.repos.d/proxysql.repo
[proxysql]
name=ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.7.x/centos/7
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.7.x/repo_pub_key
EOF
  1. 安装
1
yum install -y proxysql
  1. 开启启动并查看状态
1
systemctl enable --now proxysql
1
systemctl status proxysql
  1. 验证端口
1
ss -untlp |egrep '6032|6033'
  1. 安装 mysql 连接工具
1
yum -y install mysql
  1. 连接管理口验证
1
mysql -u admin -padmin -h 127.0.0.1 -P 6032
1
mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='ProxySQLAdmin> '

4.4 环境准备

注意： mysql8需要注意，使用之前的密码方式。

**Master（mysql101）**上创建监控用户，业务数据库，业务数据库

1
mysql -uroot -p'Root21..'
  1. 创建监控用户（可根据业务需要最小授权）
1
2
CREATE USER 'monitor'@'%' IDENTIFIED WITH mysql_native_password BY 'Monitor21..';
GRANT REPLICATION CLIENT ON *.* TO 'monitor'@'%';
1
ALTER USER 'monitor'@'%' IDENTIFIED WITH mysql_native_password BY 'Monitor21..';
  1. 创建业务数据库，以 my_blog_db 为例
1
CREATE DATABASE my_blog_db;
  1. 创建业务用户并授权，以 blog 为例
1
2
CREATE USER 'blog'@'%' IDENTIFIED WITH mysql_native_password BY 'Blog21..';
GRANT ALL PRIVILEGES ON my_blog_db.* TO 'blog'@'%';
1
ALTER USER 'blog'@'%' IDENTIFIED WITH mysql_native_password BY 'Blog21..';
  1. 刷新权限
1
FLUSH PRIVILEGES;

4.5 配置 ProxySQL

需要管理口配置，6032

1
mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='ProxySQLAdmin> '
  1. 添加上游的数据库，读、写划分不同的组
1
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (10, '192.168.10.101', 3306);
1
2
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (20, '192.168.10.102', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (20, '192.168.10.103', 3306);
1
2
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
  1. 修改监控用户密码（因为默认就存在，无需创建）
1
2
3
4
5
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='Monitor21..' WHERE variable_name='mysql-monitor_password';

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
  1. 添加业务用户
1
2
3
4
5
-- 这里的 default_hostgroup 是默认路由，万一没匹配到规则，就去这个组（通常设为写组10）
INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('blog', 'Blog21..', 10);

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
  1. 配置读写分离规则
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 先清空旧规则，防止干扰
DELETE FROM mysql_query_rules;

-- 规则 1：特殊读 -> 走主库 (HG 10)
INSERT INTO mysql_query_rules 
(rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT.*FOR UPDATE$', 10, 1);

-- 规则 2：普通读 -> 走从库 (HG 20)
INSERT INTO mysql_query_rules 
(rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (2, 1, '^SELECT', 20, 1);

-- 加载生效
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
  1. 验证上有数据库正常
1
SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;

4.6 验证

  1. 验证读写分离

在 ProxySQL 的机制里，只要你开启了事务（BEGIN），为了保证数据一致性，它会强制把后续的所有操作（包括查询）都锁定在主库，直到 COMMIT。

1
2
3
4
5
6
# 使用业务账号 blog 连接 6033 端口
# 每 0.5 秒执行一次查询主机名
while true; do 
  mysql -u blog -p'Blog21..' -h 192.168.10.104 -P 6033 -N -e "SELECT @@hostname, @@port"
  sleep 0.5
done
1
2
3
# 这是一个组合拳：开启事务 -> 查主机名 -> 提交
# ProxySQL 会把这整个包判定为“写操作”，扔给主库
mysql -u blog -p'Blog21..' -h 192.168.10.104 -P 6033 -e "BEGIN; SELECT @@hostname AS '我是写节点(Master)'; COMMIT;"
  1. proxysql 统计
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
[root@proxysql ~]# mysql -uadmin -padmin -h 127.0.0.1 -P 6032

-- 查看查询统计
SELECT hostgroup, count_star, digest_text 
FROM stats.stats_mysql_query_digest 
ORDER BY count_star DESC 
LIMIT 10;
+-----------+------------+----------------------------------------------------------------+
| hostgroup | count_star | digest_text                                                    |
+-----------+------------+----------------------------------------------------------------+
| 20        | 3          | SELECT @@hostname,@@port                                       |
| 10        | 2          | select @@version_comment limit ?                               |
| 10        | 1          | show databases                                                 |
| 10        | 1          | CREATE DATABASE IF NOT EXISTS test_proxysql                    |
| 10        | 1          | INSERT INTO test_table VALUES (?,?)                            |
| 20        | 1          | select user,host from mysql.user                               |
| 10        | 1          | CREATE TABLE IF NOT EXISTS test_table (id INT,data VARCHAR(?)) |
| 10        | 1          | show tables                                                    |
| 20        | 1          | SELECT DATABASE()                                              |
+-----------+------------+----------------------------------------------------------------+

-- 查看连接池状态
SELECT * FROM stats_mysql_connection_pool;

-- 查看当前连接数
SELECT * FROM stats_mysql_global;

-- 重置统计（用于重新测试）
SELECT * FROM stats.stats_mysql_query_digest_reset;