MySQL是一种关系型数据库,拥有数据备份等功能,可以开启主从复制。而市面上有一些可以实现主从复制的中间件,MyCat是基于JDBC实现的Java分库分表中间件。通过MyCat能够实现数据的主从复制,以及分库分表等策略。解决了数据库的性能瓶颈和扩展并发需要。
1.1数据性能优化方案
sql优化
缓存
建好索引
读写分离
分库分表
本文将围绕主从复制和分库分表来讲解MyCat。
二、主从复制
Mysql作为目前世界上使用最广泛的免费数据库,相信所有从事系统运维的工程师都一定接触过。但在实际的生产环境中,由单台Mysql作为独立的数据库是完全不能满足实际需求的,无论是在安全性,高可用性以及高并发等各个方面。
2.1MyCat主从复制原理
- master将操作记录到二进制日志(binary log)中(这些记录叫做二进制日志事件,binary logevents)
- Slave通过I/O Thread异步将master的binary logevents拷贝到它的中继日志(relay log);
- Slave执行relay日志中的事件,匹配自己的配置将需要执行的数据,在slave服务上执行一遍从而达到复制数据的目的。
2.2主节点配置
1.接入mysql并创建主从复制的用户create user "username" identified by 'password';
2.给新建的用户赋权GRANT REPLICATION SLAVE ON *.* TO 'username'@'%' IDENTIFIED BY 'password';
3.指定服务ID,开启binlog日志记录,在my.cnf中加入
server-id=137 // 唯一
log-bin=dbstore_binlog //日志
binlog-do-db=db_store //需要同步的db 可以不写,同步所有
skip-name-resolve //禁止dns 反解析,也可以不配置,需要配置/etc/hosts 文件,域名和IP的对应关系。
4.重启mysql服务。
2.3主节点其他配置
(1) log-bin=mysql-bin
控制master的是否开启binlog记录功能;二进制文件最好放在单独的目录下,这不但方便优化、更方便维护。如下例子:要重新调整logbin的路径为“/home/mysql/binlog”
log_bin=/home/mysql/binlog/binlog.log需要注意:指定目录时候一定要以*.log结尾,即不能仅仅指定到文件夹的级别,否则在重启mysql时会报错。
(2) server-id=1
每个server服务的标识,在master/slave环境中,此变量一定要不一样
(3)expire_logs_days=15
通过此来实现master自动删除binlog
(4) innodb_flush_log_at_trx_commit=1
此参数表示在事务提交时,处理重做日志的方式;此变量有三个可选值0,1,2:
0:当事务提交时,并不将事务的重做日志写入日志文件,而是等待每秒刷新一次
1:当事务提交时,将重做日志缓存的内容同步写到磁盘日志文件,为了保证数据一致性,在replication环境中使用此值。
2:当事务提交时,将重做日志缓存的内容异步写到磁盘日志文件(写到文件系统缓存中)
建议必须设置innodb_flush_log_at_trx_commit=1
(5)sync_binlog=1
1、此参数表示每写缓冲多少次就同步到磁盘;
2、sync_binlog=1表示同步写缓冲和磁盘二进制日志文件,不使用文件系统缓存
在使用innodb事务引擎时,在复制环境中,为了保证最大的可用性,都设置为“1”,但会对影响io的性能。
3、即使设置为“1”,也会有问题发生:
假如当二进制日志写入磁盘,但事务还没有commit,这个时候宕机,当服务再次起来的恢复的时候,无法回滚以及记录到二进制日志的未提交的内容;这个时候就会造成master和slave数据不一致
解决方案:
需要参数innodb_support_XA=1来保证。建议必须设置
(6)innodb_support_xa=1
此参数与XA事务有关,它保证了二进制日志和innodb数据文件的同步,保证复制环境中数据一致性。建议必须设置
(7)binlog-do-db=skate_db
只记录指定数据库的更新到二进制日志中
(8)binlog-do-table=skate_tab
只记录指定表的更新到二进制日志中
(9) binlog-ignore-db=skate_db
忽略指定数据库的更新到二进制日志中
(10)log_slave_updates=1
此参数控制slave数据库是否把从master接受到的log并在本slave执行的内容记录到slave的二进制日志中
在级联复制环境中(包括双master环境),这个参数是必须的
(11)binlog_format=statement|row|mixed
控制以什么格式记录二进制日志的内容,默认是mixed
(12) max_binlog_size
master的每个二进制日志文件的大小,默认1G
(13)binlog_cache_size
1、所有未提交的事务都会被记录到一个缓存或临时文件中,待提交时,统一同步到二进制日志中,
2、此变量是基于session的,每个会话开启一个binlog_cache_size大小的缓存。
3、通过变量“Binlog_cache_disk_use”和“Binlog_cache_use”来设置binlog_cache_size的大小。
说明:
Binlog_cache_disk_use: 使用临时文件写二进制日志的次数
Binlog_cache_use: 使用缓冲记写二进制的次数
(14)auto_increment_increment=2 //增长的步长
auto_increment_offset=1 //起始位置
在双master环境下可以防止键值冲突
2.4slave 配置
(1)指定服务器ID,指定同步的binlog存储位置,在 my.cnf中加入
server-id=101
relay-log=slave-relay-bin --中继日志
relay-log-index=slave-relay-bin.index
read_only=1 //从服务器只读
replicate_do_db=db_store //同步哪个db,可以不写同步所有
skip-name-resolve //禁止dns 反解析,也可以不配置,需要配置/etc/hosts 文件,域名和IP的对应关系。
(2)重启mysql服务。接入slave的mysql服务,并配置
change master to master_host='192.168.1.1',master_port=3306,master_user='slave1',master_password='slave1',master_log_file='dbstore_binlog.000001',master_log_pos=120;
(3)start slave; 如果要结束主从 执行 stop slave
2.5MyCat
下载MyCat解压后进入 mycat 主目录,
启动 mycat ,进入 bin 目录 ./mycat start :默认端口 8066
由于mycat 可以看作是一个数据库的代理(或者直接看成加强版数据库),mycat 可以支持所有的基于JDBC这种连接的相关的东西。命令如下:
mysql -uroot -p -h192.168.1.1 -P8066
或者 mysql -uroot -p123456 -h192.168.1.1 -P8066
Mycat不负责任何的数据同步,需要事先配置mysql的主从复制哦。
2.5.1MyCat配置
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- 逻辑库配置-->
<!--一个schema标签就是一个逻辑库, 逻辑库名称 -->
<schema name="db_store" checkSQLschema="false" sqlMaxLimit="100">
<!--非分片表的配置 名称,从属节点 主键-->
<table name="store" dataNode="db_store_dataNode" primaryKey="storeID"/>
<table name="employee" dataNode="db_store_dataNode" primaryKey="employeeID"/>
</schema>
<schema name="db_user" checkSQLschema="false" sqlMaxLimit="100">
<!--全局表 指定 type ,指定所在分片 ,主键-->
<table name="data_dictionary" type="global" dataNode="db_user_dataNode1,db_user_dataNode2" primaryKey="dataDictionaryID"/>
<!--分片表的配置 ,配置分片节点 ,rule 配置分片规则,具体配置在rule.xml里面,在rule.xml再细说-->
<table name="users" dataNode="db_user_dataNode$1-2" rule="mod-userID-long" primaryKey="userID">
<!--ER表,通过parentKey去找 users表对应的分片,放到同一个分片下-->
<childTable name="user_address" joinKey="userID" parentKey="userID" primaryKey="addressID"/>
</table>
</schema>
<!-- 节点配置
为什么 db_store只有一个节点呢?因为这个节点理的两个主机是主从复制实现了读写分离
这里即具备读也具备了写。在节点主机配置中有体现。
-->
<!-- db_store -->
<dataNode name="db_store_dataNode" dataHost="db_storeHOST" database="db_store" />
<!-- db_user -->
<dataNode name="db_user_dataNode1" dataHost="db_userHOST1" database="db_user" />
<dataNode name="db_user_dataNode2" dataHost="db_userHOST2" database="db_user" />
<!-- 主从复制 节点主机配置 -->
<!-- 配置db_store的节点主机 最大连接数100 最小10 -->
<dataHost name="db_storeHOST" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<!-- 心跳机制 -->
<heartbeat>select user()</heartbeat>
<!-- 读写分离 can have multi write hosts 写节点(master)-->
<writeHost host="hostM1" url="192.168.1.1:3306" user="root" password="javagongfu">
<!-- can have multi read hosts 读节点(slave)-->
<readHost host="hostS1" url="192.168.1.1:3306" user="root" password="javagongfu" />
</writeHost>
</dataHost>
<!-- 配置db_user的节点主机 -->
<dataHost name="db_userHOST1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="userHost1" url="192.168.1.1:3306" user="root" password="javagongfu">
</writeHost>
</dataHost>
<dataHost name="db_userHOST2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="userHost2" url="192.168.1.1:3306" user="root" password="javagongfu">
</writeHost>
</dataHost>
</mycat:schema>
2.5.2配置规则
<mycat:rule xmlns:mycat="http://io.mycat/">
........
<tableRule name="mod-long">
<rule>
<columns>id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<!-- 分片规则定义-->
<tableRule name="mod-userID-long">
<rule>
<!--根据哪个列来分区-->
<columns>userID</columns>
<!--分区算法,指向下面的function 标签-->
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<tableRule name="sharding-by-murmur">
<rule>
<columns>id</columns>
<algorithm>murmur</algorithm>
</rule>
</tableRule>
<tableRule name="crc32slot">
<rule>
<columns>id</columns>
<algorithm>crc32slot</algorithm>
</rule>
</tableRule>
<!--指定分片类。指定规则与2取模-->
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">2</property>
</function>
<function name="murmur"
class="io.mycat.route.function.PartitionByMurmurHash">
<property name="seed">0</property><!-- 默认是0 -->
<property name="count">2</property><!-- 要分片的数据库节点数量,必须指定,否则没法分片 -->
<property name="virtualBucketTimes">160</property><!-- 一个实际的数据库节点被映射为这么多虚拟节点,默认是160倍,也就是虚拟节点数是物理节点数的160倍 -->
<!-- <property name="weightMapFile">weightMapFile</property> 节点的权重,没有指定权重的节点默认是1。以properties文件的格式填写,以从0开始到count-1的整数值也就是节点索引为key,以节点权重值为值。所有权重值必须是正整数,否则以1代替 -->
<!-- <property name="bucketMapPath">/etc/mycat/bucketMapPath</property>
用于测试时观察各物理节点与虚拟节点的分布情况,如果指定了这个属性,会把虚拟节点的murmur hash值与物理节点的映射按行输出到这个文件,没有默认值,如果不指定,就不会输出任何东西 -->
</function>
<function name="crc32slot"
class="io.mycat.route.function.PartitionByCRC32PreSlot">
<property name="count">2</property><!-- 要分片的数据库节点数量,必须指定,否则没法分片 -->
</function>
........
</mycat:rule>2.5.3主节点
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<system>
<property name="useSqlStat">0</property> <!-- 1为开启实时统计、0为关闭 -->
<property name="useGlobleTableCheck">0</property> <!-- 1为开启全加班一致性检测、0为关闭 -->
<property name="sequnceHandlerType">2</property>
<!-- <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
<!-- <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
<!-- <property name="processorBufferChunk">40960</property> -->
<!-- <property name="processors">1</property>
<property name="processorExecutor">32</property>-->
<!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
<property name="processorBufferPoolType">0</property>
<!--默认是65535 64K 用于sql解析时最大文本长度 -->
<!--<property name="maxStringLiteralLength">65535</property>-->
<!--<property name="sequnceHandlerType">0</property>-->
<!--<property name="backSocketNoDelay">1</property>-->
<!--<property name="frontSocketNoDelay">1</property>-->
<!--<property name="processorExecutor">16</property>-->
<!--
<property name="serverPort">8066</property> <property name="managerPort">9066</property>
<property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>
<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
<!--分布式事务开关,0为不过滤分布式事务,1为过滤分布式事务(如果分布式事务内只涉及全局表,则不过滤),
2为不过滤分布式事务,但是记录分布式事务日志-->
<property name="handleDistributedTransactions">0</property>
<!--off heap for merge/order/group/limit 1开启 0关闭-->
<property name="useOffHeapForMerge">1</property>
<!--单位为m-->
<property name="memoryPageSize">1m</property>
<!--单位为k-->
<property name="spillsFileBufferSize">1k</property>
<property name="useStreamOutput">0</property>
<!--单位为m-->
<property name="systemReserveMemorySize">384m</property>
<!--是否采用zookeeper协调切换 -->
<property name="useZKSwitch">true</property>
</system>
<!-- 全局SQL防火墙设置 -->
<!--
<firewall>
<whitehost>
<host host="127.0.0.1" user="mycat"/>
<host host="127.0.0.2" user="mycat"/>
</whitehost>
<blacklist check="false">
</blacklist>
</firewall>
-->
<!--把mycat看成一个超级数据库,在这里配置数据库的用户密码及可以访问的数据库-->
<user name="root">
<property name="password">wuzhenzhao</property>
<property name="schemas">db_store,db_user</property>
<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false">
<schema name="db_user" dml="0110" >
<table name="users" dml="1111"></table> IUSD
<table name="useraddres" dml="1110"></table>
</schema>
</privileges>-->
</user>
</mycat:server>
-->
</user>
</mycat:server>
重启MyCat,就完成了主从复制。此时从主数据库插入数据,就会同步到从数据库。
三、实现分库分表策略逻辑
前面MyCat配置上面已经讲到
配置主从数据库
<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- 设置表的存储方式.schema name="TESTDB" 与 server.xml中的 db_user设置一致 -->
<schema name="db_user" checkSQLschema="false" sqlMaxLimit="100">
<table name="users" primaryKey="id" dataNode="node_db01" />
<table name="item" primaryKey="id" dataNode="node_db02,node_db03" rule="role1" />
</schema>
<!-- 设置dataNode 对应的数据库,及 mycat 连接的地址dataHost -->
<dataNode name="node_db01" dataHost="dataHost01" database="db01" />
<dataNode name="node_db02" dataHost="dataHost01" database="db02" />
<dataNode name="node_db03" dataHost="dataHost01" database="db03" />
<!-- mycat 逻辑主机dataHost对应的物理主机.其中也设置对应的mysql登陆信息 -->
<dataHost name="dataHost01" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<writeHost host="server1" url="192.168.1.1:3306" user="root" password="javagongfu"/>
</dataHost>
3.1创建相关表图
两张表users和item,三个数据库db01,db02,db03(三个库在一个数据库实例上),users只在db01中存储。
create database db01;
CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL default '',
indate DATETIME NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (id)
)AUTO_INCREMENT= 1 ENGINE=InnoDB DEFAULT CHARSET=utf8
item表被分割到db02和db03中存储
create database db02;
CREATE TABLE item (
id INT NOT NULL AUTO_INCREMENT,
value INT NOT NULL default 0,
indate DATETIME NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (id)
)AUTO_INCREMENT= 1 ENGINE=InnoDB DEFAULT CHARSET=utf8;
create database db03;
CREATE TABLE item (
id INT NOT NULL AUTO_INCREMENT,
value INT NOT NULL default 0,
indate DATETIME NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (id)
)AUTO_INCREMENT= 1 ENGINE=InnoDB DEFAULT CHARSET=utf8;
重启MyCat后:
mysql -uroot -p123456 -h192.168.1.1 -P8066
查看数据库,数据表进行组合:
然后给item表,分别插入两条数据id为1、2的数据,发现插入的数据会分别存入db02以及db03的item表中,成功分表。
至于分库分表与Spring Boot整合,后面还会进行分享。
,