proxysql的安装步骤
本篇内容介绍了"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)
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的安装步骤"的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站，小编将为大家输出更多高质量的实用文章！
