使用Docker搭建MySQL集群实战

达到三高（高可用、高性能、高负载）需要至少12个节点。

高可用：冗余节点通过订阅的模式与主节点保持数据同步，依赖MySQL自带的master-slave（主从）数据同步模式，主节点开启binlog日志文件记录查询外的所有操作，从节点订阅binlog文件以保持数据一致。如果主节点再订阅从节点，就能双向同步数据，也叫设置双向主从同步，互为主从节点。

高性能：读写分离，根据Web系统读多写少的特点，使用1主2从的方案，主节点负责写入，从节点负责读取。再使用一组1主2从的节点，2组节点的主节点之间设置双向主从同步，这样又达到高可用。

数据切分（据说MySQL数据库单表数据达到2kw表的读写性能便会急剧下降），也称分库分表。假如将20kw条记录切分到20个节点中，可使用主键求余20得到的余数方法决定切分到的记录。数据切分后也要满足三高，即最少分片后为2个分片，每个分片都要满足三高则需要2组1主2从的节点并设置双向同步，因此至少要12个节点。

版本选择：MySQL5主从同步优于MySQL8

使用MyCat中间件进行MySQL集群的管理，因为只需要主从同步和读写分离。

搭建双主四从的MySQL集群

第一组主从: 创建MySQL_1主节点

拉取MySQL5.7镜像docker pull mysql:5.7
创建网段mysql_netdocker network create --subnet=172.18.0.0/18 mysql_net
创建MySQL_1节点--privileged=true给容器读写Linux系统的权限，--lower_case_table_names=1不区分表名的大小写

1
2
3
4
5
6
docker run	-it -d --name mysql_1 -p 7001:3306 \
--net mysql_net --ip 172.18.0.2 \
-m 400m -v /root/mysql_1/data:/var/lib/mysql -v /root/mysql_1/config:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=123456 -e TZ=Asia/Shanghai \
--privileged=true \
mysql:5.7 --lower_case_table_names=1

为MySQL_1新建用户sync:123456，服务器权限选择reload/replication slave/super
修改MySQL_1配置文件~/mysql_1/config/my.cnf，先stop容器，修改后再start容器

[mysqld]
# 数据库字符集
character_set_server = utf8
#mysql编号
server_id = 1

#开启binlog日志
log_bin = mysql_bin
#开启relaylog日志
relay_log= relay_bin

#从库的写操作是否写入binlog
log-slave-updates = 1
#采用严格的SQL语句模式
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

创建MySQL_2从节点

拉取MySQL5.7镜像docker pull mysql:5.7
创建MySQL_2节点--privileged=true给容器读写Linux系统的权限，--lower_case_table_names=1不区分表名的大小写

1
2
3
4
5
6
docker run	-it -d --name mysql_2 -p 7002:3306 \
--net mysql_net --ip 172.18.0.3 \
-m 400m -v /root/mysql_2/data:/var/lib/mysql -v /root/mysql_2/config:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=123456 -e TZ=Asia/Shanghai \
--privileged=true \
mysql:5.7 --lower_case_table_names=1

修改MySQL_2配置文件~/mysql_2/config/my.cnf，先stop容器，修改后再start容器

[mysqld]
# 数据库字符集
character_set_server = utf8
#mysql编号
server_id = 2

#从节点开启binlog日志，为将来挂载更多读节点而准备
log_bin = mysql_bin
#开启relaylog日志
relay_log= relay_bin

# 限制普通用户只能查询
read-only = 1
#采用严格的SQL语句模式
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

执行SQL语句让MySQL_2订阅MySQL_1的日志文件

1
2
3
4
5
6
7
8
9
stop slave; #-- 停止数据同步服务

# 设置与mysql_1同步数据
change master to master_host='172.18.0.2',master_port=3306,master_user='sync',master_password='123456';

start slave; #-- 开启数据同步服务

# 查询数据同步服务状态, 显示Slave_IO_Running：Yes； Slave_SQL_Running：Yes表示成功
show slave status;

创建MySQL_3从节点

拉取MySQL5.7镜像docker pull mysql:5.7
创建MySQL_3节点--privileged=true给容器读写Linux系统的权限，--lower_case_table_names=1不区分表名的大小写

1
2
3
4
5
6
docker run	-it -d --name mysql_3 -p 7003:3306 \
--net mysql_net --ip 172.18.0.4 \
-m 400m -v /root/mysql_3/data:/var/lib/mysql -v /root/mysql_3/config:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=123456 -e TZ=Asia/Shanghai \
--privileged=true \
mysql:5.7 --lower_case_table_names=1

修改**MySQL_3配置文件~/mysql_3/config/my.cnf，先stop容器，修改后再start容器

[mysqld]
# 数据库字符集
character_set_server = utf8
#mysql编号
server_id = 3

#从节点开启binlog日志，为将来挂载更多读节点而准备
log_bin = mysql_bin
#开启relaylog日志
relay_log= relay_bin

# 限制普通用户只能查询
read-only = 1
#采用严格的SQL语句模式
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

执行SQL语句让MySQL_3订阅MySQL_1的日志文件

1
2
3
4
5
6
7
8
9
stop slave; #-- 停止数据同步服务

# 设置与mysql_1同步数据
change master to master_host='172.18.0.2',master_port=3306,master_user='sync',master_password='123456';

start slave; #-- 开启数据同步服务

# 查询数据同步服务状态, 显示Slave_IO_Running：Yes； Slave_SQL_Running：Yes表示成功
show slave status;

第二组主从: 创建MySQL_4主节点

拉取MySQL5.7镜像docker pull mysql:5.7
创建MySQL_4节点--privileged=true给容器读写Linux系统的权限，--lower_case_table_names=1不区分表名的大小写

1
2
3
4
5
6
docker run	-it -d --name mysql_4 -p 7004:3306 \
--net mysql_net --ip 172.18.0.5 \
-m 400m -v /root/mysql_4/data:/var/lib/mysql -v /root/mysql_4/config:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=123456 -e TZ=Asia/Shanghai \
--privileged=true \
mysql:5.7 --lower_case_table_names=1

为MySQL_4新建用户sync:123456，服务器权限选择reload/replication slave/super ![[Pasted image 20240304003039.png|300]]
修改MySQL_4配置文件~/mysql_4/config/my.cnf，先stop容器，修改后再start容器

[mysqld]
# 数据库字符集
character_set_server = utf8
#mysql编号
server_id = 4

#开启binlog日志
log_bin = mysql_bin
#开启relaylog日志
relay_log= relay_bin

#从库的写操作是否写入binlog
log-slave-updates = 1
#采用严格的SQL语句模式
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

创建MySQL_5从节点

拉取MySQL5.7镜像docker pull mysql:5.7
创建MySQL_5节点--privileged=true给容器读写Linux系统的权限，--lower_case_table_names=1不区分表名的大小写

1
2
3
4
5
6
docker run	-it -d --name mysql_5 -p 7005:3306 \
--net mysql_net --ip 172.18.0.6 \
-m 400m -v /root/mysql_5/data:/var/lib/mysql -v /root/mysql_5/config:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=123456 -e TZ=Asia/Shanghai \
--privileged=true \
mysql:5.7 --lower_case_table_names=1

修改MySQL_5配置文件~/mysql_5/config/my.cnf，先stop容器，修改后再start容器

[mysqld]
# 数据库字符集
character_set_server = utf8
#mysql编号
server_id = 5

#从节点开启binlog日志，为将来挂载更多读节点而准备
log_bin = mysql_bin
#开启relaylog日志
relay_log= relay_bin

# 限制普通用户只能查询
read-only = 1
#采用严格的SQL语句模式
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

执行SQL语句让MySQL_5订阅MySQL_4的日志文件

1
2
3
4
5
6
7
8
9
stop slave; #-- 停止数据同步服务

# 设置与mysql_4同步数据
change master to master_host='172.18.0.5',master_port=3306,master_user='sync',master_password='123456';

start slave; #-- 开启数据同步服务

# 查询数据同步服务状态, 显示Slave_IO_Running：Yes； Slave_SQL_Running：Yes表示成功
show slave status;

创建MySQL_6从节点

拉取MySQL5.7镜像docker pull mysql:5.7
创建MySQL_6节点--privileged=true给容器读写Linux系统的权限，--lower_case_table_names=1不区分表名的大小写

1
2
3
4
5
6
docker run	-it -d --name mysql_6 -p 7006:3306 \
--net mysql_net --ip 172.18.0.7 \
-m 400m -v /root/mysql_6/data:/var/lib/mysql -v /root/mysql_6/config:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=123456 -e TZ=Asia/Shanghai \
--privileged=true \
mysql:5.7 --lower_case_table_names=1

修改MySQL_6配置文件~/mysql_6/config/my.cnf，先stop容器，修改后再start容器

[mysqld]
# 数据库字符集
character_set_server = utf8
#mysql编号
server_id = 6

#从节点开启binlog日志，为将来挂载更多读节点而准备
log_bin = mysql_bin
#开启relaylog日志
relay_log= relay_bin

# 限制普通用户只能查询
read-only = 1
#采用严格的SQL语句模式
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

执行SQL语句让MySQL_6订阅MySQL_4的日志文件

1
2
3
4
5
6
7
8
9
stop slave; #-- 停止数据同步服务

# 设置与mysql_4同步数据
change master to master_host='172.18.0.5',master_port=3306,master_user='sync',master_password='123456';

start slave; #-- 开启数据同步服务

# 查询数据同步服务状态, 显示Slave_IO_Running：Yes； Slave_SQL_Running：Yes表示成功
show slave status;

配置MySQL_1和MySQL_4双向主从同步

在MySQL_1中执行SQL

1
2
3
4
5
6
7
8
9
stop slave; #-- 停止数据同步服务

# 设置与mysql_4同步数据
change master to master_host='172.18.0.5',master_port=3306,master_user='sync',master_password='123456';

start slave; #-- 开启数据同步服务

# 查询数据同步服务状态, 显示Slave_IO_Running：Yes； Slave_SQL_Running：Yes表示成功
show slave status;

在MySQL_4中执行SQL

1
2
3
4
5
6
7
8
9
stop slave; #-- 停止数据同步服务

# 设置与mysql_1同步数据
change master to master_host='172.18.0.2',master_port=3306,master_user='sync',master_password='123456';

start slave; #-- 开启数据同步服务

# 查询数据同步服务状态, 显示Slave_IO_Running：Yes； Slave_SQL_Running：Yes表示成功
show slave status;

如果存在问题，参考https://blog.csdn.net/shaopengjie2/article/details/124149702

在任一主节点中创建项目db数据库，并导入sql，检查其他节点是否同步成功

使用MyCat管理MySQL集群

参考文章:【后端】使用 mycat 建立读写分离 - 明尼苏达的微笑的文章 - 知乎 https://zhuanlan.zhihu.com/p/673445183

运行mycat容器，从容器中复制配置文件到服务器

1
2
3
docker cp mycat:/usr/local/mycat/conf/rule.xml ~/mycat/conf 
docker cp mycat:/usr/local/mycat/conf/schema.xml ~/mycat/conf 
docker cp mycat:/usr/local/mycat/conf/server.xml ~/mycat/conf

修改server.xml配置文件

 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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
	<system>
	<property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户-->
	<property name="ignoreUnknownCommand">0</property><!-- 0遇上没有实现的报文(Unknown command:),就会报错、1为忽略该报文，返回ok报文。
	在某些mysql客户端存在客户端已经登录的时候还会继续发送登录报文,mycat会报错,该设置可以绕过这个错误-->
	<property name="useHandshakeV10">1</property>
    <property name="removeGraveAccent">1</property>
	<property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
	<property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->
	<property name="sqlExecuteTimeout">300</property>  <!-- SQL 执行超时 单位:秒-->
	<property name="sequenceHandlerType">1</property>
	<property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
	<property name="subqueryRelationshipCheck">false</property> <!-- 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false -->
	<property name="sequenceHanlderClass">io.mycat.route.sequence.handler.HttpIncrSequenceHandler</property>
 	<property name="processorBufferPoolType">0</property>
	<property name="handleDistributedTransactions">0</property>
	<property name="useOffHeapForMerge">0</property>
		<!--
			单位为m
		-->
  <property name="memoryPageSize">64k</property>
		<!--
			单位为k
		-->
		<property name="spillsFileBufferSize">1k</property>

		<property name="useStreamOutput">0</property>

		<!--
			单位为m
		-->
		<property name="systemReserveMemorySize">384m</property>


		<!--是否采用zookeeper协调切换  -->
		<property name="useZKSwitch">false</property>

		<property name="strictTxIsolation">false</property>
		<property name="parallExecute">0</property>
	</system>

	<user name="root" defaultAccount="true">
		<property name="password">123456</property>
		<property name="schemas">heathy_db</property>
		<property name="defaultSchema">heathy_db</property>

	</user>
</mycat:server>

修改schema.xml配置文件

 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
30
31
32
33
34
35
36
37
38
39
40
41
42
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="heathy_db" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">

		<table name="tb_action" primaryKey="id" dataNode="dn1" type="global" />
		<table name="tb_customer" primaryKey="id" dataNode="dn1" type="global" />
		<table name="tb_dept" primaryKey="id" dataNode="dn1" type="global" />
		<table name="tb_goods" primaryKey="id" dataNode="dn1" type="global" />
		<table name="tb_module" primaryKey="id" dataNode="dn1" type="global" />
		<table name="tb_permission" primaryKey="id" dataNode="dn1" type="global" />
		<table name="tb_role" primaryKey="id" dataNode="dn1" type="global" />
		<table name="tb_user" primaryKey="id" dataNode="dn1" type="global" />
		<table name="tb_rule" primaryKey="id" dataNode="dn1" type="global" />
		<table name="tb_order" primaryKey="id" dataNode="dn1" type="global" />
		<table name="tb_appointment" primaryKey="id" dataNode="dn1" type="global" />
		<table name="tb_appointment_restriction" primaryKey="id" dataNode="dn1" type="global" />
		<table name="tb_system" primaryKey="id" dataNode="dn1" type="global" />
		<table name="tb_checkup_report" primaryKey="id" dataNode="dn1" type="global" />
		<table name="tb_customer_location" primaryKey="id" dataNode="dn1" type="global" />
		<table name="tb_flow_regulation" primaryKey="id" dataNode="dn1" type="global" />
		<table name="tb_customer_im" primaryKey="id" dataNode="dn1" type="global" />

	</schema>

	<dataNode name="dn1" dataHost="ds_1" database="heathy_db" />

	<dataHost name="ds_1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select 1</heartbeat>
		<!-- can have multi write hosts -->
		<writeHost host="w1" url="jdbc:mysql://172.18.0.2:3306" user="root" password="123456">
				<readHost host="w1r1" url="jdbc:mysql://172.18.0.3:3306" user="root" password="123456" />
				<readHost host="w1r2" url="jdbc:mysql://172.18.0.4:3306" user="root" password="123456" />
		</writeHost>
		<writeHost host="w2" url="jdbc:mysql://172.18.0.5:3306" user="root" password="123456">
				<readHost host="w2r1" url="jdbc:mysql://172.18.0.6:3306" user="root" password="123456" />
				<readHost host="w2r2" url="jdbc:mysql://172.18.0.7:3306" user="root" password="123456" />
		</writeHost>
	</dataHost>

</mycat:schema>

创建mycat容器

1
2
3
4
5
6
7
8
docker run -it -d --name mycat -p 7011:8066 -p 7012:9066 \
--net mysql_net --ip 172.18.0.8 -m 2048m \
-v ~/mycat/conf/schema.xml:/usr/local/mycat/conf/schema.xml  \
-v ~/mycat/conf/rule.xml:/usr/local/mycat/conf/rule.xml \
-v ~/mycat/conf/server.xml:/usr/local/mycat/conf/server.xml \
-v ~/mycat/logs:/usr/local/mycat/logs \
-e TZ=Asia/Shanghai --privileged=true \
manondidi/mycat

使用navicat连接mycat并测试对表的增删改查操作是否与其他MySQL节点同步。

主从同步的思考

弱一致性：

写入和查询的间隔时间非常短，会查不到刚刚插入的数据，间隔时间需要短到毫秒级。
主从同步失效，写入后无法读取最新的数据，需要监控show slave status查看同步状态

强一致性方案： PXC集群方案

使用Percona数据库（MySQL衍生版），牺牲写入性能为代价
每一次写入，都要其他节点都成功同步了数据之后，才算写入成功。

搭建双主四从的MySQL集群#

第一组主从: 创建MySQL_1主节点#

创建MySQL_2从节点#

创建MySQL_3从节点#

第二组主从: 创建MySQL_4主节点#

创建MySQL_5从节点#

创建MySQL_6从节点#

配置MySQL_1和MySQL_4双向主从同步#

在任一主节点中创建项目db数据库，并导入sql，检查其他节点是否同步成功#

使用MyCat管理MySQL集群#

主从同步的思考#