ProxySQL 配置文件

注意: 需要修改 proxysql_servers、interfaces ，其他的不用改动

#file proxysql.cnf
datadir="/data/proxysql/data"
errorlog="/data/proxysql/logs/proxysql.log"
admin_variables=
{
    admin_credentials="admin:admin;yoon_ha:aRnzLdkJq3cF7#99"
    mysql_ifaces="0.0.0.0:6032"
        cluster_username="yoon_ha"                           #集群用户名称，与最上面的相同
        cluster_password="aRnzLdkJq3cF7#99"                      #集群用户密码，与最上面的相同
        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.153"
                port=6032
                weight=1
                comment="proxysql_node01"
        },
        {
                hostname="192.168.1.44"
                port=6032
                weight=1
                comment="proxysql_node02"
        },
)
mysql_variables=
{
    threads=8
    max_connections=2048
    default_query_delay=0
    default_query_timeout=36000000
    have_compress=true
    poll_timeout=2000
    interfaces="0.0.0.0:6033"
    default_schema="information_schema"
    stacksize=1048576
    server_version="5.7.28"
    connect_timeout_server=3000
    monitor_username="monitor"
    monitor_password="Yoon123!@#"
    monitor_history=600000
    monitor_connect_interval=60000
    monitor_ping_interval=10000
    monitor_read_only_interval=1500
    monitor_read_only_timeout=500
    ping_interval_server_msec=120000
    ping_timeout_server=500
    commands_stats=true
    sessions_sort=true
    connect_retries_on_failure=10
}

配置proxysql.service

注意: ExecStart 中的配置文件名称，其他的不用修改

cat > /etc/systemd/system/proxysql.service<<EOF
[Unit]
Description=High Performance Advanced Proxy for MySQL
After=network.target
[Service]
Type=forking
RuntimeDirectory=proxysql
#PermissionsStartOnly=true
#ExecStartPre=/usr/bin/mkdir -p /var/run/proxysql /var/run/proxysql
#ExecStartPre=/usr/bin/chown -R proxysql: /var/run/proxysql/
ExecStart=/usr/local/proxysql/bin/proxysql --idle-threads -c /data/proxysql/conf/proxysql_6520.conf $PROXYSQL_OPTS
PIDFile=/data/proxysql/data/proxysql.pid
#StandardError=null  # all output is in stderr
SyslogIdentifier=proxysql
Restart=no
User=proxysql
Group=proxysql
PermissionsStartOnly=true
UMask=0007
LimitNOFILE=102400
LimitCORE=1073741824
ProtectHome=yes
NoNewPrivileges=true
CapabilityBoundingSet=CAP_SETGID CAP_SETUID CAP_SYS_RESOURCE
RestrictAddressFamilies=AF_INET AF_INET6 AF_UNIX AF_ALG
ProtectSystem=full
PrivateDevices=yes
[Install]
WantedBy=multi-user.target
EOF

启动服务

systemctl start  proxysql.service

systemctl status  proxysql.service

配置MySQL 信息

1、配置读写组

# hostgroup_id = 0 是主节点，hostgroup_id=1 是从节点
INSERT INTO mysql_replication_hostgroups VALUES(0,1,"read_only","mysql replication with read and write separation”);

load mysql servers to runtime;save mysql servers to disk;

2、配置后端MySQL 主从服务信息

# hostgroup_id = 0 是主节点，hostgroup_id=1 是从节点
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'192.168.1.44',5520,1,'Read Group’);

insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(0,'192.168.1.153',5520,1,'write Group’);

 
load mysql servers to runtime;
save mysql servers to disk;

3、配置访问账号

#SQL 审核账号
insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('sql_check','Yoon123!@#$%^',0,1);

#业务账号
insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('sql_rw','MVspG!WfyN4rHTKX',0,1);
 
 load mysql users to runtime;
 save mysql users to disk;

4、通过命令在集群中添加新节点

在集群中通过命令加入新节点：
mysql> update global_variables set variable_value="admin:admin;yoon_ha:fjdl#666" where variable_name ='admin-admin_credentials'; 

mysql> update global_variables set variable_value="yoon_ha" where variable_name ='admin-cluster_username';

mysql> update global_variables set variable_value="fjdl#666" where variable_name ='admin-cluster_password';

# 插入ProxySQL实例信息
mysql> insert into proxysql_servers(hostname,port) values('192.168.1.1',6032),('192.168.1.2',6032),('192.168.1.3',6032);

# 将更改的信息载入runtime环境
mysql >load admin variables to runtime;

mysql >load proxysql servers to runtime;

如果日志输出如下警告：

2021-05-01 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.

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

如果有如下输出警告：

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

需要手动LOAD PROXYSQL SERVERS TO RUNTIME，然后在 2 或者 3 上重新加上 1 的信息上，同步到整个集群中，1 实例方能排除数据冲突，真正的与 2，3 组成的集群保持同步。