本文主要給大家介紹ProxySQL如何幫助MySQL實(shí)行讀寫分離，文章內(nèi)容都是筆者用心摘選和編輯的，具有一定的針對性，對大家的參考意義還是比較大的，下面跟筆者一起了解下ProxySQL如何幫助MySQL實(shí)行讀寫分離吧。
ProxySQL是一個基于C++開發(fā)的高性能輕量級產(chǎn)品，是一款強(qiáng)大的mysql的中間件，他可以實(shí)現(xiàn)多種方式的讀寫分離。
Master IP：172.16.75.4 CentOS 7.5D server_id：401
Slave IP：172.16.75.3 CentOS 7.5C server_id：301
1.首先，yum安裝即可，然后啟動，啟動端口為6032（我的proxysql安裝在了master上）；
[root@slave2 ~]# ss -tnl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 50 *:3306 *:* LISTEN 0 128 *:111 *:* LISTEN 0 128 *:6032 *:* LISTEN 0 128 *:6033 *:* LISTEN 0 128 *:6033 *:* LISTEN 0 128 *:6033 *:* LISTEN 0 128 *:6033 *:*
2.使用mysql客戶端工具登錄proxysql，用戶名和密碼都是admin，端口為6032，默認(rèn)不允許localhost登錄，所以要用127.0.0.1IP地址登錄；
[root@slave2 ~]# mysql -uadmin -padmin -h227.0.0.1 -P6032 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.30 (ProxySQL Admin Module) 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; +-----+---------------+-------------------------------------+ | seq | name | file | +-----+---------------+-------------------------------------+ | 0 | main | | | 2 | disk | /var/lib/proxysql/proxysql.db | | 3 | stats | | | 4 | monitor | | | 5 | stats_history | /var/lib/proxysql/proxysql_stats.db | +-----+---------------+-------------------------------------+ 5 rows in set (0.00 sec)
3.主要介紹其中mian庫和monitor庫；
MySQL [(none)]> show tables from main; +--------------------------------------------+ | tables | +--------------------------------------------+ | global_variables | | mysql_collations | | mysql_group_replication_hostgroups | | mysql_query_rules | | mysql_query_rules_fast_routing | | mysql_replication_hostgroups | | mysql_servers | 插入監(jiān)控節(jié)點(diǎn)，master節(jié)點(diǎn)和slave節(jié)點(diǎn) | mysql_users | | proxysql_servers | | runtime_checksums_values | | runtime_global_variables | | runtime_mysql_group_replication_hostgroups | | runtime_mysql_query_rules | | runtime_mysql_query_rules_fast_routing | | runtime_mysql_replication_hostgroups | | runtime_mysql_servers | | runtime_mysql_users | | runtime_proxysql_servers | | runtime_scheduler | | scheduler | +--------------------------------------------+ 20 rows in set (0.00 sec)
4.在mysql_servers表中有幾個重要的屬性：
hostgroup_id：組ID，用于區(qū)分master和slave；
hostname：后端master和slave的IP地址；
port：后端master和slave的IP端口，默認(rèn)3306；
MySQL [main]> insert into mysql_servers (hostgroup_id,hostname,port) values (10,'172.16.75.4',3306),(20,'172.16.75.3',3306); Query OK, 2 rows affected (0.02 sec) MySQL [main]> load mysql servers to runtime; Query OK, 0 rows affected (0.01 sec) MySQL [main]> save mysql servers to disk; Query OK, 0 rows affected (0.06 sec)
注意：
1.設(shè)置的master的hostgroup_id為10（寫組）；
2.設(shè)置的slave的hostgroup_id為20（讀組）；
3.每次在proxysql執(zhí)行完操作之后，需要手動加載至內(nèi)存上，然后手動保存至磁盤上，表名中的”_”改為空格； mysql_servers à mysql servers
5.在master上授權(quán)一個監(jiān)控用戶，用于監(jiān)控后端的節(jié)點(diǎn)（注意：是master的mysql，不是Proxysql）；
這個用戶需要的權(quán)限：replication client和replication slave
MariaDB [(none)]> grant replication client,replication slave on *.* to 'monitor'@'%' identified by '123456'; Query OK, 0 rows affected (0.02 sec)
6.在proxysql上加入該節(jié)點(diǎn)；
MySQL [main]> set mysql-monitor_username='monitor'; Query OK, 1 row affected (0.00 sec) MySQL [main]> set mysql-monitor_password='123456'; Query OK, 1 row affected (0.00 sec) MySQL [main]> load mysql variables to runtime; Query OK, 0 rows affected (0.00 sec) MySQL [main]> save mysql variables to disk; Query OK, 95 rows affected (0.03 sec)
通過查看表mysql_server_ping_log了解后端云服務(wù)器狀態(tài)信息；
MySQL [main]> select * from mysql_server_ping_log limit 1,10; +-------------+------+------------------+----------------------+----------------------------------------------------------------------+ | hostname | port | time_start_us | ping_success_time_us | ping_error | +-------------+------+------------------+----------------------+----------------------------------------------------------------------+ | 172.16.75.4 | 3306 | 1541505676594192 | 0 | Access denied for user 'monitor'@'172.16.75.4' (using password: YES) | | 172.16.75.3 | 3306 | 1541505686592082 | 0 | Host '172.16.75.4' is not allowed to connect to this MariaDB server | | 172.16.75.4 | 3306 | 1541505686594872 | 0 | Access denied for user 'monitor'@'172.16.75.4' (using password: YES) | | 172.16.75.3 | 3306 | 1541505696592635 | 0 | Host '172.16.75.4' is not allowed to connect to this MariaDB server | | 172.16.75.4 | 3306 | 1541505696595442 | 0 | Access denied for user 'monitor'@'172.16.75.4' (using password: YES) | | 172.16.75.3 | 3306 | 1541505706593101 | 0 | Host '172.16.75.4' is not allowed to connect to this MariaDB server | | 172.16.75.4 | 3306 | 1541505706596427 | 0 | Access denied for user 'monitor'@'172.16.75.4' (using password: YES) | | 172.16.75.3 | 3306 | 1541505716593471 | 0 | Host '172.16.75.4' is not allowed to connect to this MariaDB server | | 172.16.75.4 | 3306 | 1541505716596416 | 0 | Access denied for user 'monitor'@'172.16.75.4' (using password: YES) | | 172.16.75.3 | 3306 | 1541505726593810 | 0 | Host '172.16.75.4' is not allowed to connect to this MariaDB server | +-------------+------+------------------+----------------------+----------------------------------------------------------------------+ 10 rows in set (0.00 sec)
7.對后端定義的云服務(wù)器的分組進(jìn)行讀組和寫組的設(shè)定，mysql_replication_hostgroups表中添加定義即可，通過查看monitor庫中的mysql_server_read_only_log表查看后端節(jié)點(diǎn)是否具有read_only權(quán)限；
MySQL [main]> insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup) values (10,20); Query OK, 1 row affected (0.00 sec) MySQL [main]> load mysql servers to runtime; Query OK, 0 rows affected (0.00 sec) MySQL [main]> save mysql servers to disk; Query OK, 0 rows affected (0.03 sec) MySQL [main]> select * from monitor.mysql_server_read_only_log limit 3; +-------------+------+------------------+-----------------+-----------+-------+ | hostname | port | time_start_us | success_time_us | read_only | error | +-------------+------+------------------+-----------------+-----------+-------+ | 172.16.75.4 | 3306 | 1541506648164762 | 766 | 0 | NULL | | 172.16.75.3 | 3306 | 1541506648162822 | 3585 | 1 | NULL | | 172.16.75.3 | 3306 | 1541506649664049 | 993 | 1 | NULL | +-------------+------+------------------+-----------------+-----------+-------+ 3 rows in set (0.00 sec)
8.至此，基本配置完畢，我們在后端的master上創(chuàng)建兩個用戶賬戶，在proxysql上添加不同的hostgroup_id，完成基于不同用戶之間進(jìn)行讀寫分離；
Master mysql：
MariaDB [(none)]> grant all on *.* to 'reader'@'%' identified by '123456'; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> grant all on *.* to 'writer'@'%' identified by '123456'; Query OK, 0 rows affected (0.00 sec)
Proxysql：
重慶分公司
重慶分公司
MySQL [main]> insert into mysql_users (username,password,default_hostgroup) values ('reader','123456',20),('writer','123456',10);
Query OK, 2 rows affected (0.00 sec)
MySQL [main]> load mysql users to runtime;
Query OK, 0 rows affected (0.01 sec)
MySQL [main]> save mysql users to disk;
Query OK, 0 rows affected (0.03 sec)
MySQL [main]> select * from mysql_users\G
*************************** 1. row ***************************
username: reader
password: 123456
active: 1
use_ssl: 0
default_hostgroup: 20
default_schema: NULL
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 1
frontend: 1
max_connections: 10000
*************************** 2. row ***************************
username: writer
password: 123456
active: 1
use_ssl: 0
default_hostgroup: 10
default_schema: NULL
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 1
frontend: 1
max_connections: 10000
2 rows in set (0.00 sec)8.任意一臺主機(jī)，測試基于用戶的讀寫分離機(jī)制；
[root@slave2 ~]# mysql -uwriter -h272.16.75.4 -P6033 -p123456 -e 'select @@server_id'; +-------------+ | @@server_id | +-------------+ | 401 | +-------------+ [root@slave2 ~]# mysql -ureader -h272.16.75.4 -P6033 -p123456 -e 'select @@server_id'; +-------------+ | @@server_id | +-------------+ | 301 | +-------------+
9.基于SQL語句實(shí)現(xiàn)讀寫分離；
需要在mysql_query_rules表中添加兩條正則表達(dá)式的規(guī)則；
MySQL [main]> insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) values (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1); Query OK, 2 rows affected (0.00 sec) MySQL [main]> load mysql query rules to runtime; Query OK, 0 rows affected (0.00 sec) MySQL [main]> save mysql query rules to disk; Query OK, 0 rows affected (0.02 sec)
10.任意一臺主機(jī)測試基于SQL語句讀寫分離的機(jī)制；
[root@slave2 ~]# mysql -ureader -h272.16.75.4 -P6033 -p123456 -e 'set @@autocommit=0;\ start transaction;\ use hellodb;\ insert into coc (ID,ClassID,CourseID) values (100,100,100);\ select @@server_id;\ commit;' +-------------+ | @@server_id | +-------------+ | 301 | +-------------+
Master中的hellodb數(shù)據(jù)庫進(jìn)行查看；
MariaDB [hellodb]> select * from coc; +----+---------+----------+ | ID | ClassID | CourseID | +----+---------+----------+ | 1 | 1 | 2 | | 2 | 1 | 5 | | 3 | 2 | 2 | | 4 | 2 | 6 | | 5 | 3 | 1 | | 6 | 3 | 7 | | 7 | 4 | 5 | | 8 | 4 | 2 | | 9 | 5 | 1 | | 10 | 5 | 9 | | 11 | 6 | 3 | | 12 | 6 | 4 | | 13 | 7 | 4 | | 14 | 7 | 3 | +----+---------+----------+ 14 rows in set (0.00 sec) MariaDB [hellodb]> select * from coc; +-----+---------+----------+ | ID | ClassID | CourseID | +-----+---------+----------+ | 1 | 1 | 2 | | 2 | 1 | 5 | | 3 | 2 | 2 | | 4 | 2 | 6 | | 5 | 3 | 1 | | 6 | 3 | 7 | | 7 | 4 | 5 | | 8 | 4 | 2 | | 9 | 5 | 1 | | 10 | 5 | 9 | | 11 | 6 | 3 | | 12 | 6 | 4 | | 13 | 7 | 4 | | 14 | 7 | 3 | | 100 | 100 | 100 | +-----+---------+----------+ 15 rows in set (0.00 sec)
看完以上關(guān)于ProxySQL如何幫助MySQL實(shí)行讀寫分離，很多讀者朋友肯定多少有一定的了解，如需獲取更多的行業(yè)知識信息 ，可以持續(xù)關(guān)注我們的行業(yè)資訊欄目的。
另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務(wù)可用性高、性價比高”等特點(diǎn)與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場景需求。
