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.companyMyCat会把语句修改为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 status3：基于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);
}
}
通过观察数据库数据变化，进行验证是否成功！
微信关注
编程那点事儿
本站所发布的一切软件资源、文章内容、页面内容可能整理来自于互联网，在此郑重声明本站仅限用于学习和研究目的；并告知用户不得将上述内容用于商业或者非法用途，否则一切后果请用户自负。
如果本站相关内容有侵犯到您的合法权益，请仔细阅读本站公布的投诉指引页相关内容联系我，依法依规进行处理！
作者：理想
链接：https://www.imyjs.cn/archives/845
共有 0 条评论