ProxySQL 2.0安装和Percona Cluster5.7集成

版权声明:本文为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

Your email is never published nor shared. Required fields are marked *