Linux系统下MySQL主从复制、集群之读写分离架构设计与简单实现
Linux系统下MySQL主从复制、集群之读写分离架构设计与简单实现
最近在学习Linux操作系统相关知识,那么众所周知,Linux系统在服务器领域早已确定了地位,除了基本命令的学习与运维相关的知识外,常见的一些系统架构技术也是需要依赖于Linux系统之上进行搭建的,这里就简单模拟实现下MySQL数据库的主从复制以及MySQL集群之读写分离架构的实现。
一、MySQL主从复制
MySQL 的主从复制又叫 Replication、AB 复制。至少需要两个 MySQL 服务(可以是同一台机器,也可以是不同机器之间进行)。
比如A服务器做主服务器,B服务器做从服务器,在A服务器上进行数据的更新,通过 binlog 日志记录同步到B服务器上,并重新执行同步过来的 binlog 数据,从而达到两台服务器数据一致。
MySQL 数据库的主从复制方案,与使用 scp/rsync 等命令进行的文件级别复制类似,都是数据的远程传输。
只不过 MySQL 的主从复制是其自带的功能,无需借助第三方工具,而且MySQL的主从复制并不是数据库磁盘上的文件直接拷贝,而是通过逻辑的 binlog 日志复制到要同步的服务器本地,然后由本地的线程读取日志里面的 SQL 语句,重新应用到 MySQL 数据库中。
作用:
- 可以实时灾备,用于故障切换;
- 读写分离,提供查询服务,实现负载均衡;
- 数据热备,避免影响业务。
0.环境准备
由于是模拟操作,需要提前准备好环境,我这里在VMware虚拟机中克隆出两台环境一致的Linux系统作为两台不同的服务器,注意两台系统中都安装好了Mysql的软件环境:master
和slave
。
提醒:在进行操作过程中,一定要注意命令是在主服务器还是从服务器上执行的!
1.修改MySQL配置文件
vim /etc/my.cnf
# mysql(master): server-id=1 log-bin=mysql-bin log-slave-updates slave-skip-errors=all # msyql(slave): server-id=2 log-bin=mysql-bin log-slave-updates slave-skip-errors=all
注意:两个机器的server-id不能一致!
- 重启MySQL服务
systemctl restart mysqld
2.登入MySQL
- 分别登录两台服务器的MySQL并执行如下命令检测配置是否生效
mysql> SHOW VARIABLES like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 1 | +---------------+-------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 2 | +---------------+-------+ 1 row in set (0.00 sec)
- 登录
master
节点执行如下命令(master服务器
)show master status; mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
- 登录从节点执行如下命令(
slave服务器
)change master to master_host='192.168.149.131', # 主服务器IP地址 master_user='root', # 主服务器MySQL服务的用户名 master_password='Yjs0612**', # 主服务器MySQL服务的密码 master_log_file='mysql-bin.000001', # 在主服务器MySQL服务中通过show master status;查询 master_log_pos=154; # 在主服务器MySQL服务中通过show master status;查询
- 开启从节点(
slave服务器
)start slave;
- 查看从节点状态(
slave服务器
)show slave status\G; mysql> show slave status\G;
注意:
1.出现如下表示成功
Slave_IO_Running: Yes
//表示I/O线程读取成功Slave_SQL_Running: Yes
//表示SQL线程执行成功2.如果在搭建过程出现错误,可以查看查看错误日志文件
cat /var/log/mysqld.log
3.客户端工具进行测试
4.关闭主从复制
关闭主从复制(slave服务器
)
stop slave;
补充
Slave_IO_Running:NO
的解决办法:
注意:如果出现Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. Error_code: 1593错误,请执行如下命令,rm -rf /var/lib/mysql/auto.cnf删除这个文件,之所以出现会出现这样的问题,是因为我的从库主机是克隆的主库所在的主机,所以auto.cnf文件中保存的UUID会出现重复.
- 重启
master
库:service mysqld restart
(master服务器
) - 查看状态:
show master status;
(master服务器
) - 关闭主从复制:
stop slave;
(slave服务器
) - 执行SQL:
change master to Master_Log_File='mysql-bin.000003',Master_Log_Pos=154;
(slave服务器
)注意修改! - 开启主从复制:
start slave;
(slave服务器
) - 重新查询Slave状态:
show slave status\G;
二、MySQL读写分离
在实现数据库集群之读写分离架构之前,首先需要简单学习下数据库中间件---->MyCat
!
当然在这里只是简单使用MyCat中间件提供强大功能的冰山一角来实现数据库的读写分离架构!如果需要深入了解可以去详细学习MyCat。Mycat2 中文教程_w3cschool
0.预备知识
MyCat 官方文档:MyCat2
1.MyCat 简介
Mycat是一款基于阿里开源产品Cobar而研发的开源数据库分库分表中间件
(基于Java语言开发
)。它支持分布式SQL查询,兼容MySQL通信协议,以Java生态支持多种后端数据,通过数据分片提高数据查询处理能力。官网所言:Mycat国内最活跃的、性能最好的开源数据库中间件!
Mycat实现了 MySQL 协议的服务器,前端用户可以把它看作是一个数据库代理,用 MySQL 客户端工具和命令行访问,而其后端可以用 MySQL 原生协议与多个 MySQL 服务器通信,也可以用 JDBC 协议与大多数主流数据库服务器通信。
Mycat不单只可以做MySQL的代理,它的后端可以支持MySQL、SQL Server、Oracle、DB2、PostgreSQL 等主流数据库,也支持 MongoDB 这种新型NoSQL 方式的存储,未来还会支持更多类型的存储。
2.MyCat 实现原理
MyCat 主要是通过对SQL的拦截,然后经过一定规则的分片解析、路由分析、读写分离分析、缓存分析等,然后将SQL发给后端真实的数据块,并将返回的结果做适当处理返回给客户端。
3.MyCat 应用场景
数据库读写分离
,此时配置最为简单,支持读写分离,主从切换数据库分库分表
,这也是MyCat 的核心功能之一,可以解决数据库分库分表之后的数据插入和查询问题多租户应用
,每个应用一个数据库,但程序只需连接mycat,程序不改变,实现多租户化海量数据的存储及实时查询
,使用MyCat 是一种简单有效的方案替代Hbase,分析大数据
4.MyCat核心配置
MyCat的配置文件在 conf 文件夹下,最核心的配置文件有三个:schema.xml
、server.xml
、rule.xml
1、schema.xml
该配置文件主要是用于配置逻辑库、逻辑表等相关信息
- schema标签
定义MyCat实例中的逻辑库,MyCat可以有多个逻辑库,每个逻辑库都有自己的相关配置。可以使用schema标签来划分这些不同的逻辑库。如果不配置schema标签,所有表的配置会属于同一个默认的逻辑库。
逻辑库的概念和MySQL的database的概念一样,在查询两个不同逻辑库中的表的时候,需要切换到该逻辑库下进行查询。示例:
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1"></schema>
name
:定义逻辑库名,必须唯一不能重复checkSQLschema
:检查发给MyCat的SQL是否含有库名。当该值为true时,例如执行语句select * from TESTDB.company
MyCat会把语句修改为select * from company
去掉库名 TESTDBsqlMaxLimit
:限制返回结果集的行数。该值设置为某个数值时,每条执行的sql语句,如果没有加上limit参数,MyCat会自动加上这个属性的值。不写的话,默认返回所有的值。需要注意的是,如果运行的schema为非拆分库的,那么该属性不会生效。需要自己sql语句加limit。randomDataNode
:这是新版MyCat新加入的属性,定义将一些随机语句发送到该数据节点中
- table 标签
定义mycat中的逻辑表,所有需要拆分的表都需要在这个标签中定义。示例:
<table name="customer" primaryKey="id" dataNode="dn1,dn2" rule="sharding-by-intfile" autoIncrement="true" fetchStoreNodeByJdbc="true"> <childTable name="customer_addr" primaryKey="id" joinKey="customer_id" parentKey="id"> </childTable> </table>
name
:定义逻辑表名,同一个schema中逻辑表名必须唯一primaryKey
:指定逻辑表中的主键,也是需要与物理表的主键一致dataNode
:指定物理表所在数据节点的名称,该属性的值需要和dataNode标签中的name值相对应。配置多个以逗号隔开,或者使用dn1$0-100
方式rule
:指定逻辑表使用的分片规则名称,规则名称在rule.xml中定义,必须与tableRule标签中name属性对应autoIncrement
:是否自增,mycat提供了自增长主键功能,但是对应的mysql节点上数据表,没有auto_increment,那么在mycat层调用last_insert_id()也是不会返回结果的fetchStoreNodeByJdbc
:是否启用ER表使用JDBC方式获取DataNode
childTable标签:用于定义 ER 分片的子表。通过标签上的属性与父表进行关联
name
:子表名称primaryKey
:主键joinKey
:子表中字段的名称parentKey
:父表中字段名称
- dataNode标签
定义mycat中的数据节点,也就是我们所说的数据分片。一个dataNode标签就是一个独立的数据分片。数据节点指向的是存储逻辑表的物理数据库。示例:
<dataNode name="dn1" dataHost="localhost1" database="db1" /> <dataNode name="dn2" dataHost="localhost1" database="db2" /> <dataNode name="dn3" dataHost="localhost1" database="db3" />
name
:定义数据节点的名字,这个名字需要唯一。上边在table标签上用这个名字来建立表与分片对应的关系dataHost
:定义该分片属于哪个数据库实例,属性与dataHost标签上定义的name对应database
:定义该分片属于数据库实例上的具体库
- dataHost标签
这个标签直接定义了具体数据库实例,读写分离配置和心跳语句。示例:
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1" url="jdbc:mysql://localhost:3306" user="root" password="root"> <readHost host="hostS1" url="jdbc:mysql://localhost:3306" user="root" password="123456"/> </writeHost> </dataHost>
name
:唯一标示dataHost标签,供上层使用,必须唯一maxCon
:指定每个读写实例连接池的最大连接minCon
:指定每个读写实例连接池的最小连接,初始化连接池的大小balance
:指定读写分离的负载均衡类型,目前有4种类型0:代表不开启读写分离机制,所有读操作都发送到当前可用的writeHost上
1:代表全部的readHost与stand by writeHost参与select语句的负载均衡,简单的说,当双主双从模式(M1-S1,M2-S2 并且M1 M2互为主备),正常情况下,M2,S1,S2都参与select语句的负载均衡。
2:所有读操作都随机的在writeHost、readHost上分发
3:所有读请求随机的分发到writeHost对应的readHost执行,writeHost不负担读压力(1.4 新增)
writeType
:指定写实例的负载均衡类型,目前有4种类型-1:表示不自动切换
0:所有写操作发送到配置的第一个writeHost,第一个挂了切到还生存的第二个writeHost。重新启动后以切换后的为准,切换记录在配置文件中:dnindex.properties
1:所有写操作都随机的发送到配置的writeHost,1.5 以后废弃不推荐使用
2:基于MySQL主从同步的状态决定是否切换(1.4 新增)
dbType
:指定后端连接的数据库类型dbDriver
:指定连接后端数据库使用的驱动,目前可选的值有 native 和 jdbcswitchType
:指定主从切换的方式-1:表示不自动切换
1:默认值,自动切换
2:基于MySQL主从同步的状态决定是否切换,心跳检测语句为:
show slave status
3:基于MySQL galary cluster 的切换机制(适合集群,1.4.1新增),心跳检测语句为:
show status like 'wsrep%'
slaveThreshold
:定义主从复制延时阈值,当 Seconds_Behind_Master > slaveThreshold 时,读写分离筛选器会过滤掉此Slave机器,防止读到很久之前的旧数据。该属性是用于配合writeType
属性实现根据主从延时来进行主从切换的
4.1)heartbeat 标签:指明用于和后端数据库进行心跳检查的语句。例如,MySQL可以使用
select user()
,Oracle可以使用select 1 from dual
等4.2)writehost 标签及 readHost 标签:这两个标签属性相同,这里一起介绍
host
:标识不同实例名称,一般writeHost名称使用M1作为后缀,readHost则使用S1作为后缀url
:配置数据库的连接地址,如果dbDriver属性是 native,则一般为address:port
这种形式。如果是jdbc或其他的,则需要特殊指定。user
:配置数据库用户名password
:配置数据库密码weight
:配置某个数据库在 readHost 中作为读节点的权重usingDecrypt
:指定是否对密码加密,默认为0, 若需要开启则配置为1
4、server.xml
该配置文件主要是用于配置系统参数、用户信息、访问权限及SQL防火墙和SQL拦截功能等
- system标签
用于配置mycat的系统配置参数。常见的配置参数示例:
<system> <!-- mycat 服务连接端口 --> <property name="serverPort">8066</property> <!-- mycat 服务管理端口 --> <property name="managerPort">9066</property> <!-- mycat 服务监听的ip --> <property name="bindIp">0.0.0.0</property> <!-- 0为需要密码登陆、1为不需要密码登陆;默认为0,设置为1则需要指定默认账户--> <property name="nonePasswordLogin">0</property> <!-- 前端连接的写队列大小 --> <property name="frontWriteQueueSize">2048</property> <!-- 设置字符集编码 --> <property name="charset">utf8</property> <!-- mycat 的进程数量 --> <property name="processors">8</property> <!-- 闲置连接超时时间,单位:毫秒 --> <property name="idleTimeout">1800000</property> <!-- 默认最大返回的数据集大小 --> <property name="defaultMaxLimit">100</property> <!-- 允许的最大包大小 --> <property name="maxPacketSize">104857600</property> <!-- 0遇上没有实现的报文(Unknown command:),就会报错、1为忽略该报文,返回ok报文。 在某些mysql客户端存在客户端已经登录的时候还会继续发送登录报文,mycat会报错,该设置可以绕过这个错误--> <property name="ignoreUnknownCommand">0</property> <property name="useHandshakeV10">1</property> <property name="removeGraveAccent">1</property> <!-- 1为开启实时统计、0为关闭 --> <property name="useSqlStat">0</property> <!-- 1为开启全加班一致性检测、0为关闭 --> <property name="useGlobleTableCheck">0</property> <!-- SQL 执行超时 单位:秒--> <property name="sqlExecuteTimeout">300</property> <property name="sequnceHandlerType">1</property> <!--必须带有MYCATSEQ_或者 mycatseq_进入序列匹配流程 注意MYCATSEQ_有空格的情况--> <property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property> <!-- 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false --> <property name="subqueryRelationshipCheck">false</property> <property name="sequenceHanlderClass">io.mycat.route.sequence.handler.HttpIncrSequenceHandler</property> <!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena | type 2 NettyBufferPool --> <property name="processorBufferPoolType">0</property> <!--分布式事务开关,0为不过滤分布式事务,1为过滤分布式事务(如果分布式事务内只涉及全局表,则不过滤),2为不过滤分布式事务,但是记录分布式事务日志--> <property name="handleDistributedTransactions">0</property> <!-- off heap for merge/order/group/limit 1开启;0关闭 --> <property name="useOffHeapForMerge">0</property> <!--是否采用zookeeper协调切换 --> <property name="useZKSwitch">false</property> <!--如果为 true的话 严格遵守隔离级别,不会在仅仅只有select语句的时候在事务中切换连接--> <property name="strictTxIsolation">false</property> <!-- Mycat连接数据库时使用的隔离级别 1 - 读未提交 2 - 读已提交 3 - 可重复读 4 - 串行化 --> <property name="txIsolation">2</property> <property name="useZKSwitch">true</property> <!--如果为0的话,涉及多个DataNode的catlet任务不会跨线程执行--> <property name="parallExecute">0</property> </system>
- user标签
用于配置mycat的访问用户及权限。示例:
<!-- 用户名,defaultAccount属性是指定当前账户是否为默认账户 --> <user name="root" defaultAccount="true"> <!-- 密码 --> <property name="password">123456</property> <!-- 允许该用户访问的逻辑库 --> <property name="schemas">TESTDB</property> <!-- 可配置多个允许访问的逻辑库,使用逗号分隔 --> <!-- <property name="schemas">db1,db2</property> --> <!-- 是否只读 --> <property name="readOnly">false</property> <!-- 表级 DML 权限配置,check属性表示是否开启该配置 --> <privileges check="true"> <!-- 特别权限应用的逻辑库 --> <schema name="TESTDB" dml="0110"> <!-- 配置用户对该表的访问权限,dml属性用于指定权限位, 如果table标签没有配置该属性的话,默认取schema标签的dml属性值, 剩余没有配置的其他表默认也是取schema标签的dml属性值 --> <table name="tb01" dml="0000"></table> <table name="tb02" dml="1111"></table> </schema> </privileges> </user>
dml属性配置的数字是权限位,分别对应着 insert,update,select,delete 四种权限。例如,当dml的值为0110时,表示拥有 update 和 select 权限,不具有 insert 和 delete 权限。所以权限位为1时代表拥有对应的操作权限,为0时代表没有该操作权限。
3、rule.xml
该配置文件主要用于配置切分规则的。数据库水平拆分之后,就需要用这个配置文件定义分片算法规则
配置文件中主要有两种标签,tableRule 和 function ,这两个是一一对应的,示例:
<!-- name属性指定分片规则的名称,必须在 rule.xml 文件中是唯一的 -->
<tableRule name="mod-long">
<rule>
<!-- 指定使用表中的哪个列(字段)进行分片 -->
<columns>id</columns>
<!-- 指定表的分片算法,取值为<function>标签的name属性 -->
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<!-- name属性指定分片算法的名称,同样需要是唯一的;class属性指定该算法的具体实现类 -->
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- 要分片的数据库节点数量,必须指定,否则没法分片 -->
<property name="count">3</property>
</function>
MyCat默认已经内置了很多分片算法,除了使用这些外也可以根据自己的实际需求自定义算法规则。
MyCat内置的常用分片算法有:
PartitionByMod
:简单取模,直接通过列值进行取模得出分片位置。适用于整数类型的列,不能用于非整型的列,且不需要人工干预PartitionByHashMod
:哈希取模,先将列值进行hash运算之后再取模得出分片位置。适用于非整型的列,且不需要人工干预PartitionByFileMap
:分片枚举,根据枚举值对数据进行分片。适用于需要人工指定某些数据到哪个分片下。例如在异地多活的场景中通过地区id进行数据分片的场景。PartitionByPrefixPattern
:字符串范围取模,根据长字符串的前面几位进行取模分片
注意:
使用MyCat前必须安装Java的JDK环境!
1.MyCat安装
- 下载:Index of / (mycat.org.cn)
我这里下载的版本为:http://dl.mycat.org.cn/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
- 上传:可以通过一些软件进行上传文件:
CRT
、WinSCP
、NxShell
。 - 解压缩:
[root@localhost mycat]# pwd /usr/mycat [root@localhost mycat]# ls Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz [root@localhost mycat]# tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
2.MyCat配置
- 配置
conf
下的配置schema.xml
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <!-- 定义MyCat的逻辑库 --> <schema name="imyjs" checkSQLschema="false" sqlMaxLimit="100" dataNode="yjsNode"></schema> <!-- 定义MyCat的数据节点 --> <dataNode name="yjsNode" dataHost="yjsNode" database="test" /> <dataHost name="yjsNode" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="-1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!--写节点--> <writeHost host="hostM1" url="192.168.149.131:3306" user="root" password="**"> <!--从节点--> <readHost host="hostS1" url="192.168.149.133:3306" user="root" password="**" /> </writeHost> </dataHost> </mycat:schema>
- 配置登陆mycat的权限
server.xml
<?xml version="1.0" encoding="UTF-8"?> <!-- - - Licensed under the Apache License, Version 2.0 (the "License"); - you may not use this file except in compliance with the License. - You may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 - - Unless required by applicable law or agreed to in writing, software - distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the License for the specific language governing permissions and - limitations under the License. --> <!DOCTYPE mycat:server SYSTEM "server.dtd"> <mycat:server xmlns:mycat="http://io.mycat/"> <system> <!-- 这里配置的都是一些系统属性,可以自己查看mycat文--> <property name="defaultSqlParser">druidparser</property> <property name="charset">utf8</property> </system> <user name="root"> <property name="password">root</property> <property name="schemas">imyjs</property> </user> </mycat:server>
3.启动MyCat
- 启动MyCat:
./mycat console
[root@localhost bin]# ./mycat console Running Mycat-server... wrapper | --> Wrapper Started as Console wrapper | Launching a JVM... wrapper | JVM exited while loading the application. jvm 1 | Unrecognized VM option 'MaxPermSize=64M' jvm 1 | Error: Could not create the Java Virtual Machine. jvm 1 | Error: A fatal exception has occurred. Program will exit.
解决办法:
[root@localhost conf]# vim wrapper.conf
# wrapper.java.additional.3=-XX:MaxPermSize=64M
[root@localhost bin]# ./mycat console
Running Mycat-server...
wrapper | --> Wrapper Started as Console
wrapper | Launching a JVM...
jvm 1 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
jvm 1 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
jvm 1 |
jvm 1 | MyCAT Server startup successfully. see logs in logs/mycat.log
注意:我这里JDK版本1.8!如果版本过高会出现一些问题!!
已踩坑
!
- 查看日志:
tail -f ../logs/mycat.log
4.数据库连接测试
创建SpringBoot项目, 进行数据库测试。
application.yml
:
spring:
datasource:
# mycat的逻辑库 端口也是mycat的
url: jdbc:mysql://192.168.149.134:8066/imyjs
driver-class-name: com.mysql.jdbc.Driver
username: root
# 这里配置的是Mycat中server.xml中配置账号密码,不是数据库的密码。
password: root
测试类:
@SpringBootTest
class SpringbootQuickstartApplicationTests {
@Autowired
private UserMapper userMapper;
@Test
void contextLoads() {
// List<User> users = userMapper.selectList(null);
// users.forEach(System.out::println);
User user = new User();
user.setUsername("test");
user.setPassword("test");
userMapper.insert(user);
List<User> users = userMapper.selectList(null);
users.forEach(System.out::println);
}
}
通过观察数据库数据变化,进行验证是否成功!
微信关注

编程那点事儿