实战案例：利用 Mycat 实现 MySQL 的读写分离
所有主机的系统环境：
[root@mycat ~]# cat /etc/centos-release CentOS Linux release 8.3.2011
服务器共三台
client 10.0.0.7 mycat-server 10.0.0.8 #内存建议2G以上 mysql-master 10.0.0.18 MySQL 8.0 mysql-slave 10.0.0.28 MySQL 8.0
关闭SELinu和防火墙
systemctl stop firewalld setenforce 0 时间同步
1.创建 MySQL 主从数据库
#主服务器 [root@master ~]# yum -y install mysql-server [root@master ~]# systemctl enable --now mysqld #从服务器 [root@slave ~]# yum -y install mysql-server [root@slave ~]# systemctl enable --now mysqld
1)修改master和slave上的配置文件
#master上的my.cnf [root@master ~]# vim /etc/my.cnf [mysqld] server-id=18 [root@master ~]# systemctl restart mysqld #slave上的my.cnf [root@slave ~]# vim /etc/my.cnf [mysqld] server-id=28 [root@slave ~]# systemctl restart mysqld
2)Master上创建复制用户（10.0.0.8）
[root@master ~]# mysql mysql> show master logs; +---------------+-----------+-----------+ | Log_name | File_size | Encrypted | +---------------+-----------+-----------+ | binlog.000001 | 179 | No | | binlog.000002 | 156 | No | +---------------+-----------+-----------+ 2 rows in set (0.00 sec) mysql> create user repluser@'10.0.0.%' identified by '123456' ; mysql> grant replication slave on *.* to repluser@'10.0.0.%';
3) Slave上执行（10.0.0.18）
[root@slave ~]# mysql mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.18', MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=156; mysql> start slave; mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 10.0.0.18 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000002 Read_Master_Log_Pos: 11277 Relay_Log_File: slave-relay-bin.000002 Relay_Log_Pos: 11442 Relay_Master_Log_File: binlog.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes ...省略...
2、在MySQL代理服务器10.0.0.8安装mycat并启动
[root@mycat ~]# yum -y install java [root@mycat ~]# java -version #确认安装成功 openjdk version "1.8.0_312" OpenJDK Runtime Environment (build 1.8.0_312-b07) OpenJDK 64-Bit Server VM (build 25.312-b07, mixed mode) #下载并安装 [root@mycat ~]# wge thttp://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz [root@mycat ~]# mkdir /apps [root@mycat ~]# tar xvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /apps/ #配置环境变量 [root@mycat ~]# echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh [root@mycat ~]# . /etc/profile.d/mycat.sh #启动mycat 注意: 此步启动较慢,需要等一会儿,另外如果内存太小,会导致无法启动 建议内存3G [root@mycat ~]# mycat start Starting Mycat-server... #查看日志，确定成功，可能需要等一会儿才能看到成功的提示 [root@mycat ~]# tail -f /apps/mycat/logs/wrapper.log INFO | jvm 1 | 2021/12/25 00:22:40 | MyCAT Server startup successfully. see logs in logs/mycat.log #用默认密码123456来连接mycat [root@centos7 ~]# mysql -uroot -p123456 -h10.0.0.8 -P8066 mysql> show databases; +----------+ | DATABASE | +----------+ | TESTDB | +----------+ 1 row in set (0.00 sec)
4、在mycat 服务器上修改server.xml文件配置Mycat的连接信息(10.0.0.8)
...省略... #删除注释,并修改下面行的8066改为3306 [root@mycat ~]# cat /apps/mycat/conf/server.xml <property name="serverPort">3306</property> 46 <property name="managerPort">3306</property> 47 <property name="idleTimeout">300000</property> 48 <property name="authTimeout">15000</property> 49 <property name="bindIp">0.0.0.0</property> 50 <property name="dataNodeIdleCheckPeriod">300000</property> #删掉 5 * 60 * 1000L; //连接空闲检查 删除#后面此部分 110 <user name="root" defaultAccount="true"> #连接mycat的用户名 111 <property name="password">magedu</property> #连接mycat的密码 112 <poperty name="schemas">TESTDB</property> #数据库名要和schema.xml相对应 ...省略... 125 </user>
这里使用的是root，密码为magedu,逻辑数据库为TESTDB，这些信息都可以自己随意定义,读写权限都
有，没有针对表做任何特殊的权限。重点关注上面这段配置，其他默认即可。
5、修改schema.xml实现读写分离策略（10.0.0.8）
[root@mycat ~]# vim /apps/mycat/conf/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>
#以上***部分表示原配置文件中需要修改的内容
#注意大小写
#最终的文件内容
<?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@mycat ~]# 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后，对库和表操作失败！
范例：schema.xml
6.在后端主服务器创建用户并对mycat授权（10.0.0.18）
[root@master ~]# mysql mysql> create root@'10.0.0.%' identified by '123456'; mysql> grant all on *.* to root@'10.0.0.%';
7、在客户端上连接并测试(10.0.0.7)
[root@centos7 ~]# mysql -uroot -pmagedu -h10.0.0.8 mysql> show databases; +----------+ | DATABASE | +----------+ | TESTDB | +----------+ 1 row in set (0.00 sec) mysql> 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> show tables; +-------------------+ | Tables_in_hellodb | +-------------------+ | classes | | coc | | courses | | scores | | students | | teachers | | toc | +-------------------+
8.通过通用日志确认实现读写分离
在mysql中查看通用日志
#查看慢日志是否开启 mysql> show variables like 'general%'; +------------------+--------------------------+ | Variable_name | Value | +------------------+--------------------------+ | general_log | OFF | | general_log_file | /var/lib/mysql/slave.log | +------------------+--------------------------+ 2 rows in set (0.01 sec) #开启慢日志 mysql> set global general_log =1; # mysql> show variables like 'general_log_file'; +------------------+--------------------------+ | Variable_name | Value | +------------------+--------------------------+ | general_log_file | /var/lib/mysql/slave.log | +------------------+--------------------------+ 1 row in set (0.00 sec)
在主和从服务器分别启用通用日志，查看读写分离
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] general_log=ON [root@centos8 ~]#systemctl restart mariadb [root@centos8 ~]#tail -f /var/lib/mysql/centos8.log
9、停止从节点，MyCAT自动调度读请求至主节点
[root@slave ~]#systemctl stop mysqld [root@client ~]#mysql -uroot -pmagedu -h10.0.0.8 -P8066 mysql> select @@server_id; +-------------+ | @@server_id | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) mysql> #停止主节点，MyCAT不会自动调度写请求至从节点 MySQL [TESTDB]> insert teachers values(5,'wang',30,'M'); ERROR 1184 (HY000): java.net.ConnectException: Connection refused
10、MyCAT对后端服务器的健康性检查方法select user()
#开启通用日志 [root@master ~]#mysql mysql> set global general_log=1; [root@slave ~]#mysql mysql> set global general_log=1; #查看通用日志 [root@master ~]# tail -f /var/lib/mysql/master.log /usr/libexec/mysqld, Version: 8.0.26 (Source distribution). started with: Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock Time Id Command Argument 2022-01-02T15:42:42.887840Z 60 Query select user() 2022-01-02T15:42:52.889744Z 58 Query select user() 2022-01-02T15:43:02.887842Z 57 Query select user() [root@slave ~]# tail -f /var/lib/mysql/slave.log /usr/libexec/mysqld, Version: 8.0.26 (Source distribution). started with: Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock Time Id Command Argument 2021-12-25T17:38:29.668676Z 45 Query select user() 2021-12-25T17:38:32.291701Z 46 Query show variables like 'general%' 2021-12-25T17:38:39.670626Z 42 Query select user() 2021-12-25T17:38:49.669060Z 43 Query select user() 2021-12-25T17:38:59.673280Z 44 Query select user()
257
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
