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 组成的集群保持同步。
