今天分享的是mysql数据库的复制过滤器，其中包括GTID复制以及监控和维护，还有MYCAT的安装及其使用mycat使用过程中所需要注意的事项
复制过滤器
GTID复制
GTID配置范例
- 主服务器
vim /etc/my.cnf
server-id=1
gtid_mode=ON
enforce_gtid_consistency
log-bin=mysql-bin #可选
systemctl restart mysqld
mysql> create user 'repluser'@'10.0.0.%' identified by '123456';
mysql> grant replication slave on *.* to 'repluser'@'10.0.0.%'
- 从服务器
vim /etc/my.cnf
server-id=2
read-only
super-read-only
#开启GTID
gtid_mode=ON
enforce_gtid_consistency #从MySQL5.7版本后可以开启MTS(enhanced multi-threaded slave）功能,彻底解决复制延迟问题
slave-parallel-type=LOGICAL_CLOCK # 支持事务级别的sql进行并发回放, 默认是database级别并发
slave-parallel-workers=4 # 并发回访的线程数, 建议设为cpu数的一半,≤ 8.0.26默认为0，≥ 8.0.27默认为4，新版替代replica_parallel_workers
slave_pending_jobs_size_max = 2147483648
slave_preserve_commit_order=1 # 保证事务按照relay log记录的顺序回放，配合LOGICAL_CLOCK使用
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
systemctl restart mysqld #如果主服务器和从服务器数据不一致,需要先将主库数据备份还原至从库,再执行下面操作
mysqldump -A --master-data=2 > /backup/full.sql
mysql>CHANGE MASTER TO MASTER_HOST='10.0.0.100',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1; #使用GTID
mysql>start slave; #注意观察:Retrieved_Gtid_set和Executed_Gtid_Set这两个值,对比主节点执行show master status的值,如果相同表示同步完成
mysql> show processlist;
+----+-----------------+-----------------+------+---------+------+---------------
-------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State
| Info |
+----+-----------------+-----------------+------+---------+------+---------------
-------------------------------------------+------------------+
| 5 | system user | connecting host | NULL | Connect | 6 | Waiting for
source to send event | NULL |
| 6 | system user | | NULL | Query | 6 | Replica has
read all relay log; waiting for more updates | NULL |
| 7 | event_scheduler | localhost | NULL | Daemon | 6 | Waiting on
empty queue | NULL |
| 8 | system user | | NULL | Connect | 6 | Waiting for
an event from Coordinator | NULL |
| 11 | system user | | NULL | Connect | 6 | Waiting for
an event from Coordinator | NULL |
| 12 | system user | | NULL | Connect | 6 | Waiting for
an event from Coordinator | NULL |
| 13 | system user | | NULL | Connect | 6 | Waiting for
an event from Coordinator | NULL |
| 14 | root | localhost | NULL | Query | 0 | init
| show processlist |
+----+-----------------+-----------------+------+---------+------+---------------
-------------------------------------------+------------------+
8 rows in set (0.00 sec)
复制的监控和维护
清理日志
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
RESET MASTER TO # #mysql 不支持
RESET SLAVE [ALL]复制监控
SHOW MASTER STATUS
SHOW BINARY LOGS
SHOW BINLOG EVENTS
SHOW SLAVE STATUS
SHOW PROCESSLIST
从服务器是否落后于主服务
Seconds_Behind_Master：0
如何确定主从节点数据是否一致
percona-toolkit
数据不一致如何修复
删除从数据库，重新复制
手动重建不一致的表 在从库发现某几张表与主库数据不一致，而这几张表数据量也比较大，手工比对数据不现实，并且 重做整个库也比较慢，这个时候可以只重做这几张表来修复主从不一致 这种方案缺点是在执行导入期间需要暂时停止从库复制，不过也是可以接受的
范例：A,B,C这三张表主从数据不一致
1、从库停止Slave复制
mysql>stop slave;
2、在主库上dump这三张表，并记录下同步的binlog和POS点
mysqldump -uroot -p123456 -q --single-transaction --master-data=2 testdb A B
C >/backup/A_B_C.sql
3、查看A_B_C.sql文件，找出记录的binlog和POS点
head A_B_C.sql
例如:MASTERLOGFILE='mysql-bin.888888', MASTERLOGPOS=666666;
#以下指令是为了保障其他表的数据不丢失，一直同步直到那个点结束，A,B,C表的数据在之前的备份已经生成了一份快照，只需要导入进入，然后开启同步即可
4、把A_B_C.sql拷贝到Slave机器上，并做指向新位置
mysql>start slave until MASTERLOGFILE='mysql-bin.888888',
MASTERLOGPOS=666666;
5、在Slave机器上导入A_B_C.sql
mysql -uroot -p123456 testdb
mysql>set sql_log_bin=0;
mysql>source /backup/A_B_C.sql
mysql>set sql_log_bin=1;
6、导入完毕后，从库开启同步即可。
mysql>start slave;
Mycat
Mycat 安装
下载安装JDK
yum -y install java
#确认安装成功
java -version
openjdk version "1.8.0_201"
OpenJDK Runtime Environment (build 1.8.0_201-b09)
OpenJDK 64-Bit Server VM (build 25.201-b09, mixed mode)
下载安装mycat
wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
mkdir /apps
tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps
ls /apps/mycat/
bin catlet conf lib logs version.txt
mycat安装目录结构：
- bin mycat命令，启动、重启、停止等
- catlet catlet为Mycat的一个扩展功能
- conf Mycat 配置信息,重点关注
- lib Mycat引用的jar包，Mycat是java开发的
- logs 日志文件，包括Mycat启动的日志和运行的日志
- version.txt mycat版本说明
logs目录:
- wrapper.log mycat启动日志
- mycat.log mycat详细工作日志
Mycat的配置文件都在conf目录里面，这里介绍几个常用的文件：
- server.xml Mycat软件本身相关的配置文件，设置账号、参数等
- schema.xml Mycat对应的物理数据库和数据库表的配置,读写分离、高可用、分布式策略定制、 节点控制
- rule.xml Mycat分片（分库分表）规则配置文件,记录分片规则列表、使用方法等
启动和连接
#配置环境变量
vim /etc/profile.d/mycat.sh
PATH=/apps/mycat/bin:$PATH
source /etc/profile.d/mycat.sh
#启动
mycat start
#查看日志，确定成功
cat /app/mycat/logs/wrapper.log
...省略...
INFO | jvm 1 | 2019/11/01 21:41:02 | MyCAT Server startup successfully. see
logs in logs/mycat.log
#连接mycat：
mysql -uroot -p123456 -h 127.0.0.1 -P8066
实战案例：利用 Mycat 实现 MySQL 的读写分离
在MySQL代理服务器10.0.0.8安装mycat并启动
root@centos8 ~]#yum -y install java
#确认安装成功
[root@centos8 ~]#java -version
openjdk version "1.8.0_201"
OpenJDK Runtime Environment (build 1.8.0_201-b09)
OpenJDK 64-Bit Server VM (build 25.201-b09, mixed mode)
#下载并安装
[root@centos8 ~]#wget #wget ttp://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz
#wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-.6.7.4-release-20200105164103-linux.tar.gz
[root@centos8 ~]#mkdir /apps
[root@centos8 ~]#tar xvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /apps/
#tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps
#查看文件内容是否足够
[root@rocky8 apps]# free -h
total used free shared buff/cache available
Mem: 1.7Gi 305Mi 720Mi 9.0Mi 759Mi 1.3Gi
Swap: 2.0Gi 0B 2.0Gi
#为了bin使用起来个更方便，给放到环境变量里
#配置环境变量，并生效变量
[root@centos8 ~]#echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@centos8 ~]#source /etc/profile.d/mycat.sh
#查看端口
[root@centos8 ~]#ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
#启动mycat
[root@mycat ~]#file /apps/mycat/bin/mycat
/apps/mycat/bin/mycat: POSIX shell script, ASCII text executable
[root@mycat ~]#mycat
Usage: /apps/mycat/bin/mycat { console | start | stop | restart | status | dump }
#注意: 此步启动较慢,需要等一会儿,另外如果内存太小,会导致无法启动
#查看start之前要查看下监听端口
[root@centos8 ~]#mycat start
Starting Mycat-server...
#可以看到打开多个端口,其中8066端口用于连接MyCAT，其中要关闭中间的数据库
[root@centos8 ~]#ss -ntlp
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
users:(("sshd",pid=791,fd=5))
LISTEN 0 1 127.0.0.1:32000 0.0.0.0:*
users:(("java",pid=4640,fd=4))
LISTEN 0 128 [::]:22 [::]:*
users:(("sshd",pid=791,fd=7))
LISTEN 0 50 *:1984 *:*
users:(("java",pid=4640,fd=57))
LISTEN 0 100 *:8066 *:*
users:(("java",pid=4640,fd=87))
LISTEN 0 50 *:43465 *:*
users:(("java",pid=4640,fd=58))
LISTEN 0 100 *:9066 *:*
users:(("java",pid=4640,fd=83))
LISTEN 0 50 *:45259 *:*
users:(("java",pid=4640,fd=56))
#开启mycat，并查看进程
[root@rocky8 apps]#mycat start
Starting Mycat-server...
[root@rocky8 apps]#mycat status
Mycat-server is running (33921).
[root@rocky8 ~]#ss -ntlp -p可以显示更全面
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 1 127.0.0.1:32000 0.0.0.0:*
LISTEN 0 25 0.0.0.0:514 0.0.0.0:*
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 128 0.0.0.0:80 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:* #查看日志，确定成功，可能需要等一会儿才能看到成功的提示
[root@centos8 ~]#tail /apps/mycat/logs/wrapper.log
ERROR | wrapper | 2020/02/28 15:21:48 | Startup failed: Timed out waiting for
a signal from the JVM.
ERROR | wrapper | 2020/02/28 15:21:48 | JVM did not exit on request,
terminated
INFO | wrapper | 2020/02/28 15:21:48 | JVM exited on its own while waiting to
kill the application.
STATUS | wrapper | 2020/02/28 15:21:48 | JVM exited in response to signal
SIGKILL (9).
STATUS | wrapper | 2020/02/28 15:21:52 | Launching a JVM...
INFO | jvm 2 | 2020/02/28 15:21:52 | OpenJDK 64-Bit Server VM warning:
ignoring option MaxPermSize=64M; support was removed in 8.0
INFO | jvm 2 | 2020/02/28 15:22:13 | Wrapper (Version 3.2.3)
http://wrapper.tanukisoftware.org
INFO | jvm 2 | 2020/02/28 15:22:13 | Copyright 1999-2006 Tanuki Software,
Inc. All Rights Reserved.
INFO | jvm 2 | 2020/02/28 15:22:13 |
INFO | jvm 2 | 2020/02/28 15:22:31 | MyCAT Server startup successfully. see
logs in logs/mycat.log
#用默认密码123456来连接mycat,换到另一台机子执行
[root@centos8 ~]#mysql -uroot -p123456 -h 10.0.0.8 -P8066
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
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)]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.01 sec)
MySQL [TESTDB]> show tables;
+------------------+
| Tables in TESTDB |
+------------------+
| address |
| travelrecord |
+------------------+
2 rows in set (0.01 sec)
MySQL [TESTDB]> select * from travelrecord ;
ERROR 1105 (HY000): backend connect: java.lang.IllegalArgumentException: Invalid
DataSource:0
MySQL [TESTDB]>
在mycat 服务器上修改server.xml文件配置Mycat的连接信息
[root@rocky8 ~]#vim /apps/mycat/conf/server.xml
#先停止mycat程序
[root@rocky8 ~]#mycat stop
Stopping Mycat-server...
Stopped Mycat-server.
#查看端口号关闭，然后修改配置
[root@rocky8 ~]#ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 25 0.0.0.0:514 0.0.0.0:*
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 128 0.0.0.0:80 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 100 127.0.0.1:25 0.0.0.0:*
LISTEN 0 25 [::]:514 [::]:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 100 [::1]:25 [::]:*
#先修改端口号和密码
[root@rocky8 ~]#cd /apps/mycat/conf/
[root@rocky8 ~]#vim server.xml
[root@rocky8 conf]#pwd
/apps/mycat/conf
[root@rocky8 conf]#vim server.xml
#用搜索把这个复制，并且贴到一个非注释地方，更改端口号，密码现在不用改，工作需要改
<property name="serverPort">8066</property>
更改成<property name="serverPort">3306</property>
#还有个需要改，是定义后端的主从复制，所以需要改
[root@rocky8 conf]#vim schema.xml
修改schema.xml实现读写分离策略
[root@centos8 ~]#vim /apps/mycat/conf/schema.xml
#还有个需要改，是定义后端的主从复制，所以需要改
[root@rocky8 ~]#cd /apps/mycat/conf/
[root@rocky8 conf]#vim schema.xml
<!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="localhost1" database="***mycat***" /> #其中mycat表示后端服务器实际的数据库名称
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="***1***"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"
slaveThreshold="100">
<heartbeat>select user()</heartbeat>
***<writeHost host="host1" url="10.0.0.18:3306" user="root"
password="123456">***
***<readHost host="host2" url="10.0.0.28:3306" user="root" password="123456"
/>***
</writeHost>
</dataHost>
</mycat:schema>
#以上***部分表示原配置文件中需要修改的内容
#注意大小写
#最终文件内容
#需要更改的文件可以在24天文件中找到
[root@mycat ~]#cat /apps/mycat/conf/schema.xml
<?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="localhost1" database="hellodb" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"
slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="host1" url="10.0.0.18:3306" user="root"
password="123456">
<readHost host="host2" url="10.0.0.28:3306" user="root"
password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
#重新启动mycat
[root@centos8 ~]#mycat restart
上面配置中，balance改为1，表示读写分离。以上配置达到的效果就是10.0.0.18为主库，10.0.0.28为从库
注意：要保证能使用root/123456权限成功登录10.0.0.18和10.0.0.28机器上面的mysql数据库。同时，也一定要授权mycat机器能使用root/123456权限成功登录这两台机器的mysql数据库！！这很重要，否则会导致登录mycat后，对库和表操作失败！
在后端主服务器创建用户并对mycat授权
[root@centos8 ~]#mysql -uroot -p
mysql> create database mycat;
mysql>GRANT ALL ON *.* TO 'root'@'10.0.0.%' IDENTIFIED BY '123456' ;
mysql> flush privileges;
#下处为自己做的实验
mysql> create user 'root'@'10.0.0.%' IDENTIFIED BY '123456' ;
Query OK, 0 rows affected (0.06 sec)
mysql> GRANT ALL ON hellodb.* TO 'root'@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)
#要开启general_log，可以接受，默认是关闭的，所以要开启
mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
| 0 |
+---------------+
1 row in set (0.01 sec)
mysql> set global general_log=1;
Query OK, 0 rows affected (0.04 sec)
在Mycat服务器上连接并测试
[root@centos8 ~]#mysql -uroot -p123456 -h127.0.0.1 TESTDB
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB | //只能看一个虚拟数据库
+----------+
mysql> use TESTDB;
mysql> create table t1(id int);
MySQL> select @@server_id;
MySQL> select @@hostname;
今天分享的就到这里，谢谢各位的观看！
标签：过虑,0.0,Mycat,mysql,mycat,root,LISTEN From： https://blog.51cto.com/chengpitang/6096772