- 克隆 MySQL ( MGR ) 01 后的VM & 修改配置(本次为 214节点）
[root@xag200 ~]# hostname
xag214
[root@xag200 ~]# cat /etc/hostname
xag214
[root@xag200 ~]# cat /etc/sysconfig/network
NETWORKING=yes
NOZEROCONF=yes
HOSTNAME=xag214
[root@xag200 ~]# cat /etc/hosts
...
192.168.40.211 xag211 xag211
192.168.40.212 xag212 xag212
192.168.40.213 xag213 xag213
192.168.40.214 xag214 xag214
[root@xag200 ~]# cat /etc/sysconfig/network-scripts/ifcfg-ens33
。。。
IPADDR="192.168.40.211"
。。。
[root@xag200 ~]# reboot
- 安装mysql客户端，用于在本机连接到ProxySQL的管理接口
[root@xag214 src]# cat /etc/yum.repos.d/mariadb.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
安装mysql-clinet客户端
[root@ProxySQL-node1 ~]# yum install -y MariaDB-client
3.下载
[root@xag214 src]# pwd
/usr/local/src
wget https://github.com/sysown/proxysql/releases/download/v2.0.16/proxysql-2.0.16-1-centos7.x86_64.rpm
- 安装 ProxySQL
[root@xag214 src]# yum install -y perl-DBI perl-DBD-MySQL
[root@xag214 src]# yum install -y gnutls
[root@xag214 src]# rpm -ivh proxysql-2.0.16-1-centos7.x86_64.rpm
or
-----------------------------------------------------------------------------------------------------
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
yum install proxysql-2.0.16
------------------------------------------------------------------------------------------------------
# 查看相应安装文件路径，有哪些文件
[root@xag214 src]# rpm -ql proxysql
/etc/logrotate.d/proxysql
/etc/proxysql.cnf
/etc/systemd/system/proxysql-initial.service
/etc/systemd/system/proxysql.service
/usr/bin/proxysql
/usr/share/proxysql/tools/proxysql_galera_checker.sh
/usr/share/proxysql/tools/proxysql_galera_writer.pl
- 启动 ProxySQL
[root@xag214 src]# systemctl start proxysql.service
or
service proxysql start
# 查看是否启动
[root@xag214 src]# netstat -anlp | grep proxysql
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 1467/proxysql
tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 1467/proxysql
[root@xag214 src]# ss -lntup | grep proxysql
tcp LISTEN 0 128 *:6032 *:* users:(("proxysql",pid=1467,fd=39))
tcp LISTEN 0 1024 *:6033 *:* users:(("proxysql",pid=1467,fd=38))
tcp LISTEN 0 1024 *:6033 *:* users:(("proxysql",pid=1467,fd=37))
tcp LISTEN 0 1024 *:6033 *:* users:(("proxysql",pid=1467,fd=36))
tcp LISTEN 0 1024 *:6033 *:* users:(("proxysql",pid=1467,fd=33))
#check the ProxySQL version
[root@xag214 src]# proxysql --version
ProxySQL version 2.0.16-54-g96bfd0c, codename Truls
6032 是 ProxySQL 的管理端口号，
6033是对外服务的端口号
ProxySQL 的用户名和密码都是默认的 admin
- Stopping ProxySQL
service proxysql stop
Or
alternatively via the Admin interface:
$ mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
Admin> proxysql stop
7.初始化Proxysql，将之前的proxysql数据都删除
Reinitializing ProxySQL from the config file (after first startup the DB file is used instead of the config file):
systemctl start proxysql-initial
# or
service proxysql-initial start
or
----------------------------------------------------------------------------------------------------
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
delete from scheduler ;
delete from mysql_servers;
delete from mysql_users;
delete from mysql_query_rules;
delete from mysql_group_replication_hostgroups ;
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
LOAD SCHEDULER TO RUNTIME;
SAVE SCHEDULER TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
-----------------------------------------------------------------------------------------------------
- 管理员登录 ProxySQL
[root@xag214 src]# mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='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 |
+-----+---------------+-------------------------------------+
有五个库： main、disk、stats 、monitor 和 stats_history
main: 内存配置数据库，即 MEMORY，表里存放后端 db 实例、用户验证、路由规则等信息。main 库中有如下信息
库下的主要表：
mysql_servers: 后端可以连接 MySQL 服务器的列表
mysql_users: 配置后端数据库的账号和监控的账号。
mysql_query_rules: 指定 Query 路由到后端不同服务器的规则列表。
注： 表名以 runtime_开头的表示 ProxySQL 当前运行的配置内容，不能通过 DML 语句修改。
只能修改对应的不以 runtime 开头的表，然后 “LOAD” 使其生效，“SAVE” 使其存到硬盘以供下次重启加载。
disk ：持久化的磁盘的配置
stats： 统计信息的汇总
monitor：一些监控的收集信息，比如数据库的健康状态等
stats_history: 这个库是 ProxySQL 收集的有关其内部功能的历史指标
Admin> show tables;
+----------------------------------------------------+
| tables |
+----------------------------------------------------+
| global_variables |
| mysql_aws_aurora_hostgroups |
| mysql_collations |
| mysql_firewall_whitelist_rules |
| mysql_firewall_whitelist_sqli_fingerprints |
| mysql_firewall_whitelist_users |
| mysql_galera_hostgroups |
| mysql_group_replication_hostgroups |
| mysql_query_rules |
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| proxysql_servers |
| restapi_routes |
| runtime_checksums_values |
| runtime_global_variables |
| runtime_mysql_aws_aurora_hostgroups |
| runtime_mysql_firewall_whitelist_rules |
| runtime_mysql_firewall_whitelist_sqli_fingerprints |
| runtime_mysql_firewall_whitelist_users |
| 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_restapi_routes |
| runtime_scheduler |
| scheduler |
+----------------------------------------------------+
- 查看是否MGR集群节点信息
SELECT a.member_id,a.member_host,member_port,member_state
,if(b.variable_name is not null,'PRIMARY','SECONDDARY') as MEMBER_ROLE
FROM performance_schema.replication_group_members a left join performance_schema.global_status b
on b.variable_name='group_replication_primary_member' and b.variable_value=a.member_id;
+--------------------------------------+-------------+-------------+--------------+-------------+
| member_id | member_host | member_port | member_state | MEMBER_ROLE |
+--------------------------------------+-------------+-------------+--------------+-------------+
| 2116c7e9-63c9-11eb-a1ba-000c299e2211 | xag211 | 3306 | ONLINE | PRIMARY |
| 2126c7e9-63c9-11eb-a1ba-000c299e2212 | xag212 | 3306 | ONLINE | SECONDDARY |
| 2136c7e9-63c9-11eb-a1ba-000c299e2213 | xag213 | 3306 | ONLINE | SECONDDARY |
+--------------------------------------+-------------+-------------+--------------+-------------+
- 在proxysql节点中添加主机
INSERT INTO mysql_servers (hostgroup_id,hostname,port) values (2,'xag211',3306);
INSERT INTO mysql_servers (hostgroup_id,hostname,port) values (2,'xag212',3306);
INSERT INTO mysql_servers (hostgroup_id,hostname,port) values (2,'xag213',3306);
- 在proxysql配置mysql_group_replication_hostgroups表：
INSERT INTO mysql_group_replication_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);
字段名 描述
writer_hostgroup 包含作者的所有成员的主机组的id
backup_writer_hostgroup 在多主模式下运行有多个写入节点，
如果这些节点的数量大于max_writers，则额外节点位于该备份写入组中
reader_hostgroup 包含read_only中所有成员的主机组的id
offline_hostgroup 包含主机不在线或不属于该组的主机组的ID
active 启用后，ProxySQL将监视组并根据相应的主机组移动服务器
max_writers 如果是多主模式下的组，则限制写入程序主机组中的节点数量
writer_is_also_reader 布尔值，0或1，启用时，写入程序主机组中的节点也将属于读取器主机组
max_transactions_behind 如果该值大于0，则它定义节点在应用组中的事务时可能滞后的程度
- 保存修改配置并生效：
[root@xag214 ~]# mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
Admin> SAVE MYSQL SERVERS TO DISK;
Admin> LOAD MYSQL SERVERS TO RUNTIME;
- 在MGR数据库主节点中下载SQL脚本并导入
[root@xag211 src]#
wget https://github.com/lefred/mysql_gr_routing_check/blob/master/addition_to_sys.sql
[root@xag211 src]# ps -ef|grep mysql
root 926 1 0 15:58 ? 00:00:00 /bin/sh /usr/local/servers/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/mysql.pid
mysql 2453 926 0 15:58 ? 00:00:20 /usr/local/servers/mysql/bin/mysqld --basedir=/usr/local/servers/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/servers/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/log/error.log --open-files-limit=65535 --pid-file=/usr/local/mysql/data/mysql.pid --socket=/usr/local/mysql/tmp/mysql.socket --port=3306
root 2560 2500 0 16:40 pts/0 00:00:00 grep --color=auto mysql
查看结果
[root@xag211 src]#
mysql -uroot -p123456 -S /usr/local/mysql/tmp/mysql.socket < addition_to_sys.sql
root@xag211:(none) [:05: ] 22 SQL->show tables from sys like 'gr%';
+------------------------------------+
| Tables_in_sys (gr%) |
+------------------------------------+
| gr_member_routing_candidate_status |
+------------------------------------+
- 在MGR数据库主节点中创建监控账户
GRANT SELECT on sys.* to 'monitor'@'192.168.40.%' identified by 'monitor';
FLUSH PRIVILEGES ;
- 查看在ProxySQL中配置用户(如与上面创建的的不同则修改之)
Admin> select * from global_variables where variable_name='mysql-monitor_username';
+------------------------+----------------+
| variable_name | variable_value |
+------------------------+----------------+
| mysql-monitor_username | monitor |
+------------------------+----------------+
Admin> select * from global_variables where variable_name='mysql-monitor_password';
+------------------------+----------------+
| variable_name | variable_value |
+------------------------+----------------+
| mysql-monitor_password | monitor |
+------------------------+----------------+
- 修改监控时间间隔
Admin>
SELECT * FROM global_variables WHERE variable_name IN
('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
+----------------------------------+----------------+
| variable_name | variable_value |
+----------------------------------+----------------+
| mysql-monitor_connect_interval | 60000 |
| mysql-monitor_ping_interval | 10000 |
| mysql-monitor_read_only_interval | 1500 |
+----------------------------------+----------------+
UPDATE global_variables SET variable_value='2000' WHERE variable_name
IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
Admin> LOAD MYSQL VARIABLES TO RUNTIME;
Admin> SAVE MYSQL VARIABLES TO DISK;
- 查看导入的视图信息
各个节点的gr_member_routing_candidate_status视图也显示了当前节点是否是正常状态的，
proxysql就是读取的这个视图的信息来决定此节点是否可用。
root@xag211:(none) [:50: ] 4 SQL->select * from sys.gr_member_routing_candidate_status\G;
*************************** 1. row ***************************
viable_candidate: YES
read_only: NO
transactions_behind: 0
transactions_to_cert: 0
-------------------------------------------------------------------------
root@xag212:(none) [:13: ] 2 SQL->select * from sys.gr_member_routing_candidate_status\G;
*************************** 1. row ***************************
viable_candidate: YES
read_only: YES
transactions_behind: 0
transactions_to_cert: 0
-----------------------------------------------------------------------------
root@xag213:(none) [:52: ] 1 SQL->select * from sys.gr_member_routing_candidate_status\G;
*************************** 1. row ***************************
viable_candidate: YES
read_only: YES
transactions_behind: 0
transactions_to_cert: 0
- 在proxysql节点中查看MGR中的节点信息状态：
Admin> SELECT hostgroup_id,hostname,status FROM runtime_mysql_servers;
+--------------+----------+--------+
| hostgroup_id | hostname | status |
+--------------+----------+--------+
| 2 | xag211 | ONLINE |
| 3 | xag213 | ONLINE |
| 3 | xag212 | ONLINE |
+--------------+----------+--------+
- 配置简单读写分离规则（仅测试用）：
介绍一下改表mysql_query_rules的几个字段：
active：是否启用这个规则，1表示启用，0表示禁用
match_pattern 字段就是代表设置规则
destination_hostgroup 字段代表默认指定的分组，
apply 代表真正执行应用规则。
Admin>
INSERT INTO mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
VALUES (1,1,'^SELECT.*FOR UPDATE$',2,1),(2,1,'^SELECT',3,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
20.查看MGR主节点所在位置
root@xag211:(none) [:01: ] 11 SQL->SELECT * FROM performance_schema.global_status WHERE VARIABLE_NAME= 'group_replication_primary_member';
+----------------------------------+--------------------------------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 2116c7e9-63c9-11eb-a1ba-000c299e2211 |
+----------------------------------+--------------------------------------+
- 查看MGR节点是否存活
root@xag211:(none) [:03: ] 16 SQL->
SELECT member_state FROM performance_schema.replication_group_members
WHERE member_id=@@server_uuid ;
+--------------+
| member_state |
+--------------+
| ONLINE |
+--------------+
1 row in set (0.01 sec)
root@xag211:(none) [:03: ] 17 SQL->
SELECT * FROM performance_schema.global_variables
WHERE variable_name in ('read_only','super_read_only');
+-----------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-----------------+----------------+
| read_only | OFF |
| super_read_only | OFF |
+-----------------+----------------+
22.查看主从延迟
root@xag211:(none) [:05: ] 21 SQL->
SELECT RECEIVED_TRANSACTION_SET FROM performance_schema.replication_connection_status
WHERE Channel_name = 'group_replication_applier';
+-----------------------------------------------------------+
| RECEIVED_TRANSACTION_SET |
+-----------------------------------------------------------+
| aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-30:1000012:2000012 |
+-----------------------------------------------------------+
- 后端MySQL服务健康检测
检测上述配置是否正确：connect_error & ping_error 为NULL则正确。
1）查看后端连接状态：
Admin> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
+----------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+----------+------+------------------+-------------------------+---------------+
| xag213 | 3306 | 1612688918404167 | 3549 | NULL |
| xag212 | 3306 | 1612688918379695 | 3734 | NULL |
| xag211 | 3306 | 1612688918358624 | 4468 | NULL |
| xag211 | 3306 | 1612688916390191 | 3475 | NULL |
| xag212 | 3306 | 1612688916370033 | 1572 | NULL |
| xag213 | 3306 | 1612688916353355 | 1768 | NULL |
| xag213 | 3306 | 1612688914389843 | 1320 | NULL |
| xag211 | 3306 | 1612688914371416 | 1658 | NULL |
| xag212 | 3306 | 1612688914350738 | 4325 | NULL |
| xag213 | 3306 | 1612688912399851 | 3503 | NULL |
+----------+------+------------------+-------------------------+---------------+
2）查看后端存活状态
Admin> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;
+----------+------+------------------+----------------------+------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+----------+------+------------------+----------------------+------------+
| xag213 | 3306 | 1612688948256540 | 1218 | NULL |
| xag212 | 3306 | 1612688948234746 | 1272 | NULL |
| xag211 | 3306 | 1612688948211061 | 1044 | NULL |
| xag212 | 3306 | 1612688946251934 | 464 | NULL |
| xag211 | 3306 | 1612688946230802 | 504 | NULL |
| xag213 | 3306 | 1612688946209215 | 526 | NULL |
| xag213 | 3306 | 1612688944244765 | 3256 | NULL |
| xag212 | 3306 | 1612688944224932 | 1305 | NULL |
| xag211 | 3306 | 1612688944206977 | 1141 | NULL |
| xag213 | 3306 | 1612688942254723 | 1506 | NULL |
+----------+------+------------------+----------------------+------------+
3）配置的生效和持久化
Admin> SELECT * FROM runtime_mysql_servers;
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 2 | xag211 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 3 | xag213 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 3 | xag212 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.002 sec)
Admin> SELECT * FROM disk.mysql_servers;
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 2 | xag211 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | xag212 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | xag213 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
加载配置到RUNTIME层：
Admin> LOAD MYSQL SERVERS TO RUNTIME;
持久化配置到DISK层：
Admin> SAVE MYSQL SERVERS TO DISK;
- 设置MySQL Users(连接后端使用)
1）在后端MySQL创建业务账号
Admin> SELECT * FROM mysql_users;
Empty set (0.000 sec)
在后端MySQL(211)上创建业务库及读写账号
GRANT select,insert,update,delete ON testdb.* TO 'test_rw'@'%' IDENTIFIED BY '123456';
2）将业务账号加入ProxySQL
INSERT INTO mysql_users(username,password,default_hostgroup,comment)
VALUES ('test_rw','123456',2,'业务读写账号');
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 |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------------------+
| test_rw | 123456 | 1 | 0 | 2 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | 业务读写账号 |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------------------+
3）配置的生效
Admin> LOAD MYSQL USERS TO RUNTIME;
Admin> SELECT * FROM runtime_mysql_users;
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------------------+
| test_rw | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 | 0 | 2 | | 0 | 1 | 0 | 0 | 1 | 10000 | 业务读写账号 |
| test_rw | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 | 0 | 2 | | 0 | 1 | 0 | 1 | 0 | 10000 | 业务读写账号 |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------------------+
4）加密MEMORY层用户密码, 从RUNTIME层获取用户加密后密码，更新MEMORY层
Admin> SAVE MYSQL USERS FROM RUNTIME;
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 |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------------------+
| test_rw | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 | 0 | 2 | | 0 | 1 | 0 | 1 | 1 | 10000 | 业务读写账号 |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------------------+
5）配置持久化, 持久化用户信息到DISK层
Admin> SELECT * FROM disk.mysql_users;
Empty set (0.000 sec)
Admin> SAVE MYSQL USERS TO DISK;
Admin> SELECT * FROM disk.mysql_users;
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------------------+
| test_rw | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 | 0 | 2 | | 0 | 1 | 0 | 1 | 1 | 10000 | 业务读写账号 |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------------------+
- 业务账号可用性验证
Admin> select * from stats.stats_mysql_query_digest;
Empty set (0.002 sec)
[root@xag214 ~]# mysql -u test_rw -p123456 -h 127.0.0.1 -P6033
test_rw@127.0.0.1 : (none)【05:37:28】1 SQL->show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| testdb |
+--------------------+
[root@xag214 ~]# mysql -u test_rw -p123456 -h xag214 -P6033 -e "SELECT 1"
[root@xag214 ~]# mysql -u test_rw -p123456 -h xag214 -P6033 -e "SELECT @@port"
+--------+
| @@port |
+--------+
| 3306 |
+--------+
mysql -u test_rw -p123456 -h xag214 -P6033 -e "delete from testdb.t1;"
mysql -u test_rw -p123456 -h xag214 -P6033 -e "insert into testdb.t1 values(1,'a');"
mysql -u test_rw -p123456 -h xag214 -P6033 -e "update testdb.t1 set TestName='b' where id=1;"
mysql -u test_rw -p123456 -h xag214 -P6033 -e "SELECT * from testdb.t1;"
Admin> select * from stats.stats_mysql_query_digest;
+-----------+--------------------+----------+----------------+--------------------+--------------------------------------------+------------+------------+------------+----------+----------+----------+-------------------+---------------+
| hostgroup | schemaname | username | client_address | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time | sum_rows_affected | sum_rows_sent |
+-----------+--------------------+----------+----------------+--------------------+--------------------------------------------+------------+------------+------------+----------+----------+----------+-------------------+---------------+
| 3 | information_schema | test_rw | | 0x63E4D64EF2D6179E | SELECT * from testdb.t1 | 1 | 1612690976 | 1612690976 | 1975 | 1975 | 1975 | 0 | 1 |
| 2 | information_schema | test_rw | | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 2 | 1612690648 | 1612690772 | 0 | 0 | 0 | 0 | 0 |
| 2 | information_schema | test_rw | | 0x594F2C744B698066 | select USER() | 2 | 1612690648 | 1612690772 | 0 | 0 | 0 | 0 | 0 |
| 2 | information_schema | test_rw | | 0x02033E45904D3DF0 | show databases | 1 | 1612690654 | 1612690654 | 2120 | 2120 | 2120 | 0 | 2 |
| 2 | information_schema | test_rw | | 0x1FAA742A35A78D70 | delete from test.t1 | 1 | 1612690935 | 1612690935 | 1602 | 1602 | 1602 | 0 | 0 |
| 3 | information_schema | test_rw | | 0x620B328FE9D6D71A | SELECT DATABASE() | 1 | 1612690776 | 1612690776 | 2106 | 2106 | 2106 | 0 | 1 |
| 2 | testdb | test_rw | | 0x99531AEFF718C501 | show tables | 1 | 1612690779 | 1612690779 | 2627 | 2627 | 2627 | 0 | 1 |
| 3 | information_schema | test_rw | | 0x1C46AE529DD5A40E | SELECT ? | 1 | 1612690662 | 1612690662 | 1550 | 1550 | 1550 | 0 | 1 |
| 3 | testdb | test_rw | | 0x3765930C7143F468 | select * from t1 | 1 | 1612690784 | 1612690784 | 30939 | 30939 | 30939 | 0 | 1 |
| 2 | information_schema | test_rw | | 0x2A58805736DA75D1 | update testdb.t1 set TestName=? where id=? | 1 | 1612690975 | 1612690975 | 2733 | 2733 | 2733 | 1 | 0 |
| 2 | information_schema | test_rw | | 0x8F3313DEB6912889 | insert into testdb.t1 values(?,?) | 1 | 1612690975 | 1612690975 | 2094 | 2094 | 2094 | 1 | 0 |
| 3 | information_schema | test_rw | | 0x831B091BA90D80E5 | SELECT @@port | 1 | 1612690747 | 1612690747 | 542 | 542 | 542 | 0 | 1 |
| 2 | information_schema | test_rw | | 0x1079F3981E546DAE | delete from testdb.t1 | 1 | 1612690975 | 1612690975 | 5999 | 5999 | 5999 | 1 | 0 |
+-----------+--------------------+----------+----------------+--------------------+--------------------------------------------+------------+------------+------------+----------+----------+----------+-------------------+---------------+
26.模拟主节点挂掉（xag211）
[root@xag211 ~]# service mysqld stop
root@xag212:(none) [:45: ] 3 SQL->
SELECT a.member_id,a.member_host,member_port,member_state
,if(b.variable_name is not null,'PRIMARY','SECONDDARY') as MEMBER_ROLE
FROM performance_schema.replication_group_members a left join performance_schema.global_status b
on b.variable_name='group_replication_primary_member' and b.variable_value=a.member_id;
+--------------------------------------+-------------+-------------+--------------+-------------+
| member_id | member_host | member_port | member_state | MEMBER_ROLE |
+--------------------------------------+-------------+-------------+--------------+-------------+
| 2126c7e9-63c9-11eb-a1ba-000c299e2212 | xag212 | 3306 | ONLINE | PRIMARY |
| 2136c7e9-63c9-11eb-a1ba-000c299e2213 | xag213 | 3306 | ONLINE | SECONDDARY |
+--------------------------------------+-------------+-------------+--------------+-------------+
Admin> SELECT hostgroup_id,hostname,status FROM runtime_mysql_servers;
+--------------+----------+---------+
| hostgroup_id | hostname | status |
+--------------+----------+---------+
| 2 | xag212 | ONLINE |
| 1 | xag211 | SHUNNED |
| 3 | xag213 | ONLINE |
+--------------+----------+---------+
mysql -u test_rw -p123456 -h xag214 -P6033 -e "update testdb.t1 set TestName='c' where id=1;"
mysql -u test_rw -p123456 -h xag214 -P6033 -e "SELECT * from testdb.t1;"
+----+----------+
| Id | TestName |
+----+----------+
| 1 | c |
+----+----------+
- 原主节点故障修复（xag211）即启动
[root@xag211 ~]# service mysqld start
root@xag211:(none) [:26: ] 2 SQL-> START GROUP_REPLICATION;
root@xag212:(none) [:45: ] 3 SQL->
SELECT a.member_id,a.member_host,member_port,member_state
,if(b.variable_name is not null,'PRIMARY','SECONDDARY') as MEMBER_ROLE
FROM performance_schema.replication_group_members a left join performance_schema.global_status b
on b.variable_name='group_replication_primary_member' and b.variable_value=a.member_id;
+--------------------------------------+-------------+-------------+--------------+-------------+
| member_id | member_host | member_port | member_state | MEMBER_ROLE |
+--------------------------------------+-------------+-------------+--------------+-------------+
| 2126c7e9-63c9-11eb-a1ba-000c299e2212 | xag212 | 3306 | ONLINE | PRIMARY |
| 2116c7e9-63c9-11eb-a1ba-000c299e2211 | xag211 | 3306 | ONLINE | SECONDDARY |
| 2136c7e9-63c9-11eb-a1ba-000c299e2213 | xag213 | 3306 | ONLINE | SECONDDARY |
+--------------------------------------+-------------+-------------+--------------+-------------+
Admin> SELECT hostgroup_id,hostname,status FROM runtime_mysql_servers;
+--------------+----------+--------+
| hostgroup_id | hostname | status |
+--------------+----------+--------+
| 2 | xag212 | ONLINE |
| 3 | xag211 | ONLINE |
| 3 | xag213 | ONLINE |
+--------------+----------+--------+
- 关闭
关闭所有的组成员必须先先关闭slave，最后关闭master.
service mysqld stop
Admin> SELECT hostgroup_id,hostname,status FROM runtime_mysql_servers;
+--------------+----------+---------+
| hostgroup_id | hostname | status |
+--------------+----------+---------+
| 1 | xag213 | SHUNNED |
| 1 | xag212 | SHUNNED |
| 1 | xag211 | SHUNNED |
+--------------+----------+---------+
[root@xag214 ~]# service proxysql stop
- 启动
[root@xag214 ~]# service proxysql start
[root@xag214 ~]#
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
Admin> SELECT hostgroup_id,hostname,status FROM runtime_mysql_servers;
+--------------+----------+---------+
| hostgroup_id | hostname | status |
+--------------+----------+---------+
| 2 | xag211 | SHUNNED |
| 2 | xag213 | SHUNNED |
| 2 | xag212 | SHUNNED |
+--------------+----------+---------+
开启组复制需要先开始primary，接着一个启动slave server.
root@xag211:(none) [:07: ] 3 SQL->set global group_replication_bootstrap_group=on;
root@xag211:(none) [:07: ] 4 SQL->
select * from performance_schema.replication_group_members;
+---------------------------+-----------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+-----------+-------------+-------------+--------------+
| group_replication_applier | | | NULL | OFFLINE |
+---------------------------+-----------+-------------+-------------+--------------+
#在没有开启组复制之前member_state为OFFLINE,开启组复制之后记得将group_replication_bootstrap_group再设为off
root@xag211:(none) [:08: ] 6 SQL->start group_replication;
root@xag211:(none) [:08: ] 7 SQL->set global group_replication_bootstrap_group=off;
root@xag211:(none) [:09: ] 8 SQL->
SELECT a.member_id,a.member_host,member_port,member_state
,if(b.variable_name is not null,'PRIMARY','SECONDDARY') as MEMBER_ROLE
FROM performance_schema.replication_group_members a left join performance_schema.global_status b
on b.variable_name='group_replication_primary_member' and b.variable_value=a.member_id;
+--------------------------------------+-------------+-------------+--------------+-------------+
| member_id | member_host | member_port | member_state | MEMBER_ROLE |
+--------------------------------------+-------------+-------------+--------------+-------------+
| 2116c7e9-63c9-11eb-a1ba-000c299e2211 | xag211 | 3306 | ONLINE | PRIMARY |
+--------------------------------------+-------------+-------------+--------------+-------------+
Admin> SELECT hostgroup_id,hostname,status FROM runtime_mysql_servers;
+--------------+----------+---------+
| hostgroup_id | hostname | status |
+--------------+----------+---------+
| 2 | xag213 | SHUNNED |
| 2 | xag211 | ONLINE |
| 2 | xag212 | SHUNNED |
+--------------+----------+---------+
待primary server准备好后，其它的slave数据库可以一个个打开并开启组复制
[root@xag212 ~]# service mysqld start
[root@xag212 ~]# mysql.login
root@xag212:(none) [:13: ] 2 SQL->start group_replication;
#启动后检查
SELECT a.member_id,a.member_host,member_port,member_state
,if(b.variable_name is not null,'PRIMARY','SECONDDARY') as MEMBER_ROLE
FROM performance_schema.replication_group_members a left join performance_schema.global_status b
on b.variable_name='group_replication_primary_member' and b.variable_value=a.member_id;
+--------------------------------------+-------------+-------------+--------------+-------------+
| member_id | member_host | member_port | member_state | MEMBER_ROLE |
+--------------------------------------+-------------+-------------+--------------+-------------+
| 2116c7e9-63c9-11eb-a1ba-000c299e2211 | xag211 | 3306 | ONLINE | PRIMARY |
| 2126c7e9-63c9-11eb-a1ba-000c299e2212 | xag212 | 3306 | ONLINE | SECONDDARY |
| 2136c7e9-63c9-11eb-a1ba-000c299e2213 | xag213 | 3306 | ONLINE | SECONDDARY |
+--------------------------------------+-------------+-------------+--------------+-------------+
Admin> SELECT hostgroup_id,hostname,status FROM runtime_mysql_servers;
+--------------+----------+--------+
| hostgroup_id | hostname | status |
+--------------+----------+--------+
| 2 | xag211 | ONLINE |
| 3 | xag213 | ONLINE |
| 3 | xag212 | ONLINE |
+--------------+----------+--------+
问题处理
9006 - ProxySQL Error: connection is locked to hostgroup 2 but trying to reach hostgroup 3
Admin> set mysql-set_query_lock_on_hostgroup=0;
Admin> load mysql variables to runtime;
Admin> save mysql variables to disk;
https://github.com/malongshuai/proxysql/wiki/Global-variables
网友评论