一、前期工作
参考文章《Centos7下采用haproxy+keepalived搭建mysql高可用负载均衡》
1、关闭防火墙和SELINUX(4台虚拟机)
2、设置yum源(4台虚拟机)
3、安装mysql(7.80、7.81、7.82)
二、Mysql一主两从配置
1、修改MySQL配置文件
在7.80、7.81、7.82的MySQL配置文件/etc/my.cnf分别增加下面这一段。
server-id=80 #任意自然数n,保证三台MySQL主机id不一样。
log-bin=mysql-bin #开启二进制日志
replicate-do-db=aa #要同步的数据库,默认所有库
保存后,重启mysql服务
sudo systemctl restart mysqld
或sudo service mysqld restart
2、查看主mysql(7.80)二进制日志
登录mysql -uroot -p
密码Lr12345!
mysql> show master status;
3、在从mysql(7.81、7.82)告知二进制文件名与位置
mysql> change master to
-> master_host='192.168.7.80',
-> master_user='root',
-> master_password='Lr12345!',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=154;
查看结果
mysql> start slave;
mysql> show slave status\G;
4、验证主从同步
192.168.7.80主MySQL操作:
mysql>create database aa;
mysql>use aa;
mysql>create table tab1(id int auto_increment,name varchar(10),primary key(id));
mysql>show databases;
mysql>show tables;
在7.80数据库aa上写入数据,看是否同步到7.81和7.82中。
三、7.83上安装Mycat
#安装Java环境(mycat基于java)
yum install java-1.8.0-openjdk.x86_64
#将下载的mycat安装包拷到/usr/local/ 并解压
cd /usr/local
tar -zxvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
# 创建专门运行mycat账号
adduser mycat
# 切换到mycat文件夹路径下
cd /usr/local
# 将文件权限赋给mycat账号
chown mycat:mycat -R mycat
#配置环境变量,最后面添加
vim /etc/profile
export JAVA_HOME=/usr
export MYCAT_HOME=/usr/local/mycat
# 刷新环境变量文件
source /etc/profile
# 切换mycat用户
su mycat
#切换目录
cd /usr/local/mycat/bin/
#启动mycat
./mycat start
#查看mycat运行状态
./mycat status
四、修改配置参数
1、修改配置文件schema.xml
vi /usr/local/mycat/conf/schema.xml
内容如下:
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="aa" checkSQLschema="false" sqlMaxLimit="100" dataNode="jgywnode">
</schema>
<dataNode name="jgywnode" dataHost="jgywhost" database="aa" />
<dataHost name="jgywhost" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<writeHost host="master" url="192.168.7.80:3306" user="root" password="Lr12345!">
<readHost host="slave" url="192.168.7.81:3306" user="root" password="Lr12345!"/>
<readHost host="slave2" url="192.168.7.82:3306" user="root" password="Lr12345!"/>
</writeHost>
</dataHost>
</mycat:schema>
2、修改配置文件server.xml
vi /usr/local/mycat/conf/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="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="sequnceHandlerType">1</property>
<!--<property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
INSERT INTO `travelrecord` (`id`,user_id) VALUES ('next value for MYCATSEQ_GLOBAL',"xxx");
-->
<!--必须带有MYCATSEQ_或者 mycatseq_进入序列匹配流程 注意MYCATSEQ_有空格的情况-->
<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="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="dataNodeIdleCheckPeriod">300000</property> 5 * 60 * 1000L; //连接空闲检查
<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
<!--分布式事务开关,0为不过滤分布式事务,1为过滤分布式事务(如果分布式事务内只涉及全局表,则不过滤),2为不过滤分布式事务,但是记录分布式事务日志-->
<property name="serverPort">3310</property>
<property name="managerPort">9066</property>
<property name="handleDistributedTransactions">0</property>
<!--off heap for merge/order/group/limit 1开启 0关闭-->
<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>
<!-- 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>
<!--如果为0的话,涉及多个DataNode的catlet任务不会跨线程执行-->
<property name="parallExecute">0</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="root" defaultAccount="true">
<property name="password">Lr12345!</property>
<property name="schemas">aa</property>
<property name="defaultSchema">aa</property>
<!--No MyCAT Database selected 错误前会尝试使用该schema作为schema,不设置则为null,报错 -->
<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false">
<schema name="aa" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
<user name="readonly">
<property name="password">Lr12345!</property>
<property name="schemas">aa</property>
<property name="readOnly">true</property>
<property name="defaultSchema">aa</property>
</user>
</mycat:server>
3、重启mycat
#切换目录
cd /usr/local/mycat/bin/
./mycat restart
#查看mycat状态
./mycat status
五、验证读写分离和负载均衡
用客户端连192.168.7.83:3310
六、Mycat命令行监控
1、7.83安装mysql
2、连接管理端口
mysql -h192.168.7.83 -uroot -pLr12345! -P9066
3、常见管理命令
1) 查看mycat版本
show @@version;
2) 查看当前的库
show @@database;
3) 查看MyCAT数据节点的列表,dataNode节点:
mysql> show @@datanode;
其中,“NAME”表示 dataNode 的名称;“dataHost”表示对应 dataHost 属性的值,即数据主机; “ACTIVE”表示活跃连接数;“IDLE”表示闲置连接数;“SIZE”对应总连接数量。
4) 查看心跳报告:
mysql> show @@heartbeat; 该命令用于报告心跳状态
5) 查看Mycat的前端连接状态,即应用与mycat的连接:
mysql> show @@connection\G
从上面获取到的连接 ID 属性,可以手动杀掉某个连接。
kill @@connection id,id,id
6) 显示后端连接状态:
mysql> show @@backend\G
7) 显示数据源:
mysql> show @@datasource;
七、MyCAT-WEB性能监控工具安装
这里安装在7.83(与mycat同一台)
两个安装包:
apache-zookeeper-3.7.1-bin.tar.gz
Mycat-web-1.0-SNAPSHOT-20170102153329-linux.tar.gz
1、安装zookeeper
#1下载安装包https://www.apache.org/dyn/closer.lua/zookeeper/zookeeper-3.7.1/apache-zookeeper-3.7.1-bin.tar.gz
#2 安装包拷贝到Linux系统/root目录下,并解压
cd /root
tar -zxvf apache-zookeeper-3.7.1-bin.tar.gz
#3进入ZooKeeper解压后的配置目录(conf),复制配置文件并改名
cd /root/apache-zookeeper-3.7.1-bin/conf
cp zoo_sample.cfg zoo.cfg
#4 进入ZooKeeper的命令目录(bin),运行启动命令
cd /root/apache-zookeeper-3.7.1-bin/bin
./zkServer.sh start
#5 ZooKeeper服务端口为2181,查看服务已经启动
netstat -ant | grep 2181
2、安装Mycat-web
#1下载安装包
http://dl.mycat.org.cn/mycat-web-1.0/
#2 安装包拷贝到Linux系统/usr/local目录下,并解压
cd /usr/local
tar -zxvf Mycat-web-1.0-SNAPSHOT-20170102153329-linux.tar.gz
#3进入mycat-web的目录下运行启动命令
cd /usr/local/mycat-web/
./start.sh &
#4 Mycat-web服务端口为8082,查看服务已经启动
netstat -ant | grep 8082
#5 通过地址访问服务
http://192.168.7.83:8082/mycat/
3、设置mycat-eye
Mycat服务管理
Mycat-VM管理
Mysql管理
八、Mycat多库实现
1、Schema.xml配置
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="testbb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1" > </schema>
<schema name="666" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2" > </schema>
<dataNode name="dn1" dataHost="localhost1" database="testbb"/>
<dataNode name="dn2" dataHost="localhost2" database="666"/>
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<writeHost host="master" url="192.168.7.80:3306" user="root" password="Lr12345!">
<readHost host="slave" url="192.168.7.81:3306" user="root" password="Lr12345!"/>
<readHost host="slave2" url="192.168.7.82:3306" user="root" password="Lr12345!"/>
</writeHost>
</dataHost>
<dataHost name="localhost2" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<writeHost host="master" url="192.168.7.80:3306" user="root" password="Lr12345!">
<readHost host="slave" url="192.168.7.81:3306" user="root" password="Lr12345!"/>
<readHost host="slave2" url="192.168.7.82:3306" user="root" password="Lr12345!"/>
</writeHost>
</dataHost>
</mycat:schema>
2、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="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="sequnceHandlerType">1</property>
<!--<property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
INSERT INTO `travelrecord` (`id`,user_id) VALUES ('next value for MYCATSEQ_GLOBAL',"xxx");
-->
<!--必须带有MYCATSEQ_或者 mycatseq_进入序列匹配流程 注意MYCATSEQ_有空格的情况-->
<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="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="dataNodeIdleCheckPeriod">300000</property> 5 * 60 * 1000L; //连接空闲检查
<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
<!--分布式事务开关,0为不过滤分布式事务,1为过滤分布式事务(如果分布式事务内只涉及全局表,则不过滤),2为不过滤分布式事务,但是记录分布式事务日志-->
<property name="serverPort">3310</property>
<property name="managerPort">9066</property>
<property name="handleDistributedTransactions">0</property>
<!--off heap for merge/order/group/limit 1开启 0关闭-->
<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>
<!-- 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>
<!--如果为0的话,涉及多个DataNode的catlet任务不会跨线程执行-->
<property name="parallExecute">0</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="root" defaultAccount="true">
<property name="password">Lr12345!</property>
<property name="schemas">testbb,666</property>
<property name="defaultSchema">666</property>
<!--No MyCAT Database selected 错误前会尝试使用该schema作为schema,不设置则为null,报错 -->
<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false">
<schema name="aa" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
<user name="readonly">
<property name="password">Lr12345!</property>
<property name="schemas">testbb,666</property>
<property name="readOnly">true</property>
</user>
</mycat:server>