扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >MySQL中搭建ProxySQL Cluster的详细步骤
  • 848

0
分享到

MySQL中搭建ProxySQL Cluster的详细步骤

2024-04-02 19:04:59 848人浏览 八月长安
摘要

下文给大家带来关于Mysql中搭建Proxysql Cluster的详细步骤，感兴趣的话就一起来看看这篇文章吧，相信看完mysql中搭建ProxySQL Cluster的详细步骤对大家多少有点帮助吧。环境：

下文给大家带来关于Mysql中搭建Proxysql Cluster的详细步骤，感兴趣的话就一起来看看这篇文章吧，相信看完mysql中搭建ProxySQL Cluster的详细步骤对大家多少有点帮助吧。

环境：

实例名版本IP系统备注
ProxySQL 11.4.6208Centos7最初启动
ProxySQL 21.4.6209CentOS7最初启动
ProxySQL 31.4.6210Debian9后面加入

搭建：

集群的搭建有很多种方式，如1+1+1的方式，还可以(1+1)+1的方式。

这里采用较简单的(1+1)+1，即先将两个节点作为集群启动，然后其他节点选择性加入的方式

1.更改所有实例的配置文件：

vim /etc/proxysql.cnf

# 需要更改的部分
admin_variables=
{
        admin_credentials="admin:admin;cluster_20X:123456"       #配置用于实例间通讯的账号
#       mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
        mysql_ifaces="0.0.0.0:6032"                            #全网开放登录
#       refresh_interval=2000
#       debug=true
        cluster_username="cluster_20X"                         #集群用户名称，与最上面的相同
        cluster_passWord="123456"                              #集群用户密码，与最上面的相同
        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="192.168.1.208"
                port=6032
                comment="primary"                           #注释
        },
        {
                hostname="192.168.1.209"
                port=6032
                comment="secondary"
        },
        {
                hostname="192.168.1.210"
                host=6032
                comment="secondary"
        }
)
2.启动208和209实例：

systemctl start proxysql

3.观察集群状况：
mysql> select * from proxysql_servers;
+---------------+------+--------+-----------+
| hostname      | port | weight | comment   |
+---------------+------+--------+-----------+
| 192.168.1.208 | 6032 | 0      | primary   |
| 192.168.1.209 | 6032 | 0      | secondary |
+---------------+------+--------+-----------+
2 rows in set (0.00 sec)
mysql> select * from   stats_proxysql_servers_metrics;
+---------------+------+--------+-----------+------------------+----------+---------------+---------+------------------------------+----------------------------+
| hostname      | port | weight | comment   | response_time_ms | Uptime_s | last_check_ms | Queries | Client_Connections_connected | Client_Connections_created |
+---------------+------+--------+-----------+------------------+----------+---------------+---------+------------------------------+----------------------------+
| 192.168.1.209 | 6032 | 0      | secondary | 0                | 670769   | 11027         | 0       | 0                            | 0                          |
| 192.168.1.208 | 6032 | 0      | primary   | 0                | 702316   | 1169          | 5       | 0                            | 1                          |
+---------------+------+--------+-----------+------------------+----------+---------------+---------+------------------------------+----------------------------+
4.观察ProxySQL集群中实例之间的数据同步：
#原有数据
mysql> select * from mysql_servers;
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 7            | 192.168.1.181 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 10           | 192.168.1.182 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 8            | 192.168.1.180 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)
#在209上插入一条数据：
mysql> insert into mysql_servers(hostgroup_id,hostname,port,comment) values (20,'192.168.1.120',3306,'zabbix');
# 持久化，并加载到运行环境中
mysql> save mySQL Servers to disk; 
mysql> load mysql servers to runtime;  
# 观察208实例的数据：
mysql> select * from mysql_servers;
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 7            | 192.168.1.181 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | 192.168.1.120 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | zabbix  |
| 10           | 192.168.1.182 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 8            | 192.168.1.180 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)
mysql> select * from runtime_mysql_servers;
+--------------+---------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | status  | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 7            | 192.168.1.181 | 3306 | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 8            | 192.168.1.180 | 3306 | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 10           | 192.168.1.182 | 3306 | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | 192.168.1.120 | 3306 | SHUNNED | 1      | 0           | 1000            | 0                   | 0       | 0              | zabbix  |
+--------------+---------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)

# 可以看到新插入的数据，已经被更新到208实例中的memory和runtime环境中。
# 注意：数据差异检查是根据runtime进行检查的，只对memory和disk进行更改，并不触发同步操作。
5.查看208实例的ProxySQL日志
2018-04-16 19:10:21 [INFO] Cluster: detected a new checksum for mysql_servers from peer 192.168.1.209:6032, version 99434, epoch 1523986027, checksum 0x9AFEA97C6D622D69 . Not syncing yet ... #检测到209实例传来的新配置文件校验值
2018-04-16 19:10:22 [INFO] Cluster: detected a peer 192.168.1.209:6032 with mysql_servers version 99434, epoch 1523986027, diff_check 3. Own version: 3, epoch: 1523876751. Proceeding with remote sync #根据传来的配置校验值，版本号，时间戳，与自己的版本进行比较，决定进行同步操作
2018-04-16 19:10:22 [INFO] Cluster: detected a peer 192.168.1.209:6032 with mysql_servers version 99434, epoch 1523986027, diff_check 4. Own version: 3, epoch: 1523876751. Proceeding with remote sync #根据传来的配置校验值，版本号，时间戳，与自己的版本进行比较，决定进行同步操作
2018-04-16 19:10:22 [INFO] Cluster: detected peer 192.168.1.209:6032 with mysql_servers version 99434, epoch 1523986027
2018-04-16 19:10:22 [INFO] Cluster: Fetching MySQL Servers from peer 192.168.1.209:6032 started. Expected checksum 0x9AFEA97C6D622D69
2018-04-16 19:10:22 [INFO] Cluster: Fetching MySQL Servers from peer 192.168.1.209:6032 completed      #从远端获取新的差异配置信息
2018-04-16 19:10:22 [INFO] Cluster: Fetching checksum for MySQL Servers from peer 192.168.1.209:6032 before proceessing 
2018-04-16 19:10:22 [INFO] Cluster: Fetching checksum for MySQL Servers from peer 192.168.1.209:6032 successful. Checksum: 0x9AFEA97C6D622D69 #获取完信息后，本地进行校验，并请求远端校验值进行比较
2018-04-16 19:10:22 [INFO] Cluster: Writing mysql_servers table #开始写mysql_servers表
2018-04-16 19:10:22 [INFO] Cluster: Writing mysql_replication_hostgroups table
2018-04-16 19:10:22 [INFO] Cluster: Loading to runtime MySQL Servers from peer 192.168.1.209:6032   #将刚刚接收并保存到memory的配置加载到runtime环境中
2018-04-16 19:10:22 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 7 , address: 192.168.1.181 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 10 , address: 192.168.1.182 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 8 , address: 192.168.1.180 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
2018-04-16 19:10:22 [INFO] Dumping mysql_servers #先输出之前自己的配置信息
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
| hostgroup_id | hostname      | port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer     |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
| 7            | 192.168.1.181 | 3306 | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 140116687433856 |
| 8            | 192.168.1.180 | 3306 | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 140116687434240 |
| 10           | 192.168.1.182 | 3306 | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 140116687434112 |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
2018-04-16 19:10:22 [INFO] Dumping mysql_servers_incoming #再输出一遍更新传来的的配置信息
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 7            | 192.168.1.181 | 3306 | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | 192.168.1.120 | 3306 | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              | zabbix  |
| 10           | 192.168.1.182 | 3306 | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
| 8            | 192.168.1.180 | 3306 | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2018-04-16 19:10:22 [INFO] New mysql_replication_hostgroups table
2018-04-16 19:10:22 [INFO] New mysql_group_replication_hostgroups table
2018-04-16 19:10:22 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 7 , address: 192.168.1.181 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 20 , address: 192.168.1.120 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: zabbix
HID: 10 , address: 192.168.1.182 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 8 , address: 192.168.1.180 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
2018-04-16 19:10:22 [INFO] Dumping mysql_servers #最后输出一遍自己更新后的信息
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
| hostgroup_id | hostname      | port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer     |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
| 7            | 192.168.1.181 | 3306 | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 140116687433856 |
| 8            | 192.168.1.180 | 3306 | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 140116687434240 |
| 10           | 192.168.1.182 | 3306 | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 140116687434112 |
| 20           | 192.168.1.120 | 3306 | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              | zabbix  | 140116687433984 |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
2018-04-16 19:10:22 [INFO] Cluster: Saving to disk MySQL Servers from peer 192.168.1.209:6032 #经过设置的时间后，自动保存到disk环境中
2018-04-16 19:10:22 [INFO] Cluster: detected a new checksum for mysql_servers from peer 192.168.1.208:6032, version 4, epoch 1523877022, checksum 0x9AFEA97C6D622D69 . Not syncing yet ...
2018-04-16 19:10:22 [INFO] Cluster: checksum for mysql_servers from peer 192.168.1.208:6032 matches with local checksum 0x9AFEA97C6D622D69 , we won't sync.
2018-04-16 19:10:24 MySQL_Monitor.cpp:1370:monitor_ping(): [ERROR] Server 192.168.1.120:3306 missed 3 heartbeats, shunning it and killing all the connections
2018-04-16 19:10:34 MySQL_Monitor.cpp:1370:monitor_ping(): [ERROR] Server 192.168.1.120:3306 missed 3 heartbeats, shunning it and killing all the connections
6.加入210节点：

210为全新的节点，我们尝试不使用conf文件启动，而使用更改global_variable的方式加入集群。

# 更改管理端口的验证信息
mysql> update global_variables set variable_value="admin:admin;cluster_20X:123456" where variable_name ='admin-admin_credentials'; 
mysql> update global_variables set variable_value="cluster_20X" where variable_name ='admin-cluster_username';
mysql> update global_variables set variable_value="123456" where variable_name ='admin-cluster_password';
# 插入ProxySQL实例信息
mysql> insert into proxysql_servers(hostname,port) values('192.168.1.208',6032)，('192.168.1.209',6032)，('192.168.1.210',6032);
# 将更改的信息载入runtime环境
mysql >load admin variables to runtime;
mysql >load proxysql servers to runtime;

观察日志：

Standard Query Processor rev. 0.2.0902 -- Query_Processor.cpp -- Thu Feb  1 02:57:56 2018
In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Thu Feb  1 02:57:56 2018
Standard MySQL Monitor (StdMyMon) rev. 1.2.0723 -- MySQL_Monitor.cpp -- Thu Feb  1 02:57:56 2018
2018-04-17 22:40:55 [INFO] Received load admin variables to runtime command
2018-04-17 22:44:19 [INFO] Received load proxysql servers to runtime command
2018-04-17 22:44:19 [INFO] Created new Cluster node Entry for host 192.168.1.208:6032 #
2018-04-17 22:44:19 [INFO] Created new Cluster Node Entry for host 192.168.1.209:6032 #
2018-04-17 22:44:19 [INFO] Created new Cluster Node Entry for host 192.168.1.210:6032 #为其他实例开启自身入口
2018-04-17 22:44:19 [INFO] Cluster: starting thread for peer 192.168.1.210:6032 #
2018-04-17 22:44:19 [INFO] Cluster: starting thread for peer 192.168.1.209:6032 #
2018-04-17 22:44:19 [INFO] Cluster: starting thread for peer 192.168.1.208:6032 # 为其他实例连入创建线程
2018-04-17 22:44:19 [INFO] Cluster: detected a new checksum for mysql_query_rules from peer 192.168.1.210:6032, version 1, epoch 1523975806, checksum 0x0000000000000000 . Not syncing yet ...
2018-04-17 22:44:19 [INFO] Cluster: checksum for mysql_query_rules from peer 192.168.1.210:6032 matches with local checksum 0x0000000000000000 , we won't sync.
2018-04-17 22:44:19 [INFO] Cluster: detected a new checksum for mysql_servers from peer 192.168.1.210:6032, version 1, epoch 1523975806, checksum 0x0000000000000000 . Not syncing yet ...
2018-04-17 22:44:19 [INFO] Cluster: checksum for mysql_servers from peer 192.168.1.210:6032 matches with local checksum 0x0000000000000000 , we won't sync.
2018-04-17 22:44:19 [INFO] Cluster: detected a new checksum for mysql_users from peer 192.168.1.210:6032, version 1, epoch 1523975806, checksum 0x0000000000000000 . Not syncing yet ...
2018-04-17 22:44:19 [INFO] Cluster: checksum for mysql_users from peer 192.168.1.210:6032 matches with local checksum 0x0000000000000000 , we won't sync.
2018-04-17 22:44:19 [INFO] Cluster: detected a new checksum for proxysql_servers from peer 192.168.1.210:6032, version 2, epoch 1523976259, checksum 0x42904D5D92E2A8FE . Not syncing yet ...
2018-04-17 22:44:19 [INFO] Cluster: checksum for proxysql_servers from peer 192.168.1.210:6032 matches with local checksum 0x42904D5D92E2A8FE , we won't sync.
2018-04-17 22:44:19 [INFO] Cluster: detected a new checksum for mysql_query_rules from peer 192.168.1.209:6032, version 1, epoch 1523173084, checksum 0x0000000000000000 . Not syncing yet ...
2018-04-17 22:44:19 [INFO] Cluster: checksum for mysql_query_rules from peer 192.168.1.209:6032 matches with local checksum 0x0000000000000000 , we won't sync.
2018-04-17 22:44:19 [INFO] Cluster: detected a new checksum for mysql_servers from peer 192.168.1.209:6032, version 99434, epoch 1523986027, checksum 0x9AFEA97C6D622D69 . Not syncing yet ...
2018-04-17 22:44:19 [INFO] Cluster: detected a new checksum for mysql_users from peer 192.168.1.209:6032, version 2, epoch 1523174009, checksum 0x8EEF803C41343944 . Not syncing yet ...
2018-04-17 22:44:19 [INFO] Cluster: detected a new checksum for proxysql_servers from peer 192.168.1.209:6032, version 1, epoch 1523173084, checksum 0xDF7CA570731DA09D . Not syncing yet ...
2018-04-17 22:44:19 [INFO] Cluster: detected a new checksum for mysql_query_rules from peer 192.168.1.208:6032, version 1, epoch 1523876494, checksum 0x0000000000000000 . Not syncing yet ...
2018-04-17 22:44:19 [INFO] Cluster: checksum for mysql_query_rules from peer 192.168.1.208:6032 matches with local checksum 0x0000000000000000 , we won't sync.
2018-04-17 22:44:19 [INFO] Cluster: detected a new checksum for mysql_servers from peer 192.168.1.208:6032, version 4, epoch 1523877022, checksum 0x9AFEA97C6D622D69 . Not syncing yet ...
2018-04-17 22:44:19 [INFO] Cluster: detected a new checksum for mysql_users from peer 192.168.1.208:6032, version 2, epoch 1523876495, checksum 0x8EEF803C41343944 . Not syncing yet ...
2018-04-17 22:44:19 [INFO] Cluster: detected a new checksum for proxysql_servers from peer 192.168.1.208:6032, version 1, epoch 1523876494, checksum 0xDF7CA570731DA09D . Not syncing yet ...
2018-04-17 22:44:21 [INFO] Cluster: detected a peer 192.168.1.209:6032 with mysql_servers version 99434, epoch 1523986027, diff_check 3. Own version: 1, epoch: 1523975806. Proceeding with remote sync
2018-04-17 22:44:21 [INFO] Cluster: detected a peer 192.168.1.209:6032 with mysql_users version 2, epoch 1523174009, diff_check 3. Own version: 1, epoch: 1523975806. Proceeding with remote sync
2018-04-17 22:44:21 [INFO] Cluster: detected a peer 192.168.1.208:6032 with mysql_servers version 4, epoch 1523877022, diff_check 3. Own version: 1, epoch: 1523975806. Proceeding with remote sync
2018-04-17 22:44:21 [INFO] Cluster: detected a peer 192.168.1.208:6032 with mysql_users version 2, epoch 1523876495, diff_check 3. Own version: 1, epoch: 1523975806. Proceeding with remote sync
2018-04-17 22:44:22 [INFO] Cluster: detected a peer 192.168.1.209:6032 with mysql_servers version 99434, epoch 1523986027, diff_check 4. Own version: 1, epoch: 1523975806. Proceeding with remote sync
2018-04-17 22:44:22 [INFO] Cluster: detected peer 192.168.1.209:6032 with mysql_servers version 99434, epoch 1523986027
2018-04-17 22:44:22 [INFO] Cluster: Fetching MySQL Servers from peer 192.168.1.209:6032 started. Expected checksum 0x9AFEA97C6D622D69
2018-04-17 22:44:22 [INFO] Cluster: Fetching MySQL Servers from peer 192.168.1.209:6032 completed
2018-04-17 22:44:22 [INFO] Cluster: Fetching checksum for MySQL Servers from peer 192.168.1.209:6032 before proceessing
2018-04-17 22:44:22 [INFO] Cluster: Fetching checksum for MySQL Servers from peer 192.168.1.209:6032 successful. Checksum: 0x9AFEA97C6D622D69
2018-04-17 22:44:22 [INFO] Cluster: Writing mysql_servers table
2018-04-17 22:44:22 [INFO] Cluster: Writing mysql_replication_hostgroups table
2018-04-17 22:44:22 [INFO] Cluster: Loading to runtime MySQL Servers from peer 192.168.1.209:6032
2018-04-17 22:44:22 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
2018-04-17 22:44:22 [INFO] Dumping mysql_servers
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+
| hostgroup_id | hostname | port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+
2018-04-17 22:44:22 [INFO] Dumping mysql_servers_incoming
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 7            | 192.168.1.181 | 3306 | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | 192.168.1.120 | 3306 | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              | zabbix  |
| 10           | 192.168.1.182 | 3306 | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
| 8            | 192.168.1.180 | 3306 | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2018-04-17 22:44:22 [INFO] New mysql_replication_hostgroups table
2018-04-17 22:44:22 [INFO] New mysql_group_replication_hostgroups table
2018-04-17 22:44:22 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 7 , address: 192.168.1.181 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 20 , address: 192.168.1.120 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: zabbix
HID: 10 , address: 192.168.1.182 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 8 , address: 192.168.1.180 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
2018-04-17 22:44:22 [INFO] Dumping mysql_servers
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
| hostgroup_id | hostname      | port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer     |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
| 7            | 192.168.1.181 | 3306 | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 140641893576576 |
| 8            | 192.168.1.180 | 3306 | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 140641893867776 |
| 10           | 192.168.1.182 | 3306 | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 140641893867648 |
| 20           | 192.168.1.120 | 3306 | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              | zabbix  | 140641893867520 |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
2018-04-17 22:44:22 [INFO] Cluster: Saving to disk MySQL Servers from peer 192.168.1.209:6032
2018-04-17 22:44:22 [INFO] Cluster: detected a peer 192.168.1.209:6032 with mysql_users version 2, epoch 1523174009, diff_check 4. Own version: 1, epoch: 1523975806. Proceeding with remote sync
2018-04-17 22:44:22 ProxySQL_Cluster.cpp:1268:get_peer_to_sync_mysql_users(): [WARNING] Cluster: detected a peer with mysql_users epoch 1523876495 , but not enough diff_check. We won't sync from epoch 1523174009: temporarily skipping sync
2018-04-17 22:44:22 [INFO] Cluster: detected a peer 192.168.1.208:6032 with mysql_users version 2, epoch 1523876495, diff_check 4. Own version: 1, epoch: 1523975806. Proceeding with remote sync
2018-04-17 22:44:22 [INFO] Cluster: detected peer 192.168.1.208:6032 with mysql_users version 2, epoch 1523876495
2018-04-17 22:44:22 [INFO] Cluster: Fetching MySQL Users from peer 192.168.1.208:6032 started
2018-04-17 22:44:22 [INFO] Cluster: Fetching MySQL Users from peer 192.168.1.208:6032 completed
2018-04-17 22:44:22 [INFO] Cluster: Loading to runtime MySQL Users from peer 192.168.1.208:6032
2018-04-17 22:44:22 [INFO] Cluster: Saving to disk MySQL Query Rules from peer 192.168.1.208:6032
2018-04-17 22:44:23 [INFO] Cluster: detected a new checksum for mysql_servers from peer 192.168.1.210:6032, version 2, epoch 1523976262, checksum 0x9AFEA97C6D622D69 . Not syncing yet ...
2018-04-17 22:44:23 [INFO] Cluster: checksum for mysql_servers from peer 192.168.1.210:6032 matches with local checksum 0x9AFEA97C6D622D69 , we won't sync.
2018-04-17 22:44:23 [INFO] Cluster: detected a new checksum for mysql_users from peer 192.168.1.210:6032, version 2, epoch 1523976262, checksum 0x8EEF803C41343944 . Not syncing yet ...
2018-04-17 22:44:23 [INFO] Cluster: checksum for mysql_users from peer 192.168.1.210:6032 matches with local checksum 0x8EEF803C41343944 , we won't sync.
2018-04-17 22:44:48 ProxySQL_Cluster.cpp:551:set_checksums(): [WARNING] Cluster: detected a peer 192.168.1.208:6032 with proxysql_servers version 1, epoch 1523876494, diff_check 30. Own version: 2, epoch: 1523976259. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD PROXYSQL SERVERS TO RUNTIME is executed on candidate master.
2018-04-17 22:44:48 ProxySQL_Cluster.cpp:551:set_checksums(): [WARNING] Cluster: detected a peer 192.168.1.209:6032 with proxysql_servers version 1, epoch 1523173084, diff_check 30. Own version: 2, epoch: 1523976259. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD PROXYSQL SERVERS TO RUNTIME is executed on candidate master.
2018-04-17 22:44:56 MySQL_Monitor.cpp:1370:monitor_ping(): [ERROR] Server 192.168.1.120:3306 missed 3 heartbeats, shunning it and killing all the connections
2018-04-17 22:45:06 MySQL_Monitor.cpp:1370:monitor_ping(): [ERROR] Server 192.168.1.120:3306 missed 3 heartbeats, shunning it and killing all the connections
2018-04-17 22:45:16 MySQL_Monitor.cpp:1370:monitor_ping(): [ERROR] Server 192.168.1.120:3306 missed 3 heartbeats, shunning it and killing all the connections
2018-04-17 22:45:18 ProxySQL_Cluster.cpp:551:set_checksums(): [WARNING] Cluster: detected a peer 192.168.1.208:6032 with proxysql_servers version 1, epoch 1523876494, diff_check 60. Own version: 2, epoch: 1523976259. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD PROXYSQL SERVERS TO RUNTIME is executed on candidate master.
2018-04-17 22:45:18 ProxySQL_Cluster.cpp:551:set_checksums(): [WARNING] Cluster: detected a peer 192.168.1.209:6032 with proxysql_servers version 1, epoch 1523173084, diff_check 60. Own version: 2, epoch: 1523976259. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD PROXYSQL SERVERS TO RUNTIME is executed on candidate master.
2018-04-17 22:45:26 MySQL_Monitor.cpp:1370:monitor_ping(): [ERROR] Server 192.168.1.120:3306 missed 3 heartbeats, shunning it and killing all the connections
2018-04-17 22:45:36 MySQL_Monitor.cpp:1370:monitor_ping(): [ERROR] Server 192.168.1.120:3306 missed 3 heartbeats, shunning it and killing all the connections
2018-04-17 22:45:46 MySQL_Monitor.cpp:1370:monitor_ping(): [ERROR] Server 192.168.1.120:3306 missed 3 heartbeats, shunning it and killing all the connections
2018-04-17 22:45:48 ProxySQL_Cluster.cpp:551:set_checksums(): [WARNING] Cluster: detected a peer 192.168.1.208:6032 with proxysql_servers version 1, epoch 1523876494, diff_check 90. Own version: 2, epoch: 1523976259. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD PROXYSQL SERVERS TO RUNTIME is executed on candidate master.
2018-04-17 22:45:48 ProxySQL_Cluster.cpp:551:set_checksums(): [WARNING] Cluster: detected a peer 192.168.1.209:6032 with proxysql_servers version 1, epoch 1523173084, diff_check 90. Own version: 2, epoch: 1523976259. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD PROXYSQL SERVERS TO RUNTIME is executed on candidate master.
2018-04-17 22:45:56 MySQL_Monitor.cpp:1370:monitor_ping(): [ERROR] Server 192.168.1.120:3306 missed 3 heartbeats, shunning it and killing all the connections
2018-04-17 22:46:06 MySQL_Monitor.cpp:1370:monitor_ping(): [ERROR] Server 192.168.1.120:3306 missed 3 heartbeats, shunning it and killing all the connections
大致信息即为接收并更新自己的配置文件，但有一点出现了问题，我在210上插入了三条信息（208.209，210），但在之前208和209在达成共识后，将210的proxysql_server信息踢出了表中（只有208，209，没有210）。导致从现有集群中获取的proxysql_server信息与自身的不符，且自身的信息版本（时间戳）高于集群中的信息。需要手动LOAD PROXYSQL SERVERS TO RUNTIME，然后在208或者209上重新加上210的信息上，同步到整个集群中，210实例方能排除数据冲突，真正的与208，209组成的集群保持同步。

疑难解答：

1.新加入的节点在已经有和主节点配置不同时，主节点日志输出如下：
Sun Apr  8 10:06:37 CST 2018 ###### TRYING TO FIX MISSING WRITERS ######
Sun Apr  8 10:06:37 CST 2018 ###### TRYING TO FIX MISSING READERS ######
2018-04-08 10:06:38 ProxySQL_Cluster.cpp:488:set_checksums(): [WARNING] Cluster: detected a peer 192.168.1.209:6032 with mysql_query_rules version 1, epoch 1523107592, diff_check 60. Own version: 9, epoch: 1523005649. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL QUERY RULES TO RUNTIME is executed on candidate master.
2018-04-08 10:06:38 ProxySQL_Cluster.cpp:509:set_checksums(): [WARNING] Cluster: detected a peer 192.168.1.209:6032 with mysql_servers version 1, epoch 1523107592, diff_check 60. Own version: 85814, epoch: 1523153195. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL SERVERS TO RUNTIME is executed on candidate master.
2018-04-08 10:06:38 ProxySQL_Cluster.cpp:530:set_checksums(): [WARNING] Cluster: detected a peer 192.168.1.209:6032 with mysql_users version 1, epoch 1523107592, diff_check 60. Own version: 9, epoch: 1523110710. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL USERS TO RUNTIME is executed on candidate master.
2018-04-08 10:06:38 ProxySQL_Cluster.cpp:551:set_checksums(): [WARNING] Cluster: detected a peer 192.168.1.209:6032 with proxysql_servers version 1, epoch 1523107592, diff_check 60. Own version: 3, epoch: 1523077120. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD PROXYSQL SERVERS TO RUNTIME is executed on candidate master.
Sun Apr  8 10:06:40 CST 2018 ###### TRYING TO FIX MISSING WRITERS ######
Sun Apr  8 10:06:40 CST 2018 ###### TRYING TO FIX MISSING READERS ######

这种情况要求我们强制覆盖一端的数据。不建议手动在控制台进行load或者save等操作进行覆盖，最好将一个实例的配置手动更新至最全的版本，然后删除另一个ProxySQL的proxysql.db配置文件，并在conf文件中写定集群信息。启动后，缺失proxysql.db的实例，会自动下载集群中的配置信息，并生成新的proxysql.db。

2.因为密码间隔用的‘，’导致日志中输出无法登录的情况
2018-04-08 09:40:10 ProxySQL_Cluster.cpp:180:ProxySQL_Cluster_Monitor_thread(): [WARNING] Cluster: unable to connect to peer 192.168.1.208:6032 . Error: ProxySQL Error: Access denied for user 'cluster'@'' (using password: YES)
2018-04-08 09:40:11 ProxySQL_Cluster.cpp:180:ProxySQL_Cluster_Monitor_thread(): [WARNING] Cluster: unable to connect to peer 192.168.1.209:6032 . Error: ProxySQL Error: Access denied for user 'cluster'@'' (using password: YES)
2018-04-08 09:40:11 ProxySQL_Cluster.cpp:180:ProxySQL_Cluster_Monitor_thread(): [WARNING] Cluster: unable to connect to peer 192.168.1.208:6032 . Error: ProxySQL Error: Access denied for user 'cluster'@'' (using password: YES)
ERROR 1045 (28000): ProxySQL Error: Access denied for user 'admin'@'' (using password: YES)
ERROR 1045 (28000): ProxySQL Error: Access denied for user 'admin'@'' (using password: YES)
ERROR 1045 (28000): ProxySQL Error: Access denied for user 'admin'@'' (using password: YES)
ERROR 1045 (28000): ProxySQL Error: Access denied for user 'admin'@'' (using password: YES)

将admin_credentials="admin:admin，cluster_20X:123456"中间隔两个账户和密码对的‘，’改成’；‘

看了以上关于MySQL中搭建ProxySQL Cluster的详细步骤详细内容，是否有所收获。如果想要了解更多相关，可以继续关注我们的数据库板块。

您可能感兴趣的文档:

--结束END--

本文标题: MySQL中搭建ProxySQL Cluster的详细步骤

本文链接: https://lsjlt.com/news/36199.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

猜你喜欢
  • MySQL中搭建ProxySQL Cluster的详细步骤
    下文给大家带来关于MySQL中搭建ProxySQL Cluster的详细步骤，感兴趣的话就一起来看看这篇文章吧，相信看完MySQL中搭建ProxySQL Cluster的详细步骤对大家多少有点帮助吧。环境：...
    99+
    2024-04-02
  • 搭建redis+mysql架构的详细步骤
    下面一起来了解下搭建redis+mysql架构的详细步骤，相信大家看完肯定会受益匪浅，文字在精不在多，希望搭建redis+mysql架构的详细步骤这篇短内容是你想要的。redis+mysql框架搭建&nbs...
    99+
    2024-04-02
  • Gitlab搭建详细步骤
    Gitlab的概念        GitLab 是一个用于仓库管理系统的开源项目，使用Git作为代码管理工具，并在此基础上搭建起来的Web服务。安装方法是参考GitLab在GitHub上的Wiki页面。Gitlab是目前被广泛使用的基于gi...
    99+
    2023-09-04
    运维 git linux centos 服务器
  • MySQL搭建主从复制详细步骤
    下面讲讲关于MySQL搭建主从复制详细步骤，文字的奥妙在于贴近主题相关。所以，闲话就不谈了，我们直接看下文吧，相信看完MySQL搭建主从复制详细步骤这篇文章你一定会有所受益。搭建MySQL主从复制需要提前准...
    99+
    2024-04-02
  • docker搭建memcached的详细步骤
    目录搭建步骤worker登录机器新建文件夹下载magent-0.5.tar.gz并安装创建Dockerfile并构建镜像构建镜像magent:v1pull memcached镜像手动...
    99+
    2024-04-02
  • 在CentOS中搭建Hadoop的详细步骤
    搭建说明：第一次搭建 Hadoop 的小伙伴，请严格按照文章中的软件环境和步骤搭建，不一样的版本都可能会导致问题。 软件环境： 虚拟机：VMware Pro14 linux：CentOS-6.4（下载地址，下载DVD版本...
    99+
    2022-06-04
    CentOS搭建Hadoop CentOS Hadoop搭建
  • mysql建表的详细步骤
    本文主要给大家简单讲讲mysql建表的详细步骤，相关专业术语大家可以上网查查或者找一些相关书籍补充一下，这里就不涉猎了，我们就直奔主题吧，希望mysql建表的详细步骤这篇文章可以给大家带来一些实际帮助。1....
    99+
    2024-04-02
  • 用XtraBackup搭建mysql主从复制的详细步骤
    本篇内容介绍了“用XtraBackup搭建mysql主从复制的详细步骤”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够...
    99+
    2024-04-02
  • 搭建Python Web环境的详细步骤
    这篇文章主要介绍“搭建Python Web环境的详细步骤”，在日常操作中，相信很多人在搭建Python Web环境的详细步骤问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”搭建Python Web环境的详细步骤...
    99+
    2023-06-17
  • centos搭建Gitlab的详细步骤（分享）
    随着软件开发的不断发展，代码版本控制成为了任何一个软件开发团队的必备工具。Gitlab作为一款免费、开源、易于安装和管理的代码管理平台已经广泛应用于各个开发团队中。本文将介绍在CentOS系统上搭建Gitlab的详细步骤。准备工作在开始安装...
    99+
    2023-10-22
  • VScode搭建OpenCV环境的详细步骤
    目录安装MinGW-w64安装CMake生成MakeFiles编译OpencvVScode配置　　用vscode来写opencv代码需要自己编译OpenCV，主要用到MinGW-w6...
    99+
    2024-04-02
  • Git服务器的详细搭建步骤
    这篇文章主要介绍“Git服务器的详细搭建步骤”，在日常操作中，相信很多人在Git服务器的详细搭建步骤问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”Git服务器的详细搭建步骤”的疑惑有所帮助！接下来，请跟着小编...
    99+
    2023-06-04
  • Redis的Cluster集群搭建的实现步骤
    目录一、引言二、Redis的Cluster模式介绍1、Redis群集101 2、Redis群集TCP端口 3、Redis集群和Docker 4、Redis集群数据分片 5、Redis...
    99+
    2024-04-02
  • vite的搭建与使用的详细步骤
    目录1.安装：2.在vite项目中使用TypeScript3.vite项目使用less sass scss 4.vite打包5.下面就来创建一个标准的项目实际开发中编写的代...
    99+
    2024-04-02
  • MySQL创建视图的详细步骤
    下面讲讲关于MySQL创建视图的详细步骤，文字的奥妙在于贴近主题相关。所以，闲话就不谈了，我们直接看下文吧，相信看完MySQL创建视图的详细步骤这篇文章你一定会有所受益。1.视图概述：视图是从一个或多个表导...
    99+
    2024-04-02
  • linux搭建ldap服务器的详细步骤
    本篇内容介绍了“linux搭建ldap服务器的详细步骤”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！安装openldap-servers软件...
    99+
    2023-06-13
  • RHEL搭建FTP服务器的详细步骤
    这篇文章主要介绍“RHEL搭建FTP服务器的详细步骤”，在日常操作中，相信很多人在RHEL搭建FTP服务器的详细步骤问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”RHEL搭建FTP服务器的详细步骤”的疑惑有所...
    99+
    2023-06-17
  • centos搭建部署docker环境的详细步骤
    目录1、Docker的存在解决的问题1.1 统一标准1.2 资源隔离2、Docker的建构3、安装Docker1、卸载以前安装的docker2、配置yun源3、安装docker4、启动docker并设置为开机自启5、查看一下当前的docke...
    99+
    2024-04-02
  • 使用vscode搭建javaweb项目的详细步骤
    目录工具准备创建web项目运行项目总结工具准备 jdk, maven, tomcat9, vscode; 上述软件的安装配置在百度就能找到很多教程, 所以此处不详述. (jdk使用1...
    99+
    2022-11-13
    vscode开发javaweb项目 vscode怎么创建javaweb工程 vscode配置javaweb环境
  • eclipse搭建android开发环境详细步骤
    搭建android应用的开发环境，一套程序下来也是相当繁琐的，这里我整理下一整套详细流程： 1，下载JDK 去oracle官网下载最新版本的jdk，官网地址 http://ww...
    99+
    2022-06-06
    环境 Eclipse android开发 Android
软考高级职称资格查询
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作