CentOS7下使用RPM安装ProxySQL以及Cluster搭建

发表于 | 分类于 | | 阅读次数:
CentOS7下使用RPM安装ProxySQL以及Cluster搭建

基础信息

ProxySQL

版本：1.4.8-32-g669c149
ProxySQL 1：proxysql1 192.168.1.15 监听端口6032
ProxySQL 2：proxysql2 192.168.1.16 监听端口6032

机器信息

1
2
3
4
# cat /etc/redhat-release
CentOS Linux release 7.4.1708 (Core) 
# uname -a
Linux 192-168-1-16 3.10.0-693.5.2.el7.x86_64 #1 SMP Fri Oct 20 20:32:50 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

安装

安装依赖

1
yum install perl-DBD-MySQL

准备RPM包

1
wget "https://github.com/sysown/proxysql/releases/download/v1.4.8/proxysql-1.4.8-1-centos7.x86_64.rpm"

安装

1
rpm -ivh *.rpm

启动

配置修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
vim /etc/proxysql.cnf
datadir="/var/lib/proxysql"

admin_variables =
{
        admin_credentials="admin:admin;cluster1:secret1pass"
        mysql_ifaces="0.0.0.0:6032"
        cluster_username="cluster1"
        cluster_password="secret1pass"
        cluster_check_interval_ms=200
        cluster_check_status_frequency=100
        cluster_mysql_query_rules_save_to_disk=true
        cluster_mysql_servers_save_to_disk=true
        cluster_mysql_users_save_to_disk=true
        cluster_proxysql_servers_save_to_disk=true
        cluster_mysql_query_rules_diffs_before_sync=3
        cluster_mysql_servers_diffs_before_sync=3
        cluster_mysql_users_diffs_before_sync=3
        cluster_proxysql_servers_diffs_before_sync=3
}

proxysql_servers =
(
    {
        hostname="proxysql1"
        port=6032
        comment="proxysql1"
    },
    {
        hostname="proxysql2"
        port=6032
        comment="proxysql2"
    }
)
1
2
3
4
# 启动
systemctl start proxysql
# 停止
systemctl stop proxysql

连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 连接管理端口
mysql -u admin -padmin -h 127.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 2
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, 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.

检查集群状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Admin>select * from proxysql_servers;
+----------+------+--------+-----------+
| hostname | port | weight | comment   |
+----------+------+--------+-----------+
| master   | 6032 | 0      | proxysql1 |
| slave    | 6032 | 0      | proxysql2 |
+----------+------+--------+-----------+
2 rows in set (0.00 sec)

Admin>SELECT * FROM runtime_checksums_values ORDER BY name;
+-------------------+---------+------------+--------------------+
| name              | version | epoch      | checksum           |
+-------------------+---------+------------+--------------------+
| admin_variables   | 0       | 0          |                    |
| mysql_query_rules | 1       | 1527154033 | 0x0000000000000000 |
| mysql_servers     | 1       | 1527154033 | 0x0000000000000000 |
| mysql_users       | 1       | 1527154033 | 0x0000000000000000 |
| mysql_variables   | 0       | 0          |                    |
| proxysql_servers  | 1       | 1527154033 | 0x2F2F2BFD35FC2D9C |
+-------------------+---------+------------+--------------------+
6 rows in set (0.00 sec)

version：代表LOAD TO RUNTIME执行了多少次，ProxySQL每次LOAD TO RUNTIME执行时都会生成一个新的配置校验
epoch：LOAD TO RUNTIME执行时间的时间戳

#配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
+-------------------------+
|         RUNTIME         |
+-------------------------+
       /|\          |
        |           |
    [1] |       [2] |
        |          \|/
+-------------------------+
|         MEMORY          |
+-------------------------+ _
       /|\          |      |\
        |           |        \
    [3] |       [4] |         \ [5]
        |          \|/         \
+-------------------------+  +-------------------------+
|          DISK           |  |       CONFIG FILE       |
+-------------------------+  +-------------------------+

添加backends

1
2
3
4
5
6
7
8
9
10
11
Admin>INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'master',3306),(2,'slave',3306);    
Query OK, 1 row affected (0.00 sec)

Admin>SELECT * FROM mysql_servers;
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | master   | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | slave    | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)

配置监控

创建监控账号

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
# mysql实例添加账号
GRANT SUPER, REPLICATION CLIENT ON *.* TO proxysql_monitor IDENTIFIED BY '12345678';
# 修改proxysql配置
Admin>UPDATE global_variables SET variable_value='proxysql_monitor' WHERE variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.00 sec)

Admin>UPDATE global_variables SET variable_value='12345678' WHERE variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.00 sec)

Admin>SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';
+-----------------------------------------------------+------------------+
| variable_name                                       | variable_value   |
+-----------------------------------------------------+------------------+
| mysql-monitor_enabled                               | true             |
| mysql-monitor_connect_timeout                       | 600              |
| mysql-monitor_ping_max_failures                     | 3                |
| mysql-monitor_ping_timeout                          | 1000             |
| mysql-monitor_read_only_max_timeout_count           | 3                |
| mysql-monitor_replication_lag_interval              | 10000            |
| mysql-monitor_replication_lag_timeout               | 1000             |
| mysql-monitor_groupreplication_healthcheck_interval | 5000             |
| mysql-monitor_groupreplication_healthcheck_timeout  | 800              |
| mysql-monitor_replication_lag_use_percona_heartbeat |                  |
| mysql-monitor_query_interval                        | 60000            |
| mysql-monitor_query_timeout                         | 100              |
| mysql-monitor_slave_lag_when_null                   | 60               |
| mysql-monitor_wait_timeout                          | true             |
| mysql-monitor_writer_is_also_reader                 | true             |
| mysql-monitor_username                              | proxysql_monitor |
| mysql-monitor_password                              | 12345678         |
| mysql-monitor_history                               | 600000           |
| mysql-monitor_connect_interval                      | 60000            |
| mysql-monitor_ping_interval                         | 10000            |
| mysql-monitor_read_only_interval                    | 1500             |
| mysql-monitor_read_only_timeout                     | 500              |
+-----------------------------------------------------+------------------+
22 rows in set (0.01 sec)

应用和保存配置

1
2
3
4
5
6
7
8
9
10
11
Admin>LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin>SAVE MYSQL VARIABLES TO DISK;
Query OK, 94 rows affected (0.02 sec)

Admin>LOAD MYSQL SERVERS TO RUNTIME;              
Query OK, 0 rows affected (0.00 sec)

Admin>SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.08 sec)

配置用户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
Admin>select * from global_variables where variable_name='admin-hash_passwords'; 
+----------------------+----------------+
| variable_name        | variable_value |
+----------------------+----------------+
| admin-hash_passwords | true           |
+----------------------+----------------+
1 row in set (0.01 sec)

Admin>SELECT * FROM mysql_users;
Empty set (0.00 sec)

Admin>INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('proxysql','12345678',1);
Query OK, 1 row affected (0.00 sec)

Admin>SELECT username,password FROM mysql_users;
+----------+----------+
| username | password |
+----------+----------+
| proxysql | 12345678 |
+----------+----------+
1 row in set (0.00 sec)

Admin>LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin>SELECT username,password FROM mysql_users;
+----------+----------+
| username | password |
+----------+----------+
| proxysql | 12345678 |
+----------+----------+
1 row in set (0.00 sec)

Admin>SAVE MYSQL USERS FROM RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin>SELECT username,password FROM mysql_users;
+----------+-------------------------------------------+
| username | password                                  |
+----------+-------------------------------------------+
| proxysql | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 |
+----------+-------------------------------------------+
1 row in set (0.00 sec)

Admin>SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.03 sec)

查看集群同步状态

分别到proxysql1和proxysql2操作

1
2
3
4
5
6
7
8
9
10
11
12
Admin>SELECT * FROM runtime_checksums_values ORDER BY name;
+-------------------+---------+------------+--------------------+
| name              | version | epoch      | checksum           |
+-------------------+---------+------------+--------------------+
| admin_variables   | 0       | 0          |                    |
| mysql_query_rules | 1       | 1527154033 | 0x0000000000000000 |
| mysql_servers     | 2       | 1527154464 | 0xCDFB3987855F1469 |
| mysql_users       | 2       | 1527154515 | 0x77717322EF8E08EB |
| mysql_variables   | 0       | 0          |                    |
| proxysql_servers  | 1       | 1527154033 | 0x2F2F2BFD35FC2D9C |
+-------------------+---------+------------+--------------------+
6 rows in set (0.00 sec)

检查proxysql2的mysql_servers\mysql_users

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Admin>select * from mysql_servers; 
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | master   | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | slave    | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (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 |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+-
--------+----------+-----------------+
| proxysql | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 | 1      | 0       | 1                 |                | 0             | 1                      | 0            | 
0       | 1        | 10000           |
| proxysql | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 | 1      | 0       | 1                 |                | 0             | 1                      | 0            | 
1       | 0        | 10000           |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+-
--------+----------+-----------------+
2 rows in set (0.00 sec)

可以看到mysql_servers\mysql_users的版本号已经加1，但是admin_variables依然为0，因为目前proxysql仅支持如下四个模块的同步，其他模块数据变更需要每个实例单独修改

1
2
3
4
mysql_query_rules
mysql_servers
mysql_users
proxysql_servers

运行日志查看

1
tail -f /var/lib/proxysql/proxysql.log

测试

连接操作mysql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# 连接
mysql -u proxysql -p -h 127.0.0.1 -P6033
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, 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.

proxysql:(none)> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

参考
  1. http://www.proxysql.com/blog/proxysql-cluster