版权声明：本文为Buddy Yuan原创文章，转载请注明出处。原文地址：ProxySQL 2.0安装和Percona Cluster5.7集成
ProxySQL是MySQL的一款中间件产品，是灵活和强大的MySQL代理层，它可以实现像查询缓存，多路路由(multiplexing)，镜像，读/写分离，路由等。在ProxySQL的早期版本中并不能原生支持Galera支持，需要通过schedule调度脚本实现。但是在2.0版本后，就增加了原生支持，只需要配置好参数表即可。
首先我们来安装它来控制我们安装好的PXC集群。
IP地址 主机名
192.168.56.161 pxc1
192.168.56.162 pxc2
192.168.56.163 pxc3
192.168.56.160 proxysql
1.添加yum源
cat <<EOF | tee /etc/yum.repos.d/proxysql.repo [proxysql_repo] name= ProxySQL YUM repository baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever gpgcheck=1 gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key EOF
2.使用yum进行安装
yum install proxysql
3.启动proxysql服务
[root@localhost ~]# service proxysql start Starting ProxySQL: 2019-07-02 14:03:13 [INFO] Using config file /etc/proxysql.cnf 2019-07-02 14:03:13 [INFO] No SSL keys/certificates found in datadir (/var/lib/proxysql). Generating new keys/certificates. DONE!
4.在PXC集群中安装视图
参考上一篇文章：传送门
5.连接管理端
[root@localhost /]# mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> ' bash: mysql: 未找到命令...
这里报错是因为没有安装mysql客户端。随便安装一个mysql客户端就行了。这里直接安装pxc，也可以安装其他MySQL产品。
yum install Percona-XtraDB-Cluster-57
安装好之后再次运行就可以登录了
[root@localhost /]# mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt 'admin> ' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2009-2019 Percona LLC and/or its affiliates Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. admin> 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.00 sec)
这里自带了5个数据库.
main库：内存配置数据库，即memory，表里面存放后端db实例、用户验证、路由规则等信息。
disk库：持久化磁盘的配置。
stats库：统计信息的汇总。
monitor库：一些监控的收集信息，包括数据库的健康状态等。
stats_history 统计信息历史库
5.添加pxc信息到ProxySQL的mysql_servers 表
admin> show tables from main; +--------------------------------------------+ | tables | +--------------------------------------------+ | global_variables | | mysql_aws_aurora_hostgroups | | mysql_collations | | mysql_galera_hostgroups | | 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_aws_aurora_hostgroups | | runtime_mysql_galera_hostgroups | | 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 | +--------------------------------------------+ 24 rows in set (0.00 sec) admin> insert into mysql_servers(hostgroup_id,hostname,port) values -> (10,'192.168.56.161',3306), -> (10,'192.168.56.162',3306), -> (10,'192.168.56.163',3306); Query OK, 3 rows affected (0.00 sec) INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight) VALUES (2,'192.168.56.161',3306,100); INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight) VALUES (2,'192.168.56.162',3306,10); INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight) VALUES (3,'192.168.56.163',3306,100);
增加galera的配置
INSERT INTO mysql_galera_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) VALUES (2,4,3,1,1,1,0,100); load mysql servers to runtime; save mysql servers to disk;
之后可以查看三台机器的状态。
Admin> select * from mysql_servers; +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 2 | 192.168.56.161 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 192.168.56.162 | 3306 | 0 | ONLINE | 10 | 0 | 1000 | 0 | 0 | 0 | | | 3 | 192.168.56.163 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 0 | 0 | 0 | | +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.01 sec) Admin> select * from mysql_galera_hostgroups; +------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+ | writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment | +------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+ | 2 | 4 | 3 | 1 | 1 | 1 | 0 | 100 | NULL | +------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+ 1 row in set (0.00 sec)
可以看到，我们通过插入数据，配置了Galera集群的一些设置。192.168.56.161和192.168.56.162属于组2，而192.168.56.163属于组3。而组2是writer_hostgroup，组3是reader_hostgroup。而在我们的组2中，192.168.56.161的权重是大于192.168.56.162的，因为161的weight是100，而162的weight是10。这里我们还设置了max_writers为1，也就是最多只有一个提供写入。而一旦该写入组出现故障，就会切换到备用的写入组backup_writer_hostgroup，也就是组4。
我们现在来看一下整个集群的连接情况。因为暂时没有连接。所以现在把192.168.56.163设置是写入组，而192.168.56.161和192.168.56.162设置的是备份写入组。
Admin> select hostgroup,srv_host,status,ConnUsed,MaxConnUsed,Queries,Latency_us from stats.stats_mysql_connection_pool order by srv_host; +-----------+----------------+--------+----------+-------------+---------+------------+ | hostgroup | srv_host | status | ConnUsed | MaxConnUsed | Queries | Latency_us | +-----------+----------------+--------+----------+-------------+---------+------------+ | 4 | 192.168.56.161 | ONLINE | 0 | 0 | 0 | 1226 | | 4 | 192.168.56.162 | ONLINE | 0 | 0 | 0 | 1117 | | 2 | 192.168.56.163 | ONLINE | 0 | 0 | 0 | 1160 | +-----------+----------------+--------+----------+-------------+---------+------------+ 3 rows in set (0.01 sec
当前的状态和我们插入到mysql_server表的设置不符。但是也符合当前的配置，为什么这么说呢？因为PXC默认三个节点是可写的，而163的延迟最低，所以他默认就把3当做了主要写入的节点。
6.创建监控用户
在pxc集群中的任意一个主机上创建监控用户，并赋予权限；
create user monitor@'192.168.56.%' identified by 'monitor'; grant all privileges on *.* to monitor@'192.168.56.%';
在pxc上创建完账号之后就回到ProxySQL节点上配置
UPDATE global_variables SET variable_value='monitor' where variable_name='mysql-monitor_username'; UPDATE global_variables SET variable_value='proxysql' where variable_name='mysql-monitor_password'; load mysql variables to runtime; save mysql variables to disk;
之后就可以验证监控信息：
Admin> 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.56.162 | 3306 | 1562313405719594 | 4853 | NULL | | 192.168.56.161 | 3306 | 1562313405093564 | 2650 | NULL | | 192.168.56.163 | 3306 | 1562313404467721 | 5336 | NULL | | 192.168.56.161 | 3306 | 1562313345470323 | 1261 | NULL | | 192.168.56.163 | 3306 | 1562313344968944 | 3461 | NULL | | 192.168.56.162 | 3306 | 1562313344467233 | 2646 | NULL | +----------------+------+------------------+-------------------------+---------------+ 6 rows in set (0.00 sec) Admin> select * from monitor.mysql_server_ping_log order by time_start_us desc limit 6; +----------------+------+------------------+----------------------+------------+ | hostname | port | time_start_us | ping_success_time_us | ping_error | +----------------+------+------------------+----------------------+------------+ | 192.168.56.163 | 3306 | 1562313454473362 | 847 | NULL | | 192.168.56.161 | 3306 | 1562313454363177 | 900 | NULL | | 192.168.56.162 | 3306 | 1562313454253065 | 1398 | NULL | | 192.168.56.161 | 3306 | 1562313444460262 | 1114 | NULL | | 192.168.56.163 | 3306 | 1562313444356808 | 2072 | NULL | | 192.168.56.162 | 3306 | 1562313444253269 | 1643 | NULL | +----------------+------+------------------+----------------------+------------+ 6 rows in set (0.00 sec)
7.设置ProxySQL，连接后端使用的PXC的用户，配置mysql_uses表。
在pxc集群的任意节点上创建一个连接用户。这里使用root用户来测试。
mysql> create user 'buddy'@'%' identified by 'buddy'; Query OK, 0 rows affected (0.03 sec) mysql> grant all on *.* to 'buddy'@'%'; Query OK, 0 rows affected (0.03 sec) mysql> flush privileges; Query OK, 0 rows affected (0.03 sec)
然后回到ProxySQL，配置mysql_users表，将刚才的用户添加到表中。
admin> insert into mysql_users(username,password,default_hostgroup,transaction_persistent) values('buddy','buddy',2,1);
Query OK, 1 row affected (0.00 sec)
Admin> select * from mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| buddy | buddy | 1 | 0 | 2 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
1 row in set (0.00 sec)
admin> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
admin> save mysql users to disk;
Query OK, 0 rows affected (0.02 sec)
9.接下来就可以连接pxc集群了
通过6033端口进行连接
[root@localhost ~]# mysql -ubuddy -pbuddy -h 127.0.0.1 -P 6033 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.7.26 (ProxySQL) Copyright (c) 2009-2019 Percona LLC and/or its affiliates Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> select @@hostname; +------------+ | @@hostname | +------------+ | pxc3 | +------------+ 1 row in set (0.01 sec)
Post a Comment