- 环境
| 主机 | ip |
| master1 | 192.168.233.101 |
| master2 | 192.168.233.102 |
| slave1 | 192.168.233.103 |
| slave2 | 192.168.233.104 |
| client | 192.168.233.105 |
- 需求
master1和master2互为主从，slave1是master1的从，slave2是master2的从。
master主机通过proxysql代理实现负载均衡的写请求处理
slave主机通过proxy SQL代理实现负载均衡的读请求处理
- 四台主机安装mysql8.0，这里以master为例
[root@master1 ~]# wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
[root@master1 ~]# rpm -ivh mysql80-community-release-el7-3.noarch.rpm
[root@master1 ~]# yum -y install mysql mysql-server mysql-devel --nogpgcheck
[root@master1 ~]# systemctl enable --now mysqld
[root@master1 ~]# mysql --version
mysql Ver 8.0.40 for Linux on x86_64 (MySQL Community Server - GPL)
[root@master1 ~]# systemctl status mysqld
● mysqld.service - MySQL ServerLoaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)Active: active (running) since Thu 2025-01-09 17:29:24 HKT; 16s agoDocs: man:mysqld(8)http://dev.mysql.com/doc/refman/en/using-systemd.htmlProcess: 72132 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)Main PID: 72205 (mysqld)Status: "Server is operational"CGroup: /system.slice/mysqld.service└─72205 /usr/sbin/mysqldJan 09 17:29:13 master1 systemd[1]: Starting MySQL Server...
Jan 09 17:29:24 master1 systemd[1]: Started MySQL Server.
- 修改master1的root密码
[root@master1 ~]# grep password /var/log/mysqld.log
2025-01-09T09:29:17.052126Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: VL74ZHqS*%?-
[root@master1 ~]# mysql -uroot -p'VL74ZHqS*%?-'
#修改root密码
mysql> alter user 'root'@'localhost' identified by 'b]<T%7uu!$|THr#%Fx';
Query OK, 0 rows affected (0.03 sec)#授权root远程访问
mysql> update mysql.user set host='%' where user='root';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0#刷新
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)#查看
mysql> select host,user from mysql.user where user='root';
+------+------+
| host | user |
+------+------+
| % | root |
+------+------+
1 row in set (0.00 sec)
其余三台也是同样方式修改，这里不再赘述
- 编辑master1配置文件
[mysqld]#数据目录
datadir=/var/lib/mysql#套接字文件
socket=/var/lib/mysql/mysql.sock#错误日志
log-error=/var/log/mysqld.log#进程文件
pid-file=/var/run/mysqld/mysqld.pid#端口
port=3308#默认字符集
character-set-server=utf8mb4#默认字符序列
collation-server=utf8mb4_unicode_ci#开启自动提交事务
autocommit=1#唯一标识符
server-id=1#启用二进制日志
log-bin=mysql-bin#事务隔离级别
transaction-isolation=SERIALIZABLE#启用gtid模式
gtid_mode=ON#强制执行gtid一致性
enforce-gtid-consistency=ON#启用从服务器记录复制更新到其二进制日志中
log_replica_updates=ON#禁用只读模式
read-only=0#避免从库启动时自动开始复制
skip_replica_start=true#主键自增，2个一增
auto_increment_increment=2#主键从1开始增加，后面就是1、3、5..
auto_increment_offset = 1#事务每次提交都将记录到二进制日志
sync_binlog=1#设置缓存池大小（一般设置内存的70%-80%）
innodb_buffer_pool_size=35G#设置缓冲池实例数，结合缓冲池大小调整
innodb_buffer_pool_instances=30#缓冲池实例大小
innodb_buffer_pool_chunk_size=1G
- 重启mysql
注意所有服务器要关闭selinux，不然这一步会有问题
[root@master1 ~]# getenforce
Permissive
[root@master1 ~]# systemctl restart mysqld- 修改master2配置文件
[mysqld]#数据目录
datadir=/var/lib/mysql#套接字文件
socket=/var/lib/mysql/mysql.sock#错误日志
log-error=/var/log/mysqld.log#进程文件
pid-file=/var/run/mysqld/mysqld.pid#端口
port=3308#默认字符集
character-set-server=utf8mb4#默认字符序列
collation-server=utf8mb4_unicode_ci#开启自动提交事务
autocommit=1#唯一标识符
server-id=2#启用二进制日志
log-bin=mysql-bin#事务隔离级别
transaction-isolation=SERIALIZABLE#启用gtid模式
gtid_mode=ON#强制执行gtid一致性
enforce-gtid-consistency=ON#启用从服务器记录复制更新到其二进制日志中
log_replica_updates=ON#禁用只读模式
read-only=0#避免从库启动时自动开始复制
skip_replica_start=true#主键自增，2个一增
auto_increment_increment=2#主键从2开始增加，后面就是2、4、6..
auto_increment_offset = 2#事务每次提交都将记录到二进制日志
sync_binlog=1#设置缓存池大小（一般设置内存的70%-80%）
innodb_buffer_pool_size=35G#设置缓冲池实例数，结合缓冲池大小调整
innodb_buffer_pool_instances=30#缓冲池实例大小
innodb_buffer_pool_chunk_size=1G#重启
[root@master2 ~]# systemctl restart mysqld- 修改slave1配置文件
[mysqld]#数据目录
datadir=/var/lib/mysql#套接字文件
socket=/var/lib/mysql/mysql.sock#错误日志
log-error=/var/log/mysqld.log#进程文件
pid-file=/var/run/mysqld/mysqld.pid#端口
port=3308#默认字符集
character-set-server=utf8mb4#默认字符序列
collation-server=utf8mb4_unicode_ci#唯一标识符
server-id=3#启用二进制日志
log-bin=mysql-bin#中继日志
relay-log=relay-log-bin#中继日志索引信息
relay-log-index=slave-relay-bin.index#事务隔离级别
transaction-isolation=SERIALIZABLE#启用gtid模式
gtid_mode=ON#强制执行gtid一致性
enforce-gtid-consistency=ON#开启只读模式
read-only=1#避免从库启动时自动开始复制
skip_replica_start=true#设置缓存池大小（一般设置内存的70%-80%）
innodb_buffer_pool_size=35G#设置缓冲池实例数，结合缓冲池大小调整
innodb_buffer_pool_instances=30#缓冲池实例大小
innodb_buffer_pool_chunk_size=1G[root@slave1 ~]# systemctl restart mysqld
- 修改slave2配置文件
[mysqld]#数据目录
datadir=/var/lib/mysql#套接字文件
socket=/var/lib/mysql/mysql.sock#错误日志
log-error=/var/log/mysqld.log#进程文件
pid-file=/var/run/mysqld/mysqld.pid#端口
port=3308#默认字符集
character-set-server=utf8mb4#默认字符序列
collation-server=utf8mb4_unicode_ci#唯一标识符
server-id=4#启用二进制日志
log-bin=mysql-bin#中继日志
relay-log=relay-log-bin#中继日志索引信息
relay-log-index=slave-relay-bin.index#事务隔离级别
transaction-isolation=SERIALIZABLE#启用gtid模式
gtid_mode=ON#强制执行gtid一致性
enforce-gtid-consistency=ON#开启只读模式
read-only=1#避免从库启动时自动开始复制
skip_replica_start=true#设置缓存池大小（一般设置内存的70%-80%）
innodb_buffer_pool_size=35G#设置缓冲池实例数，结合缓冲池大小调整
innodb_buffer_pool_instances=30#缓冲池实例大小
innodb_buffer_pool_chunk_size=1G[root@slave2 ~]# systemctl restart mysqld- 在master1、master2上面创建同步用户
[root@master1 ~]# mysql -u root -p'b]<T%7uu!$|THr#%Fx'mysql> create user 'myslave'@'%' identified by 'b]<T%7uu!$|THr#%Fx';
Query OK, 0 rows affected (0.09 sec)mysql> grant replication slave on *.* to 'myslave'@'%';
Query OK, 0 rows affected (0.02 sec)mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
- 查看主状态
master1
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 157 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)master2
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 157 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)- 在master2、slave1上面配置主库为master1
master2
mysql> change master to master_host='192.168.233.101',master_user='myslave',master_port=3308,master_password='b]<T%7uu!$|THr#%Fx',master_log_file='mysql-bin.000001',master_log_pos=157,get_master_public_key=1;
Query OK, 0 rows affected, 10 warnings (0.07 sec)
slave1
mysql> change master to master_host='192.168.233.101',master_user='myslave',master_port=3308,master_password='b]<T%7uu!$|THr#%Fx',master_log_file='mysql-bin.000001',master_log_pos=157,get_master_public_key=1;
Query OK, 0 rows affected, 10 warnings (0.07 sec)- 在master1、slave2上面配置 上面配置主库为master2
master1
mysql> change master to master_host='192.168.233.102',master_user='myslave',master_port=3308,master_password='b]<T%7uu!$|THr#%Fx',master_log_file='mysql-bin.000001',master_log_pos=157,get_mastter_public_key=1;
Query OK, 0 rows affected, 10 warnings (0.07 sec)slave2
mysql> change master to master_host='61.4.122.170',master_user='myslave',master_port=3308,master_password='b]<T%7uu!$|THr#%Fx',master_log_file='mysql-bin.000001',master_log_pos=157,get_master_public_key=1;
Query OK, 0 rows affected, 10 warnings (0.05 sec)
所有服务器上启动
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.06 sec)查看主从状态， Slave_IO_Running 和 Slave_SQL_Running 这两个参数的值必须为 yes 才可以
master1状态
mysql> show slave status\G
...省略部分内容Master_User: myslaveMaster_Port: 3308Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 157Relay_Log_File: master1-relay-bin.000002Relay_Log_Pos: 326Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:
..省略部分内容
1 row in set, 1 warning (0.00 sec)
master2状态
mysql> show slave status\G
*************************** 1. row ***************************
......省略部分内容Master_User: myslaveMaster_Port: 3308Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 157Relay_Log_File: master2-relay-bin.000002Relay_Log_Pos: 326Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:
......省略部分内容
1 row in set, 1 warning (0.00 sec)
slave1状态
mysql> show slave status\G
......省略部分内容Master_User: myslaveMaster_Port: 3308Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 157Relay_Log_File: relay-log-bin.000002Relay_Log_Pos: 326Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:
......省略部分内容
1 row in set, 1 warning (0.00 sec)
slave2状态
mysql> show slave status\G
......省略部分内容Master_User: myslaveMaster_Port: 3308Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 157Relay_Log_File: relay-log-bin.000002Relay_Log_Pos: 326Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:
......省略部分内容
1 row in set, 1 warning (0.00 sec)
测试
在master1上面创建数据
mysql> create database test;
Query OK, 1 row affected (0.01 sec)mysql> use test;
Database changedmysql> CREATE TABLE test (-> id INT AUTO_INCREMENT PRIMARY KEY,-> name VARCHAR(100) NOT NULL,-> value VARCHAR(100)-> );
Query OK, 0 rows affected (0.05 sec)mysql> INSERT INTO test (name, value) VALUES -> ('Test a', 'Value a'),-> ('Test b', 'Value b'),-> ('Test c', 'Value c');
Query OK, 3 rows affected (0.02 sec)mysql> select * from test.test;
+----+--------+---------+
| id | name | value |
+----+--------+---------+
| 1 | Test a | Value a |
| 3 | Test b | Value b |
| 5 | Test c | Value c |
+----+--------+---------+
3 rows in set (0.00 sec)
在master2，slave1，slave2上面查看
#master2
mysql> select * from test.test;
+----+--------+---------+
| id | name | value |
+----+--------+---------+
| 1 | Test a | Value a |
| 3 | Test b | Value b |
| 5 | Test c | Value c |
+----+--------+---------+
3 rows in set (0.00 sec)#slave1
mysql> select * from test.test;
+----+--------+---------+
| id | name | value |
+----+--------+---------+
| 1 | Test a | Value a |
| 3 | Test b | Value b |
| 5 | Test c | Value c |
+----+--------+---------+
3 rows in set (0.00 sec)#slave2
mysql> select * from test.test;
+----+--------+---------+
| id | name | value |
+----+--------+---------+
| 1 | Test a | Value a |
| 3 | Test b | Value b |
| 5 | Test c | Value c |
+----+--------+---------+
3 rows in set (0.00 sec)可以看到，在master1上面写的数据，成功同步
接下来测试在master2上面写入数据
mysql> INSERT INTO test.test (name, value) VALUES -> ('Test d', 'Value d'),-> ('Test e', 'Value e'),-> ('Test f', 'Value f');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql> select * from test.test;
+----+--------+---------+
| id | name | value |
+----+--------+---------+
| 1 | Test a | Value a |
| 3 | Test b | Value b |
| 5 | Test c | Value c |
| 6 | Test d | Value d |
| 8 | Test e | Value e |
| 10 | Test f | Value f |
+----+--------+---------+
6 rows in set (0.00 sec)mysql> 在master1、slave1、slave2上面查看
#master1
mysql> select * from test.test;
+----+--------+---------+
| id | name | value |
+----+--------+---------+
| 1 | Test a | Value a |
| 3 | Test b | Value b |
| 5 | Test c | Value c |
| 6 | Test d | Value d |
| 8 | Test e | Value e |
| 10 | Test f | Value f |
+----+--------+---------+
6 rows in set (0.01 sec)#slave1
mysql> select * from test.test;
+----+--------+---------+
| id | name | value |
+----+--------+---------+
| 1 | Test a | Value a |
| 3 | Test b | Value b |
| 5 | Test c | Value c |
| 6 | Test d | Value d |
| 8 | Test e | Value e |
| 10 | Test f | Value f |
+----+--------+---------+
6 rows in set (0.00 sec)#slave2
mysql> select * from test.test;
+----+--------+---------+
| id | name | value |
+----+--------+---------+
| 1 | Test a | Value a |
| 3 | Test b | Value b |
| 5 | Test c | Value c |
| 6 | Test d | Value d |
| 8 | Test e | Value e |
| 10 | Test f | Value f |
+----+--------+---------+
6 rows in set (0.00 sec)
双主双从到此全部搞定，接下来配置proxysql代理
- 在master1上面安装proxysql
添加存储库
[root@master1 ~]# cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
> [proxysql_repo]
> name=ProxySQL repository
> baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.7.x/centos/\$releasever
> gpgcheck=1
> gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.7.x/repo_pub_key
> EOF
安装
[root@master1 ~]# yum -y install proxysql
[root@master1 ~]# proxysql --version
2025-01-10 14:16:59 [INFO] Using jemalloc with MALLOC_CONF: config.xmalloc:1, lg_tcache_max:16, opt.prof_accum:1, opt.prof_leak:1, opt.lg_prof_sample:20, opt.lg_prof_interval:30, rc:0
ProxySQL version 2.7.1-16-g2726c27, codename Truls
[root@master1 ~]# systemctl enable --now proxysql.service
[root@master1 ~]# systemctl status proxysql
● proxysql.service - High Performance Advanced Proxy for MySQLLoaded: loaded (/etc/systemd/system/proxysql.service; enabled; vendor preset: disabled)Active: active (running) since Fri 2025-01-10 14:17:30 HKT; 10s agoProcess: 79898 ExecStart=/usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf $PROXYSQL_OPTS (code=exited, status=0/SUCCESS)Main PID: 79900 (proxysql)CGroup: /system.slice/proxysql.service└─79900 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnfJan 10 14:17:30 master1 systemd[1]: Starting High Performance Advanced Proxy for MySQL...
Jan 10 14:17:30 master1 proxysql[79898]: 2025-01-10 14:17:30 [INFO] Using jemalloc with MALLOC_CONF: config.xmalloc:1, lg_tcache_max:16, opt.prof_accum:1, opt.prof_leak:1, opt.lg_prof_sample:20...erval:30, rc:0
Jan 10 14:17:30 master1 proxysql[79898]: 2025-01-10 14:17:30 [INFO] Using config file /etc/proxysql.cnf
Jan 10 14:17:30 master1 proxysql[79898]: 2025-01-10 14:17:30 [INFO] Current RLIMIT_NOFILE: 102400
Jan 10 14:17:30 master1 proxysql[79898]: 2025-01-10 14:17:30 [INFO] Using OpenSSL version: OpenSSL 3.3.1 4 Jun 2024
Jan 10 14:17:30 master1 proxysql[79898]: 2025-01-10 14:17:30 [INFO] SSL keys/certificates found in datadir (/var/lib/proxysql): loading them.
Jan 10 14:17:30 master1 systemd[1]: Started High Performance Advanced Proxy for MySQL.
Hint: Some lines were ellipsized, use -l to show in full.[root@master1 ~]# netstat -anpt | grep proxysql
tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 79833/proxysql
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 79833/proxysql
#6032是管理端口
#6033是SQL处理端口- 登录proxysql管理界面
默认用户名为admin，密码为admin，端口6032
[root@master1 ~]# mysql -u admin -p'admin' -h 127.0.0.1 -P 6032
配置后端数据库实例 信息
添加主服务器（写）
mysql> INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES (10, '192.168.233.101', 3308, 1);
Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES (10, '192.168.233.102', 3308, 1);
Query OK, 1 row affected (0.00 sec)
添加从服务器（读）
mysql> INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES (20, '192.168.233.103', 3308, 1);
Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES (20, '192.168.233.104', 3308, 1);
Query OK, 1 row affected (0.00 sec)
hostgroup_id：用于分组服务器，表示组id。
hostname：后端服务器的ip地址。
port：后端服务器端口
weight：服务器权重，用于负载均衡
- 创建连接用户
连接用户是客户端连接到proxysql的用户
mysql> INSERT INTO mysql_users (username, password, active, default_hostgroup) VALUES ('proxysql', 'b]<T%7uu!$|THr#%Fx', 1, 0);
Query OK, 1 row affected (0.00 sec)username：用户名
password：用户的密码
active： 用户是否启用，1表示启用，0表示禁用
default_hostgroup：默认连接到后端服务器的组id，0表示没有，也就是访问的时候完全依赖路由规则，如果访问的时候没有匹配到任何规则，会报错
- 在后端数据库服务器上面创建相同用户
ProxySQL 本质上是一个中间代理层，当客户端连接到 ProxySQL 并使用某个用户身份发起请求时，ProxySQL 会以同样的用户身份将请求转发到后端 MySQL 服务器。后端服务器必须验证该用户的身份并授予适当的权限，才能完成请求的处理。
mysql> create user 'proxysql'@'%' identified by 'b]<T%7uu!$|THr#%Fx';
Query OK, 0 rows affected (0.06 sec)mysql> grant select,insert,update,delete on *.* to 'proxysql'@'%';
Query OK, 0 rows affected (0.01 sec)mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)mysql> select host,user from mysql.user where user='proxysql';
+------+----------+
| host | user |
+------+----------+
| % | proxysql |
+------+----------+
1 row in set, 1 warning (0.00 sec)
授予增删改查权限
四台服务器上面都要创建，由于做了主从，只需要在master上面创建就可以
- 创建监视用户
proxysql上创建
mysql> INSERT INTO mysql_users (username, password, active, default_hostgroup) VALUES ('monitor', 'b]<T%7uu!$|THr#%Fx', 1, 10);
Query OK, 1 row affected (0.01 sec)
后端服务器上创建
mysql> create user 'monitor'@'%' identified by 'b]<T%7uu!$|THr#%Fx';
Query OK, 0 rows affected (0.02 sec)mysql> grant select on *.* to 'monitor'@'%';
Query OK, 0 rows affected (0.01 sec)mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
proxysql配置监视用户
mysql> SET mysql-monitor_username = 'monitor';
Query OK, 1 row affected (0.00 sec)mysql> SET mysql-monitor_password = 'b]<T%7uu!$|THr#%Fx';
Query OK, 1 row affected (0.00 sec)
- 添加路由规则
写流量
mysql> INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup, apply, active) VALUES (10, '^INSERT|^UPDATE|^DELETE|^REPLACE', 10, 1,1);
Query OK, 1 row affected (0.00 sec)
读流量
mysql> INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup, apply, active) VALUES (20, '^SELECT', 20, 1,1);
Query OK, 1 row affected (0.00 sec)
rule_id：路由规则编号
match_pattern：匹配SQL语句正则表达式。
^INSERT|^UPDATE|^DELETE|^REPLACE匹配所有写操作
^SELECT 匹配所有读操作
destination_hostgroup: 指定SQL请求的目标 hostgroup。
apply：应用规则
active：启用规则
保存配置
mysql> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.02 sec)mysql> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)mysql> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)mysql> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.10 sec)mysql> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.02 sec)mysql> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.04 sec)
