mysql讀寫分離實現方式是什么

在mysql中，可以利用“mysql-proxy”實現讀寫分離；“mysql-proxy”是一個mysql官方提供用于實現讀寫分離的軟件，也叫中間件，可以讓主數據庫處理寫操作，而從數據庫處理查詢的操作，數據庫的一致性則通過主從復制來實現。

本教程操作環境：windows10系統、mysql8.0.22版本、Dell G3電腦。

mysql讀寫分離的實現方式是什么

Mysql中可以實現讀寫分離的插件有mysql-proxy / Mycat / Amoeba ，mysql-proxy是系統自帶的一個插件，此次實驗主要用它來實現讀寫分離

mysql-proxy是實現"讀寫分離(Read/Write Splitting)"的一個軟件（MySQL官方提供 ，也叫中間件），基本的原理是讓主數據庫處理寫操作（insert、update、delete），而從數據庫處理查詢操作（select）。而數據庫的一致性則通過主從復制來實現

MySQL-proxy 它能實現讀寫語句的區分主要依靠內部的一個lua腳本（能實現讀寫語句的判斷）

如果只在主服務器（寫服務器）上完成數據的寫操作，此時從服務器上沒有執行寫操作，是沒有數據的

這個時候需要使用另外一個技術來實現主從服務器的數據一致性，這個技術叫做 主從復制技術， 所以說主從復制是讀寫分離的基礎

讀寫分離(MySQL- Proxy)是指讓master處理寫操作，讓slave處理讀操作，非常適用于讀操作量比較大的場景，可減輕master的壓力

使用mysql-proxy實現mysql的讀寫分離，mysql-proxy實際上是作為后端mysql主從服務器的代理，它直接接受客戶端的請求，對SQL語句進行分析，判斷出是讀操作還是寫操作，然后分發至對應的mysql服務器上

因為數據庫的寫操作相對讀操作是比較耗時的，所以數據庫的讀寫分離，解決的是數據庫的寫入，影響了查詢的效率

在server1和server2先配置gtid主從復制

gtid主從復制上篇博客已經說明，這里不再贅述，只展示最終效果
可以看到server1上建立一個westos數據庫，對應的server2上會同步過來

配置server3代理端（mysql-proxy）

在server3上搭建mysql-proxy代理服務器（實現客戶端寫在server1上、讀server2上的數據）
(1) 從物理機獲取mysql-proxy安裝包到server3

（2）在server3上進行配置

[root@server3 ~]# systemctl status mysqld	##查看mysqld服務狀態 [root@server3 ~]# systemctl stop mysqld	##關閉mysqld服務，因為代理服務器要用3306端口 [root@server3 ~]# tar zxf mysql-proxy-0.8.5-linux-glibc2-x86-64bit.tar.gz -C /usr/local/	##解壓到/usr/local/目錄下

做一個軟連接以便管理

ln -s mysql-proxy-0.8.5-linux-glibc2-x86-64bit mysql-proxy

mysql-proxy目錄下是沒有配置文件的，所以需要自行建立一個配置文件的目錄，創建配置文件

使用下面兩條命令可以查配置文件中寫入的參數

[root@server3 bin]# ./mysql-proxy --help [root@server3 bin]# ./mysql-proxy --help-proxy

[mysql-proxy]	##指定語句塊 proxy-address=0.0.0.0:3306	##指定proxy訪問的主機和端口，3306是一個對外的通用端口 proxy-read-only-backend-addresses=172.25.254.2:3306	##讀主機的ip和端口 proxy-backend-addresses=172.25.254.1:3306	##執行寫主機的ip和端口 proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua	##指定讀寫分離操作使用的lua文件路徑 pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid	##pid存放路徑 log-file=/usr/local/mysql-proxy/log/mysql-proxy.log	##日志存放路徑 plugins=proxy	##指定使用的插件 log-level=debug	##日志的等級 keepalive=true	##開啟守護進程 daemon=true	##使用后臺方式運行

保存后需要將配置文件的權限改為660，需要創建 log 目錄

修改數據庫發生讀寫分離時的最大和最小連接數

[root@server3 mysql-proxy]# find . -name *.lua  ./share/doc/mysql-proxy/rw-splitting.lua [root@server3 mysql-proxy]# cd share/doc/mysql-proxy  [root@server3 mysql-proxy]# ls  [root@server3 mysql-proxy]# vim rw-splitting.lua		##將lua腳本里原本啟動機制的最小4個最大8個連接，改為1和2 min_idle_connections = 1, 最小連接數  max_idle_connections = 2, 最大連接數

（3）啟動mysql-proxy

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf	##啟動 cat /usr/local/mysql-proxy/log/mysql-proxy.log	##查看日志

測試讀寫分離

（1）在server1上創建新的用戶并且授權

mysql> grant insert,update,select on *.* to kkxili@'%' identified by 'Red1hat*'; mysql> FLUSH PRIVILEGES;	##刷新授權表 mysql> USE westos; Database changed mysql> CREATE TABLE linux (     -> username varchar(10) not null,     -> password varchar(15) not null); mysql>DESC linux;

（2）server3安裝lsof


（3）在用戶端虛擬機server4上第一次連接數據庫代理server3


在server3上面:lsof -i:3306


（4）在用戶端虛擬機server4上第二次連接數據庫代理server3
在server3上面:lsof -i:3306


（5）在用戶端虛擬機server4上第三次連接數據庫代理server3
在server3上面:lsof -i:3306
開始讀寫分離

上面是讀寫分離的讀訪問測試
寫測試
在用戶端插入數據

use westos; insert into linux values('user1','123');

server1和server2都可以看到插入的數據


在server2中關閉主從復制
用戶端再次寫入數據，看不到剛剛寫的數據

寫在server1上，可以查看到數據

在server2上實現了讀寫分離

server2重新開啟主從復制可以看到數據

客戶端讀的是server2，server2只能讀，不能寫，因此看不到剛才寫進去的東西，server1可以看到
實現了客戶端（虛擬機）對server1的寫，對server2的讀

當訪問數據庫的用戶數量很多時，數據庫的代理就把后端的數據庫實現讀寫分離
server1是寫的數據庫、server2是讀的數據庫
當server1和server2滿足gtid的主從復制時，用戶往數據庫寫入的數據其實是寫入了server1，并沒有寫入server2，server2上面的數據是復制過去的，因此server1、server2、客戶機上面都能查到剛剛寫進去的數據，其實客戶機查的是server2（讀）
當關閉server1和server2的異步復制時，客戶機往數據庫寫入的數據只寫進了server1，沒有寫進去server2，server2也沒有復制一份
因此server1可以查看到，server2和客戶機上面都查不到剛剛寫進去的數據，此時的客戶機讀的是server2

推薦學習：mysql視頻教程