前面一篇部落格我介紹瞭如何通過Orachestrator+指令碼實現自動導換。
這一章，我來演示一下如何通過Orchestrator+Proxysql 實現自動導換+應用透明讀寫分離
總體架構
可選架構1
實際生產系統所用架構應該類似於上圖：
1）應用APP，通過VIP連線ProxySQL
2）ProxySQL為了實現高可用，可部署2個。通過VIP管理，即同一時候只有一個ProxySQL是活動並接受外界請求。
當proxysql出現故障時，通過keepyalive實現導換，業務切換到另一個Porxysql。
3）MySQL Master和Slave組成高可用MySQL叢集
4）Orchestrator叢集負責監控MySQL伺服器的導換
可選架構2
如上架構與架構1的區別是，每個應用連線自有的Proxysql。如應用/proxysql crash，則相應的proxysql+app整體不可用。
演示步驟
簡化架構
為了演示簡單一些，在這裡我簡化了很多元件。結構圖如下
測試機器
如上，為了測試我用了5臺虛擬機器
host01 192.168.56.103
host02 192.168.56.104
host03 192.168.56.105
arch 192.168.56.130
proxysql 192.168.56.131
ProxySQL安裝
ProxySQL安裝點 這裡.
MySQL複製拓撲圖
MySQL建立使用者許可權
testuser*是作為proxysql讀寫操作的。
monitor使用者是proxysql用來監控MySQL活動狀態的。
create user testuser_w@'%' identified by 'Testpass1.'; grant insert,update,delete on *.* to 'testuser_w'@'%'; create user testuser_r@'%' identified by 'Testpass1.'; grant select on *.* to 'testuser_r'@'%'; create user testuser_rw@'%' identified by 'Testpass1.'; grant select,insert,update,delete on *.* to 'testuser_rw'@'%'; create user 'monitor'@'%' identified by 'monitor'; grant all privileges on *.* to 'monitor'@'%';
proxysql啟動並插入規則
[root@proxysql-server proxysql]# service proxysql.service start
Redirecting to /bin/systemctl start proxysql.service.service
proxysql啟動後，會開啟兩個埠。6032預設是用來管理，6033是預設用來連線mysql
[root@proxysql-server proxysql]# netstat -nlp|grep proxysql
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 17840/proxysql
tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 17840/proxysql
登入proxysql管理埠配置資料
mysql -uadmin -padmin -h 127.0.0.1 -P 6032
插入如下配置。如下是指定600 hostgroup為只讀，601埠為寫入。（需要注意的是我這裡的配置是master也做為讀取結點）
其中scheduler的插入資料是做何用的，後面再講述。
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_replication_lag) VALUES ('192.168.56.103',601,3306,1000,60); INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_replication_lag) VALUES ('192.168.56.104',601,3306,1000,60); INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_replication_lag) VALUES ('192.168.56.105',601,3306,1000,60); INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_replication_lag) VALUES ('192.168.56.103',600,3306,1000,0); INSERT INTO mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup) values(600,601); LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; insert into mysql_query_rules (username,destination_hostgroup,active) values('testuser_w',600,1); insert into mysql_query_rules (username,destination_hostgroup,active) values('testuser_r',601,1); insert into mysql_query_rules (username,destination_hostgroup,active,retries,match_digest) values('testuser_rw',601,1,3,'^SELECT'); LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK; insert into mysql_users (username,password,active,default_hostgroup,transaction_persistent) values ('testuser_w','Testpass1.',1,600,1); insert into mysql_users (username,password,active,default_hostgroup,transaction_persistent) values ('testuser_r','Testpass1.',1,601,1); insert into mysql_users (username,password,active,default_hostgroup,transaction_persistent) values ('testuser_rw','Testpass1.',1,600,1); LOAD MYSQL USERS TO RUNTIME;SAVE MYSQL USERS TO DISK; INSERT INTO scheduler (id,interval_ms,filename,arg1) values (10,2000,"/var/lib/proxysql/server_monitor.pl","-u=admin -p=admin -h=127.0.0.1 -G=601 -P=6032 --debug=0 --log=/var/lib/proxysql/server_check"); LOAD SCHEDULER TO RUNTIME;SAVE SCHEDULER TO DISK;
插入成功後，檢視伺服器狀態
如下可以看到，所以的伺服器的狀態都顯示為ONLINE。
mysql> select * from mysql_servers; +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 601 | 192.168.56.104 | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 60 | 0 | 0 | | | 601 | 192.168.56.105 | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 60 | 0 | 0 | | | 600 | 192.168.56.103 | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 60 | 0 | 0 | | | 601 | 192.168.56.103 | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 60 | 0 | 0 | | +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
停止host01，檢視導換後的變化
通過service mysqld stop，或是啟動firewall，shutdwon mysql都可以。
host01無法連線後,mysql的拓撲結構變成了如下
檢視proxysql狀態資料的變化
如下，可以看到104的hostgroup變成了600（寫入埠），host01（192.168.56.103）的狀態變為了SHUNNED
mysql> select * from mysql_servers; +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 601 | 192.168.56.103 | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 60 | 0 | 0 | | | 601 | 192.168.56.105 | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 60 | 0 | 0 | | | 600 | 192.168.56.104 | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 60 | 0 | 0 | | | 601 | 192.168.56.104 | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 60 | 0 | 0 | | +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ mysql> select * from stats_mysql_connection_pool where hostgroup between 600 and 601 order by hostgroup,srv_host desc; +-----------+----------------+----------+---------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us | +-----------+----------------+----------+---------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | 600 | 192.168.56.104 | 3306 | ONLINE | 0 | 1 | 1 | 242 | 1 | 18 | 0 | 306 | 198 | 1282 | | 601 | 192.168.56.105 | 3306 | ONLINE | 0 | 1 | 1 | 0 | 1 | 127 | 0 | 2159 | 1397 | 550 | | 601 | 192.168.56.104 | 3306 | ONLINE | 0 | 1 | 1 | 0 | 1 | 207 | 0 | 3519 | 2277 | 1282 | | 601 | 192.168.56.103 | 3306 | SHUNNED | 0 | 0 | 0 | 28 | 1 | 0 | 0 | 0 | 0 | 389 | +-----------+----------------+----------+---------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ 4 rows in set (0.01 sec)
通過mysql客戶端，測試當前行為
如下，寫一個簡單的迴圈語句，測試mysql的行為。如下，可以發現寫入操作都只會傳送到新的host02機器。
只讀操作，會傳送到host02和host03機器。host01已經從讀取列表內排除。
[root@proxysql-server proxysql]# while true; do mysql -u testuser_w -pTestpass1. -h 127.0.0.1 -P 6033 -e "select @@hostname"; sleep 1; done mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@hostname | +------------+ | host02 | +------------+ mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@hostname | +------------+ | host02 | +------------+ ^C [root@proxysql-server proxysql]# while true; do mysql -u testuser_r -pTestpass1. -h 127.0.0.1 -P 6033 -e "select @@hostname"; sleep 1; done mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@hostname | +------------+ | host03 | +------------+ mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@hostname | +------------+ | host02 | +------------+
當host01重新啟動後，會發生什麼？
細心的讀者，可能會發生，mysql_servers列表內，並沒有排除host01。那如果host01啟動後，會發生什麼呢？
結果就是隻讀操作會繼續傳送到host01機器。
因為實際上host01機器在導換後，並不屬於複製叢集了。
如果只讀操作繼續傳送到host01，肯定會讀到舊的資料。造成應用出現問題。
那我們如何避免出現此問題呢？方法就是通過上面的指令碼來把host01排除出複製叢集。
後續的修復需要DBA來干預，如加入複製叢集，等複製同步完成後，再開放host01可以作為讀結點。