MyCAT实现MySQL读写分离
环境准备
Client:10.0.0.7
MyCAT：10.0.0.8
Master:10.0.0.18
Slave:10.0.0.28
创建mysql主从同步
[root@Master ~]#dnf -y install mariadb-server
[root@Slave ~]#dnf -y install mariadb-server
修改Master和Slave配置文件
[root@Master ~]# vi /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18
log-bin
[root@Slave ~]# vi /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=28
log-bin
read-only
Master上创建复制用户
#显示二进制日志位置
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 28198 |
| mariadb-bin.000002 | 344 |
+--------------------+-----------+
2 rows in set (0.000 sec)
#创建复制账号
MariaDB [(none)]> grant replication slave on *.* to repluser@'10.0.0.%' identified by 'repluser';
Query OK, 0 rows affected (0.000 sec)
#刷新权限
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)
Slave上执行
#指定二进制日志位置开始复制
[root@Slave ~]# mysql
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.18',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='repluser',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mariadb-bin.000002',
-> MASTER_LOG_POS=344;
Query OK, 0 rows affected (0.003 sec)
#开始复制线程
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
#显示复制线程状态
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.18
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 543
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 756
Relay_Master_Log_File: mariadb-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: 543
Relay_Log_Space: 1067
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: 18
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 1
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
ERROR: No query specified
在10.0.0.8安装mycat并启动
#安装jdk和mariadb客户端工具
[root@MyCAT ~]# yum -y install java mariadb
#确认jdk版本
[root@MyCAT ~]# java -version
openjdk version "1.8.0_265"
OpenJDK Runtime Environment (build 1.8.0_265-b01)
OpenJDK 64-Bit Server VM (build 25.265-b01, mixed mode)
#下载mycat
[root@MyCAT ~]# wget http://dl.mycat.org.cn/1.6.7.5/2020-4-10/Mycat-server-1.6.7.5-release-20200410174409-linux.tar.gz
#安装mycat
[root@MyCAT ~]# mkdir /app
[root@MyCAT ~]# tar xvf Mycat-server-1.6.7.5-release-20200410174409-linux.tar.gz -C /app/
#配置环境变量
[root@MyCAT ~]# echo 'PATH=/apps/mycat/bin/:$PATH' > /etc/profile.d/mycat.sh
[root@MyCAT ~]# source /etc/profile.d/mycat.sh
#启动mycat
#启动较慢，需要等一会，如果内存太小，会导致无法启动（不小于1G，建议2G以上）
[root@MyCAT ~]#mycat start
Starting Mycat-server...
#查看端口情况
#其中8066端口用于链接mycat
[root@MyCAT ~]# 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=743,fd=4))
LISTEN 0 1 127.0.0.1:32000 0.0.0.0:* users:(("java",pid=4387,fd=4))
LISTEN 0 100 *:8066 *:* users:(("java",pid=4387,fd=96))
LISTEN 0 100 *:9066 *:* users:(("java",pid=4387,fd=92))
LISTEN 0 50 *:43731 *:* users:(("java",pid=4387,fd=73))
LISTEN 0 128 [::]:22 [::]:* users:(("sshd",pid=743,fd=6))
LISTEN 0 50 *:44443 *:* users:(("java",pid=4387,fd=71))
LISTEN 0 50 *:1984 *:* users:(("java",pid=4387,fd=72))
#显示启动日志
[root@MyCAT ~]# tail -f /app/mycat/logs/wrapper.log
INFO | jvm 1 | 2020/10/14 21:34:52 |
INFO | jvm 1 | 2020/10/14 21:34:53 | MyCAT Server startup successfully. see logs in logs/mycat.log
STATUS | wrapper | 2020/10/14 21:36:28 | TERM trapped. Shutting down.
STATUS | wrapper | 2020/10/14 21:36:29 | <-- Wrapper Stopped
STATUS | wrapper | 2020/10/14 21:40:44 | --> Wrapper Started as Daemon
STATUS | wrapper | 2020/10/14 21:40:44 | Launching a JVM...
INFO | jvm 1 | 2020/10/14 21:40:45 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2020/10/14 21:40:45 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2020/10/14 21:40:45 |
INFO | jvm 1 | 2020/10/14 21:40:45 | MyCAT Server startup successfully. see logs in logs/mycat.log
链接测试
#利用默认密码链接
[root@MyCAT ~]# grep 123456 /app/mycat/conf/schema.xml
<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
<writeHost host="hostM1" url="jdbc:oracle:thin:@127.0.0.1:1521:nange" user="base" password="123456" > </writeHost> </dataHost>
<writeHost host="hostM" url="mongodb://192.168.0.99/test" user="admin" password="123456" ></writeHost> </dataHost>
url="jdbc:mysql://localhost:3306" user="root" password="123456"> </writeHost>
#链接成功
[root@MyCAT ~]# mysql -uroot -p123456 -h 10.0.0.8 -P8066
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.29-mycat-1.6.7.5-release-20200410174409 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.000 sec)
#此处要稍微等会
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]> show tables;
+------------------+
| Tables in TESTDB |
+------------------+
| customer |
| customer_addr |
+------------------+
2 rows in set (0.000 sec)
MySQL [TESTDB]>
mycat服务器上修改server.xml文件配置默认的mycat链接信息
[root@MyCAT ~]# vi /app/mycat/conf/server.xml
<user name="user"> #链接mycat的用户名
<property name="password">user</property> #链接mycat的密码
<property name="schemas">TESTDB</property> #数据库名和schema.xml相对应
<property name="readOnly">true</property>
<property name="defaultSchema">TESTDB</property>
</user>
修改schema.xml配置读写分离策略
[root@centos8 ~]# cat /app/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" />
<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授权
MariaDB [(none)]> create database mycat;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> grant all on *.* to root@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)
客户端链接测试
[root@centos7 ~]# mysql -uroot -p123456 -h10.0.0.8 -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.5-release-20200410174409 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.00 sec)
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]> create table t1 (id int);
Query OK, 0 rows affected (0.01 sec)
MySQL [TESTDB]> show tables;
+-----------------+
| Tables_in_mycat |
+-----------------+
| t1 |
+-----------------+
2 rows in set (0.01 sec)
#读写分离实现
MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 28 |
+-------------+
1 row in set (0.00 sec)
279
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
