撰写写在前面
因为最近要用到MySQL的分库分表，所以自己尝试在测试机器里搭建一套分库分表的MySQL数据库，我这里是通过Docker运行了3台MySQL 5.7 ，然后官方下载MyCat的程序包进行部署的，分库分表做的是水平拆分
构建MySQL
首先拉取MySQL镜像
docker pull mysql:5.7然后创建数据库数据的持久化目录
mkdir -p /data/mysqlone/log /data/mysqlone/data /data/mysqlone/conf
mkdir -p /data/mysqltwo/log /data/mysqltwo/data /data/mysqltwo/conf
mkdir -p /data/mysqlthree/log /data/mysqlthree/data /data/mysqlthree/conf运行第一个容器然后查看是否能正常运行
docker run -p 3307:3306 --name mysqlone -v /data/mysqlone/log:/var/log/mysql -v /data/mysqlone/data:/var/lib/mysql -v /data/mysqlone/conf:/etc/mysql -e MYSQL_ROOT_PASSWORD=123456 -e TZ=Asia/Shanghai -d mysql:5.7
#端口我改为了3307，因为本机也有mysql，为了防止端口冲突导致容器运行失败就改了一下，也是为了区分
#root密码为123456，可以根据需求更改，这里为了测试方便就弄成了简单的
#通过命令查看容器状态，如果运行正常就可以进行自定义配置文件了
docker ps -a
#自定义配置文件，因为已经把配置文件目录挂载出来了，所以直接在宿主机创建即可
vim /data/mysqlone/conf/my.conf
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
#basedir = /var/lib/mysql
datadir = /var/lib/mysql
pid-file = /var/run/mysqld/mysqld.pid
user = mysql
bind-address = 0.0.0.0
server-id = 1 #另外两台数据库可以考虑改成不一样的，方便以后做主从复制和读写分离
default-time-zone = '+8:00'
character-set-server = utf8mb4
skip-name-resolve
#skip-networking
back_log = 300
max_connections = 1000
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 128
max_allowed_packet = 500M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M
key_buffer_size = 4M
thread_cache_size = 8
query_cache_type = 1
query_cache_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4
log_bin = mysql-bin
binlog_format = mixed
expire_logs_days = 7
log_error = /var/log/mysql/mysqld.log
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
performance_schema = 0
explicit_defaults_for_timestamp
skip-external-locking
default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 64M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
interactive_timeout = 28800
wait_timeout = 28800
sql_mode =
[mysqldump]
quick然后重启容器查看是否正常
#重启容器
docker restart mysqlone
#查看状态
docker inspect mysqlone
#如果运行正常，表示配置文件可用，复制到其他两个目录
cp /data/mysqlone/conf/my.conf /data/mysqltwo/conf/
cp /data/mysqlone/conf/my.conf /data/mysqlthree/conf/启动另外两台数据库
docker run -p 3308:3306 --name mysqltwo -v /data/mysqltwo/log:/var/log/mysql -v /data/mysqltwo/data:/var/lib/mysql -v /data/mysqltwo/conf:/etc/mysql -e MYSQL_ROOT_PASSWORD=123456 -e TZ=Asia/Shanghai -d mysql:5.7docker run -p 3309:3306 --name mysqlthree -v /data/mysqlthree/log:/var/log/mysql -v /data/mysqlthree/data:/var/lib/mysql -v /data/mysqlthree/conf:/etc/mysql -e MYSQL_ROOT_PASSWORD=123456 -e TZ=Asia/Shanghai -d mysql:5.7运行成功后MySQL就部署完成了
部署MyCat
下载并部署MyCat
#下载程序包，为这里下载到是1.6.6，最高版本好像到了2.0，根据需求下载即可
wget http://dl.mycat.org.cn/1.6.6/Mycat-server-1.6.6.1-test-20180709095126-linux.tar.gz
#解压
tar zxvf Mycat-server-1.6.6.1-test-20180709095126-linux.tar.gz -C /usr/local/编辑配置文件
#这里只需求修改server和schema两个文件就可以，其他文件用的到到时候不多，首先修改server
cd /usr/local/mycat/conf
vim 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>
<property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户-->
<property name="useHandshakeV10">1</property>
<property name="useSqlStat">0</property> <!-- 1为开启实时统计、0为关闭 -->
<property name="useGlobleTableCheck">0</property> <!-- 1为开启全加班一致性检测、0为关闭 -->
<property name="sequnceHandlerType">0</property>
<property name="subqueryRelationshipCheck">false</property> <!-- 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false -->
<!-- <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 | type 2 NettyBufferPool -->
<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">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>
<!-- XA Recovery Log日志路径 -->
<!--<property name="XARecoveryLogBaseDir">./</property>-->
<!-- XA Recovery Log日志名称 -->
<!--<property name="XARecoveryLogBaseName">tmlog</property>-->
<!--如果为 true的话 严格遵守隔离级别,不会在仅仅只有select语句的时候在事务中切换连接-->
<property name="strictTxIsolation">false</property>
<property name="useZKSwitch">true</property>
</system>
<!-- 全局SQL防火墙设置 -->
<!--白名单可以使用通配符%或着*-->
<!--例如<host host="127.0.0.*" user="root"/>-->
<!--例如<host host="127.0.*" user="root"/>-->
<!--例如<host host="127.*" user="root"/>-->
<!--例如<host host="1*7.*" user="root"/>-->
<!--这些配置情况下对于127.0.0.1都能以root账户登录-->
<!--
<firewall>
<whitehost>
<host host="1*7.0.0.*" user="root"/>
</whitehost>
<blacklist check="false">
</blacklist>
</firewall>
-->
<!-- ** 修改这里就可以了 ** -->
<user name="sheep" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">sheepdb</property>
<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">sheepdb</property>
<property name="readOnly">true</property>
</user>
</mycat:server>
#user 中name为连接MyCat的用户名，password为密码，schemas后面的数据库如果修改，一定要和schemas文件中的数据库对应，并且也要修改下面user用户的数据库
接着修改schemas文件
vim schemas.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- ** 这里的name名字要和server里数据库名一致 ** -->
<schema name="sheepdb" checkSQLschema="false" sqlMaxLimit="100">
<!-- auto sharding by id (long) -->
<!-- ** 这里表的名字要在库里存在 ** -->
<table name="user" primaryKey="id" dataNode="dn1,dn2,dn3" rule="mod-long" autoIncrement="true" />
</schema>
<!-- ** 后面database的名字的库要在数据库里存在 ** -->
<dataNode name="dn1" dataHost="localhost1" database="sheepdb" />
<dataNode name="dn2" dataHost="localhost2" database="sheepdb" />
<dataNode name="dn3" dataHost="localhost3" database="sheepdb" />
<!-- ** balance要改为0不做负载均衡 ** -->
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100" maxRetryCount="4">
<heartbeat>select user()</heartbeat>
<!-- ** 填写数据库地址、用户名和密码，注意host名字不要都一样 ** -->
<writeHost host="hostM1" url="192.168.1.212:3307" user="root" password="123456">
<!--<readHost host="hostS1" url="192.168.1.212:3306" user="sheep" password="Ming456!" />-->
</writeHost>
</dataHost>
<dataHost name="localhost2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100" maxRetryCount="4">
<heartbeat>select user()</heartbeat>
<!-- ** 注意host名字 ** -->
<writeHost host="hostM2" url="192.168.1.212:3308" user="root" password="123456">
<!--<readHost host="hostS1" url="192.168.1.212:3306" user="sheep" password="Ming456!" />-->
</writeHost>
</dataHost>
<dataHost name="localhost3" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100" maxRetryCount="4">
<heartbeat>select user()</heartbeat>
<!-- ** 注意host名字 ** -->
<writeHost host="hostM3" url="192.168.1.212:3309" user="root" password="123456">
<!--<readHost host="hostS1" url="192.168.1.212:3306" user="sheep" password="Ming456!" />-->
</writeHost>
</dataHost>
</mycat:schema>修改完成就可以启动MyCat了
#启动
/usr/local/mycat/bin/mycat start
#查看日志
cd /usr/local/mycat/logs
tail -f wrapper.log
日志没有报错的话服务就已经启动了，如果启动有报错会在这个日志中体现，再去查看运行日志
tail -f mycat.log如果没有任何报错信息，MyCat就已经启动完成，可以通过命令或者工具连接了，用户名密码为server文件中的信息，默认端口8066
配置读写分离
可以直接使用前面的库，先开启MySQL的主从复制功能
#把mysqlone作为主库，修改配置文件
vim /data/mysqlone/conf/my.cnf
#在[mysqld]模块内增加以下配置
#开启二进制日志功能
log-bin=mysql-bin
#复制过滤：不需要备份的数据库，不输出（mysql库一般不同步）
binlog-ignore-db=mysql
#日志保留时间（天）
expire-logs-days=10
#跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断
#如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062然后重启mysqlone容器
docker restart mysqlone接着修改从库
#同样在[mysqld]模块内增加下面的配置
binlog-ignore-db=mysql
log-bin=mysql-slave1-bin
slave_skip_errors=1062
log_slave_updates=1
# relay_log配置中继日志
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index配置完成后重启mysqltwo
docker restart mysqltwo到这里配置文件就已经修改完成了，然后登陆mysql进行配置
#如果本机安装了mysql客户端程序可以在本机登陆，如果没有就要进到容器内登陆
#本机登陆主库，一定要加-h 不然是以localhost登陆，会提示无法登陆
mysql -h127.0.0.1 -uroot -p123456 -P3307
#增加一个用于同步的账户（可不创建，直接使用root）
GRANT REPLICATION SLAVE ON *.* to 'slave'@'%' identified by '123456';
FLUSH PRIVILEGES;
#查看binlog日志是否已经开启
show variables like '%log_bin%';
#查看当前binlog日志的信息
show master status;
我们要的是binlong文件名和偏移，让从库一会通过这个两个信息开启主从复制功能
#登陆从库mysqltwo
mysql -h 127.0.0.1 -uroot -p123456 -P3308
#绑定主库（如果数据库有人使用，偏移可能变化很快，建议使用量少或者无人使用时配置）
change master to master_host='192.168.1.212', master_user='root', master_password='123456', master_port=3307, master_log_file='mysql-bin.000001',master_log_pos=35861;
#解释
#master_host为主库的IP地址
#master_user为连接主库用的用户名,可以是之前创建的，也可以用root
#master_password对应用户的密码
#master_port主库的端口
#master_log_file主库binlog日志文件名，就是上面截图中的file
#master_log_pos为偏移数，上面截图中Position
然后开启主从复制功能（从库操作）
start slave;
#查看主从复制状态
show slave status\G;
两个位置均为Yes表示已经开启成功，如果没有成功可以重置以后再开启
#先停掉主从复制
stop slave;
#重置
reset slave;
#重新配置绑定，查看新的偏移数后进行配置，再次启动查看信息，直到成功到这里主从复制就已经配置完成，接着去配MyCat开启读写分离（读写分离和水平拆分可同时使用）
先修改server文件，这里为了演示所以修改了之前的配置，读写分离和分库分表可在一个配置内
cd /usr/local/mycat/conf/
#修改配置,只需要修改user部分
vim server.xml
<user name="mycat" defaultAccount="true">
<property name="password">123456</property>
<!-- 我这里添加了2个库做读写分离，多个库可以用逗号隔开 -->
<property name="schemas">account,money_record</property>
<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
<user name="user">
<property name="password">user</property>
<!-- 这里要写成一样的，不然无法启动 -->
<property name="schemas">account,money_record</property>
<property name="readOnly">true</property>
</user>然后修改schema.xml
#我这里做了精简，如果想让读写分离和分库分表同时使用，添加多个schema模块即可
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="account" dataNode="dn1" checkSQLschema="false" sqlMaxLimit="100"></schema>
<schema name="money_record" dataNode="dn2" checkSQLschema="false" sqlMaxLimit="100"></schema>
<dataNode name="dn1" dataHost="localhost1" database="account" />
<dataNode name="dn2" dataHost="localhost1" database="money_record" />
<!-- balance配置为1 性能相对更好一些 -->
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100" maxRetryCount="4">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.1.212:3307" user="root" password="123456">
<readHost host="hostS1" url="192.168.1.212:3308" user="root" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>文档顺序一定是schema —> dataNode —> dataHost 顺序错误会无法启动，读写分离因为没有配置table，所以一定要在schema中配置dataNode，而且必须添加写节点和读节点，写节点内添加读节点，写为主库读为从库，不然数据会不一致
接着开启MyCat
/usr/local/mycat/bin/mycat start查看启动日志状态
tail -f /usr/local/mycat/logs/wrapper.log
没有错误就已经启动成功了，到此读写分离配置完成
示例
#读写分离和分库分表同时使用示例
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<! -- 分库分表 -->
<schema name="sheepdb" dataNode="dn1" checkSQLschema="false" sqlMaxLimit="100"></schema>
<table name="user" primaryKey="id" dataNode="dn1,dn2" rule="mod-long" autoIncrement="true" />
</schema>
<!-- 读写分离 -->
<schema name="Mingdb" dataNode="dn3" checkSQLschema="false" sqlMaxLimit="100"></schema>
<dataNode name="dn1" dataHost="localhost1" database="sheepdb" />
<dataNode name="dn2" dataHost="localhost2" database="sheepdb" />
<dataNode name="dn3" dataHost="localhost3" database="Mingdb" />
<!-- 分库分表 -->
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100" maxRetryCount="4">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.1.212:3307" user="root" password="123456">
</writeHost>
</dataHost>
<dataHost name="localhost2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100" maxRetryCount="4">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM2" url="192.168.1.212:3308" user="root" password="123456">
</writeHost>
</dataHost>
<!-- 读写分离 -->
<dataHost name="localhost3" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100" maxRetryCount="4">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM3" url="192.168.1.212:3307" user="root" password="123456">
<readHost host="hostS3" url="192.168.1.212:3308" user="root" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>