proxysql的安装过程
发布时间：2022-01-19 13:21:07 所属栏目：MySql教程 来源：互联网
导读：本篇内容介绍了proxysql的安装步骤的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！ MySQL环境信息: 操作系统：CentOS release 6.9 主库: 192.168.140.
|
本篇内容介绍了“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的安装步骤”的内容就介绍到这里了，感谢大家的阅读。 （编辑：好传媒网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
