ProxySQL讀寫分離從配置到使用

在美團點評DBProxy讀寫分離使用說明文章中已經說明了使用目的，本文介紹ProxySQL的使用方法以及和DBProxy的效能差異。具體的介紹可以看官網的相關說明，而這個中間件也是percona推的一款中間件。其特性和其他讀寫分離的中間件差距不大，具體的會在文中介紹。本文大致簡單的介紹在使用過程中的一些說明，也可以看官方的wiki獲得使用幫助。

環境： 

Distributor ID：    Ubuntu
Description：    Ubuntu 14.04.5 LTS
Release：   14.04#codename： 
percona網站：

https：//www.percona.com/downloads/proxysql/

github/官網：

https：//github .com/sysown/proxysql/releases

#我們先來看看自己的環境：

MHA已經建置：

master：172.16.16.35:3306slave：172.16.16.35:3307slave：172.16.16.34:3307

MHA manager在172.16.16.34，設定檔如下：

#

[root@localhost bin]# cat /etc/masterha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1.log
master_binlog_dir=/home/mysql/db3306/log/master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
password=123456ping_interval=1remote_workdir=/tmp
repl_password=123456repl_user=root
report_script=/usr/local/bin/send_report
shutdown_script=""ssh_user=root
user=root
[server1]
hostname=172.16.16.35port=3306[server2]
candidate_master=1check_repl_delay=0hostname=172.16.16.34port=3306[server3]
hostname=172.16.16.35port=3307

 

下面我們基於這樣一套MHA環境建構讀寫分離。

1：安裝ProxySQL軟體，這個我們部署到172.16.16.34上

[root@localhost bin]# sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm[root@localhost bin]# yum install proxysql

 

#最後有以下提示：

Installed:
proxysql.x86_64 0:1.3.7-1.1.el6
Complete!

 

#也就是安裝完成了。然後查一下具體的檔案：

[root@localhost bin]# find / -name proxysql/var/lib/proxysql/var/run/proxysql/etc/rc.d/init.d/proxysql/usr/bin/proxysql

 

#發現確實已經將ProxySQL安裝成功了

2 ：啟動設定ProxySQL

看一下設定檔：

[root@localhost bin]# cat /etc/proxysql-admin.cnf
# proxysql admin interface credentials.
export PROXYSQL_USERNAME="admin"export PROXYSQL_PASSWORD="admin"export PROXYSQL_HOSTNAME="localhost"export PROXYSQL_PORT="6032"
 # PXC admin credentials for connecting to pxc-cluster-node.
export CLUSTER_USERNAME="admin"export CLUSTER_PASSWORD="admin"export CLUSTER_HOSTNAME="localhost"export CLUSTER_PORT="3306"
 # proxysql monitoring user. proxysql admin script will create this user in pxc to monitor pxc-nodes.
export MONITOR_USERNAME="monitor"export MONITOR_PASSWORD="monit0r"
 # Application user to connect to pxc-node through proxysql
export CLUSTER_APP_USERNAME="proxysql_user"export CLUSTER_APP_PASSWORD="passw0rd"
 # ProxySQL read/write hostgroup
export WRITE_HOSTGROUP_ID="10"export READ_HOSTGROUP_ID="11"
 # ProxySQL read/write configuration mode.
export MODE="singlewrite"

 

啟動：

#

[root@localhost bin]# proxysql-admin --config-file=/etc/proxysql-admin.cnf --enable
This script will assist with configuring ProxySQL (currently only Percona XtraDB cluster in combination with ProxySQL is supported)
ProxySQL read/write configuration mode is singlewrite
ProxySQL is not running; please start the proxysql service

 

現在來說ProxySQL 的路由已經啟動，提示我們要啟動proxysql service

[root@localhost bin]# service proxy
proxy proxysql proxysql-admin proxysql_galera_checker proxysql_node_monitor
[root@localhost bin]# service proxysql start
Starting ProxySQL: DONE![root@localhost bin]# mysql -uadmin -padmin -h127.0.0.1 -P6032
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1Server version: 5.7.14 (ProxySQL Admin Module)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

 

可以看到我們已經登入成功了，這裡要說明的是proxysql的預設設定檔是在：

[root@localhost bin]# find / -name proxysql.cnf/etc/proxysql.cnf

 

接下來我們開始設定ProxySQL：

[root@localhost bin]# mysql -uadmin -padmin -h127.0.0.1 -P6032
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2Server version: 5.7.14 (ProxySQL Admin Module)
 
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> show databases;+-----+---------+-------------------------------+
| seq | name | file |
+-----+---------+-------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
+-----+---------+-------------------------------+4 rows in set (0.00 sec)
 
mysql> use admin
Database changed
mysql> show tables;+--------------------------------------+
| tables |
+--------------------------------------+
| global_variables |
| mysql_collations |
| mysql_query_rules |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| runtime_global_variables |
| runtime_mysql_query_rules |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers |
| runtime_mysql_users |
| runtime_scheduler |
| scheduler |
+--------------------------------------+13 rows in set (0.00 sec)

# 

下面加入主從訊息：

mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(100,'172.16.16.35',3306,1,1000,10,'test');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(101,'172.16.16.34',3306,1,1000,10,'test');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(101,'172.16.16.35',3307,1,1000,10,'test');
Query OK, 1 row affected (0.00 sec)
mysql> select * from mysql_servers;+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 100 | 172.16.16.35 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | test |
| 101 | 172.16.16.34 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | test |
| 101 | 172.16.16.35 | 3307 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | test |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+3 rows in set (0.00 sec)

 

然後加入讀寫映射（主要是MHA後端切換的時候保證ProxySQL也能夠自動切換）：

#

mysql> insert into mysql_replication_hostgroups values(100,101,'masterha') ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from mysql_replication_hostgroups;+------------------+------------------+----------+
| writer_hostgroup | reader_hostgroup | comment |
+------------------+------------------+----------+
| 100 | 101 | masterha |
+------------------+------------------+----------+1 row in set (0.00 sec)

 

為ProxySQL新增監控帳號：

mysql> GRANT SUPER, REPLICATION CLIENT ON *.* TO 'proxysql'@'172.16.16.%' IDENTIFIED BY 'proxysql';
Query OK, 0 rows affected, 1 warning (0.09 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.10 sec)

 

#設定監控帳號（在proxySQL當中進行設定）：

##

mysql> set mysql-monitor_username='proxysql';
Query OK, 1 row affected (0.00 sec)
mysql> set mysql-monitor_password='proxysql';
Query OK, 1 row affected (0.00 sec)
mysql> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql variables to disk;
Query OK, 74 rows affected (0.02 sec)

 

PS:有時候runtime_mysql_servers的status不為ONLINE狀態的話可以透過查看monitor.mysql_server_ping_log這個表來查看具體的報錯資訊。

mysql> select * from monitor.mysql_server_ping_log;

然後配置程式帳號，簡單點統一使用root：123456最高權限來設定：

mysql> insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('root','123456',1,100,1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from mysql_users;+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| root | 123456 | 1 | 0 | 100 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+1 row in set (0.00 sec)

 

設定完成以後開始重新載入並儲存我們的設定：

##

mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)
mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.08 sec)
mysql> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql users to disk;
Query OK, 0 rows affected (0.03 sec)

#接下來開始設定路由規則：

mysql> INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',100,1);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',101,1);
Query OK, 1 row affected (0.00 sec)
mysql> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
mysql> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.04 sec)

# 

至此設定已經完成了

3：測試讀寫分離

在172.16.16.35上連結proxySQL埠6033，並且做簡單的select操作：

#

[root@localhost ~]# mysql -uroot -p123456 -h172.16.16.34 -P6033
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22Server version: 5.7.14 (ProxySQL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from maxiangqian.test;+-----+------+
| id | name |
+-----+------+
| 1 | qq |
| 2 | qq |
| 4 | aa |
| 11 | a |
| 111 | a |
+-----+------+5 rows in set (0.04 sec)

 

然後在進行查詢看一下：

mysql> select * from stats_mysql_query_digest;

可以看到已經完成了讀寫分離了。

mysql> select @@server_id;+-------------+
| @@server_id |
+-------------+
| 353307 |
+-------------+1 row in set (0.01 sec)

 

看server ID已經路由到了172.16.16.35:3307這個從庫上了。

測一下for update：

mysql> select * from maxiangqian.test for update;+-----+------+
| id | name |
+-----+------+
| 1 | qq |
| 2 | qq |
| 4 | aa |
| 11 | a |
| 111 | a |
+-----+------+5 rows in set (0.00 sec)

# 

再查看

##

mysql> select * from stats_mysql_query_digest;

已經自動路由到了主庫。

至此ProxySQL+MySQL MHA讀寫分離測試完成。 。 。未完待續

5：關於ProxySQL的思考以及簡單的指令

#

ProxySQL透過以上方式是可以實現讀寫分離，但是這種方式真的就沒有問題了嗎，如果是一些比如查詢訂單狀態的這種要求實時性非常高的SQL的話，似乎被路由到了從庫就會出現BUG。我們可以選擇在程式端控制這些參數，ProxySQL只作為一個負載平衡來使用，為ProxySQL建立多個帳號，一個讀寫，一個只讀。然後程式去實作讀寫分離。

ProxySQL是分三層設計運行的，分別為RUNTIME ，MEMORY ，DISK ：

RUNTIME 代表的是ProxySQL目前生效的配置，包括 global_variables, mysql_servers, mysql_users, mysql_query_rules。無法直接修改這裡的配置，必須從下一層load進來。

MEMORY 是平時在mysql命令列修改的main 裡頭配置，可以認為是SQLite資料庫在記憶體的鏡像

DISK / CONFIG FILE 持久儲存的那份配置，一般在$(DATADIR )/proxysql.db，在重啟的時候會從硬碟載入。 /etc/proxysql.cnf檔案只在第一次初始化的時候用到，完了後，如果要修改監聽端口，還是需要在管理命令行裡修改，再save 到硬碟

常用命令：

LOAD MYSQL SERVERS TO RUNTIME -- 讓修改後的配置生效，也就是從MEMORY 把參數LOAD過來，等價於LOAD MYSQL USERS FROM MEMORY，這個語句的語法比較單間，FROM代表從上層LOAD過來， TO代表從本層到某層。例如前面我們設定了MySQL的監控帳號，但還是要執行LOAD和SAVE儲存變數並且讓變數生效。

 

以上是ProxySQL讀寫分離從配置到使用的詳細內容。更多資訊請關注PHP中文網其他相關文章！