MySQL MyCAT 讀寫分離實戰

語言: CN / TW / HK

1.MySQL讀寫分離概念:

MYSQL讀寫分離的原理其實就是讓Master資料庫處理事務性增、刪除、修改、更新操作(CREATE、 INSERT、UPDATE、DELETE),而讓Slave資料庫處理SELECT操作,MYSQL讀寫分離前提是基於 MYSQL主從複製,這樣可以保證在Master上修改資料,Slave同步之後,WEB應用可以讀取到Slave端 的資料。

1.1讀寫分離實現方式:

實現MYSQL讀寫分離可以基於第三方外掛,也可以通過開發修改程式碼實現,具體實現的讀寫分離的常見 方式有如下四種:

Amoeba 讀 寫 分 離 ; MySQL-Proxy讀寫分離;

Mycat讀寫分離;

基於程式讀寫分離(效率很高,實施難度大,開發改程式碼);

Amoeba 是阿里08年開源的以MySQL為底層資料儲存,並對WEB、APP應用提供MySQL協議介面的proxy。它集中地響應WEB應用的請求,依據使用者事先設定的規則,將SQL請求傳送到特定的資料庫上 執行,基於此可以實現負載均衡、讀寫分離、高可用性等需求。

Mysql-Proxy是MySQL官方提供的mysql中介軟體服務,支援無數客戶端連線,同時後端可連線若干臺Mysql-Server伺服器,MYSQL-Proxy自身基於MySQL協議,連線MYSQL-Proxy的客戶端無需修改任何 設定, 跟正常連線MYSQL Server沒有區別,無需修改程式程式碼。

Mycat是基於阿里12年開源的cobar開發的一個數據庫中介軟體,在架構體系中是位於資料庫和應用層之 間的一個元件,並且對於應用層是透明的,它可實現讀寫分離,分庫分表。

2.基於mysql-proxy實現讀寫分離:

proxy: 192.168.75.133 master: 192.168.75.134 slave: 192.168.75.135

2.1工作原理圖解:

file

2.2配置proxy:

proxy可以選擇和mysql部署在同一臺伺服器,也可以選擇單獨部署在另一臺獨立伺服器。 ```

下載mysql-proxy:

wget http://mirrors.163.com/mysql/Downloads/MySQL-Proxy/mysql-proxy-0.8.4-linux- el6-x86-64bit.tar.gz

解壓:

tar xf mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz mv mysql-proxy-0.8.4-linux-el6-x86-64bit /usr/local/mysql-proxy

配置環境變數:

[root@node3 src]# echo "export PATH=/usr/local/mysql-proxy/bin:$PATH" > /etc/profile.d/mysql-proxy.sh [root@node3 src]# . /etc/profile.d/mysql-proxy.sh # 啟動MYSQL-Proxy中介軟體: [root@node3 src]# useradd -r mysql-proxy [root@node3 src]# mysql-proxy --daemon --log-level=debug --user=mysql-proxy -- keepalive --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend- addresses="192.168.75.134:3306" --proxy-read-only-backend- addresses="192.168.75.135:3306" --proxy-lua-script="/usr/local/mysql- proxy/share/doc/mysql-proxy/rw-splitting.lua" --plugins=admin --admin- username="admin" --admin-password="admin" --admin-lua-script="/usr/local/mysql- proxy/lib/mysql-proxy/lua/admin.lua"

檢視埠/日誌:

[root@node3 src]# netstat -ntlp |grep 40 tcp 0 0 0.0.0.0:4040 0.0.0.0: LISTEN 1348/mysql-proxy tcp 0 0 0.0.0.0:4041 0.0.0.0: LISTEN 1348/mysql-proxy ```

2.3啟動的相關引數:

```

Mysql-Proxy的相關引數詳解如下:

--help-all :獲取全部幫助資訊; --proxy-address=host:port :代理服務監聽的地址和埠,預設為4040; --admin-address=host:port :管理模組監聽的地址和埠,預設為4041; --proxy-backend-addresses=host:port :後端mysql伺服器的地址和埠; --proxy-read-only-backend-addresses=host:port :後端只讀mysql伺服器的地址和埠; --proxy-lua-script=file_name :完成mysql代理功能的Lua指令碼; --daemon :以守護程序模式啟動mysql-proxy; --keepalive :在mysql-proxy崩潰時嘗試重啟之; --log-file=/path/to/log_file_name :日誌檔名稱; --log-level=level :日誌級別; --log-use-syslog :基於syslog記錄日誌; --plugins=plugin :在mysql-proxy啟動時載入的外掛; --user=user_name :執行mysql-proxy程序的使用者; --defaults-file=/path/to/conf_file_name :預設使用的配置檔案路徑,其配置段使用[mysql- proxy]標識; --proxy-skip-profiling :禁用profile; --pid-file=/path/to/pid_file_name :程序檔名; ```

2.4啟動master/slave

systemctl start mariadb

2.5檢視讀寫分離狀態:

基於4041埠MySQL-Proxy檢視讀寫分離狀態,登入4041管理埠

file

2.5授權proxy:

grant all on *.* to "mysql-proxy"@"192.168.75.133" identified by "123456";

2.6通過代理建立資料:

通過4040代理埠插入資料,該sql語句會走master,於是可以啟用master狀態: mysql -h192.168.75.133 -umysql-proxy -p123456 -P4040 -e "create database lutixia charset utf8;" 在4041管理埠,再次檢視: file

2.7通過代理查詢資料:

```

先在從庫選擇lutixia資料庫(因為主從關係,在主庫建立的lutixia會同步至從庫),建立表格,並插入資料:

MariaDB [(none)]> use lutixia Database changed MariaDB [lutixia]> create table t1( id int, name varchar(20) ); Query OK, 0 rows affected (0.00 sec)

MariaDB [lutixia]> insert t1 values(1,"xiaoming"); Query OK, 1 row affected (0.00 sec) ```

通過4040代理埠查詢資料,該sql語句會走slave,於是可以啟用slave狀態: ```

多執行幾次!

[root@node4 ~]# mysql -h192.168.75.133 -umysql-proxy -p123456 -P4040 -e "select * from lutixia.t1;" +------+----------+ | id | name | +------+----------+ | 1 | xiaoming | +------+----------+ ``` 在4041管理埠,再次檢視:

mysql> select * from backends; +-------------+---------------------+-------+------+------+-------------------+ | backend_ndx | address | state | type | uuid | connected_clients | +-------------+---------------------+-------+------+------+-------------------+ | 1 | 192.168.75.134:3306 | up | rw | NULL | 0 | | 2 | 192.168.75.135:3306 | up | ro | NULL | 0 | +-------------+---------------------+-------+------+------+-------------------+ 2 rows in set (0.00 sec)

3.基於Mycat實現讀寫分離:

Mycat基於阿里開源的Cobar產品而研發 , 一個徹底開源的,面向企業應用開發的大資料庫叢集 , 一個可以視為MySQL叢集的企業級資料庫,用來替代昂貴的Oracle叢集 ,MYCAT並不依託於任何一個商業公司, 永不收費,永不閉源 !

file

mycat:192.168.75.133 master:192.168.75.134 slave:192.168.75.135

3.1安裝mycat:

```

下載mycat:

wget http://dl.mycat.io/1.6.7.1/Mycat-server-1.6.7.1-release-20190627191042- linux.tar.gz

解壓:

tar xf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz -C /usr/local/

安裝java-jdk:

yum install java-1.8.0-openjdk -y

配置mycat環境變數:

echo "export PATH=/usr/local/mycat/bin:$PATH" > /etc/profile.d/mycat.sh . /etc/profile.d/mycat.sh ```

3.2授權mycat:

```

在主庫執行授權資訊,從庫會自動同步:

grant all on . to "mycat-proxy"@"192.168.75.133" identified by "123456"; ```

3.3配置mycat:

```

配置server.xml

預設管理使用者,可讀可寫:

123456 lutixiadb ...

只讀使用者:

user lutixiadb true

配置schema.xml

設定邏輯庫以及資料庫節點

配置資料庫節點對應的後端真實的資料庫:

配置讀寫庫以及均衡:

select user()

```

3.3啟動mycat:

mycat start

3.4連線測試:

``` mysql -umycat -p123456 -P8066 -h127.0.0.1 MySQL [(none)]> show databases; +-----------+ | DATABASE | +-----------+ | lutixiadb | +-----------+ 1 row in set (0.00 sec)

可以在後端主庫建立一個表,繼續查詢表測試:

MySQL [(none)]> use lutixiadb; 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 [lutixiadb]> show tables; +--------------------+ | Tables_in_students | +--------------------+ | t1 | +--------------------+ 1 row in set (0.01 sec)

在從庫插入資料,繼續查詢:

MySQL [lutixiadb]> select * from t1; +------+----------+ | id | name | +------+----------+ | 1 | xiaowang | +------+----------+ 1 rows in set (0.00 sec) ``` 在主庫查不到資料,通過代理可以查到,即可驗證讀寫分離成功。

3.5報錯解決:

``` MySQL [lutixiadb]> show tables; ERROR 1184 (HY000): Invalid DataSource:0

可能是後端節點出現了問題,也有可能是代理端無法連上後端節點導致: 可以先在代理端直接用授權使用者名稱和密碼登入後端資料庫測試連線問題: [root@node3 conf]# mysql -umycat-proxy -h192.168.75.134 -p123456 ERROR 1129 (HY000): Host 'node3' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

可以看到因為多次錯誤,代理端伺服器被鎖定了,所以也會出現上面的報錯:

在後端主庫執行如下命令: mysqladmin flush-hosts 再次測試,一般問題就能解決。 ```