溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本文主要給大家介紹MySQL中通過ProxySQL的使用來實現讀寫分離，文章內容都是筆者用心摘選和編輯的，具有一定的針對性，對大家的參考意義還是比較大的，下面跟筆者一起了解下MySQL中通過ProxySQL的使用來實現讀寫分離吧。
ProxySQL是一個高性能的MySQL中間件，擁有強大的規則引擎。
官方文檔：https://github.com/sysown/proxysql/wiki/
下載地址：https://github.com/sysown/proxysql/releases/
# 配ProxySQL源 [root@ProxySQL ~]# cat <<EOF | tee /etc/yum.repos.d/proxysql.repo [proxysql_repo] name= ProxySQL baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever gpgcheck=1 gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key EOF # 安裝 [root@ProxySQL ~]# yum install proxysql -y # 記一次安裝依賴： perl-Compress-Raw-Bzip2 perl-Compress-Raw-Zlib perl-DBD-MySQL perl-DBI perl-IO-Compress perl-Net-Daemon perl-PlRPC #安裝生成的文件： [root@ProxySQL ~]# rpm -ql proxysql /etc/init.d/proxysql # 啟動腳本 /etc/proxysql.cnf # 配置文件，僅在第一次(/var/lib/proxysql/proxysql.db文件不存在)啟動時有效 # 啟動后可以在proxysql管理端中通過修改數據庫的方式修改配置并生效(官方推薦方式。) /usr/bin/proxysql #主程序文件 /usr/share/proxysql/tools/proxysql_galera_checker.sh /usr/share/proxysql/tools/proxysql_galera_writer.pl
github
官網
[root@ProxySQL ~]# egrep -v "^#|^$" /etc/proxysql.cnf
datadir="/var/lib/proxysql"
admin_variables=
{
admin_credentials="admin:admin" # 定義連接管理端口的用戶名和密碼
mysql_ifaces="0.0.0.0:6032" # 定義管理端口6032；用來連接proxysql的管理數據庫，修改proxysql服務的設置以及路由策略
}
mysql_variables=
{
threads=4 # 定義每個轉發端口開啟多少個線程
max_connections=2048
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
interfaces="0.0.0.0:6033" # 定義轉發端口6033；用來連接后端的mysql實例，起到代理轉發的作用；
default_schema="information_schema"
stacksize=1048576
server_version="5.7.22" # 設置后端mysql實例的版本號，僅起到comment的作用
connect_timeout_server=3000
monitor_username="monitor"
monitor_password="monitor"
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}
mysql_servers =
(
)
mysql_users:
(
)
mysql_query_rules:
(
)
scheduler=
(
)
mysql_replication_hostgroups=
(
)
[root@ProxySQL ~]# sed -i 's#5.5.30#5.7.22#g' /etc/proxysql.cnf # 把5.5.30改為自己的版本[root@ProxySQL ~]# chkconfig proxysql on # 添加到開機自啟動，默認已添加 [root@ProxySQL ~]# chkconfig --list |grep proxysql # 查看是否開機自啟動 Note: This output shows SysV services only and does not include native systemd services. SysV configuration data might be overridden by native systemd configuration. If you want to list systemd services use 'systemctl list-unit-files'. To see services enabled on particular target use 'systemctl list-dependencies [target]'. proxysql 0:off 1:off 2:on 3:on 4:on 5:on 6:off
默認情況下，rpm安裝的ProxySQL只提供了SysV風格的服務腳本/etc/init.d/proxysql。
所以，可通過該腳本管理ProxySQL的啟動、停止等功能。
[root@ProxySQL ~]# /etc/init.d/proxysql --help
Usage: ProxySQL {start|stop|status|reload|restart|initial}
# 啟動
[root@ProxySQL ~]# service proxysql start
Starting ProxySQL: DONE!
# 查看
[root@tcloud-113 ~]# service proxysql status
ProxySQL is running (30422).
# 啟動后會監聽兩個端口，默認為6032和6033。6032端口是ProxySQL的管理端口，6033是ProxySQL對外提供服務的端口。
[root@ProxySQL ~]# ss -lntup |grep proxysql
tcp LISTEN 0 128 *:6032 *:* users:(("proxysql",1322,20))
tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",1322,19))
tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",1322,18))
tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",1322,17))
tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",1322,16))
# 可以看到轉發端口的6033開啟了4個線程，線程數由全局變量"threads"控制，受cpu物理核心數的影響（每個端口下的線程數<=cpu物理核心數）如果想要通過systemd管理ProxySQL，可在/usr/lib/systemd/system/proxysql.service中寫入如下內容：
[root@ProxySQL ~]# vim /usr/lib/systemd/system/proxysql.service [Unit] Description=High Performance Advanced Proxy for MySQL After=network.target [Service] Type=simple User=mysql Group=mysql PermissionsStartOnly=true LimitNOFILE=102400 LimitCORE=1073741824 ExecStartPre=/bin/mkdir -p /var/lib/proxysql ExecStartPre=/bin/chown mysql:mysql -R /var/lib/proxysql /etc/proxysql.cnf ExecStart=/usr/bin/proxysql -f Restart=always [root@ProxySQL ~]#
一般來說，ProxySQL很少停止或重啟，因為絕大多數配置都可以在線修改。
例如：
user：proxysql；
password：pwproxysql
mysql> GRANT ALL ON *.* TO 'proxysql'@'192.168.1.%' IDENTIFIED BY 'pwproxysql';
[root@ProxySQL ~]# yum install mysql -y # 安裝mysql客戶端命令；依賴：mysql-libs [root@ProxySQL ~]# export MYSQL_PS1="(\u@\h:\p) [\d]> " [root@ProxySQL ~]# mysql -uadmin -padmin -h227.0.0.1 -P6032 # 默認的用戶名密碼都是 admin。 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2013, 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. (admin@127.0.0.1:6032) [(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)
庫說明：
(admin@127.0.0.1:6032) [(none)]> show tables from main; +--------------------------------------------+ | tables | +--------------------------------------------+ | global_variables | # ProxySQL的基本配置參數，類似與MySQL | mysql_collations | # 配置對MySQL字符集的支持 | mysql_group_replication_hostgroups | # MGR相關的表，用于實例的讀寫組自動分配 | mysql_query_rules | # 路由表 | mysql_query_rules_fast_routing | # 主從復制相關的表，用于實例的讀寫組自動分配 | mysql_replication_hostgroups | # 存儲MySQL實例的信息 | mysql_servers | # 現階段存儲MySQL用戶，當然以后有前后端賬號分離的設想 | mysql_users | # 存儲ProxySQL的信息，用于ProxySQL Cluster同步 | 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)
runtime_開頭的是運行時的配置，這些是不能修改的。要修改ProxySQL的配置，需要修改了非runtime_表，修改后必須執行LOAD ... TO RUNTIME才能加載到RUNTIME生效，執行save ... to disk才能將配置持久化保存到磁盤。
下面語句中沒有先切換到main庫也執行成功了，因為ProxySQL內部使用的SQLite3數據庫引擎，和MySQL的解析方式是不一樣的。即使執行了USE main語句也是無任何效果的，但不會報錯。
使用insert語句添加mysql主機到mysql_servers表中，其中：hostgroup_id 1 表示寫組，2表示讀組。
(admin@127.0.0.1:6032) [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'192.168.1.102',3306,1,'Write Group'); Query OK, 1 row affected (0.00 sec) (admin@127.0.0.1:6032) [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'192.168.1.103',3306,1,'Read Group'); Query OK, 1 row affected (0.00 sec) (admin@127.0.0.1:6032) [(none)]> select * from mysql_servers; +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+ | 1 | 192.168.1.102 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Write Group | | 2 | 192.168.1.103 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Read Group | +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+ 2 rows in set (0.00 sec)
修改后，加載到RUNTIME，并保存到disk。
(admin@127.0.0.1:6032) [(none)]> load mysql servers to runtime; (admin@127.0.0.1:6032) [(none)]> save mysql servers to disk;
在proxysql主機的mysql_users表中添加剛才創建的賬號，proxysql客戶端需要使用這個賬號來訪問數據庫。
default_hostgroup默認組設置為寫組，也就是1;
當讀寫分離的路由規則不符合時，會訪問默認組的數據庫;
(admin@127.0.0.1:6032) [(none)]> insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('proxysql','pwproxysql',1,1);
Query OK, 1 row affected (0.00 sec)
(admin@127.0.0.1:6032) [(none)]> select * from mysql_users \G
*************************** 1. row ***************************
username: proxysql # 后端mysql實例的用戶名
password: pwproxysql # 后端mysql實例的密碼
active: 1 # active=1表示用戶生效，0表示不生效
use_ssl: 0
default_hostgroup: 1 # 用戶默認登錄到哪個hostgroup_id下的實例
default_schema: NULL # 用戶默認登錄后端mysql實例時連接的數據庫，這個地方為NULL的話，則由全局變量mysql-default_schema決定，默認是information_schema
schema_locked: 0
transaction_persistent: 1 # 如果設置為1，連接上ProxySQL的會話后，如果在一個hostgroup上開啟了事務，那么后續的sql都繼續維持在這個hostgroup上，不倫是否會匹配上其它路由規則，直到事務結束。雖然默認是0
fast_forward: 0 # 忽略查詢重寫/緩存層，直接把這個用戶的請求透傳到后端DB。相當于只用它的連接池功能，一般不用，路由規則 .* 就行了
backend: 1
frontend: 1
max_connections: 10000 # #該用戶允許的最大連接數
1 row in set (0.00 sec)修改后，加載到RUNTIME，并保存到disk。
(admin@127.0.0.1:6032) [(none)]> load mysql users to runtime; (admin@127.0.0.1:6032) [(none)]> save mysql users to disk;
首先在后端master節點上創建一個用于監控的用戶名(只需在master上創建即可，因為會復制到slave上)，這個用戶名只需具有USAGE權限即可。如果還需要監控復制結構中slave是否嚴重延遲于master(先混個眼熟：這個俗語叫做"拖后腿"，術語叫做"replication lag")，則還需具備replication client權限。這里直接賦予這個權限。
mysql> GRANT replication client ON *.* TO 'monitor'@'192.168.1.%' IDENTIFIED BY 'monitor';
(admin@127.0.0.1:6032) [(none)]> set mysql-monitor_username='monitor'; Query OK, 1 row affected (0.00 sec) (admin@127.0.0.1:6032) [(none)]> set mysql-monitor_password='monitor'; Query OK, 1 row affected (0.00 sec)
以上設置實際上是在修改global_variables表，它和下面兩個語句是等價的：
(admin@127.0.0.1:6032) [(none)]> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username'; Query OK, 1 row affected (0.00 sec) (admin@127.0.0.1:6032) [(none)]> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password'; Query OK, 1 row affected (0.00 sec)
修改后，加載到RUNTIME，并保存到disk。
(admin@127.0.0.1:6032) [(none)]> load mysql variables to runtime; (admin@127.0.0.1:6032) [(none)]> save mysql variables to disk;
(admin@127.0.0.1:6032) [(none)]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',1,1); Query OK, 1 row affected (0.00 sec) (admin@127.0.0.1:6032) [(none)]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',2,1); Query OK, 1 row affected (0.00 sec) (admin@127.0.0.1:6032) [(none)]> select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules; +---------+--------+----------------------+-----------------------+-------+ | rule_id | active | match_digest | destination_hostgroup | apply | +---------+--------+----------------------+-----------------------+-------+ | 1 | 1 | ^SELECT.*FOR UPDATE$ | 1 | 1 | | 2 | 1 | ^SELECT | 2 | 1 | +---------+--------+----------------------+-----------------------+-------+ 2 rows in set (0.00 sec)
(admin@127.0.0.1:6032) [(none)]> load mysql query rules to runtime; (admin@127.0.0.1:6032) [(none)]> load admin variables to runtime;
(admin@127.0.0.1:6032) [(none)]> save mysql query rules to disk; (admin@127.0.0.1:6032) [(none)]> save admin variables to disk;
登錄用戶是剛才我們在mysql_user表中創建的用戶，端口為6033
[root@centos7 ~]#mysql -uproxysql -ppwproxysql -h227.0.0.1 -P6033 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.5.30 (ProxySQL) Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) MySQL [(none)]>
創建兩個數據庫和查個表。
MySQL [(none)]> create database bigboss; Query OK, 1 row affected (0.01 sec) MySQL [(none)]> create database weijinyun; Query OK, 1 row affected (0.00 sec) MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | bigboss | | mysql | | performance_schema | | test | | weijinyun | +--------------------+ 6 rows in set (0.01 sec) MySQL [(none)]> select user,host from mysql.user; +-------------+---------------+ | user | host | +-------------+---------------+ | root | 127.0.0.1 | | monitor | 192.168.1.% | | proxysql | 192.168.1.% | | repliaction | 192.168.1.% | | root | ::1 | | | centos7 | | root | centos7 | | | localhost | | root | localhost | +-------------+---------------+ 9 rows in set (0.01 sec)
(admin@127.0.0.1:6032) [(none)]> select * from stats_mysql_query_digest; +-----------+--------------------+----------+--------------------+----------------------------------------+------------+------------+------------+----------+----------+----------+ | hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time| max_time | +-----------+--------------------+----------+--------------------+----------------------------------------+------------+------------+------------+----------+----------+----------+ | 2 | information_schema | proxysql | 0x3EA85877510AC608 | select * from stats_mysql_query_digest | 2 | 1527233735 | 1527233782 | 4092 | 792| 3300 | | 1 | information_schema | proxysql | 0x594F2C744B698066 | select USER() | 1 | 1527233378 | 1527233378 | 0 | 0| 0 | | 1 | information_schema | proxysql | 0x02033E45904D3DF0 | show databases | 2 | 1527233202 | 1527233495 | 5950 | 1974| 3976 | | 1 | information_schema | proxysql | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 2 | 1527233196 | 1527233378 | 0 | 0| 0 | +-----------+--------------------+----------+--------------------+----------------------------------------+------------+------------+------------+----------+----------+----------+ 4 rows in set (0.00 sec)(admin@127.0.0.1:6032) [(none)]>
看完以上關于MySQL中通過ProxySQL的使用來實現讀寫分離，很多讀者朋友肯定多少有一定的了解，如需獲取更多的行業知識信息 ，可以持續關注我們的行業資訊欄目的。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
