文章目录
数据库准备
使用docker安装两个数据库
创建数据库配置文件
1、在root目录下创建db1和db2文件夹,然后再在这两个目录下分别创建my.cnf文件
mkdir db1
mkdir db1
touch my.cnf
2、Master(db1目录下的my.cnf)配置文件如下:
[mysqld]
## 同一局域网内注意要唯一
server-id=100
## 开启二进制日志功能,可以随便取(关键)
log-bin=mysql-bin
3、Slave(db2目录下的my.cnf)配置文件如下:
[mysqld]
## 设置server_id,注意要唯一
server-id=101
## 开启二进制日志功能,以备Slave作为其它Slave的Master时使用
log-bin=mysql-slave-bin
## relay_log配置中继日志
relay_log=edu-mysql-relay-bin
拉取镜像并创建数据库
docker pull mysql:5.7
查看命令:docker images
启动
docker run --name db1 -p 3316:3306 -v /root/db1/my.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
docker run --name db2 -p 3326:3306 -v /root/db2/my.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
查看命令:docker ps -a
配置主从复制
1、通过docker exec -it db1 /bin/bash命令进入到Master容器内部:
docker exec -it db1 /bin/bash
2、并且登录上数据库:
mysql -uroot -p123456
3、执行mysql客户端执行如下脚本:
CREATE USER ‘slave’@’%’ IDENTIFIED BY ‘123456’;
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON . TO ‘slave’@’%’;
4、在Master进入mysql,执行show master status;
show master status;
File和Position字段的值后面将会用到,在后面的操作完成之前,需要保证Master库不能做任何操作,否则将会引起状态变化,File和Position字段的值变化。
5、退出master数据库,回到宿主机执行docker命令查看两个容器的ip
docker inspect --format ‘{{ .NetworkSettings.IPAddress }}’ db1
docker inspect --format ‘{{ .NetworkSettings.IPAddress }}’ db2
6、在Slave 中进入 mysql,执行:
注意:master_host、master_log_file、master_log_pos都需要是上面查询出来的,不一定和我的一样。
change master to master_host=‘172.17.0.2’, master_user=‘slave’, master_password=‘123456’, master_port=3306, master_log_file=‘mysql-bin.000003’, master_log_pos= 617, master_connect_retry=30;
命令说明:
master_host :Master的地址,指的是容器的独立ip,可以通过如下命令查询容器的ip
docker inspect --format=’{{.NetworkSettings.IPAddress}}’ 容器名称|容器id
Copy to clipboardErrorCopied
master_port:Master的端口号,指的是容器的端口号
master_user:用于数据同步的用户
master_password:用于同步的用户的密码
master_log_file:指定 Slave 从哪个日志文件开始复制数据,即上文中提到的 File 字段的值
master_log_pos:从哪个 Position 开始读,即上文中提到的 Position 字段的值
master_connect_retry:如果连接失败,重试的时间间隔,单位是秒,默认是60秒
7、在Slave 中的mysql终端执行show slave status \G;用于查看主从同步状态。
8、正常情况下,SlaveIORunning 和 SlaveSQLRunning 都是No,因为我们还没有开启主从复制过程。使用start slave;开启主从复制过程,然后再次查询主从同步状态show slave status \G;。
测试主从复制
测试主从复制方式就十分多了,最简单的是在Master创建一个数据库,然后检查Slave是否存在此数据库。
1、再打开一个虚拟机,进入到Master(db1):
在Master(db1)中创建一个数据库:
create database testdb;
在Slaver(db2)中查看
在宿主机上安装mysql客户端,测试连通性
yum install -y mysql
mysql -uroot -p123456 -h 172.17.0.2 -P 3306
mysql -uroot -p123456 -h 172.17.0.3 -P 3306
Mycat安装启动
Mycat使用Java编写,所以需要提前安装jdk
#切换目录
cd /opt
#下载
wget https://repo.huaweicloud.com/java/jdk/8u192-b12/jdk-8u192-linux-x64.tar.gz
#解压
tar -zxvf jdk-8u201-linux-x64.tar.gz
创建符号连接统一管理(可选),以后有新版本,只要更改这个连接就可以了:
ln -s /opt/jdk1.8.0_192 /usr/jdk
3、修改环境变量:
vi /etc/profile
在末尾添加如下内容:
JAVA_HOME=/usr/jdk
CLASSPATH=
J
A
V
A
H
O
M
E
/
l
i
b
/
P
A
T
H
=
JAVA_HOME/lib/ PATH=
JAVAHOME/lib/PATH=PATH:$JAVA_HOME/bin
export PATH JAVA_HOME CLASSPATH
4、使配置生效:
source /etc/profile
5、检查是否安装成功:
java -version
下载解压Mycat
切换到/opt目录,使用wget 下载压缩包
#切换目录
cd /opt/
#下载
wget http://dl.mycat.org.cn/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
#查看
ll |grep My
#解压
tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
#查看
ll
文件复制
1、将解压后的目录copy到 /usr/local目录中(在opt目录下操作)
cp -r mycat/ /usr/local/
ls /usr/local/
2、查看mycat目录及mycat的配置文件目录
cd /usr/local/mycat/
ls
cd conf/
ls
其中主要的几个配置文件如下:
- server.xml:是Mycat服务器参数调整和用户授权的配置文件。
- schema.xml:是逻辑库定义和表以及分片定义的配置文件。
- rule.xml: 是分片规则的配置文件,分片规则的具体一些参数信息单独存放为文件,也在这个目录下,配置文件修改需要重启MyCAT。
- log4j.xml: 日志存放在logs/log中,每天一个文件,日志的配置是在conf/log4j.xml中,根据自己的需要可以调整输出级别为debug
debug级别下,会输出更多的信息,方便排查问题。
autopartition-long.txt,partition-hash-int.txt,sequence_conf.properties, sequence_db_conf.properties 分片相关的id分片规则配置文件
lib MyCAT自身的jar包或依赖的jar包的存放目录。
logs MyCAT日志的存放目录。日志存放在logs/log中,每天一个文件
配置
1、需要安装jdk,修改jdk环境,即编辑/usr/local/mycat/conf/wrapper.conf
vi /usr/local/mycat/conf/wrapper.conf
java命令绝对路径
wrapper.java.command=/opt/jdk1.8.0_291/bin/java
修改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="sqlExecuteTimeout">300</property> <!-- SQL 执行超时 单位:秒-->
<property name="sequnceHandlerType">2</property> <!-- 0 本地文件方式(也即ID生成策略交给mycat中sequence_db_conf.properties的文件来处理) 1 数据库方式 2 时间戳方式-->
<!--<property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>-->
<!--必须带有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="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> mycat数据库连接端口配置
<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="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>
</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>
-->
<!-- mycat数据库的连接用户名和密码配置
并设置root用户可以操作的数据有哪些(一般是对应schema.xml文件的逻辑数据库 -->
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
<!-- 多个schemas配置写法-->
<!-- <property name="schemas">sss_user,sss_orders,sss_products</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">TESTDB</property>
<property name="readOnly">true</property>
</user>
</mycat:server>
示例
vi 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="sqlExecuteTimeout">300</property> <!-- SQL 执行超时 单位:秒-->
<property name="sequnceHandlerType">1</property> <!-- 0 本地文件方式 1 数据库方式 2 时间戳方式-->
<!--<property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>-->
<!--必须带有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="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="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>
</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>
-->
<!--设置sss用户可以操作的数据有哪些(一般是对应schema.xml文件的逻辑数据库)-->
<user name="root" defaultAccount="true" >
<property name="password">root</property>
<property name="schemas">SSSDB</property>
<!-- 允许多个虚拟数据库配置 -->
<!--<property name="schemas">SSSDB,SSSDB2,SSSDB3</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用户只能读取哪些数据库,不能进行写入或修改等其他操作-->
<user name="user" >
<property name="password">user</property>
<property name="schemas">SSSDB</property>
<property name="readOnly">true</property>
</user>
</mycat:server>
修改schema.xml
官方配置
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!--逻辑数据库(也即是mycat虚拟出来的数据库并不是真实存在) -->
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100">
<!-- auto sharding by id (long) -->
<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
<!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
/> -->
</schema>
<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
/> -->
<!--mysql数据库里分库后的各个数据库 -->
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataNode name="dn2" dataHost="localhost1" database="db2" />
<dataNode name="dn3" dataHost="localhost1" database="db3" />
<!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
<dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
<dataNode name="jdbc_dn2" dataHost="jdbchost" database="db2" />
<dataNode name="jdbc_dn3" dataHost="jdbchost" database="db3" /> -->
<!--name 唯一标识 dataHost标签,供上层标签使用 -->
<!--maxcon 指定每个读写实例连接池的最大连接数 -->
<!--minCon 指定每个读写实例连接池的最小连接数 -->
<!--balance 负载均衡类型,目前有4种:
0,不开启读写分离机制,所有操作都发送到当前可用的writeHost上
1,全部的readHost与 stand by writeHost(非主非从)参与select语句的负载均衡
2,所有读操作都随机的在writeHost,readHost上分发
3,所有读请求随机的分发到writeHost对应的readHost执行, -->
<!--dbType 指定后端连接的数据库类型 -->
<!--dbDriver 指定连接后端数据库使用的Driver,目前可选的值有native和jdbc。 -->
<!--switchType -1 表示不自动切换
2 基于MYSQL主从同步的状态决定是否切换心跳语句为show slave status;
3 基于MYSQL galary cluster 的切换机制(适合集群)心跳语句为show status like 'wsrep%'
-->
<!--slaveThreshold 指定从节点的最大个数 -->
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="localhost:3306" user="root"
password="123456">
<!-- 配置从服务器数据库的链接地址和用户用来读取数据并复制 -->
<!-- <readHost host="slaveHost" url="192.168.85.130:3306" user="root" password="root"/> -->
</writeHost>
<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>
<!--
<dataHost name="sequoiadb1" maxCon="1000" minCon="1" balance="0" dbType="sequoiadb" dbDriver="jdbc">
<heartbeat> </heartbeat>
<writeHost host="hostM1" url="sequoiadb://1426587161.dbaas.sequoialab.net:11920/SAMPLE" user="jifeng" password="jifeng"></writeHost>
</dataHost>
<dataHost name="oracle1" maxCon="1000" minCon="1" balance="0" writeType="0" dbType="oracle" dbDriver="jdbc"> <heartbeat>select 1 from dual</heartbeat>
<connectionInitSql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'</connectionInitSql>
<writeHost host="hostM1" url="jdbc:oracle:thin:@127.0.0.1:1521:nange" user="base" password="123456" > </writeHost> </dataHost>
<dataHost name="jdbchost" maxCon="1000" minCon="1" balance="0" writeType="0" dbType="mongodb" dbDriver="jdbc">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM" url="mongodb://192.168.0.99/test" user="admin" password="123456" ></writeHost> </dataHost>
<dataHost name="sparksql" maxCon="1000" minCon="1" balance="0" dbType="spark" dbDriver="jdbc">
<heartbeat> </heartbeat>
<writeHost host="hostM1" url="jdbc:hive2://feng01:10000" user="jifeng" password="jifeng"></writeHost> </dataHost> -->
<!-- <dataHost name="jdbchost" maxCon="1000" minCon="10" balance="0" dbType="mysql"
dbDriver="jdbc"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1"
url="jdbc:mysql://localhost:3306" user="root" password="123456"> </writeHost>
</dataHost> -->
</mycat:schema>
vi schema.xml
修改dataNode、dataHost
完整的配置文件如下:
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="SSSDB" checkSQLschema="true" sqlMaxLimit="100" dataNode='dn1'>
<!--name 分库的表名称 -->
<!--dataNode 指定数据会分到哪些数据库(对应dataNode节点中name属性)-->
<!--rule 分库的规则名称 -->
<table name="user" dataNode="dn1,dn2" />
<table name="order_sss" dataNode="dn1,dn2" rule="my_mod-long" >
<!--name 要进行关联的表名称 -->
<!--primaryKey 关联表的主键列名称 -->
<!--joinKey= 进行连接条件的列名称 -->
<!--parentKey 主表关联的列名称 -->
<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id"/>
</table>
<!--全局表(也即每个数据库都有的表并且表的数据是一样的) -->
<table name="dict_order_type" dataNode="dn1,dn2" type="global" />
<!--<table name="mycat_sequence" primaryKey="name" dataNode="dn1"/> -->
</schema>
<!--mysql数据库里分库后的各个数据库 -->
<dataNode name="dn1" dataHost="Windows2" database="mycat_user" />
<!--mysql数据库里分库后的各个数据库 -->
<dataNode name="dn2" dataHost="Linux1" database="mycat_user" />
<!--分库后数据库连接信息配置 -->
<dataHost name="Linux1"
maxCon="1000"
minCon="10"
balance="0"
writeType="0"
dbType="mysql"
dbDriver="native"
switchType="1"
slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.85.128:3306" user="root"
password="root">
</writeHost>
</dataHost>
<dataHost name="Windows2"
maxCon="1000"
minCon="10"
balance="0"
writeType="0"
dbType="mysql"
dbDriver="native"
switchType="1"
slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM2" url="192.168.0.103:3306" user="root"
password="root">
</writeHost>
</dataHost>
</mycat:schema>
rule.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:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
<tableRule name="rule1">
<rule>
<columns>id</columns>
<algorithm>func1</algorithm>
</rule>
</tableRule>
<tableRule name="rule2">
<rule>
<columns>user_id</columns>
<algorithm>func1</algorithm>
</rule>
</tableRule>
<tableRule name="sharding-by-intfile">
<rule>
<columns>sharding_id</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
<tableRule name="auto-sharding-long">
<rule>
<columns>id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
<tableRule name="mod-long">
<rule>
<columns>id</columns>
<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>
<tableRule name="sharding-by-month">
<rule>
<columns>create_time</columns>
<algorithm>partbymonth</algorithm>
</rule>
</tableRule>
<tableRule name="latest-month-calldate">
<rule>
<columns>calldate</columns>
<algorithm>latestMonth</algorithm>
</rule>
</tableRule>
<tableRule name="auto-sharding-rang-mod">
<rule>
<columns>id</columns>
<algorithm>rang-mod</algorithm>
</rule>
</tableRule>
<tableRule name="jch">
<rule>
<columns>id</columns>
<algorithm>jump-consistent-hash</algorithm>
</rule>
</tableRule>
<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">
</function>
<function name="hash-int"
class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
</function>
<function name="rang-long"
class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
</function>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">3</property>
</function>
<function name="func1" class="io.mycat.route.function.PartitionByLong">
<property name="partitionCount">8</property>
<property name="partitionLength">128</property>
</function>
<function name="latestMonth"
class="io.mycat.route.function.LatestMonthPartion">
<property name="splitOneDay">24</property>
</function>
<function name="partbymonth"
class="io.mycat.route.function.PartitionByMonth">
<property name="dateFormat">yyyy-MM-dd</property>
<property name="sBeginDate">2015-01-01</property>
</function>
<function name="rang-mod" class="io.mycat.route.function.PartitionByRangeMod">
<property name="mapFile">partition-range-mod.txt</property>
</function>
<function name="jump-consistent-hash" class="io.mycat.route.function.PartitionByJumpConsistentHash">
<property name="totalBuckets">3</property>
</function>
</mycat:rule>
示例
<?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:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
<tableRule name="my_mod-long">
<rule>
<columns>user_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<tableRule name="rule1">
<rule>
<columns>id</columns>
<algorithm>func1</algorithm>
</rule>
</tableRule>
<tableRule name="rule2">
<rule>
<columns>user_id</columns>
<algorithm>func1</algorithm>
</rule>
</tableRule>
<tableRule name="sharding-by-intfile">
<rule>
<columns>sharding_id</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
<tableRule name="auto-sharding-long">
<rule>
<columns>id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
<tableRule name="mod-long">
<rule>
<columns>id</columns>
<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>
<tableRule name="sharding-by-month">
<rule>
<columns>create_time</columns>
<algorithm>partbymonth</algorithm>
</rule>
</tableRule>
<tableRule name="latest-month-calldate">
<rule>
<columns>calldate</columns>
<algorithm>latestMonth</algorithm>
</rule>
</tableRule>
<tableRule name="auto-sharding-rang-mod">
<rule>
<columns>id</columns>
<algorithm>rang-mod</algorithm>
</rule>
</tableRule>
<tableRule name="jch">
<rule>
<columns>id</columns>
<algorithm>jump-consistent-hash</algorithm>
</rule>
</tableRule>
<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">
</function>
<function name="hash-int"
class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
</function>
<function name="rang-long"
class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
</function>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">2</property>
</function>
<function name="func1" class="io.mycat.route.function.PartitionByLong">
<property name="partitionCount">8</property>
<property name="partitionLength">128</property>
</function>
<function name="latestMonth"
class="io.mycat.route.function.LatestMonthPartion">
<property name="splitOneDay">24</property>
</function>
<function name="partbymonth"
class="io.mycat.route.function.PartitionByMonth">
<property name="dateFormat">yyyy-MM-dd</property>
<property name="sBeginDate">2015-01-01</property>
</function>
<function name="rang-mod" class="io.mycat.route.function.PartitionByRangeMod">
<property name="mapFile">partition-range-mod.txt</property>
</function>
<function name="jump-consistent-hash" class="io.mycat.route.function.PartitionByJumpConsistentHash">
<property name="totalBuckets">3</property>
</function>
</mycat:rule>
启动
到mycat/bin目录执行shell脚本
控制台启动 ./mycat console
后台启动 ./mycat start
cd /usr/local/mycat/bin
pwd
ls
./mycat
./mycat console
超时错误解决办法
显示上面这样就是启动成功了,有时可能会出现超时的错误:
wrapper | Startup failed: Timed out waiting for signal from JVM.
解决办法:
cd /usr/local/mycat/conf
vi wrapper.conf
在wrapper.conf最下方添加:
wrapper.ping.timeout=120
wrapper.startup.timeout=300
Mycat登录
登录后台管理窗口
使用9066端口登录后台管理窗口,此登录方式用于管理维护Mycat
mysql -umycat -p123456 -h 127.0.0.1 -P 9066
登录数据访问窗口
使用8066端口访问数据。
mysql -umycat -p123456 -h 127.0.0.1 -P 8066
show databases;
主从读写分离
分别向主数据库 和从数据库 插入不一样的数据
在mycat客户端查询数据,发现都是写数据库的内容
修改Mycat的配置文件schema.xml的节点的balance属性
- 当balance=0 时,不开启读写分离,所有读操作都发生在当前的writeHost上
- 当balance=1 ,所有读操作都随机发送到当前的writeHost对应的readHost和备用的writeHos都参与select语句的负载均衡,简单的说当双主双从模式(M1->S1 ,M2-S2 M2和M2互为主备)
- 当balance=2,所有的读操作都随机发送到所有的writeHost,readHost上
- 当balance=3 ,所有的读操作都只发送到writeHost(db1)的readHost(db2)上
生产当中应该使用 1或者3,此处为了演示切换的效果设置balance=“2”
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="host1" database="testdb" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="2"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!--can have multi write hosts -->
<writeHost host="hostM1" url="172.17.0.2:3306" user="root" password="123456">
<!-- can have multi read hosts -->
<readHost host="hostS1" url="172.17.0.3:3306" user="root" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
双主双从读写分离
原理
一个主机ml用于处理所有写请求,它的从机s1和另一台主机m2还有它的从机s2负责所有读请
求。当m1主机宕机后,m2主机负责写请求,ml、m2互为备机。
配置数据库
此处重新准备数据库,使用docker安装如下四个mysql容器(可以删除db1、db2两个容器以释放虚拟机压力)
序号 角色 容器名 ip地址 server-id(my.cnf)
1 Master1 m1 172.17.0.2 1
2 Slave1 s1 172.17.0.3 2
3 Master2 m2 172.17.0.4 3
4 Slave s2 172.17.0.5 4
删除上述db1、db2释放压力
[root@mycat ~]# docker stop db1 && docker rm db1 && docker stop db2 && docker rm db2
db1
db1
db2
db2
创建四个配置文件
创建配置文件目录:
#创建配置文件
[root@mycat ~]# mkdir -p /root/dbcnf
#创建四个配置文件
[root@mycat ~]# touch /root/dbcnf/master1.cnf
[root@mycat ~]# touch /root/dbcnf/master2.cnf
[root@mycat ~]# touch /root/dbcnf/slave1.cnf
[root@mycat ~]# touch /root/dbcnf/slave2.cnf
[root@mycat ~]#
配置Master1(M1)
修改/root/dbcnf/master1.cnf配置文件添加如下配置:
[mysqld]
#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=testdb
#设置logbin格式
binlog_format=STATEMENT
# 在作为从数据库的时候,有写入操作也要更新二进制日志文件
log-slave-updates
#表示自增长字段每次递增的量,指自增字段的起始值,其默认值是1,取值范围是1 .. 65535
auto-increment-increment=2
# 表示自增长字段从哪个数开始,指字段一次递增多少,他的取值范围是1 .. 65535
auto-increment-offset=1
使用上述文件创建容器:
[root@mycat ~]# docker run --name m1 -p 3316:3306 -v /root/dbcnf/master1.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
ace8d2729df2f7084c277fba06d18c17725d21a08c5eed6c676c7f890075523f
[root@mycat ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
ace8d2729df2 mysql:5.7 “docker-entrypoint.s…” 5 seconds ago Up 4 seconds 33060/tcp, 0.0.0.0:3316->3306/tcp, :::3316->3306/tcp m1
[root@mycat ~]# docker inspect --format ‘{{ .NetworkSettings.IPAddress }}’ m1
172.17.0.2
通过docker命令查看到m1容器的ip地址为172.17.0.2,填入上述准备的表格中
关于mysql中部分配置的说明:
1、关于log-slave-updates()
从库开启log-bin参数,如果直接往从库写数据,是可以记录log-bin日志的,但是从库通过I0线程读取主库二进制日志文件,然后通过SQL线程写入的数据,是不会记录binlog日志的。也就是说从库从主库上复制的数据,是不写入从库的binlog日志的。所以从库做为其他从库的主库时需要在配置文件中添加log-slave-updates参数。
2、关于binlog-format
mysql复制主要有三种方式:
基于SQL语句的复制(statement-based replication, SBR),
基于行的复制(row-based replication, RBR),
混合模式复制(mixed-based replication, MBR)。对应的,
binlog的格式也有三种:STATEMENT,ROW,MIXED。
① STATEMENT模式(SBR)
每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化,减少了binlog日志量,节约IO,提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数, last_insert_id(),以及user-defined functions(udf)等会出现问题)
② ROW模式(RBR)
不记录每条sql语句的上下文信息,仅需记录哪条数据被修改了,修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志,尤其是alter table的时候会让日志暴涨。
③ MIXED模式(MBR)
以上两种模式的混合使用,一般的复制使用STATEMENT模式保存binlog,对于STATEMENT模式无法复制的操作使用ROW模式保存binlog,MySQL会根据执行的SQL语句选择日志保存方式。
配置Slave1(S1)
保持原配置/root/dbcnf/slave1.cnf不动即可
[mysqld]
#从服务器唯一ID
server-id=2
#启用中继日志
relay-log=mysql-relay
使用上述文件创建容器并查看ip
root@mycat ~]# vi /root/dbcnf/slave1.cnf
[root@mycat ~]# docker run --name s1 -p 3326:3306 -v /root/dbcnf/slave1.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
78f1f8ce6c8b1d6d4216ac2de432a95b39fee99c3bd689d1326bb9399a255d8c
[root@mycat ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
78f1f8ce6c8b mysql:5.7 “docker-entrypoint.s…” 5 seconds ago Up 3 seconds 33060/tcp, 0.0.0.0:3326->3306/tcp, :::3326->3306/tcp s1
ace8d2729df2 mysql:5.7 “docker-entrypoint.s…” 12 minutes ago Up 12 minutes 33060/tcp, 0.0.0.0:3316->3306/tcp, :::3316->3306/tcp m1
创建Master2(M2)配置文件并创建数据库
创建/root/dbcnf/master2.cnf配置文件并添加如下配置:
同Master1相似,需要修改server-id(为3)、和auto_increment_offset配置
[mysqld]
#主服务器唯一ID
server-id=3
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=testdb
#设置logbin格式
binlog_format=STATEMENT
# 在作为从数据库的时候,有写入操作也要更新二进制日志文件
log-slave-updates
#表示自增长字段每次递增的量,指自增字段的起始值,其默认值是1,取值范围是1 .. 65535
auto-increment-increment=2
# 表示自增长字段从哪个数开始,指字段一次递增多少,他的取值范围是1 .. 65535
auto-increment-offset=2
使用上述文件创建容器并查看ip
[root@mycat ~]# vi /root/dbcnf/master2.cnf
[root@mycat ~]# docker run --name m2 -p 3336:3306 -v /root/dbcnf/master2.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
d567f78b546e63eecf172b36d648f31868632b632fbad230b570d812078459f8
[root@mycat ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
d567f78b546e mysql:5.7 "docker-entrypoint.s…" 9 seconds ago Up 4 seconds 33060/tcp, 0.0.0.0:3336->3306/tcp, :::3336->3306/tcp m2
78f1f8ce6c8b mysql:5.7 "docker-entrypoint.s…" 4 minutes ago Up 3 minutes 33060/tcp, 0.0.0.0:3326->3306/tcp, :::3326->3306/tcp s1
ace8d2729df2 mysql:5.7 "docker-entrypoint.s…" 16 minutes ago Up 16 minutes 33060/tcp, 0.0.0.0:3316->3306/tcp, :::3316->3306/tcp m1
[root@mycat ~]# docker inspect --format '{{ .NetworkSettings.IPAddress }}' m2
172.17.0.4
[root@mycat ~]#
创建Slave2(S2)配置文件并创建数据库
创建/root/dbcnf/slave2.cnf配置文件并添加如下配置(同Slave1一样即可),修改server-id
[mysqld]
#从服务器唯一ID
server-id=4
#启用中继日志
relay-log=mysql-relay
使用上述文件创建容器并查看ip
[root@mycat ~]# vim /root/dbcnf/slave2.cnf
[root@mycat ~]# docker run --name s2 -p 3346:3306 -v /root/dbcnf/slave2.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
f2706a827e15c2443e4056793a9b58370e528148d279546edc677a833a6611fb
[root@mycat ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
f2706a827e15 mysql:5.7 "docker-entrypoint.s…" 5 seconds ago Up 2 seconds 33060/tcp, 0.0.0.0:3346->3306/tcp, :::3346->3306/tcp s2
d567f78b546e mysql:5.7 "docker-entrypoint.s…" 8 minutes ago Up 8 minutes 33060/tcp, 0.0.0.0:3336->3306/tcp, :::3336->3306/tcp m2
78f1f8ce6c8b mysql:5.7 "docker-entrypoint.s…" 12 minutes ago Up 12 minutes 33060/tcp, 0.0.0.0:3326->3306/tcp, :::3326->3306/tcp s1
ace8d2729df2 mysql:5.7 "docker-entrypoint.s…" 24 minutes ago Up 24 minutes 33060/tcp, 0.0.0.0:3316->3306/tcp, :::3316->3306/tcp m1
[root@mycat ~]# docker inspect --format '{{ .NetworkSettings.IPAddress }}' s2
172.17.0.5
[root@mycat ~]#
至此,四台mysql机器(实际是容器)部署完毕
在两台主机上建立主从复制账户
在两个mysql主机上(M1、M2)建立帐户并授权。CREATE USER ‘slave’@’%’ IDENTIFIED BY ‘123456’; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON . TO ‘slave’@’%’;
具体操作如下:
mysql客户端登录连接m1(IP:172.17.0.2)创建slave用户,并查询master状态
[root@mycat ~]# mysql -uroot -p123456 -h 172.17.0.2 -P3306
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
Query OK, 0 rows affected (0.01 sec)
MySQL [(none)]> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000003 | 619 | testdb | mysql,information_schema | |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
MySQL [(none)]>
mysql客户端登录连接m2(ip:172.17.0.4)创建slave用户,并查询master状态
[root@mycat ~]# mysql -uroot -p123456 -h 172.17.0.4 -P3306
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.06 sec)
MySQL [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
Query OK, 0 rows affected (0.01 sec)
MySQL [(none)]> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000003 | 619 | testdb | mysql,information_schema | |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
MySQL [(none)]>
配置完两个Master查询Log_File和Log_POS的值
分别记录下File和Position的值后,执行完此步骤后不要再操作主服务器MySQL,防止主服务器状态值变化
在从机上配置需要复制的主机
Slave1(s1) 复制 Master1(m1),Slave2(s2) 复制 Master2(m2)
CHANGE MASTER TO MASTER_HOST=‘主机的IP地址’, MASTER_USER=‘slave’, MASTER_PASSWORD=‘123123’, MASTER_LOG_FILE=‘mysql-bin.具体数字’,MASTER_LOG_POS=具体值;
进入Slave1(s1)执行 复制主机的命令,并启动从机的slave功能
CHANGE MASTER TO MASTER_HOST=‘172.17.0.2’, MASTER_USER=‘slave’, MASTER_PASSWORD=‘123456’, MASTER_LOG_FILE=‘mysql-bin.000003’,MASTER_LOG_POS=619;
[root@mycat ~]# mysql -uroot -p123456 -h172.17.0.3 -P3306
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='172.17.0.2',
-> MASTER_USER='slave',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=619;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
MySQL [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.2
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 619
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 619
Relay_Log_Space: 523
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 306d7a6c-cf33-11eb-8ea3-0242ac110002
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR: No query specified
MySQL [(none)]>
进入Slave2(s2)执行 复制主机的命令,并启动从机的slave功能
复制的是M2(IP:172.17.0.4)
CHANGE MASTER TO MASTER_HOST=‘172.17.0.4’, MASTER_USER=‘slave’, MASTER_PASSWORD=‘123456’, MASTER_LOG_FILE=‘mysql-bin.000003’,MASTER_LOG_POS=619;
[root@mycat ~]# mysql -uroot -p123456 -h172.17.0.5 -P3306
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='172.17.0.4',
-> MASTER_USER='slave',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=619;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
MySQL [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.4
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 619
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 619
Relay_Log_Space: 523
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
Master_UUID: 687b8bda-cf38-11eb-956e-0242ac110004
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR: No query specified
MySQL [(none)]>
Slave_IO_Running、Slave_SQL_Running两个参数都是Yes,则说明主从配置成功!
如果显示Connecting可以再执行一次show slave status\G;
配置两个主机相互备份
Master2(db3) 复制 Master1(db1),Master1(db1) 复制 Master2(db3) 步骤如下:
1、在Master1中配置配置复制命令
2、启动Master1的主从复制功能
3、查看Master1的slave状态
4、在Master2中配置配置复制命令
5、启动Master2的主从复制功能
6、查看Master2的slave状态
具体执行过程如下:
在Master1(IP:172.17.0.2)中配置配置复制命令,复制Master2(IP:172.17.0.4)
[root@mycat ~]# mysql -uroot -p123456 -h172.17.0.2 -P3306
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.34-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='172.17.0.4',
-> MASTER_USER='slave',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=619;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
MySQL [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.4
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 619
Relay_Log_File: ace8d2729df2-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 619
Relay_Log_Space: 534
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
Master_UUID: 687b8bda-cf38-11eb-956e-0242ac110004
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR: No query specified
在Master2(IP:172.17.0.4)中配置配置复制命令,复制Master1(IP:172.17.0.2)
[root@mycat ~]# mysql -uroot -p123456 -h172.17.0.4 -P3306
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.34-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='172.17.0.2',
-> MASTER_USER='slave',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=619;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
MySQL [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.2
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 619
Relay_Log_File: 6b3868d2f49d-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 619
Relay_Log_Space: 534
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 306d7a6c-cf33-11eb-8ea3-0242ac110002
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR: No query specified
MySQL [(none)]>
验证mysql主从复制
使用mysql客户端分别连接m1、s1、m2、s2,在M1中创建数据库create database testdb,在s1、m2、s2中查询数据库是否正常同步。
配置mycat读写分离
修改的balance属性,通过此属性配置读写分离的类型
修改 Mycat 的配置文件schema.xml,在原有基础上增加writeHost以及对应的readHost
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="host1" database="testdb" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100" >
<heartbeat>select 1</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="172.17.0.2:3306" user="root" password="123456">
<!-- can have multi read hosts -->
<readHost host="hostS1" url="172.17.0.3:3306" user="root" password="123456" />
</writeHost>
<writeHost host="hostM2" url="172.17.0.4:3306" user="root" password="123456">
<!-- can have multi read hosts -->
<readHost host="hostS2" url="172.17.0.5:3306" user="root" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
balance=“1”: 全部的readHost与stand by writeHost参与select语句的负载均衡。
writeType=“0”: 所有写操作发送到配置的第一个writeHost,第一个挂了切到还生存的第二个
writeType=“1”,所有写操作都随机的发送到配置的 writeHost,1.5 以后废弃不推荐
writeHost,重新启动后以切换后的为准,切换记录在配置文件中:dnindex.properties 。
switchType=“1”
1默认值,自动切换。
-1 表示不自动切换
2 基于 MySQL 主从同步的状态决定是否切换。
操作1
登录mycat创建数据库,成功后登录m1、s1、m2、s2查看表use testdb;,show tables;
可以看到四个数据库都出现了表结构,说明在mycat下主从复制可以用
操作2
在mycat中插入能区分的数据,比如插入时带着主机名字的变量,然后查询数据,发现数据库中主机名字在M2、S1、S2之间切换,说明读写分离生效
[root@mycat ~]# mysql -umycat -p123456 -h127.0.0.1 -P8066
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> use TESTDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MySQL [TESTDB]> INSERT INTO user_info(NAME) VALUES(@@hostname);
Query OK, 1 row affected, 1 warning (0.01 sec)
MySQL [TESTDB]> select * from user_info;
+----+--------------+
| id | name |
+----+--------------+
| 11 | 78f1f8ce6c8b |
+----+--------------+
1 row in set (0.00 sec)
MySQL [TESTDB]> select * from user_info;
+----+--------------+
| id | name |
+----+--------------+
| 11 | 6b3868d2f49d |
+----+--------------+
1 row in set (0.00 sec)
MySQL [TESTDB]> select * from user_info;
+----+--------------+
| id | name |
+----+--------------+
| 11 | 78f1f8ce6c8b |
+----+--------------+
1 row in set (0.00 sec)
MySQL [TESTDB]> select * from user_info;
+----+--------------+
| id | name |
+----+--------------+
| 11 | 78f1f8ce6c8b |
+----+--------------+
1 row in set (0.01 sec)
MySQL [TESTDB]> select * from user_info;
+----+--------------+
| id | name |
+----+--------------+
| 11 | d99c2936a62d |
+----+--------------+
1 row in set (0.01 sec)
MySQL [TESTDB]> select * from user_info;
+----+--------------+
| id | name |
+----+--------------+
| 11 | 78f1f8ce6c8b |
+----+--------------+
1 row in set (0.01 sec)
MySQL [TESTDB]> select * from user_info;
+----+--------------+
| id | name |
+----+--------------+
| 11 | 78f1f8ce6c8b |
+----+--------------+
1 row in set (0.00 sec)
MySQL [TESTDB]> select * from user_info;
+----+--------------+
| id | name |
+----+--------------+
| 11 | 78f1f8ce6c8b |
+----+--------------+
1 row in set (0.00 sec)
MySQL [TESTDB]> select * from user_info;
+----+--------------+
| id | name |
+----+--------------+
| 11 | 6b3868d2f49d |
+----+--------------+
1 row in set (0.00 sec)
MySQL [TESTDB]> select * from user_info;
+----+--------------+
| id | name |
+----+--------------+
| 11 | d99c2936a62d |
+----+--------------+
1 row in set (0.00 sec)
验证双主切换
模拟Master1(M1)异常(此处可以停止mysql服务或者停止容器),测试mycat能否自动切换到Master2正常插入。实现高可用性。
停止m1容器
root@mycat ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
d99c2936a62d mysql:5.7 "docker-entrypoint.s…" 3 hours ago Up 3 hours 33060/tcp, 0.0.0.0:3346->3306/tcp, :::3346->3306/tcp s2
6b3868d2f49d mysql:5.7 "docker-entrypoint.s…" 3 hours ago Up 3 hours 33060/tcp, 0.0.0.0:3336->3306/tcp, :::3336->3306/tcp m2
78f1f8ce6c8b mysql:5.7 "docker-entrypoint.s…" 3 hours ago Up 3 hours 33060/tcp, 0.0.0.0:3326->3306/tcp, :::3326->3306/tcp s1
ace8d2729df2 mysql:5.7 "docker-entrypoint.s…" 4 hours ago Up 4 hours 33060/tcp, 0.0.0.0:3316->3306/tcp, :::3316->3306/tcp m1
[root@mycat ~]# docker stop m1
m1
[root@mycat ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
d99c2936a62d mysql:5.7 "docker-entrypoint.s…" 3 hours ago Up 3 hours 33060/tcp, 0.0.0.0:3346->3306/tcp, :::3346->3306/tcp s2
6b3868d2f49d mysql:5.7 "docker-entrypoint.s…" 3 hours ago Up 3 hours 33060/tcp, 0.0.0.0:3336->3306/tcp, :::3336->3306/tcp m2
78f1f8ce6c8b mysql:5.7 "docker-entrypoint.s…" 3 hours ago Up 3 hours 33060/tcp, 0.0.0.0:3326->3306/tcp, :::3326->3306/tcp s1
[root@mycat ~]#
插入数据,并查询
[root@mycat ~]# mysql -umycat -p123456 -h127.0.0.1 -P8066
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> use TESTDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MySQL [TESTDB]> INSERT INTO user_info(NAME) VALUES(concat('valid-switch--',@@hostname));
Query OK, 1 row affected, 1 warning (0.01 sec)
MySQL [TESTDB]> select * from user_info;
+----+----------------------------+
| id | name |
+----+----------------------------+
| 11 | d99c2936a62d |
| 12 | valid-switch--d99c2936a62d |
+----+----------------------------+
2 rows in set (0.01 sec)
MySQL [TESTDB]> select * from user_info;
+----+----------------------------+
| id | name |
+----+----------------------------+
| 11 | d99c2936a62d |
| 12 | valid-switch--d99c2936a62d |
+----+----------------------------+
2 rows in set (0.00 sec)
MySQL [TESTDB]> select * from user_info;
+----+----------------------------+
| id | name |
+----+----------------------------+
| 11 | d99c2936a62d |
| 12 | valid-switch--d99c2936a62d |
+----+----------------------------+
2 rows in set (0.00 sec)
通过实验发现插入操作成功,且查询时能访问到M2对应的S2的机器
此时重新启动M1后登录Mycat查询数据,发现查询的内容是Master1、Salve1、Salve2其中Master1变为备份主机了。
[root@mycat ~]# mysql -umycat -p123456 -h127.0.0.1 -P8066
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)
Copyright © 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MySQL [(none)]> use TESTDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MySQL [TESTDB]> select * from user_info;
±—±---------------------------+
| id | name |
±—±---------------------------+
| 11 | d99c2936a62d |
| 12 | valid-switch–d99c2936a62d |
±—±---------------------------+
2 rows in set (0.00 sec)
MySQL [TESTDB]> select * from user_info;
±—±-------------+
| id | name |
±—±-------------+
| 11 | 78f1f8ce6c8b |
±—±-------------+
1 row in set (0.01 sec)
MySQL [TESTDB]> select * from user_info;
±—±---------------------------+
| id | name |
±—±---------------------------+
| 11 | d99c2936a62d |
| 12 | valid-switch–d99c2936a62d |
±—±---------------------------+
2 rows in set (0.01 sec)
MySQL [TESTDB]> select * from user_info;
±—±-------------+
| id | name |
±—±-------------+
| 11 | 78f1f8ce6c8b |
±—±-------------+
1 row in set (0.00 sec)
MySQL [TESTDB]> select * from user_info;
±—±---------------------------+
| id | name |
±—±---------------------------+
| 11 | d99c2936a62d |
| 12 | valid-switch–d99c2936a62d |
±—±---------------------------+
2 rows in set (0.00 sec)
MySQL [TESTDB]> select * from user_info;
±—±-------------+
| id | name |
±—±-------------+
| 11 | 78f1f8ce6c8b |
±—±-------------+
1 row in set (0.01 sec)
MySQL [TESTDB]> select * from user_info;
±—±-------------+
| id | name |
±—±-------------+
| 11 | 78f1f8ce6c8b |
±—±-------------+
1 row in set (0.00 sec)
MySQL [TESTDB]> select * from user_info;
±—±---------------------------+
| id | name |
±—±---------------------------+
| 11 | ace8d2729df2 |
| 12 | valid-switch–ace8d2729df2 |
±—±---------------------------+
2 rows in set (0.01 sec)
MySQL [TESTDB]> select * from user_info;
±—±-------------+
| id | name |
±—±-------------+
| 11 | 78f1f8ce6c8b |
±—±-------------+
1 row in set (0.00 sec)
MySQL [TESTDB]> select * from user_info;
±—±---------------------------+
| id | name |
±—±---------------------------+
| 11 | ace8d2729df2 |
| 12 | valid-switch–ace8d2729df2 |
±—±---------------------------+
2 rows in set (0.01 sec)
MySQL [TESTDB]> select * from user_info;
±—±---------------------------+
| id | name |
±—±---------------------------+
| 11 | d99c2936a62d |
| 12 | valid-switch–d99c2936a62d |
±—±---------------------------+
2 rows in set (0.00 sec)
MySQL [TESTDB]> select * from user_info;
±—±---------------------------+
| id | name |
±—±---------------------------+
| 11 | ace8d2729df2 |
| 12 | valid-switch–ace8d2729df2 |
±—±---------------------------+
2 rows in set (0.00 sec)
MySQL [TESTDB]>
通过上述查询发现查询时可以落到M1、S1、S2上。双主切换成功。