共计 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 小编将为大家输出更多高质量的实用文章！
