ProxySQL如何幫助MySQL實(shí)行讀寫分離-創(chuàng)新互聯(lián)

本文主要給大家介紹ProxySQL如何幫助MySQL實(shí)行讀寫分離，文章內(nèi)容都是筆者用心摘選和編輯的，具有一定的針對(duì)性，對(duì)大家的參考意義還是比較大的，下面跟筆者一起了解下ProxySQL如何幫助MySQL實(shí)行讀寫分離吧。

從策劃到設(shè)計(jì)制作，每一步都追求做到細(xì)膩，制作可持續(xù)發(fā)展的企業(yè)網(wǎng)站。為客戶提供成都網(wǎng)站制作、成都網(wǎng)站設(shè)計(jì)、網(wǎng)站策劃、網(wǎng)頁(yè)設(shè)計(jì)、域名注冊(cè)、雅安服務(wù)器托管、網(wǎng)絡(luò)營(yíng)銷、VI設(shè)計(jì)、網(wǎng)站改版、漏洞修補(bǔ)等服務(wù)。為客戶提供更好的一站式互聯(lián)網(wǎng)解決方案,以客戶的口碑塑造優(yōu)易品牌,攜手廣大客戶,共同發(fā)展進(jìn)步。

ProxySQL是一個(gè)基于C++開發(fā)的高性能輕量級(jí)產(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安裝即可，然后啟動(dòng)，啟動(dòng)端口為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庫(kù)和monitor庫(kù)；

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表中有幾個(gè)重要的屬性：

hostgroup_id：組ID，用于區(qū)分master和slave；

hostname：后端master和slave的IP地址；

port：后端master和slave的IP端口，默認(rèn)3306；

ProxySQL如何幫助MySQL實(shí)行讀寫分離

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í)行完操作之后，需要手動(dòng)加載至內(nèi)存上，然后手動(dòng)保存至磁盤上，表名中的”_”改為空格； mysql_servers à mysql servers

5.在master上授權(quán)一個(gè)監(jiān)控用戶，用于監(jiān)控后端的節(jié)點(diǎn)（注意：是master的mysql，不是Proxysql）；

這個(gè)用戶需要的權(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.對(duì)后端定義的云服務(wù)器的分組進(jìn)行讀組和寫組的設(shè)定，mysql_replication_hostgroups表中添加定義即可，通過查看monitor庫(kù)中的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.至此，基本配置完畢，我們?cè)诤蠖说膍aster上創(chuàng)建兩個(gè)用戶賬戶，在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.任意一臺(tái)主機(jī)，測(cè)試基于用戶的讀寫分離機(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.任意一臺(tái)主機(jī)測(cè)試基于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ù)庫(kù)進(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è)知識(shí)信息，可以持續(xù)關(guān)注我們的行業(yè)資訊欄目的。

另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無理由+7*72小時(shí)售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國(guó)服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡(jiǎn)單易用、服務(wù)可用性高、性價(jià)比高”等特點(diǎn)與優(yōu)勢(shì)，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場(chǎng)景需求。

文章標(biāo)題：ProxySQL如何幫助MySQL實(shí)行讀寫分離-創(chuàng)新互聯(lián)
網(wǎng)址分享：http://bm7419.com/article0/ihhoo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站排名、域名注冊(cè)、企業(yè)網(wǎng)站制作、品牌網(wǎng)站制作、商城網(wǎng)站、小程序開發(fā)

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容