docker 部署MySQL实现主从复制读写分离

docker 部署MySQL实现主从复制读写分离

数据库版本：mysql8.0

主节点：172.16.208.12

从节点：172.16.208.13

主从复制

实现mysql主从复制（主库）

主库（如果想所有库都复制需注释掉binlog-do-db，然后重启主从库）

mkdir -p /opt/mysql/data
mkdir -p /opt/mysql/config
vim /opt/mysql/config/my.cnf

[mysqld]
user=mysql
default-storage-engine=INNODB
character-set-server=utf8
secure_file_priv=/var/lib/mysql
server-id=1
log-bin=mysql-bin
binlog-do-db=mydb
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

编辑docker-compose.yml文件

version: '2'

services:
  mysql-master:
    image: mysql:8.0
    container_name: mysql-master
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: Tgqs@123
      MYSQL_DATABASE: mydb
      MYSQL_USER: mysql
      MYSQL_PASSWORD: Tgqs@123
    volumes:
      - /opt/mysql/data:/var/lib/mysql
      - /opt/mysql/config/my.cnf:/etc/mysql/my.cnf
    ports:
      - "3306:3306"

volumes:
  master_data:

在该yml文件的目录下

docker-compose up -d
docker exec -it mysql-master mysql -uroot -prootpassword

CREATE USER 'replica'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'Tgqs@123';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
#查看二进制文件和文件位置，记下 File 和 Position 的值，例如 mysql-bin.000001 和 154。
SHOW MASTER STATUS;

---

实现mysql主从复制（从库）

在从节点依然创建相应的文件，注意配置文件略有不同，特别是server id，中继日志等项。

vim /opt/mysql/config/my.cnf

[mysqld]
user=mysql
default-storage-engine=INNODB
character-set-server=utf8
secure_file_priv=/var/lib/mysql
server-id=2
relay-log=relay-log
log-bin=mysql-bin
binlog-do-db=mydb
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

创建docker-compose.yml文件

version: '2'

services:
  mysql-slave:
    image: mysql:8.0
    container_name: mysql-slave
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: Tgqs@123
      MYSQL_DATABASE: mydb
      MYSQL_USER: mysql
      MYSQL_PASSWORD: Tgqs@123
    volumes:
      - /opt/mysql/data:/var/lib/mysql
      - /opt/mysql/config/my.cnf:/etc/mysql/my.cnf
    ports:
      - "3306:3306"

volumes:
  slave_data:

进入yaml文件所在的文件位置

docker-compose up -d
docker exec -it mysql-slave mysql -uroot -p

CHANGE MASTER TO
  MASTER_HOST='172.16.208.12',		  -- 主节点的ip地址
  MASTER_USER='replica',
  MASTER_PASSWORD='replica_password',
  MASTER_LOG_FILE='mysql-bin.000001',  -- 使用上面查询到的日志文件名
  MASTER_LOG_POS=154;                  -- 使用上面查询到的日志位置

START SLAVE;
#查看从数据库的状态
SHOW SLAVE STATUS\G
Slave_IO_Running 和 Slave_SQL_Running 是否都为 Yes
Last_IO_Error 和 Last_SQL_Error 是否为空

查看主从节点的配置文件，特别是id，相同会报错

#主节点的my.cnf
[mysqld]
server-id=1
log_bin=mysql-bin
binlog_do_db=testdb

#从节点的my.cnf
[mysqld]
server-id=2
relay-log=relay-bin
log_bin=mysql-bin
slave_net_timeout=60
read_only=1

验证主从复制

即在主库上创建表，在从库上查询验证是否能返回插入值，注意：这里在前面设置了mydb，其他库查询无效。

在主库上：

#创建表
USE mydb;
CREATE TABLE t_user (
 id BIGINT AUTO_INCREMENT,
 uname VARCHAR(30),
 PRIMARY KEY (id)
);
INSERT INTO t_user(uname) VALUES('lisa');
INSERT INTO t_user(uname) VALUES(@@hostname);

在从库上：

show databases;
use db_user;
select * from t_user;

读写分离

本文的读写分离采用的的是ProxySQL，ProxySQL 是一种强大的 MySQL 代理软件，用于实现高可用性、负载均衡和读写分离。它位于应用程序与数据库之间，通过智能路由查询请求，优化数据库性能。通过 ProxySQL，您可以简化数据库用户的管理和提高数据库的灵活性。

ProxySQL 基本功能

• 读写分离：ProxySQL 能够根据查询的类型（读或写）将请求路由到相应的 MySQL 主节点或从节点。写操作被路由到主节点，而读操作被路由到从节点。
• 连接池：ProxySQL 提供了连接池功能，可以显著减少 MySQL 服务器的连接开销。
• 高可用性：ProxySQL 可以动态检测数据库节点的状态，并在节点失效时自动切换，提高数据库集群的高可用性。
• 查询缓存：ProxySQL 支持查询缓存，可以显著提高查询性能，特别是在频繁读取的场景下。

ProxySQL 用户管理

在传统的 MySQL 设置中，您通常需要在每个 MySQL 节点上创建用户，并设置相应的权限。但通过 ProxySQL，您可以简化这一过程。

• 集中管理用户：ProxySQL 允许您在其配置中定义数据库用户，这些用户会被 ProxySQL 用于连接到后端的 MySQL 实例。这意味着您可以只在 ProxySQL 中配置用户，而不必在每个 MySQL 实例中重复创建这些用户。
• 单一用户配置：在 ProxySQL 的配置文件中，通常只需要配置一个用户。这个用户将被 ProxySQL 用来与所有的 MySQL 实例进行交互，而应用程序也只需知道这个用户的凭据。ProxySQL 会根据预定义的规则，将查询路由到合适的 MySQL 节点。

具体实现

在从库或者主库机器上创建/opt/proxysql/config/proxysql.cnf 文件

datadir="/var/lib/proxysql"

admin_variables=
{
    admin_credentials="admin:admin" #监控管理用户的用户名和密码
    mysql_ifaces="0.0.0.0:6032" # 管理员接口地址和端口	
}

mysql_variables=
{
    threads=4
    max_connections=2048
    default_query_delay=0
    default_query_timeout=36000000
    poll_timeout=2000
    interfaces="0.0.0.0:6033" # 客户端接口地址和端口
    default_schema="information_schema"
    stacksize=1048576
    server_version="8.0" #数据库的版本
    connect_timeout_server=3000
    monitor_history=60000
    monitor_connect_interval=60000
    monitor_ping_interval=10000
    monitor_read_only_interval=1500
}

mysql_servers =
(
    { address="172.16.208.12", port=3306, hostgroup=0, max_connections=1000 },  # 主库配置
    { address="172.16.208.13", port=3306, hostgroup=1, max_connections=1000 }   # 从库配置
)

mysql_users =
(
    { username = "Tgqs", password = "Tgqs@123", default_hostgroup = 10, transaction_persistent = 1 }
)

mysql_query_rules =
(
    { rule_id=1, active=1, match_pattern="^SELECT", destination_hostgroup=1, apply=1 },  # 读操作路由到从库
    { rule_id=2, active=1, match_pattern="^(INSERT|UPDATE|DELETE|REPLACE)", destination_hostgroup=0, apply=1 }  # 写操作路由到主库
)

在主节点创建用户

CREATE USER 'tgqs'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'Tgqs@123';
GRANT ALL PRIVILEGES ON *.* TO 'tgqs'@'%';
SELECT user, host FROM mysql.user WHERE user = 'tgqs'; #查看是否创建成功

在从库或者主库机器上创建docker-compose.yml文件

version: '2'

services:
  proxysql:
    image: proxysql/proxysql:latest
    container_name: proxysql
    ports:
      - "6032:6032"  # 管理端口
      - "6033:6033"  # MySQL 连接端口
    volumes:
      - /opt/proxysql/proxysql.cnf:/etc/proxysql.cnf  # 挂载配置文件
      - /opt/proxysql/data:/var/lib/proxysql  # 持久化数据
    restart: unless-stopped

#启动容器
docker-compose up -d

验证读写分离

第一种 通过管理接口

docker exec -it proxysql /bin/bash
mysql -u admin -padmin -h 127.0.0.1 -P6032
SELECT * FROM mysql_users WHERE username='tgqs';

#查看服务器配置

SELECT * FROM mysql_servers;

#查看监控规则

SELECT * FROM mysql_query_rules \G;

第二种 进入proxy容器中，登录客户端验证

分别进行读写操作，看是否转移到相应的节点上

docker exec -it proxysql /bin/bash
mysql -utgqs -pTgqs@123 -h127.0.0.1 -P6033
use mydb;
INSERT INTO test_1  VALUES (3,'test'); #写操作
select * from test_1; #读操作

登录到 ProxySQL 管理控制台：

mysql -u admin -padmin -h 172.16.208.13 -P 6032

查看查询日志：

SELECT * FROM stats_mysql_query_digest; #这将显示查询的摘要信息，包括哪些查询被路由到哪个节点。

第三种 查看监控信息

ProxySQL 提供了监控功能，可以帮助确认读写分离的效果。

docker exec -it proxysql /bin/bash
mysql -u admin -padmin -h 127.0.0.1 -P6032

1. 查看MySQL 服务器的状态，会显示每个 MySQL 实例的连接池状态，包括连接数和负载情况。

   SELECT * FROM stats_mysql_connection_pool;

2. 查看查询路由统计，显示每个查询规则的匹配次数和路由情况。

   SELECT * FROM stats_mysql_query_rules;

验证的作用是确保写操作被路由到主节点，读操作被路由到从节点。如果测试结果不符合预期，检查配置文件中的路由规则和 MySQL 服务器的连接状态。

注意事项

• mysql8.0较旧版更新了密码加密方式，格式被更新为caching_sha2_password，需要改为旧版本mysql_native_password，即添加

  ’IDENTIFIED WITH mysql_native_password BY‘ 不改可能会报错。

• 如果启动的时候遇到network mysql_net declared as external, but could not be found时，需要创建网络，然后重启容器。

  docker network create mysql_net
  docker-compose up -d

• 读写分离的用户尽量不要使用replica，即主从复制用户。一个是权限不够，一般不会包含读写权限，即使赋予额外权限，也不是最佳选择；还有就是在进行主从复制的时候会change master暴露密码信息，影响读写分离安全性。

• proxy拥有6032管理和6033客户两个端口

  • 6032这个端口用于访问 ProxySQL 的管理控制台。通过这个端口，您可以执行 SQL 查询来查看和管理 ProxySQL 的配置、监控信息和状态。

  • 6033这个端口用于连接到 ProxySQL 以执行正常的数据库操作（包括读和写操作）。ProxySQL 将根据配置规则将这些查询路由到相应的 MySQL 服务器（主节点或从节点）。