安装 ProxySQL

 

1.安装 ProxySQL

# 添加官方仓库
sudo tee /etc/yum.repos.d/proxysql.repo <<'EOF'
[proxysql]
name=ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/centos/7
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key
EOF

# 安装 ProxySQL
sudo yum install -y proxysql

2.配置 ProxySQL

# 启动服务
sudo systemctl start proxysql
sudo systemctl enable proxysql

# 登录管理界面
mysql -u admin -padmin -h 127.0.0.1 -P 6032

3.配置数据库服务器

-- 添加主库
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (10, '124.70.211.62', 3306);

-- 添加从库
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (20, '121.37.159.140', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (20, '124.71.153.163', 3306);

-- 保存配置
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

4.配置监控用户

-- 设置监控用户
UPDATE global_variables SET variable_value='root' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='admindev123$%^' WHERE variable_name='mysql-monitor_password';

-- 设置监控间隔
UPDATE global_variables SET variable_value='2000' WHERE variable_name='mysql-monitor_connect_interval';
UPDATE global_variables SET variable_value='2000' WHERE variable_name='mysql-monitor_ping_interval';

-- 保存配置
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

5.配置读写分离

-- 创建读写分离规则
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) VALUES
(1, 1, '^SELECT.*FOR UPDATE', 10, 1),  -- 写操作路由到主库
(2, 1, '^SELECT', 20, 1),              -- 读操作路由到从库
(3, 1, '.*', 10, 1);                  -- 其他所有操作路由到主库

-- 保存配置
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

6. 配置客户端用户

-- 添加客户端用户
INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('root', 'admindev123$%^', 10);

-- 设置用户属性
UPDATE mysql_users SET transaction_persistent=1 WHERE username='root';

-- 保存配置
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

7.验证配置

-- 检查服务器状态
SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start DESC LIMIT 3;

-- 检查复制延迟
SELECT * FROM monitor.mysql_server_replication_lag_log ORDER BY time_start DESC LIMIT 3;

-- 检查查询规则
SELECT * FROM runtime_mysql_query_rules;

8. 测试连接

# 通过 ProxySQL 连接数据库
mysql -u root -p'admindev123$%^' -h 127.0.0.1 -P 6033 -e "SELECT @@server_id;"

# 测试读写分离
mysql -u root -p'admindev123$%^' -h 127.0.0.1 -P 6033 -e "CREATE DATABASE proxy_test;"
mysql -u root -p'admindev123$%^' -h 127.0.0.1 -P 6033 -e "SELECT * FROM proxy_test.test_table;"

　　

安全加固（必须完成）

1.SSH 安全加固

# 禁用 root 登录
sudo sed -i 's/#PermitRootLogin yes/PermitRootLogin no/' /etc/ssh/sshd_config

# 禁用密码登录
sudo sed -i 's/PasswordAuthentication yes/PasswordAuthentication no/' /etc/ssh/sshd_config

# 更改 SSH 端口
sudo sed -i 's/#Port 22/Port 2222/' /etc/ssh/sshd_config

# 重启 SSH
sudo systemctl restart sshd

# 创建新用户
sudo useradd adminuser
sudo passwd adminuser
sudo usermod -aG wheel adminuser

2. 防火墙配置

# 仅允许必要端口
sudo firewall-cmd --permanent --remove-service=ssh
sudo firewall-cmd --permanent --add-port=2222/tcp
sudo firewall-cmd --permanent --add-port=6033/tcp  # ProxySQL 端口
sudo firewall-cmd --reload

3. 安装 fail2ban

# 安装 fail2ban
sudo yum install -y epel-release
sudo yum install -y fail2ban

# 配置 SSH 保护
sudo tee /etc/fail2ban/jail.local <<'EOF'
[sshd]
enabled = true
port = 2222
filter = sshd
logpath = /var/log/secure
maxretry = 3
bantime = 86400
EOF

# 启动服务
sudo systemctl start fail2ban
sudo systemctl enable fail2ban

 

为什么选择 ProxySQL 而不是 MaxScale

1. ​更简单的配置​：ProxySQL 的配置更直观易懂
2. ​更好的兼容性​：ProxySQL 对 MySQL 5.7 的支持更成熟
3. ​更稳定的表现​：ProxySQL 在生产环境中表现更可靠
4. ​更活跃的社区​：ProxySQL 有更活跃的开发和用户社区
5. ​更少依赖问题​：ProxySQL 的依赖更少，安装更简单

监控和维护

1. ProxySQL 管理界面

mysql -u admin -padmin -h 127.0.0.1 -P 6032

2. 常用监控命令

-- 查看后端服务器状态
SELECT * FROM mysql_servers;

-- 查看查询规则
SELECT * FROM mysql_query_rules;

-- 查看连接统计
SELECT * FROM stats.stats_mysql_connection_pool;

-- 查看查询性能
SELECT * FROM stats.stats_mysql_query_digest;

3. 备份配置

# 备份 ProxySQL 配置
mysqldump -u admin -padmin -h 127.0.0.1 -P 6032 --databases main stats > proxysql_backup.sql