mycat docker 主从复制
2022-03-03 本文已影响0人
一生逍遥一生
准备工作
获取mysql镜像:
docker pull mysql:5.7
创建相应的master节点和slave节点:
docker run -p 3339:3306 --name mysql_master -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
docker run -p 3340:3306 --name mysql_slave -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
进入容器之后，不能使用vim进行操作，需要安装vim:
apt-get update && apt-get install vim -y
修改配置文件
在master容器中的/etc/mysql/mysql.conf.d/mysqld.cnf中添加如下内容:
[mysqld] #在配置文件中存在
server-id=1 # 1到2的32次方，不能是0，0表示不接受任何slave的连接,全局唯一
log-bin=mysql-bin #开启log-bin
# binlog-ignore-db=mysql # 不对mysql进行主从复制
# binlog-do-db=test #只对数据库test进行主从复制
# binlog_format=STATEMENT
# 要主从哪个数据库，根据需要填写
# 在mysql 5.6之后，推荐使用binlog_format=row, 只输出需要更新的字段
在slave容器中的/etc/mysql/mysql.conf.d/mysqld.cnf中添加如下内容:
server-id=2 #全局唯一
log-bin=mysql-slave-bin #开启log-bin
relay_log=mysql-relay-bin
#replicate-do-db 设定需要复制的数据库
#replicate-ignore-db 设定需要忽略的复制数据库
#replicate-do-table 设定需要复制的表
#replicate-ignore-table 设定需要忽略的复制表
#replicate-wild-do-table 同replication-do-table功能一样，但是可以通配符
#replicate-wild-ignore-table 同replication-ignore-table功能一样，但是可以加通配符
然后将master和slave容器进行重启：
docker restart mysql_master
docker restart mysql_slave
在主库中创建用户，并进行赋权:
CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
设置主从
先获取到master节点的ip地址:
docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql_master
将获取到的ip地址记录下来,以被后面用。
从mysql_master中获取相应的信息:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
在mysql_slave中执行下面的语句:
mysql> change master to master_host='172.17.0.3', master_user='slave', master_password='123456',master_port=3306,master_log_file='mysql-bin.000002', master_log_pos=154, master_connect_retry=30;
# master_host : mysql_master的ip地址
# master_user: 刚才在mysql_master创建的用户
# master_password:刚才在mysql_master创建的用户 的密码
# master_port:mysql_master 端口
# master_log_file:mysql_master 的binlog文件名称
# master_log_pos : mysql_master 的binlog写到哪个位置，从哪个位置开始复制。
在mysql_slave中执行下面的语句:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.17.0.3
Master_User: slave
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: No
Slave_SQL_Running: No
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: 154
Relay_Log_Space: 154
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: NULL
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: 0
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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:
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.3
Master_User: slave
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
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: 154
Relay_Log_Space: 527
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: 0368b534-996e-11ec-b978-0242ac110003
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:
在执行start slave;命令之后，从节点就开始从主节点开始获取binlog日志，Slave_IO_Running，Slave_SQL_Running两个字段的值从NO变成YES。
在mysql_master中创建一个数据库来验证:
mysql> create database test;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
在mysql_slave中执行命令，看这个test数据库是否也存在:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
如果存在说明主从复制已经配置好，可以继续往下执行，如果没有，按照之前的步骤进行检查。
Mycat
下载地址:
docker pull longhronshens/mycat-docker
创建文件
server.xml
<?xml version="1.0" encoding="UTF-8"?>
<!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">0</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>
<!-- 这部分是我们通过客户端连接需要设置的帐号密码 -->
<user name="root">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
</user>
<user name="user">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
<property name="readOnly">true</property>
</user>
</mycat:server>
rule.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
<tableRule name="role1">
<rule>
<columns>id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<property name="count">2</property>
</function>
</mycat:rule>
schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- 设置表的存储方式.schema name="TESTDB" 与 server.xml中的 TESTDB 设置一致 -->
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<!-- 设置dataNode 对应的数据库,及 mycat 连接的地址dataHost -->
<dataNode name="dn1" dataHost="dataHost01" database="test_master" />
<!-- mycat 逻辑主机dataHost对应的物理主机.其中也设置对应的mysql登陆信息 -->
<dataHost name="dataHost01" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- write是读的意思 host是别名可以自己随便起 url:是主机的ip地址和端口号
-->
<writeHost host="hostM1" url="192.168.0.103:3339" user="root" password="123456">
<readHost host="hostS1" url="192.168.0.103:3340" user="root" password="123456"/>
</writeHost>
</dataHost>
</mycat:schema>
balance值介绍:
balance=0:不开启读写分离机制,所有读操作都发送到当前可用的writehost上;
balance=1:全部的readhost和stand by write host参与select语句的负载均衡,即当双主双从模式下，其他的节点都参与select语句的负载均衡
balance=2:所有的读操作都随机在writehost、readhost上分发
balance=3:所有的毒请求随机的分发到readhost上执行，writehost上不负担读压力
writeType值介绍:
writeType=0，所有的写操作都发送到配置的第一个writehost，第一个挂了，切换都还存活的第二个
writeType=1，所有的写操作随机发送到配置的writehost中
#writehost的切换规则，需要写到配置文件中
switchType值介绍:
switchType=1,默认值，自动切换；
switchType=2.基于mysql主从同步的状态决定是否切换
switchType=-1，表示不自动切换
创建mycat容器:
docker run -d -p 8066:8066 -v /Users/apple/mycat/schema.xml:/usr/local/mycat/conf/schema.xml -v /Users/apple/mycat/rule.xml:/usr/local/mycat/conf/rule.xml -v /Users/apple/mycat/server.xml:/usr/local/mycat/conf/server.xml --name mycat longhronshens/mycat-docker
获取mycat的ip地址,从master节点的登录到这个系统中:
docker exec -it mysql_master bash
mysql -uroot -p123456 -h 172.17.0.5 -P 8066
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
mysql> create table psn(id int primary key,name varchar(20));
Query OK, 0 rows affected (0.02 sec)
在mysql_master 节点上就可以看到psn这个表:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| msb |
| mysql |
| performance_schema |
| sys |
| test_master |
+--------------------+
mysql> use test_master;
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_test_master |
+-----------------------+
| psn |
+-----------------------+
参考文献
基于docker mycat 配置mysql主从复制
E: Unable to locate package vim 的解决方案---docker 镜像下安装Vim
Mycat+docker+mysql实现主从复制（一主一从）
