MYSQL高可用方案主要分为两大类，一类是前置管理，一类是后置管理。
前置管理的思想是利用各种前置管理工具，动态切换或者分发请求到不同的实例并切换master，如keepalived、MHA、packmaker+Corosync、MySQLProxy等，一般通过暴露VIP将整个MYQL集群隐藏起来，其中Proxy主要用以读写分离。后置管理则是利用共享存储的思想，如NDB方案。由于我们的用户尚不足百万，所以一开始的计划是选择最简单的前置管理模式-双主+keepalived方案，后来因为阿里云停用了VIP服务，于是购买了其提供的负载均衡SLB服务(替代keepalived使用)，最终完成了双主+VIP的搭建。不管怎样，keepalived的思想还是比较经典的，所以也做了简单记录。
Part1 mysql bin 方式安装和配置
#查询已经存在的mysql并删除
rpm -qa |grep mysql
rpm -qa |grep mariadb
#删除已经存在的mysql
rpm -e --nodeps mariadbxxxx
rpm -e --nodeps mysqlxxxx
#下载解压并copy到/usr/local/mysql
#创建mysql数据日志目录
mkdir -p /data/mysql/data
mkdir -p /data/mysql/log
mkdir -p /data/mysql/bak
#初始化f
[mysql]
default-character-set=utf8
[mysqld]
character_set_server=utf8
default-storage-engine=INNODB
datadir=/data/mysql/data
#socket=/data/mysql/data/mysql.sock
port=3306
symbolic-links=0
log-error=/data/mysql/log/mysqld.log
pid-file=/data/mysql/mysqld.pid
###将support-files的mysql.server copy 到etc/init.d/以开机启动
cd /usr/local/mysql/support-files/
cp mysql.server /etc/init.d/mysql
#修改mysql默认配置
vim /etc/init.d/mysql
basedir=/usr/local/mysql
datadir=/data/mysql/data
#创建mysql系统用户并授权到mysql启动
groupadd mysql
useradd -r -g mysql mysql
passwd mysql
mysqlpwd
chown -R mysql:mysql /usr/local/mysql/
chown -R mysql:mysql /data/mysql/
#将mysqlbin目录添加到环境变量
echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile
source /etc/profile
#初始化mysql数据库
yum install libaio-devel.x86_64
mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data
#获取初始密码
tail -f /data/mysql/log/mysql.log
[Note] A temporary password is generated for root@localhost: im>wqfae&7tY
#给数据库加密
mysql_ssl_rsa_setup --datadir=/data/mysql/data
chown -R mysql:mysql /data/mysql/
##设置开机自动重启
chkconfig --add mysql
chkconfig mysql on
##查看、停止、重启mysql
systemclt status mysql.service
systemctl start mysql.service
systemctl stop mysql.service
systemctl restart mysql.service
#启动mysql服务
systemctl start mysql.service
#使用初始密码登录
mysql -uroot -p --socket=/data/mysql/data/mysql.sock
#设置新密码,授权root用户访问
set password=password('*******');
grant all privileges on *.* to root@'%' identified by '('*******');';
flush privileges;
Part2 mysql高可用配置 - keepalived
1. 配置master1和master2 binlog
#master1配置
[mysqld]
log-bin=mysql-bin
server-id=70
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates=1
#binlog-ignore=mysql
#binlog-ignore=information_schema
#replicate-do-db=platform88
#master2配置
[mysqld]
log-bin=mysql-bin
server-id=71
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates=1
#binlog-ignore=mysql
#binlog-ignore=information_schema
#replicate-do-db=platform88
#read_only=1
###备注：手动切换主库
mysql> stop slave;
#千万不要执行 reset master，否则会从最先的GTID上开始执行。
mysql> change master to master_host='127.0.0.1',master_user='rep',master_password='rep',master_port=3307,master_auto_position=1; #指定到另一个比较接近主的从上。
mysql> start slave;
2. 创建可互相访问的用户
#master1配置
CREATE USER 'ms_repl_usr'@'172.26.27.71' IDENTIFIED BY 'ms_repl_pwd';
GRANT REPLICATION SLAVE ON *.* TO 'ms_repl_usr'@'172.26.27.71';
FLUSH PRIVILEGES;
#master2配置
CREATE USER 'ms_repl_usr'@'172.26.27.70' IDENTIFIED BY 'ms_repl_pwd';
GRANT REPLICATION SLAVE ON *.* TO 'ms_repl_usr'@'172.26.27.70';
FLUSH PRIVILEGES;
查看binlog及位移
show master status \G
#master1配置
CHANGE MASTER TO
MASTER_HOST='172.26.27.71',
MASTER_PORT=30468,
MASTER_USER='ms_repl_usr',
MASTER_PASSWORD='ms_repl_pwd',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=1459;
或者：
change master to master_host='172.26.27.71',master_user='ms_repl_usr',master_password='ms_repl_pwd', master_auto_position=1;
#master2配置
CHANGE MASTER TO
MASTER_HOST='172.26.27.70',
MASTER_PORT=30468,
MASTER_USER='ms_repl_usr',
MASTER_PASSWORD='ms_repl_pwd',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=2086;
或者：
change master to master_host='172.26.27.70'',master_user='ms_repl_usr',master_password='ms_repl_pwd', master_auto_position=1;
#分别重启master1和master2
查看 salve status
show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
3. keepalived 配置
keepalived主要用检测对方服务的状态，并通过关闭或者重启自身服务达到切换VIP优先级的效果。keepalived可以监控4层或者7层网络，4层通过配置虚拟服务实现，本例子中使用7层监控。
#master1 配置
vrrp_script chk_mysql {
script "/etc/keepalived/check_mysql.sh"
interval 30 #设置检查间隔时长，可根据自己的需求自行设定
}
vrrp_instance VI_1 {
state BACKUP #通过下面的priority来区分MASTER和BACKUP，也只有如此，底下的nopreempt才有效
interface eth0
virtual_router_id 51
priority 100
advert_int 1
nopreempt #防止切换到从库后，主keepalived恢复后自动切换回主库
authentication {
auth_type PASS
auth_pass PWD
}
track_script {
chk_mysql
}
virtual_ipaddress {
192.168.72.109
}
}
#master2配置
! Configuration File for keepalived
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 90
advert_int 1
authentication {
auth_type PASS
auth_pass PWD
}
notify_master /etc/keepalived/notify_master_mysql.sh #此条指令告诉keepalived发现自己转为MASTER后执行的脚本
virtual_ipaddress {
192.168.72.109
}
}
#check_mysql.sh主要用来监听或者链接mysql实例，如果发现mysql无法连接，则直接重启keepalived，或者停止keepalived，让虚拟IP漂移到另一台服务器上。这里以监听端口为例：
#!/bin/bash
#This scripts is check for Mysql Slave status
echo 'testing...'
counter=`netstat -luntp|grep 3306|wc -l`
echo 'after counting...'
if [ "$counter" -eq 0 ];
then
echo "3306 is down..."
systemctl stop keepalived
else
echo '3306 is working..'
fi
check脚本中经常遇到的一个问题是，命令需要用全路径，如mysql登录，
/usr/local/mysql/bin/mysql -uroot -pxxxx --port 3306
修改keepalived的日志输出，默认输出在/var/log/message。从keepalived的日志中可以看到，master切换的过程。
vim /etc/rsyslog.conf
keepalived -S 0
local0.* /var/log/keepalived.log
阿里云SLB配置实现keepalived效果
#通过负载均衡管理界面创建主备服务器组
#添加双主实例
这样就完成了阿里云双主的搭建，从这个结构延伸开来，可以分别为两个主节点添加slave节点，行程4或者6节点结构。如果需要读写分离，提高资源的利用率，可以通过前置mysqlproxy来将读写分开。
如果觉得《阿里高可用mysql搭建方案_阿里云环境迁移记录 - MYSQL高可用搭建》对你有帮助，请点赞、收藏，并留下你的观点哦！
