共計 12583 個字符，預計需要花費 32 分鐘才能閱讀完成。
本篇內容介紹了“proxysql 的安裝步驟”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！
MySQL 環境信息:
操作系統：CentOS release 6.9
主庫: 192.168.140.51
從庫: 192.168.140.52
從庫: 192.168.16.150
proxysql 中間件: 192.168.140.52
備注：兩個從庫都需要開啟 read_only=on, 命令為
mysql set global read_only=on
編輯 proxysql.repo 文件
#vi /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
#yum install proxysql -y
Loaded plugins: fastestmirror
Setting up Install Process
Determining fastest mirrors
epel/metalink | 8.9 kB 00:00
* base: mirrors.huaweicloud.com
* epel: mirrors.ustc.edu.cn
* extras: mirrors.huaweicloud.com
* updates: mirrors.163.com
base | 3.7 kB 00:00
epel | 3.2 kB 00:00
epel/primary | 3.2 MB 00:00
epel 12515/12515
extras | 3.4 kB 00:00
percona | 2.9 kB 00:00
percona/primary_db | 346 kB 00:09
percona-release-noarch | 2.9 kB 00:00
percona-release-x86_64 | 2.9 kB 00:00
percona-release-x86_64/primary_db | 346 kB 00:09
proxysql_repo | 2.9 kB 00:00
proxysql_repo/primary_db | 12 kB 00:00
updates | 3.4 kB 00:00
updates/primary_db | 1.3 MB 00:00
Resolving Dependencies
— Running transaction check
— Package proxysql.x86_64 0:1.4.12-1 will be installed
— Finished Dependency Resolution
Dependencies Resolved
==============================================================================================================================================
Package Arch Version Repository Size
==============================================================================================================================================
Installing:
proxysql x86_64 1.4.12-1 proxysql_repo 5.9 M
Transaction Summary
==============================================================================================================================================
Install 1 Package(s)
Total download size: 5.9 M
Installed size: 22 M
Downloading Packages:
proxysql-1.4.12-1-centos67.x86_64.rpm | 5.9 MB 00:09
warning: rpmts_HdrFromFdno: Header V4 RSA/SHA256 Signature, key ID 79953b49: NOKEY
Retrieving key from http://repo.proxysql.com/ProxySQL/repo_pub_key
Importing GPG key 0x79953B49:
Userid: rene cannnao (Proxysql Repository) rene.cannao@gmail.com
From : http://repo.proxysql.com/ProxySQL/repo_pub_key
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : proxysql-1.4.12-1.x86_64 1/1
Verifying : proxysql-1.4.12-1.x86_64 1/1
Installed:
proxysql.x86_64 0:1.4.12-1
Complete!
啟動 proxysql:
#service proxysql start
Starting ProxySQL: 2018-10-09 09:05:58 [INFO] Using config file /etc/proxysql.cnf
DONE!
查看版本信息:
#proxysql –version
ProxySQL version 1.4.12-9-g216b872, codename Truls
登陸 proxysql:
設置 prompt:
export MYSQL_PS1= \\u@\\h [\\d] \\r:\\m:\\s
進入 ProxySQL:
#mysql -uadmin -padmin -h227.0.0.1 -P6032
admin@127.0.0.1 [(none)] 05:27:35 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.01 sec)
備注:6032 是 proxysql 的管理端口號.
數據庫信息介紹:
main 內存配置數據庫，表里存放后端 db 實例、用戶驗證、路由規則等信息。
disk 是持久化到硬盤的配置。
stats 是統計信息的匯總，是 proxysql 運行抓取的統計信息，包括到后端各命令的執行次數、流量、processlist、查詢種類匯總 / 執行時間等。
monitor 是一些監控的收集信息，主要是對后端 db 的健康 / 延遲檢查等。
在 master 上添加 proxysql 監控賬號和對外訪問賬號：
mysql create user monitor@ 192.168.140.% identified by 123456
Query OK, 0 rows affected (0.29 sec)
mysql
mysql create user monitor@ 192.168.16.% identified by 123456
Query OK, 0 rows affected (0.01 sec)
mysql grant all privileges on *.* to monitor@ 192.168.140.%
Query OK, 0 rows affected (0.04 sec)
mysql grant all privileges on *.* to monitor@ 192.168.16.%
Query OK, 0 rows affected (0.00 sec)
mysql
mysql create user dsf@ % identified by dsf
Query OK, 0 rows affected (0.09 sec)
mysql grant all privileges on *.* to dsf@ % with grant option;
Query OK, 0 rows affected (0.06 sec)
mysql flush privileges;
Query OK, 0 rows affected (0.12 sec)
mysql use main
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql show tables;
+——————————————–+
| tables |
+——————————————–+
| global_variables |
| mysql_collations |
| mysql_group_replication_hostgroups |
| mysql_query_rules |
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers |
| 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)
mysql show create table mysql_servers \G
*************************** 1. row ***************************
table: mysql_servers
Create Table: CREATE TABLE mysql_servers (
hostgroup_id INT CHECK (hostgroup_id =0) NOT NULL DEFAULT 0,
hostname VARCHAR NOT NULL,
port INT NOT NULL DEFAULT 3306,
status VARCHAR CHECK (UPPER(status) IN (ONLINE , SHUNNED , OFFLINE_SOFT , OFFLINE_HARD)) NOT NULL DEFAULT ONLINE ,
weight INT CHECK (weight = 0) NOT NULL DEFAULT 1,
compression INT CHECK (compression =0 AND compression = 102400) NOT NULL DEFAULT 0,
max_connections INT CHECK (max_connections =0) NOT NULL DEFAULT 1000,
max_replication_lag INT CHECK (max_replication_lag = 0 AND max_replication_lag = 126144000) NOT NULL DEFAULT 0,
use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,
max_latency_ms INT UNSIGNED CHECK (max_latency_ms =0) NOT NULL DEFAULT 0,
comment VARCHAR NOT NULL DEFAULT ,
PRIMARY KEY (hostgroup_id, hostname, port) )
1 row in set (0.00 sec)
添加主從服務器信息列表:
mysql insert into mysql_servers(hostgroup_id,hostname,port) values(10, 192.168.140.51 ,3306);
Query OK, 1 row affected (0.00 sec)
mysql insert into mysql_servers(hostgroup_id,hostname,port) values(10, 192.168.140.52 ,3306);
Query OK, 1 row affected (0.00 sec)
mysql insert into mysql_servers(hostgroup_id,hostname,port) values(10, 192.168.16.150 ,3306);
Query OK, 1 row affected (0.00 sec)
從 memory 加載到 runtime:
mysql load mysql servers to runtime;
Query OK, 0 rows affected (0.05 sec)
持久化到磁盤:
mysql save mysql servers to disk;
Query OK, 0 rows affected (0.56 sec)
查看 server 狀態信息，三臺應該都是 online:
mysql select * from mysql_servers;
+————–+—————-+——+——–+——–+————-+—————–+———————+———+—————-+———+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+————–+—————-+——+——–+——–+————-+—————–+———————+———+—————-+———+
| 10 | 192.168.140.51 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | 192.168.140.52 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | 192.168.16.150 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+————–+—————-+——+——–+——–+————-+—————–+———————+———+—————-+———+
3 rows in set (0.00 sec)
為 proxysql 配置監控賬號：
mysql set mysql-monitor_username= monitor
Query OK, 1 row affected (0.00 sec)
mysql set mysql-monitor_password= 123456
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, 97 rows affected (0.29 sec)
查看監控信息, 監控正常，沒有任何報錯：
mysql select * from monitor.mysql_server_connect_log order by time_start_us desc limit 6;
+—————-+——+——————+————————-+—————+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+—————-+——+——————+————————-+—————+
| 192.168.140.51 | 3306 | 1539064061326592 | 3513 | NULL |
| 192.168.140.52 | 3306 | 1539064060571978 | 2129 | NULL |
| 192.168.16.150 | 3306 | 1539064059817210 | 3859 | NULL |
| 192.168.140.52 | 3306 | 1539064000942524 | 1271 | NULL |
| 192.168.140.51 | 3306 | 1539064000379889 | 3259 | NULL |
| 192.168.16.150 | 3306 | 1539063999817183 | 2875 | NULL |
+—————-+——+——————+————————-+—————+
6 rows in set (0.01 sec)
配置讀寫分離：
設置 proxysql 主從分組信息:
mysql show create table mysql_replication_hostgroups \G
*************************** 1. row ***************************
table: mysql_replication_hostgroups
Create Table: CREATE TABLE mysql_replication_hostgroups (
writer_hostgroup INT CHECK (writer_hostgroup =0) NOT NULL PRIMARY KEY,
reader_hostgroup INT NOT NULL CHECK (reader_hostgroup writer_hostgroup AND reader_hostgroup 0),
comment VARCHAR NOT NULL DEFAULT , UNIQUE (reader_hostgroup))
1 row in set (0.00 sec)
mysql insert into mysql_replication_hostgroups values(10,20, proxy info
Query OK, 1 row affected (0.00 sec)
mysql load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql
mysql save mysql servers to disk;
Query OK, 0 rows affected (0.47 sec)
mysql select * from mysql_replication_hostgroups;
+——————+——————+————+
| writer_hostgroup | reader_hostgroup | comment |
+——————+——————+————+
| 10 | 20 | proxy info |
+——————+——————+————+
1 row in set (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 |
+————–+—————-+——+——–+——–+————-+—————–+———————+———+—————-+———+
| 10 | 192.168.140.51 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 192.168.16.150 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 192.168.140.52 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+————–+—————-+——+——–+——–+————-+—————–+———————+———+—————-+———+
3 rows in set (0.00 sec)
備注:
proxysql 會根據 server 的 read_only 的值將服務器自動進行分組，read_only= 0 的分到編號為 10 的寫組，read_only= 1 的分到編號為 20 的讀組。
配置對外訪問賬號，開啟事務持久化保護:
mysql insert into mysql_users(username,password,default_hostgroup) values(dsf , dsf ,10);
Query OK, 1 row affected (0.00 sec)
mysql update mysql_users set transaction_persistent=1 where username= dsf
Query OK, 1 row affected (0.00 sec)
mysql load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql
mysql
mysql save mysql users to disk;
Query OK, 0 rows affected (0.10 sec)
驗證主服務器，需要指定對外端口號 6033:
#mysql -udsf -pdsf -h 192.168.140.52 -P 6033 -e show slave hosts
mysql: [Warning] Using a password on the command line interface can be insecure.
+———–+——+——+———–+————————————–+
| Server_id | Host | Port | Master_id | Slave_UUID |
+———–+——+——+———–+————————————–+
| 16150 | | 3306 | 14051 | e982cd68-cac0-11e8-8cfc-525400a6c4f1 |
| 14052 | | 3306 | 14051 | dab0225f-952d-11e8-ac10-52540098ed65 |
+———–+——+——+———–+————————————–+
#mysql -udsf -pdsf -h 192.168.140.52 -P 6033 -e select @@hostname
mysql: [Warning] Using a password on the command line interface can be insecure.
+——————–+
| @@hostname |
+——————–+
| test-140-51 |
+——————–+
“proxysql 的安裝步驟”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注丸趣 TV 網站，丸趣 TV 小編將為大家輸出更多高質量的實用文章！
