# 配置MySQL Router
$ mysqlrouter –bootstrap root@pc-12345678.mysql.polardb.rds.aliyuncs.com:3306 –user=mysqlrouter

# 查看MySQL Router配置
$ cat /etc/mysqlrouter/mysqlrouter.conf
[DEFAULT]
logging_folder = /var/log/mysqlrouter
runtime_folder = /var/run/mysqlrouter
config_folder = /etc/mysqlrouter
data_folder = /var/lib/mysqlrouter
keyring_path = /var/lib/mysqlrouter/keyring
master_key_path =
connect_timeout = 10
read_timeout = 30
write_timeout = 30
dynamic_state = 1

[logger]
level = INFO

[routing:primary]
bind_address = 0.0.0.0
bind_port = 6446
destination_ports = 3306
destination_addresses = pc-12345678.mysql.polardb.rds.aliyuncs.com
mode = read-write
max_connections = 1024

[routing:replica]
bind_address = 0.0.0.0
bind_port = 6447
destination_ports = 3306
destination_addresses = pc-12345679.mysql.polardb.rds.aliyuncs.com,pc-12345680.mysql.polardb.rds.aliyuncs.com
mode = read-only
max_connections = 1024

# 启动MySQL Router
$ systemctl start mysqlrouter
$ systemctl enable mysqlrouter

# 查看MySQL Router状态
$ systemctl status mysqlrouter

# 启动ProxySQL
$ systemctl start proxysql
$ systemctl enable proxysql

# 连接ProxySQL管理接口
$ mysql -u admin -padmin -h 127.0.0.1 -P 6032

# 配置后端服务器
mysql> INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections) VALUES (1, ‘pc-12345678.mysql.polardb.rds.aliyuncs.com’, 3306, 1, 1000);
mysql> INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections) VALUES (2, ‘pc-12345679.mysql.polardb.rds.aliyuncs.com’, 3306, 1, 1000);
mysql> INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections) VALUES (2, ‘pc-12345680.mysql.polardb.rds.aliyuncs.com’, 3306, 1, 1000);

# 配置读写分离规则
mysql> INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (1, 1, ‘^SELECT.*FOR UPDATE$’, 1, 1);
mysql> INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (2, 1, ‘^SELECT’, 2, 1);

# 加载配置
mysql> LOAD MYSQL SERVERS TO RUNTIME;
mysql> SAVE MYSQL SERVERS TO DISK;
mysql> LOAD MYSQL QUERY RULES TO RUNTIME;
mysql> SAVE MYSQL QUERY RULES TO DISK;

# 连接ProxySQL
$ mysql -u fgedu -p -h 127.0.0.1 -P 6033

# 配置HAProxy
$ cat /etc/haproxy/haproxy.cfg
global
log 127.0.0.1 local2
chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid
maxconn 4000
user haproxy
group haproxy
daemon

defaults
mode http
log global
option httplog
option dontlognull
option http-server-close
option forwardfor except 127.0.0.1
option redispatch
retries 3
timeout http-request 10s
timeout queue 1m
timeout connect 10s
timeout client 1m
timeout server 1m
timeout http-keep-alive 10s
timeout check 10s
maxconn 3000

frontend mysql-read
bind *:3307
mode tcp
default_backend mysql-read-backend

frontend mysql-write
bind *:3308
mode tcp
default_backend mysql-write-backend

backend mysql-read-backend
mode tcp
balance roundrobin
server db1 pc-12345679.mysql.polardb.rds.aliyuncs.com:3306 check
server db2 pc-12345680.mysql.polardb.rds.aliyuncs.com:3306 check

backend mysql-write-backend
mode tcp
balance roundrobin
server db1 pc-12345678.mysql.polardb.rds.aliyuncs.com:3306 check

# 启动HAProxy
$ systemctl start haproxy
$ systemctl enable haproxy

# 查看HAProxy状态
$ systemctl status haproxy

# 配置Nginx
$ cat /etc/nginx/nginx.conf
user nginx;
worker_processes auto;
error_log /var/log/nginx/error.log;
pid /run/nginx.pid;

include /usr/share/nginx/modules/*.conf;

events {
worker_connections 1024;
}

stream {
upstream mysql-read {
server pc-12345679.mysql.polardb.rds.aliyuncs.com:3306 weight=1 max_fails=3 fail_timeout=30s;
server pc-12345680.mysql.polardb.rds.aliyuncs.com:3306 weight=1 max_fails=3 fail_timeout=30s;
}

upstream mysql-write {
server pc-12345678.mysql.polardb.rds.aliyuncs.com:3306 weight=1 max_fails=3 fail_timeout=30s;
}

server {
listen 3307;
proxy_pass mysql-read;
proxy_connect_timeout 10s;
proxy_timeout 30s;
}

server {
listen 3308;
proxy_pass mysql-write;
proxy_connect_timeout 10s;
proxy_timeout 30s;
}
}

http {
include /etc/nginx/mime.types;
default_type application/octet-stream;

log_format main ‘$remote_addr – $remote_user [$time_local] “$request” ‘
‘$status $body_bytes_sent “$http_referer” ‘
‘”$http_user_agent” “$http_x_forwarded_for”‘;

access_log /var/log/nginx/access.log main;

sendfile on;
tcp_nopush on;
tcp_nodelay on;
keepalive_timeout 65;
types_hash_max_size 2048;

include /etc/nginx/conf.d/*.conf;
}

# 启动Nginx
$ systemctl start nginx
$ systemctl enable nginx

# 查看Nginx状态
$ systemctl status nginx

# 高级管理的内容
– 配置管理：管理读写分离和负载均衡的配置
– 性能调优：根据监控数据，调优系统性能
– 故障处理：处理系统故障，确保系统的可用性
– 容量规划：根据系统负载，规划系统容量
– 安全管理：确保系统的安全性

# 高级监控工具
– 云监控：使用阿里云云监控监控PolarDB实例
– Prometheus：使用Prometheus监控系统和数据库指标
– Grafana：使用Grafana可视化监控数据
– Zabbix：使用Zabbix监控系统和网络设备
– ELK Stack：使用ELK Stack分析日志

# 高级管理工具
– MySQL Router：管理MySQL Router的配置
– ProxySQL：管理ProxySQL的配置
– HAProxy：管理HAProxy的配置
– Nginx：管理Nginx的配置