读写分离-正式服务器配置

本文利用ProxySQL实现MYSQL负载均衡和读写分离。ProxySQL为C语言开发，做为MYSQL的中间件其性能优越稳定。经过作者多方比较，最后在生产环境使用。除官方给出它的特点外，本人给出的其他特点如下：

• 读写分离模式下，如果从机的主宕机，将不会再读取这个从机的数据。
• 可以根据数据库名称路由到不同的服务器。
• 使用hostgroup主机组管理一组mysql实例，相同的主机组实现同一个负载均衡。
• 自带SQLite3数据库，做为其内部管理持久化，不再依赖外部其他任何数据库。
• 可通过配置查询规则进行缓存。
• 有强大的后台监控机制，时间以us（微秒）为单位。
• 可开放web监控界面（一般用不到，后台的监控已经相当好）,默认关闭。
• 可对外开放api接口，默认关闭。
• 可以Mysql的多种集群模式进行管理与配置：主从，MGR,MHR,PXC等。
• 官方文档相当完整，只是仅为英文版本。所以目前国内使用ProxySQL的并不多。
• Percona公司官方建议使用ProxySQL(或HaProxy)做为PXC的中间件。

准备工作

部署图

主机	程序	角色	从机
192.168.1.166 （166主机）	mysql5.7 proxysql:2.4 keepalived	master主	192.168.1.147
192.168.1.147 （147主机）	mysql5.7	slave从	无
192.168.1.187 （187主机）	mysql5.7 proxysql:2.4 keepalived	master备	192.168.1.188
192.168.1.188 （188主机）	mysql5.7	slave从	无

说明：

• 图中虚线部分，可以组成双机热备。
• 当上述某个可写入节点宕机后，proxysql可自动切换。示例：如果master166宕机，则proxySQL的写将切换到master备187主机，同时因slave1（188主机）访问master166失败，读也会自动切换到147主机。
• 由于有两个主，所以，对于increment自动生成的自增主键，必须要每台主机不同，请注意以下docker配置中的auto_increment_xx的配置。

准备

• 主机环境，ubuntu18.04，并安装Docker(最新版本即可)，请直接参考Docker官网安装步骤。
• 因为是多真机部署，为实现主从之间通讯，可以选择使用host模式或swarm模式。建议使用swarm。
• 四台主机，创建swarm集群。166主机执行，创建swarm集群，输出的的join串请copy

# docker swarm init --advertise-addr 192.168.1.166

执行成功后，将输出加入到这个swarm集群的 token请copy，

如果忘记可以使用docker swarm join-token worder 重新获取

docker swarm join --token SWMTKN-1-0r868ggtk... 192.168.1.166:2377

• 其他所有主机，执行以下语句，即加入到swarm集群

# docker swarm join --token SWMTKN-1-0r868ggtk... 192.168.1.166:2377

• 最后swarm集群效果，在166执行

• 添加overlay网卡

# docker network create -d overlay --attachable myingress

配置主从关系

说明：

• 建议将所有启动脚本放到每台主机的相同目录下，
• 每一个主机的脚本目录都在：/home/mysqlreplcluster/start.sh中。
• mysql之间内部通讯依然使用3306端口，此端口影射到宿主机的3307端口。所以，在外部访问mysql将使用3307端口。
• 166与187主机启动脚本
• 注意修改server-id值，可根据要求，修改mysql root用户的密码，以下仅为演示，设置密码为123456。

#!/bin/bash

id=166

echo 'stop'

docker stop mysql5-${id}

echo 'remove'

docker rm mysql5-${id}

echo 'start'

docker run --name mysql5-${id} -d \

--hostname mysql5-${id} \

--network myingress \

-p 3307:3306 \

-e TZ=Asia/Shanghai \

-e MYSQL_ROOT_PASSWORD=123456\

-v /home/mysqlreplcluster/data:/var/lib/mysql \

mysql:5.7 \

--server-id=${id} \

--log-bin=mysql_bin \

--relay-log=relay_bin \

--binlog_format=MIXED \

--expire_logs_days=30 \

--binlog_ignore_db=sys,information_schema,performance_schema \

--max-connections=512 \

--max-allowed-packet=128M \

--default-time-zone=+8:00 \

--skip-name-resolve \

--skip-host-cache \

--host-cache-size=0 \

--log-slave-updates \

--sync_binlog=10 \

--sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION \

--auto_increment_increment=2 \

--auto_increment_offset=1 \

--read-only=OFF

• 188主机与147主机启动脚本
• 此两台主机为从机，所以需要设置read_only=ON。proxySQL在运行时，会动态读取此值，并设置为只读服务器。

#!/bin/bash

id=188

echo 'Stop'

docker stop mysql5-${id}

echo 'Remove'

docker rm mysql5-${id}

echo 'Start'

docker run --name mysql5-${id} -d \

--hostname mysql5-${id} \

--network myingress \

-p 3307:3306 \

-e TZ=Asia/Shanghai \

-e MYSQL_ROOT_PASSWORD=123456 \

-v /home/mysqlreplcluster/data:/var/lib/mysql \

mysql:5.7 \

--server-id=${id} \

--log-bin=mysql_bin \

--relay-log=relay_bin \

--binlog_format=MIXED \

--expire_logs_days=30 \

--binlog_ignore_db=sys,information_schema,performance_schema \

--max-connections=512 \

--max-allowed-packet=128M \

--default-time-zone=+8:00 \

--skip-name-resolve \

--skip-host-cache \

--host-cache-size=0 \

--sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION \

--read-only=ON

• 配置主从

166主机登录mysql并创建用户：

# mysql -uroot -p123456 -h192.168.1.166 -P3307

mysql> -- 创建主从复制账户

mysql> create user ‘repl’@’%’ identified by ‘123456’;

mysql> grant replication slave,replication client on *.* to ‘repl’@’%’;

mysql> -- 创建proxysql监控账户

mysql> create user ‘monitor’@’%’ identified by ‘123456’;

mysql> grant usage on *.* to ‘monitor’@’%’;

mysql> flush privileges;

187主机登录并执行：

# mysql -uroot -p123456 -h192.168.1.187 -P3307

mysql> change master to master_host=’mysql5-166’,master_port=3306,

master_user=’repl’,master_password=’123456’;

mysql> start slave;

mysql> show slave status\G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

再回到166主机，并执行，实现与187的双主：

# mysql -uroot -p123456 -h192.168.1.166 -P3307

mysql> change master to master_host=’mysql5-187’,master_port=3306,

master_user=’repl’,master_password=’123456’;

mysql> start slave;

mysql> show slave status\G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

147主机执行：

# mysql -uroot -p123456 -h192.168.1.147 -P3307

mysql> change master to master_host=’mysql5-166’,master_port=3306,

master_user=’repl’,master_password=’123456’;

mysql> start slave;

mysql> show slave status\G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

188主机执行：

# mysql -uroot -p123456 -h192.168.1.88 -P3307

mysql> change master to master_host=’mysql5-187’,master_port=3306,

master_user=’repl’,master_password=’123456’;

mysql> start slave;

mysql> show slave status\G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

启动proxysql

ProxySQL的配置文件

• admin_credentials="admin:admin;radmin:radmin"中的第二个用户radmin密码radmin为远程登录到proxysql的用户。第一个用户admin仅为localhost登录使用。
• 请修改server_vesion指定真实数据库的版本信息。
• 修改monitor_password="123456"为监控密码。

datadir="/var/lib/proxysql"

admin_variables=

{

admin_credentials="admin:admin;radmin:radmin"

mysql_ifaces="0.0.0.0:6032"

}

mysql_variables=

{

threads=4

max_connections=4096

default_query_delay=0

default_query_timeout=36000000

have_compress=true

poll_timeout=2000

interfaces="0.0.0.0:6033"

default_schema="information_schema"

stacksize=1048576

server_version="5.7"

connect_timeout_server=3000

monitor_username="monitor"

monitor_password="123456"

monitor_history=600000

monitor_connect_interval=60000

monitor_ping_interval=10000

monitor_read_only_interval=1500

monitor_read_only_timeout=500

ping_interval_server_msec=120000

ping_timeout_server=500

commands_stats=true

sessions_sort=true

connect_retries_on_failure=10

}

启动ProxySQL

• 启动脚本：/home/proxysql/start.sh
• 6032端口和于管理ProxySQL，影射到宿主机为16032。
• 6033端口为访问数据库端口，影射到宿主机为3308。

#!/bin/bash

echo 'Stop'

docker stop proxysql

echo 'Remove'

docker rm proxysql

echo 'Start'

docker run --name proxysql -d \

--hostname proxysql \

--network myingress \

-p 16032:6032 \

-p 3308:6033 \

-e TZ=Asia/Shanghai \

-v /home/proxysql/conf/proxysql.cnf:/etc/proxysql.cnf:ro \

-v /home/proxysql/data:/var/lib/proxysql \

proxysql/proxysql:2.4.3

配置proxysql

登录：

使用radmin远程账户，宿主机端口16032。

root@server166:/home/proxysql# mysql -uradmin -pradmin -h192.168.1.166 -P16032 --prompt 'Admin> '

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.7 (ProxySQL Admin Module)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Admin>

配置mysql_servers：

• mysql-_servers表，中保存了mysql数据库的主机、端口及访问权重
• 使用insert into mysql_servers的SQL语句

Admin> insert into mysql_servers(hostgroup_id,hostname,port) values

(10,’mysql5-166’,3306),(10,’mysql5-187’,3306),

(20,’mysql5-147’,3306),(20,’mysql5-188’,3306);

Admin> load mysql servers to runtime;

Admin> save mysql servers to disk;

• select * from mysql_servers;

配置用户：

Admin> insert into mysql_users(username,password,default_hostgroup,defaut_schema)

values(‘root’,’123456’,10,’mysql’);

Admin> load mysql users to runtime;

Admin> save mysql users to disk;

select username,password,default_hostgroup,default_schema from mysql_users;

配置读写分离：

Admin> insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup)

values(10,20);

Admin> load mysql servers to runtime;

Admin> save mysql servers to disk;

select * from mysql_replication_hostgroups;

配置查询路由：

• 先设置 root用户缓存，对于刚查询过的数据，再5s内再次查询，将会命中缓存。
• 因select ..for update会执行锁表操作，所以让此类语句在可写的数据库上执行。
• 所有的select操作，都在从库执行。
• 因root用户的默认操作数据库组为10,所以其他的所有操作insert,update...都会在可写组执行。

Admin> insert into mysql_query_rules(rule_id,active,apply,username,cache_ttl) values

(10,1,0,’root’,5000);

admin> insert into mysql_query_rules(rule_id,active,apply,match_digest,destination_group)

values

(20,1,1,’^SELECT.*FOR update$’,10),

(30,1,1,’^SELECT’,20);

Admin> load mysql query rules to runtime;

Admin> save mysql query rules to disk;

select rule_id,active,apply,username,cache_ttl,match_digest,destination_hostgroup from mysql_query_rules;

宕机测试

先登录ProxySQL：

• 通过3308端口，访问proxySQL，proxySQL会将不同的请求分发给不同的数据库实例。

# mysql -uroot -p123456 -h192.168.1.166 -P3308 --prompt 'SQL> '

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.7 (ProxySQL)

SQL> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| repl_testdb |

| sys |

+--------------------+

5 rows in set (0.06 sec)

查询宕机测试

目前查询走的是147主机：

宕机147：

# docker stop mysql5-147

再次查询：

第一次查询出错，第二次查询命中188主机，切换成功，后面可正常提供查询。

147宕机的情况下，执行一些操作，正常执行：

启动147：

# docker start mysql5-147

独立登录147查询结果已经同步完成：

root@server147:/home/mysqlreplcluster# mysql -uroot -p123456 -h192.168.1.147 -P3307

mysql> select * from repl_testdb.stud;

+----+-------+

| id | name |

+----+-------+

| 12 | Jerry |

+----+-------+

1 row in set (0.00 sec)

写入宕机测试

先查看目前使用的主机：

宕机187：

# docker stop mysql5-187

再次写入：

1：第一次查询异常

2：第二次查询因命中187的从机147没有查询到数据。

3：再次查询，转换成166的从机188,查询出结果。

4：重要功能：ProxySQL再次查询时，因47为从,而它的主子187已经宕机，不再查询147。

启动187：

# docker start mysql5-187

独立登录187，数据已经同步完成：

root@server187:~/temp# mysql -uroot -p123456 -h192.168.1.187 -P3307

mysql> select * from repl_testdb.stud;

+----+------------+

| id | name |

+----+------------+

| 12 | Jerry |

| 14 | mysql5-187 |

| 15 | mysql5-166 |

+----+------------+

3 rows in set (0.00 sec)

宕机测试都通过。

配置Keepalived

说明：

keepalived+ProxySQL请配置多个主机，形成多机热备。

以下仅配置一台主机，建议在187上再配置一个kp+ProxySQL，见部署图。

安装keepalived

# apt install keepalived

添加配置文件

# cat /etc/keepalived/keepalived.conf

注意：请根据网卡名称不同，修改网卡地址

• unicast_src_ip 192.168.1.166 为本机ip
• unicast_peer是keepalived组ip。
• interface enp6s0f1指定需要绑定的网卡名称。

lobal_defs {

router_id lb-master

}

vrrp_instance VI-kube-master {

state MASTER

priority 120

dont_track_primary

interface enp6s0f1

virtual_router_id 88

advert_int 3

unicast_src_ip 192.168.1.166

unicast_peer {

192.168.1.166

192.168.1.187

}

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

192.168.1.88/24 dev enp6s0f1 label enp6s0f1:vip

}

}

启动kp:

# systemctl start keepalived

查看虚拟ip:

局域网内，任意主机都以访问这个虚拟ip:

事务测试

测试在同一个事务中，在没有提交事务的情况下，是否可以查询出事务内之前写入数据。

登录

为了模拟真实的操作，登录时选择使用虚拟IP+ProxySQL的端口：

# mysql -uroot -p123456 -h192.168.1.88 -P3308 --prompt 'ProxySQL> '

命令行测试

结论：测试通过。

-- 开启事务

ProxySQL> start transaction;

Query OK, 0 rows affected (0.00 sec)

-- 写入一行数据

ProxySQL> insert into stud(name) values('Jerry');

Query OK, 1 row affected (0.02 sec)

-- 事务内查询，如果这儿能查询到数据则说明 测试通过 ，以下测试通过。

ProxySQL> select * from stud;

+----+-------+

| id | name |

+----+-------+

| 21 | Jerry |

+----+-------+

1 row in set (0.00 sec)

-- 以下提交或是回滚都可以，即结束这个事务。

ProxySQL> rollback;

Query OK, 0 rows affected (0.05 sec)

Java代码测试

测试通过

Class.forName("com.mysql.cj.jdbc.Driver");

String url = "jdbc:mysql://61.164.242.166:3308/repl_testdb" +

"?characterEncoding=UTF-8&serverTimezone=

Asia/Shanghai&useUnicode=true&useSSL=false";

Connection con = DriverManager.getConnection(url,"root","123456");

con.setAutoCommit(false); //开启事务

String sql = "insert into stud(id,name) values(?,?)";

PreparedStatement pst = con.prepareStatement(sql);//新的st对象

int id = 3;

pst.setInt(1,id);

pst.setString(2,"Jack"+id);

int rows = pst.executeUpdate();

System.err.println("写入："+rows);

pst.close();

sql = "select * from stud where id=?";

pst = con.prepareStatement(sql); //为了测试，再这个连接内，开启一个新的st对象

pst.setInt(1,id);

while (true){ //查询，直到查询出数据才停止

ResultSet rs = pst.executeQuery();

if(rs.next()){

String name = rs.getString("name");

/*如这儿直接可以查询到数据本示例测试通过*/

System.err.println(System.currentTimeMillis()+ " 查询到数据了："+name);

break;

}else{

System.err.println(System.currentTimeMillis()+" 没有查询到数据");

}

rs.close();

}

con.commit(); //提交事务

con.setAutoCommit(true); //设置为原始值

pst.close();

con.close();

测试结果：

测试通过，仅用一次，就查询出了结果。

写入：1

1661499195116 查询到数据了：Jack3

SpringDataSource测试

结论：测试通过。

创建数据源：

package wj.ha.utils;

import com.zaxxer.hikari.HikariConfig;

import com.zaxxer.hikari.HikariDataSource;

import javax.sql.DataSource;

/**

* 数据库连接池

*/

public class DSUtils {

private static DataSource dataSource;

static {

try {

HikariConfig config = new HikariConfig();

config.setDriverClassName("com.mysql.cj.jdbc.Driver");

config.setJdbcUrl("jdbc:mysql://61.164.242.166:3308/repl_testdb" +

"?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false");

config.setUsername("root");

config.setPassword("123456");

config.setMaximumPoolSize(5);

config.setIdleTimeout(3000);

dataSource = new HikariDataSource(config);

} catch (Exception e) {

throw new RuntimeException(e.getMessage(), e);

}

}

public static DataSource getDataSource() {

return dataSource;

}

}

测试代码：

final DataSource ds = DSUtils.getDataSource();

DataSourceTransactionManager ptm = new DataSourceTransactionManager(ds);

TransactionTemplate tt = new TransactionTemplate(ptm);

TransactionCallbackWithoutResult tx =

new TransactionCallbackWithoutResult() {

@Override

public void doInTransactionWithoutResult(TransactionStatus status) {

JdbcTemplate jt = new JdbcTemplate(ds);

jt.queryForList("select * from stud where name='Jerry'");//模拟一次查询

jt = new JdbcTemplate(ds);//插入数据

int id = 7;

String sql = "insert into stud(id,name) values(?,?)";

int rows = jt.update(sql, id, "Jerry" + id);

System.err.println("写入:" + rows);

while (true) {

jt = new JdbcTemplate(ds);

List> list =

jt.queryForList("select * from stud where id=?",

new Object[]{id}, new int[]{Types.INTEGER});

if (list != null && !list.isEmpty()) {

System.err.println(System.currentTimeMillis() + " 查询到数据了:" + list);

break;

} else {

System.err.println(System.currentTimeMillis() + " 没有查询到数据");

}

}

status.setRollbackOnly();//设置回滚

}

};

tt.execute(tx);

运行结果，事务内，可以查询出数据，测试通过：

写入:1

1661499524303 查询到数据了:[{id=7, name=Jerry7}]

原理

登录ProxySQL Admin管理后台，通过查询stats_proxysql_message_metrics这个表，可知，只要开启了事务，在这个事务内，只会有一个可写的数据库给当前用户提供服务：

select sessionId,user,db,hostgroup,cli_host,srv_host

from stats_mysql_processlist;

总结：

ProxySQL后台强大的统计功能真心不错。

另可以打开6080端口，用于在浏览器上查看,以是两个相关参数：

admin-web_enabled 默认值false

admin-web_port 默认值 6080