8 MySQL中間件代理服務(wù)器-Mycat
實(shí)驗(yàn)拓?fù)?
圖片.png
MySQL: 5.7.31版本
Mycat: 1.6.7.4
實(shí)驗(yàn)步驟:
- 搭建主從
主節(jié)點(diǎn)10.0.0.52
[mysqld]
server-id=52
log-bin=/data/mysql/mysql-bin
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
[12:34:21 root@master ~]#service mysqld restart
Shutting down MySQL.. [ OK ]
Starting MySQL.. [ OK ]
[12:34:53 root@master ~]#mysql -e 'show master logs';
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
mysql> grant replication slave on *.* to repluser@'10.0.0.%' identified by '000000';
Query OK, 0 rows affected, 1 warning (0.01 sec)
從節(jié)點(diǎn)10.0.0.53
[mysqld]
server-id=53
log-bin=/data/mysql/mysql-bin
read-only=ON
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
[12:39:19 root@slave ~]#service mysqld restart
Shutting down MySQL.. [ OK ]
Starting MySQL. [ OK ]
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.52',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='000000',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.52
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 448
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 614
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| repluser | 10.0.0.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
測(cè)試主從同步
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
- 搭建mycat節(jié)點(diǎn)
[12:31:49 root@mycat ~]#yum -y install java mysql
[12:51:00 root@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
[12:54:04 root@mycat ~]#mkdir /apps
[12:54:35 root@mycat ~]#tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps
[12:55:45 root@mycat ~]#echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[12:56:12 root@mycat ~]#source /etc/profile.d/mycat.sh
[12:56:40 root@mycat ~]#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 [::]:*
[12:56:58 root@mycat ~]#mycat start
Starting Mycat-server...
[12:46:13 root@mycat ~]#ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 1 127.0.0.1:32000 *:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 100 [::1]:25 [::]:*
LISTEN 0 50 [::]:34047 [::]:*
LISTEN 0 50 [::]:1984 [::]:*
LISTEN 0 100 [::]:8066 [::]:*
LISTEN 0 50 [::]:39657 [::]:*
LISTEN 0 100 [::]:9066 [::]:*
[12:46:17 root@mycat ~]#tail /apps/mycat/logs/wrapper.log
STATUS | wrapper | 2021/06/16 12:46:13 | --> Wrapper Started as Daemon
STATUS | wrapper | 2021/06/16 12:46:13 | Launching a JVM...
INFO | jvm 1 | 2021/06/16 12:46:13 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2021/06/16 12:46:13 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2021/06/16 12:46:13 |
INFO | jvm 1 | 2021/06/16 12:46:15 | MyCAT Server startup successfully. see logs in logs/mycat.log
- mycat對(duì)外就是一個(gè)數(shù)據(jù)庫服務(wù)器, 客戶端可以通過mysql協(xié)議與mycat連接, 只是mycat接受到請(qǐng)求后不會(huì)處理, 而是轉(zhuǎn)到后端服務(wù)器處理
- 安裝mycat后, 會(huì)默認(rèn)創(chuàng)建TESTDB數(shù)據(jù)庫, 用戶名root, 密碼123456, 端口8066, 客戶端可以直接通過該賬戶登錄mycat
- mycat的TESTDB會(huì)最終映射到后端真實(shí)數(shù)據(jù)庫, 用戶可以通過TESTDB最終訪問后端數(shù)據(jù)庫
[12:49:20 root@client ~]#yum -y install mysql
[12:49:39 root@client ~]#mysql -uroot -p123456 -h10.0.0.51 -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.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)]>
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.02 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 TESTDB |
+------------------+
| address |
| travelrecord |
+------------------+
2 rows in set (0.00 sec)
#由于我們還沒有配置后端服務(wù)器的連接, 因此現(xiàn)在的數(shù)據(jù)庫是沒有數(shù)據(jù)的虛擬數(shù)據(jù)庫
MySQL [TESTDB]> select * from address;
ERROR 1105 (HY000): backend connect: java.lang.IllegalArgumentException: Invalid DataSource:0
- 配置mycat節(jié)點(diǎn)
3.1 在mycat服務(wù)器上修改server.xml文件, 配置mycat的連接信息
[13:02:40 root@mycat ~]#vim /apps/mycat/conf/server.xml
#默認(rèn)信息
<user name="root" defaultAccount="true"> #客戶端連接Mycat的用戶名
<property name="password">123456</property> #客戶端連接Mycat的密碼
<property name="schemas">TESTDB</property> #數(shù)據(jù)庫名和schema.xml里的名字要相對(duì)應(yīng)
<property name="defaultSchema">TESTDB</property>
<!--No MyCAT Database selected 錯(cuò)誤前會(huì)嘗試使用該schema作為schema橙垢，不設(shè)置則為null,報(bào)錯(cuò) -->
<!-- 表級(jí) DML 權(quán)限設(shè)置 -->
<!--
<privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
- 這里使用的是root, 密碼默認(rèn)123456, 邏輯數(shù)據(jù)庫TESTDB, 這些信息都可以自定義, 讀寫權(quán)限都有, 沒有針對(duì)表做任何特殊的權(quán)限
[13:18:20 root@mycat ~]#vim /apps/mycat/conf/schema.xml
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
3.2 修改schema.xml實(shí)現(xiàn)讀寫分離策略
[13:53:10 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數(shù)據(jù)庫要在后端數(shù)據(jù)庫創(chuàng)建, 指定客戶端連接mycat上的TESTDB, 進(jìn)而連接到后端服務(wù)器的mycat數(shù)據(jù)庫. 實(shí)際工作中, 需要根據(jù)后端是哪個(gè)數(shù)據(jù)庫, 進(jìn)行指定. 這里用mycat數(shù)據(jù)庫進(jìn)行演示
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> #balance="1", 表示讀寫分離
<heartbeat>select user()</heartbeat> #mycat會(huì)定期連接到后端數(shù)據(jù)庫, 判斷mysql是否存活
<writeHost host="host1" url="10.0.0.52:3306" user="root" password="123456"> #需要在主節(jié)點(diǎn)創(chuàng)建能遠(yuǎn)程訪問的賬戶, mycat會(huì)使用這個(gè)賬號(hào)連接主節(jié)點(diǎn)
<readHost host="host2" url="10.0.0.53:3306" user="root" password="123456"/> #需要在從節(jié)點(diǎn)創(chuàng)建能遠(yuǎn)程訪問的賬戶, mycat會(huì)使用這個(gè)賬號(hào)連接從節(jié)點(diǎn)
</writeHost>
</dataHost>
</mycat:schema>
# 直接把配置文件做個(gè)備份, 然后用以下內(nèi)容覆蓋即可
[13:01:39 root@mycat /apps/mycat/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"/>
<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.52:3306" user="root" password="123456">
<readHost host="host2" url="10.0.0.53:3306" user="root" password="123456"/>
</writeHost>
</dataHost>
</mycat:schema>
#mycat/conf中的所有文件都是777權(quán)限
[13:52:43 root@mycat ~]#chmod 777 /apps/mycat/conf/schema.xml
3.3 重啟mycat
[13:54:49 root@mycat ~]#mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
[13:55:07 root@mycat ~]#tail /apps/mycat/logs/wrapper.log
INFO | jvm 1 | 2020/11/26 12:57:21 |
INFO | jvm 1 | 2020/11/26 12:57:24 | MyCAT Server startup successfully. see logs in logs/mycat.log
STATUS | wrapper | 2020/11/26 13:54:53 | TERM trapped. Shutting down.
STATUS | wrapper | 2020/11/26 13:54:54 | <-- Wrapper Stopped
STATUS | wrapper | 2020/11/26 13:54:55 | --> Wrapper Started as Daemon
STATUS | wrapper | 2020/11/26 13:54:55 | Launching a JVM...
INFO | jvm 1 | 2020/11/26 13:54:56 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2020/11/26 13:54:56 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2020/11/26 13:54:56 |
INFO | jvm 1 | 2020/11/26 13:54:58 | MyCAT Server startup successfully. see logs in logs/mycat.log
3.4 在后端主服務(wù)器創(chuàng)建mycat數(shù)據(jù)庫,對(duì)mycat授權(quán)
要保證后端服務(wù)器能用root:123456登錄mysql數(shù)據(jù)庫, 同時(shí)也要授權(quán)mycat節(jié)點(diǎn)能使用root:123456登錄后端mysql. 否則會(huì)導(dǎo)致登錄mycat后, 對(duì)表和庫操作失敗
這個(gè)root用戶是給mycat使用的, mycat會(huì)代替程序連接后端服務(wù)器, 而前段的程序的連接用戶是不一樣的
需要和mycat配置文件中定義的賬戶和密碼一致
mysql> create database mycat;
Query OK, 1 row affected (0.00 sec)
# 這里創(chuàng)建mycat, 要和配置文件中的一致
# <dataNode name="dn1" dataHost="localhost1" database="mycat"/>
mysql> grant all on *.* to root@'10.0.0.%' identified by '123456' with grant option;
Query OK, 0 rows affected, 1 warning (0.01 sec)
#驗(yàn)證從節(jié)點(diǎn)同步到數(shù)據(jù)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mycat |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| repluser | 10.0.0.% |
| root | 10.0.0.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
5 rows in set (0.01 sec)
3.5 啟用通用日志, 確認(rèn)實(shí)現(xiàn)了讀寫分離
- 這時(shí)可以看到mycat會(huì)不斷地向后端服務(wù)器發(fā)送select user(); 來判斷后端服務(wù)器是否存活, 每10秒探測(cè)一次
#主節(jié)點(diǎn)
mysql> set global general_log=on;
Query OK, 0 rows affected (0.00 sec)
#從節(jié)點(diǎn)
mysql> set global general_log=on;
Query OK, 0 rows affected (0.00 sec)
[14:13:17 root@master ~]#tail -f /data/mysql/master.log
/usr/local/mysql/bin/mysqld, Version: 5.7.31-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /data/mysql/mysql.sock
Time Id Command Argument
2020-11-26T06:12:58.767063Z 111 Query select user()
2020-11-26T06:13:08.766786Z 111 Query select user()
2020-11-26T06:13:17.020027Z 96 Quit
2020-11-26T06:13:18.769421Z 111 Query select user()
2020-11-26T06:13:28.769830Z 111 Query select user()
2020-11-26T06:13:38.768821Z 111 Query select user()
2020-11-26T06:13:48.767409Z 111 Query select user()
2020-11-26T06:14:58.794391Z 113 Connect root@10.0.0.51 on mycat using TCP/IP
2020-11-26T06:14:58.798255Z 114 Connect root@10.0.0.51 on mycat using TCP/IP
2020-11-26T06:14:58.804344Z 112 Connect root@10.0.0.51 on mycat using TCP/IP
[14:14:49 root@slave ~]#tail -f /data/mysql/slave.log
2020-11-26T06:13:19.285994Z 84 Quit
2020-11-26T06:13:28.776945Z 101 Query select user()
2020-11-26T06:13:38.776032Z 101 Query select user()
2020-11-26T06:13:48.775052Z 101 Query select user()
2020-11-26T06:13:58.774529Z 101 Query select user()
2020-11-26T06:14:08.778030Z 101 Query select user()
2020-11-26T06:14:18.776534Z 101 Query select user()
2020-11-26T06:14:28.776300Z 101 Query select user()
2020-11-26T06:14:38.775988Z 101 Query select user()
2020-11-26T06:14:48.777638Z 101 Query select user()
2020-11-26T06:14:58.795109Z 102 Connect root@10.0.0.51 on mycat using TCP/IP
2020-11-26T06:14:58.795577Z 103 Connect root@10.0.0.51 on mycat using TCP/IP
2020-11-26T06:14:58.807600Z 104 Connect root@10.0.0.51 on mycat using TCP/IP
3.6 主節(jié)點(diǎn)創(chuàng)建數(shù)據(jù)表t1
mysql> use mycat;
Database changed
mysql> create table t1(id int,name char(10));
Query OK, 0 rows affected (0.02 sec)
客戶端連接TESTDB可以看到t1表
MySQL [TESTDB]> show tables;
+-----------------+
| Tables_in_mycat |
+-----------------+
| t1 |
+-----------------+
1 row in set (0.00 sec)
3.7 客戶端連接mycat測(cè)試讀寫分離
測(cè)試1:
- 主節(jié)點(diǎn)在mycat數(shù)據(jù)庫中, 創(chuàng)建的信息, 用戶可以通過mycat上的TESTDB庫看到
[13:41:50 root@client ~]#mysql -uroot -p123456 -h10.0.0.51 -P8066
mysql> use TESTDB;
Database changed
mysql> show tables;
Empty set (0.01 sec)
mysql> show tables;
+-----------------+
| Tables_in_mycat |
+-----------------+
| t1 |
+-----------------+
1 row in set (0.01 sec)
測(cè)試2:
- 客戶端使用select @@server_id; 會(huì)查看到從服務(wù)器的id, 說明了讀操作被調(diào)度到了從節(jié)點(diǎn)
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 53 |
+-------------+
1 row in set (0.02 sec)
# 查看從節(jié)點(diǎn)通用日志
2020-11-26T06:26:17.587241Z 105 Query SET names utf8;
2020-11-26T06:26:17.587456Z 105 Query select @@server_id
2020-11-26T06:26:18.408410Z 106 Query SET names utf8;
2020-11-26T06:26:18.408934Z 106 Query select @@server_id
測(cè)試3:
- 客戶端插入數(shù)據(jù), 請(qǐng)求會(huì)被轉(zhuǎn)發(fā)到主節(jié)點(diǎn)
mysql> insert t1 value (1,'haha');
Query OK, 1 row affected (0.18 sec)
# 查看主節(jié)點(diǎn)日志
2020-11-26T06:27:40.435921Z 114 Query SET names utf8;
2020-11-26T06:27:40.436237Z 114 Query insert t1 value (1,'haha')
# 查看從節(jié)點(diǎn)日志
2021-06-16T05:16:25.003984Z 4 Query BEGIN
2021-06-16T05:16:25.017902Z 4 Query COMMIT /* implicit, from Xid_log_event */
# 從節(jié)點(diǎn)會(huì)從主節(jié)點(diǎn)同步插入信息
停止叢節(jié)點(diǎn), mycat自動(dòng)調(diào)度讀請(qǐng)求至主節(jié)點(diǎn), 但是有延遲時(shí)間, 此時(shí)讀寫不受影響
停止主節(jié)點(diǎn), mycat不會(huì)自動(dòng)調(diào)度寫請(qǐng)求至從節(jié)點(diǎn), 此時(shí)無法寫數(shù)據(jù), 只能讀數(shù)據(jù)
此架構(gòu)的問題
- mycat存在單點(diǎn)故障
- 主服務(wù)器存在單點(diǎn)故障, 主服務(wù)器故障, 數(shù)據(jù)只能讀, 無法寫
解決方案
- 雙mycat組成keepalive集群, 對(duì)外發(fā)布虛擬vip, 兩個(gè)服務(wù)器共享虛擬vip, 用戶訪問的是vip地址
- 后端數(shù)據(jù)庫采用雙主架構(gòu), 通過前端分離器實(shí)現(xiàn)寫操作往一個(gè)服務(wù)器調(diào)度, 讀操作往另一個(gè)服務(wù)器調(diào)度. 此時(shí)正常情況下, 主節(jié)點(diǎn)的寫操作會(huì)被同步到另一個(gè)主節(jié)點(diǎn). 一旦負(fù)責(zé)寫操作的主節(jié)點(diǎn)宕機(jī), 另一個(gè)主節(jié)點(diǎn)可以接受寫操作,不過需要手動(dòng)切換, 或者通過程序?qū)崿F(xiàn), 不過另一個(gè)主的配置已經(jīng)做好了,所以無需再配置.
- mycat通過keepalive實(shí)現(xiàn)高可用, 并且配置相同的后端主從服務(wù)器
- 在mycat上利用策略監(jiān)控后端主服務(wù)器, 一旦檢測(cè)到主服務(wù)器宕機(jī), 就把寫請(qǐng)求轉(zhuǎn)到備用的主服務(wù)器上. 比如, 用MHA實(shí)現(xiàn)高可用集群時(shí), MHA管理節(jié)點(diǎn)可以通過master_ip_failover腳本來自動(dòng)切換vip, 使得vip運(yùn)行在可用的主節(jié)點(diǎn)上, 這樣mycat中就不用寫死主節(jié)點(diǎn)的ip地址了,而是寫MHA提供的vip, 否則一旦主掛了, mycat還要手動(dòng)切換主節(jié)點(diǎn)把寫請(qǐng)求轉(zhuǎn)發(fā)到另一個(gè)備用的主節(jié)點(diǎn)上
雙主模型拓?fù)?
image.png
