1.MySQL 采用了双主同步的部署方式，实现了数据高可用，单节点故障的情况下不会丢失用户的相关数据。
2.应用访问 MySQL 采用了本地 HA 代理方式，对于前端，因为是本地代理所以任何一台代理服务器（应用服务器）宕机均不会影响其它应用服务器的使用，对于后端，因为是 HA 代理模式，所以 MySQL 只要有一个节点存活，应用均可正常访问。
1.MySQL 配置双主模式
# Master 及 Slave 节点配置
[mysqld]
server-id=21 # 同一集群内不可出现相同的
id log_bin = /data/mysql/mysql-bin
# Master 节点创建主从复制用户
create user 'repl'@'%' identified with mysql_native_password by 'password'; grant replication slave on *.* to 'repl'@'%';
#查看 Master 节点 log_file 及 log_pos
show master status;
#Slave 节点配置 Master 节点信息
change master to master_host='mysql-master', master_port=4306, master_user='repli', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=123299 ;
# Slave 节点启用 slave
start slave;
左滑查看更多→
2.应用服务器配置 Nginx HA 代理
upstream mysql{
server mysql-node-1:{{ mysql_port }} max_fails=10 fail_timeout=30s;
server mysql-node-2:{{ mysql_port }} backup;
}
server {
listen 5307;
proxy_pass mysql;
proxy_connect_timeout 5s;
proxy_timeout 120s;
access_log /data/logs/mysql-ha-access.log stream_main;
error_log /data/logs/mysql-ha-error.log;
}
左滑查看更多→
3.MySQL 其中一台节点配置探测脚本，只有另一台节点宕机才允许应用访问
#!/bin/bash
export PATH='/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin'
mysql_status=`{{ mysql_app_path }}/bin/mysqladmin -u{{ database_user }} -p{{ database_password }} -hrce-mysql-node-1 -P{{ mysql_port }} PING 2>/dev/null`
Date=`date '+%Y-%m-%d %H:%M:%S'`
Date_m=`date '+%Y%m'`
if [ "$mysql_status" != "mysqld is alive" ]
then
iptables -D INPUT -s {{ host }} -j ACCEPT >/dev/null 2>&1
iptables -I INPUT -s {{ host }} -j ACCEPT
echo "$Date rce-mysql-node-1 is failed !!!" >> "{{ logs_path }}/scripts/set-mysql-iptables_$Date_m.log"
echo "$Date Accept service access rce-mysql-node-2" >> "{{ logs_path }}/scripts/set-mysql-iptables_$Date_m.log"
else
iptables -D INPUT -p tcp --dport 4306 -j DROP >/dev/null 2>&1
iptables -I INPUT -p tcp --dport 4306 -j DROP
iptables -D INPUT -s rce-mysql-node-1 -p tcp --dport 4306 -j ACCEPT >/dev/null 2>&1
iptables -I INPUT -s rce-mysql-node-1 -p tcp --dport 4306 -j ACCEPT
echo "$Date rce-mysql-node-1 is normal" >> "{{ logs_path }}/scripts/set-mysql-iptables_$Date_m.log"
echo "$Date Denial of service access rce-mysql-node-2" >> "{{ logs_path }}/scripts/set-mysql-iptables_$Date_m.log"
fi
左滑查看更多→
[mysqld]
user = {{ mysql_user }}
2.MySQL 授权需指明需访问哪个应用的库
grant all on {{ database_name }}.* to {{ database_user }}@'%' identified by {{ database_password }};
左滑查看更多→
3.MySQL 相关目录均在配置文件中指定至一级目录
[mysqld]
socket = /data/app/mysql-node-2/mysql.sock
datadir = /data/data/mysql-node-2
socket = /data/app/mysql-node-2/mysql.sock
pid-file = /data/app/mysql-node-2/mysqld.pid
tmpdir = /data/app/mysql-node-2/tmp
log-error = /data/logs/mysql-node-2/mysqld.err
slow_query_log_file = /data/logs/mysql-node-2/mysql-slow.log
log_bin = /data/logs/mysql-node-2/mysql-bin
log-bin-index = /data/logs/mysql-node-2/mysql-bin.index
relay-log = /data/logs/mysql-node-2/relay-bin
relay-log-index = /data/logs/mysql-node-2/relay-bin.index
lc_messages_dir = /data/app/mysql-node-2/share
pid-file = /data/app/mysql-node-2/mysqld.pid
左滑查看更多→
组织机构可以先从 MySQL 优化来确保通信系统底层支持系统稳定，来提高整个通信系统的安全高可用。
精彩推荐
点击阅读原文 了解更多
↓↓↓