mysql 高可用架构 proxysql 之三 基本配置
os:centos 7.4
mysql: 5.7
proxysql: 1.4.10
ip 规划如下：
192.168.56.101 node1 (proxysql)
192.168.56.102 node2 (mysql master)
192.168.56.103 node3 (mysql slave)
192.168.56.104 node4 (mysql slave)
创建proxysql监控用户
node2 节点创建proxysql监控用户
mysql> create user 'usr_proxysql_mon'@'192.168.56.%' identified by '2wsx3edc';
mysql> grant all privileges on *.* to 'usr_proxysql_mon'@'192.168.56.%';
mysql> flush privileges;
基本配置
配置有三个层次：runtime <=> memory <=> disk/config file
RUNTIME：代表 proxysql 当前生效的正在使用的配置，无法直接修改这里的配置，必须要从下一层 “load” 进来。
MEMORY：MEMORY这一层上面连接 RUNTIME 层，下面连接持久化层。在这层可以正常操作 proxysql 配置，随便修改，不会影响生产环境。修改一个配置一般都是先在 MEMORY 层完成，然后确认正常之后再加载到 RUNTIME 和持久化到磁盘上。
DISK 和 CONFIG FILE：持久化配置信息，重启后内存中的配置信息会丢失，所以需要将配置信息保留在磁盘中。重启时，可以从磁盘快速加载回来。
连接 proxysql 6032 管理端口
# mysql -u admin -p admin -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 1
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2018, 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>
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)
添加mysql服务器列表
就是插入数据到mysql_servers
Admin> use main;
Admin> insert into mysql_servers(hostgroup_id,hostname,port) values (1,'192.168.56.102',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (1,'192.168.56.103',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (1,'192.168.56.104',3306);
Admin> load mysql servers to runtime;
Admin> save mysql servers to disk;
Admin> select * from main.mysql_servers;
所有操作都要记得 load to runtime 和 save to disk。
添加监控账号
Admin> use main;
Admin> set mysql-monitor_username='usr_proxysql_mon';
Admin> set mysql-monitor_password='2wsx3edc';
Admin> load mysql variables to runtime;
Admin> save mysql variables to disk;
Admin> select * from main.global_variables where variable_name in ('mysql-monitor_username','mysql-monitor_password');
+------------------------+------------------+
| variable_name | variable_value |
+------------------------+------------------+
| mysql-monitor_password | 2wsx3edc |
| mysql-monitor_username | usr_proxysql_mon |
+------------------------+------------------+
2 rows in set (0.00 sec)
Admin> select * from main.runtime_global_variables where variable_name in ('mysql-monitor_username','mysql-monitor_password');
+------------------------+------------------+
| variable_name | variable_value |
+------------------------+------------------+
| mysql-monitor_password | 2wsx3edc |
| mysql-monitor_username | usr_proxysql_mon |
+------------------------+------------------+
2 rows in set (0.00 sec)
Admin> select * from main.global_variables where variable_name like 'mysql-monitor_%';
Admin> show tables from monitor;
+------------------------------------+
| tables |
+------------------------------------+
| mysql_server_connect_log |
| mysql_server_group_replication_log |
| mysql_server_ping_log |
| mysql_server_read_only_log |
| mysql_server_replication_lag_log |
+------------------------------------+
5 rows in set (0.00 sec)
Admin> select * from monitor.mysql_server_connect_log limit 10;
+----------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+----------------+------+------------------+-------------------------+---------------+
| 192.168.56.102 | 3306 | 1534145260644302 | 3950 | NULL |
| 192.168.56.103 | 3306 | 1534145261412556 | 2566 | NULL |
| 192.168.56.104 | 3306 | 1534145262180314 | 3095 | NULL |
| 192.168.56.102 | 3306 | 1534145320644490 | 4915 | NULL |
| 192.168.56.104 | 3306 | 1534145321231795 | 2803 | NULL |
| 192.168.56.103 | 3306 | 1534145321819413 | 3072 | NULL |
| 192.168.56.104 | 3306 | 1534145380644794 | 3341 | NULL |
| 192.168.56.102 | 3306 | 1534145381429114 | 2594 | NULL |
| 192.168.56.103 | 3306 | 1534145382213194 | 2451 | NULL |
| 192.168.56.103 | 3306 | 1534145440644673 | 1476 | NULL |
+----------------+------+------------------+-------------------------+---------------+
10 rows in set (0.00 sec)
添加复制信息
在mysql 的 slave 要执行 set @@global.read_only = 1;
Admin> select * from monitor.mysql_server_read_only_log order by time_start_us desc limit 10;
Empty set (0.00 sec)
Admin> select * from main.mysql_replication_hostgroups;
Empty set (0.00 sec)
Admin> show create table main.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)
Admin> insert into main.mysql_replication_hostgroups VALUES (1,2,'cluster1');
Query OK, 1 row affected (0.00 sec)
mysql> select * from main.mysql_replication_hostgroups;
+------------------+------------------+----------+
| writer_hostgroup | reader_hostgroup | comment |
+------------------+------------------+----------+
| 1 | 2 | cluster1 |
+------------------+------------------+----------+
1 row in set (0.00 sec)
Now, all the servers that are either configured in hostgroup 1 or 2 will be moved to the correct hostgroup:
•If they have read_only=0 , they will be moved to hostgroup 1
•If they have read_only=1 , they will be moved to hostgroup 2
But at this moment, the algorithm is still not running, because the new table isn’t loaded at runtime. In fact:
mysql> select * from main.mysql_servers;
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 | 192.168.56.102 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 192.168.56.103 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 192.168.56.104 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)
注意 第一列 hostgroup_id，应该和 main.mysql_replication_hostgroups 的信息相符合，现在的状况不符合。
大招来了，需要执行
Admin> load mysql servers to runtime;
Admin> save mysql servers to disk;再查看 mysql_servers 时，已经符合预期了。
Admin> select * from main.mysql_servers;
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 2 | 192.168.56.104 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 192.168.56.103 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 192.168.56.102 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)配置对外访问账号
Admin> select * from main.mysql_users;
Empty set (0.00 sec)
Admin> insert into main.mysql_users(username,password,default_hostgroup,transaction_persistent) values ('root','1qaz2wsx',1,1);
Admin> insert into main.mysql_users(username,password,default_hostgroup,transaction_persistent) values ('msandbox','1qaz2wsx',1,1);
Admin> select * from main.mysql_users;
Admin> load mysql users to runtime;
Admin> save mysql users to disk;
使用 root、msandbox 登录下6033端口试下：
# mysql -u root -p1qaz2wsx -h 127.0.0.1 -P6033
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 8
Server version: 5.5.30 (ProxySQL)
Copyright (c) 2000, 2018, 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> show databases;
ERROR 1045 (28000): Access denied for user 'root'@'node1' (using password: YES)可以连接上 proxysql 的 6033 端口, show databases 出错这个是账号对照的问题，在 node2 master 创建一个账号。
mysql> create user 'root'@'192.168.56.101' identified by '1qaz2wsx';
mysql> grant all privileges on *.* to 'root'@'192.168.56.101';
mysql> create user 'msandbox'@'192.168.56.101' identified by '1qaz2wsx';
mysql> grant all privileges on *.* to 'msandbox'@'192.168.56.101';
mysql> flush privileges;
再次执行
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 3 | | 3306 | 1 | 02b62c00-9e94-11e8-8ed8-0800279fa3ef |
| 2 | | 3306 | 1 | 006e5b1e-9e94-11e8-8f97-0800275ffdbc |
+-----------+------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)参考：
https://github.com/sysown/proxysql/wiki
https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration
