proxysql的安装步骤

48次阅读

没有评论

共计 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;

+————–+—————-+——+——–+——–+————-+—————–+———————+———+—————-+———+

+————–+—————-+——+——–+——–+————-+—————–+———————+———+—————-+———+

| 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;

+—————-+——+——————+————————-+—————+

+—————-+——+——————+————————-+—————+

| 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;

+————–+—————-+——+——–+——–+————-+—————–+———————+———+—————-+———+

+————–+—————-+——+——–+——–+————-+—————–+———————+———+—————-+———+

| 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 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.

+———–+——+——+———–+————————————–+

+———–+——+——+———–+————————————–+

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

正文完

发表至：数据库

2023-08-01

转载说明：除特殊说明外本站除技术相关以外文章皆由网络搜集发布，转载请注明出处。

如何快速看懂MySQL执行计划

mysql如何增加用户

PostgreSQL中不同数据类型对查询性能的影响有哪些

Cassandra支持哪些复杂查询操作

在MAC下配置mysql数据库出现乱码怎么解决