前言：
如今朋友们对“mysql双机热备方案”可能比较关怀，你们都想要分析一些“mysql双机热备方案”的相关资讯。那么小编同时在网摘上汇集了一些有关“mysql双机热备方案””的相关内容，希望大家能喜欢，大家一起来学习一下吧！读写分离-正式服务器配置
本文利用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.sh6032端口和于管理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 为本机ipunicast_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<Map<String, Object>> 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
标签： #mysql双机热备方案
