实验环境准备
# mycat-server 10.0.0.81
# mysql-master 10.0.0.82
# mysql-slave 10.0.0.83
# 关闭selinux和防火墙
# 时间同步
Mysql主从复制
主节点配置
# 修改配置文件
[root@centos82 ~]#vim /etc/my.cnf.d/mysql-server.cnf
server-id=2
log_bin
# 创建复制用账户
[root@centos82 ~]#mysql
mysql> create user slave@'10.0.0.%' identified by '744123'
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to slave@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)
# 查看binlog位置
mysql> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| centos8-bin.000001 | 763 |
+--------------------+-----------+
1 row in set (0.00 sec)
# 开启通用日志，观察读写分离
[root@centos82 ~]#vim /etc/my.cnf
[mysqld]
general_log=ON
从节点配置
# 修改配置文件
[root@centos83 ~]#vim /etc/my.cnf.d/mysql-server.cnf
server-id=3
# 指向主节点
[root@centos83 ~]#mysql
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.82',
-> MASTER_USER='slave',
-> MASTER_PASSWORD='744123',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='centos8-bin.000001',
-> MASTER_LOG_POS=763;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
# 开启slave复制线程
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
# 查看slave状态
root@localhost [(none)]>show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.82
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: centos8-bin.000001
Read_Master_Log_Pos: 763
Relay_Log_File: centos8-relay-bin.000002
Relay_Log_Pos: 322
Relay_Master_Log_File: centos8-bin.000001
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: 763
Relay_Log_Space: 531
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: 2
Master_UUID: b2fbbf25-1cfd-11eb-8651-000c293c4f1b
Master_Info_File: /data/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)
# 开启通用日志，观察读写分离
[root@centos83 ~]#vim /etc/my.cnf
[mysqld]
general_log=ON
创建数据库和用户并测试主从复制
# 主节点创建wordpress数据库和wpuser用户。
[root@centos82 ~]#mysql -uroot -p744123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.7.30-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
root@localhost [(none)]>create database wordpress;
Query OK, 1 row affected (0.00 sec)
root@localhost [(none)]>create user wpuser@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
root@localhost [(none)]>grant all on wordpress.* to wpuser@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
# 查看从节点是否复制成功
root@localhost [(none)]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| wordpress |
+--------------------+
5 rows in set (0.00 sec)
root@localhost [(none)]>show grants for wpuser@'10.0.0.%';
+--------------------------------------------------------------+
| Grants for wpuser@10.0.0.% |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wpuser'@'10.0.0.%' |
| GRANT ALL PRIVILEGES ON `wordpress`.* TO 'wpuser'@'10.0.0.%' |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)
# 没有问题
安装Mycat并启动
# 安装java和mysql客户端
[root@centos81 ~]#dnf -y install java mariadb
[root@centos81 ~]#java -version
openjdk version "1.8.0_272"
OpenJDK Runtime Environment (build 1.8.0_272-b10)
OpenJDK 64-Bit Server VM (build 25.272-b10, mixed mode)
# 下载mycat
[root@centos81 ~]#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
# 解压缩
[root@centos81 ~]#tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /usr/local/
# 配置环境变量
[root@centos81 ~]#echo 'PATH=/usr/local/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@centos81 ~]#. /etc/profile.d/mycat.sh
# 启动mycat，内存建议2G以上
[root@centos81 ~]#mycat start
Starting Mycat-server...
# 查看端口
[root@centos81 ~]#ss -nlt
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 1 127.0.0.1:32000 0.0.0.0:*
LISTEN 0 100 *:9066 *:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 50 *:33851 *:*
LISTEN 0 50 *:1984 *:*
LISTEN 0 100 *:8066 *:*
LISTEN 0 50 *:45731 *:*
# 查看日志，mycat启动成功
[root@centos81 ~]#tail /usr/local/mycat/logs/wrapper.log
STATUS | wrapper | 2020/11/02 19:41:48 | --> Wrapper Started as Daemon
STATUS | wrapper | 2020/11/02 19:41:48 | Launching a JVM...
INFO | jvm 1 | 2020/11/02 19:41:48 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2020/11/02 19:41:48 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2020/11/02 19:41:48 |
INFO | jvm 1 | 2020/11/02 19:41:49 | MyCAT Server startup successfully. see logs inlogs/mycat.log
# 使用mycat默认用户和密码连接mycat
[root@centos81 apps]#mysql -uroot -p123456 -h 10.0.0.81 -P8066
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MySQL connection id is 3
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)
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.002 sec)
# 可以看到有个默认的TESTDB逻辑数据库。
配置Mycat实现主从读写分离
修改Mycat的连接信息和逻辑数据库
[root@centos81 ~]#vim /usr/local/mycat/conf/server.xml
...省略...
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">wordpress</property>
<property name="defaultSchema">wordpress</property>
<!--No MyCAT Database selected 错误前会尝试使用该schema作为schema，不设置则为null,报错 -->
<!-- 表级 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设置删除，或者将其schemas与defaultSchema也改为wordpress，否则重启mycat会报错。
配置读写分离
[root@centos81 ~]#vim /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="wordpress" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
<dataNode name="dn1" dataHost="localhost1" database="wordpress" />
<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.82:3306" user="wpuser" password="123456">
<readHost host="host2" url="10.0.0.83:3306" user="wpuser" password="123456"/>
</writeHost>
</dataHost>
</mycat:schema>
重启mycat并测试读写分离
[root@centos81 apps]#mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
[root@centos82 apps]#mysql -uroot -p123456 -h 10.0.0.81 -P 8066
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)
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 |
+-----------+
| wordpress |
+-----------+
1 row in set (0.001 sec)
MySQL [(none)]> use wordpress
Database changed
MySQL [wordpress]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 3 | <<== 可以看到这里是从节点的server_id，证明是从从节点读的数据。
+-------------+
1 row in set (0.002 sec)
MySQL [wordpress]> create table t1(id int);
Query OK, 0 rows affected (0.007 sec)
# 查看主节点通用日志
[root@centos8 ~]#tail /data/mysql/centos8.log
2020-11-02T13:37:11.419395Z 3 Query SET @slave_uuid= '9c9d49c8-1cfd-11eb-b1cd-000c29c8e04f'
2020-11-02T13:37:11.419713Z 3 Binlog Dump Log: 'centos8-bin.000001' Pos: 2279
2020-11-02T13:37:11.464746Z 2 Query select user()
2020-11-02T13:37:21.465091Z 2 Query select user() <<== Mycat的心跳检查
2020-11-02T13:37:31.464749Z 2 Query select user()
2020-11-02T13:37:41.464749Z 2 Query select user()
2020-11-02T13:37:51.465218Z 2 Query select user()
2020-11-02T13:38:01.465218Z 2 Query select user()
2020-11-02T13:38:05.916088Z 2 Query create table t1(id int) <<== 确实是在主节点上执行写操作
2020-11-02T13:38:11.465034Z 2 Query select user()
Mycat读写分离实现成功！