溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容介紹了“proxysql的安裝步驟”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！
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的安裝步驟”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
