MySQL - MySQL实现读写分离 - 《Linux》

主从复制
Mycat 配置

主从复制

Mycat 配置

下载安装初始化 mycat

1. 下载 openjdk 
root@centos7:~$yum install java -y
---------------------------------------------------------------------------------
2. 安装 my.cat
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@centos7:~$mkdir /apps
root@centos7:~$tar -zxf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps/
root@centos7:/apps/mycat$echo PATH="/apps/mycat/bin/:$PATH" >> /etc/profile;source /etc/profile

配置 mycat 配置文件

1. conf/server.xml
# 可以自定义用户名密码
        <user name="root" defaultAccount="true">
                <property name="password">123456</property>
                <property name="schemas">TESTDB</property>
                <property name="defaultSchema">TESTDB</property>
---------------------------------------------------------------------------------

2. 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.51:3306" user="root" password="123.com">
<readHost host="host2" url="10.0.0.52:3306" user="root" password="123.com" />
</writeHost>
</dataHost>
</mycat:schema>

启动 mycat

root@centos7:~$mycat start

客户端连接 mycat 虚拟库验证是否可以进行读写分离

1. 安装 mysql 客户端
root@centos8client:~$yum install mariadb -y
---------------------------------------------------------------------------------

2. 连接测试查看
root@centos8:~$mysql -u root -p123456 -h 10.0.0.104 -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 |
+----------+
| TESTDB   |        # 只有 TESTDB 库
+----------+        
1 row in set (0.002 sec)

MySQL [(none)]> use TESTDB
Database changed
MySQL [TESTDB]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.01 sec)           
---------------------------------------------------------------------------------

3. mycat读操作
MySQL TESTDB >show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 48    |
+---------------+-------+
1 row in set (0.00 sec)

---------------------------------------------------------------------------------

4. mycat写入操作 
MySQL TESTDB >update teachers set age=@@server_id where tid=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL  TESTDB >select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  47 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

---------------------------------------------------------------------------------

健康检查

启用通用日志
root@localhost (none) >set global general_log=1;
Query OK, 0 rows affected (0.00 sec)

查看日志
[root@mysql ~]# tail -f /data/mysql/mysql.log 
2021-02-23T08:09:10.284083Z        5 Query    select user()
2021-02-23T08:09:20.283703Z        8 Query    select user()
2021-02-23T08:09:30.284920Z        9 Query    select user()
2021-02-23T08:09:40.284154Z        7 Query    select user()
2021-02-23T08:09:50.285251Z       13 Query    select user()
2021-02-23T08:10:00.284287Z       10 Query    select user()
2021-02-23T08:10:10.283849Z        6 Query    select user()
2021-02-23T08:10:20.284214Z        4 Query    select user()