前言
最近做一个项目，项目考虑了一些风险，其中就有mysql宕机的风险，mysql是申请了两台服务器。于是打算搞个主主复制，用keepalived进行漂移实现高可用。
方案可行性
首先主主复制可以保证两台机器数据同步，keeplalived通过虚拟ip，保证我们的mysql可以快速切换。方案可行。
搭建步骤
首先我们准备好两台已经搭建好的mysql服务器。
ip分别为15.1.1.46，15.1.1.50
修改配置文件
在第一台mysql服务器上的my.cf文件中，我们增加如下内容。
-
server-id=1
-
log-bin=mysql-bin
-
binlog-ignore-db=mysql (可以不加)
-
binlog-ignore-db=information_schema (可以不加)
-
binlog-do-db=mydatabase (可以不加)
-
binlog_format=STATEMENT
-
auto-increment-increment = 2
-
auto-increment-offset = 1
增加后的整体情况如下：
-
[mysqld]
-
-
server-id=1
-
log-bin=mysql-bin
-
binlog_format=STATEMENT
-
auto-increment-increment = 2
-
auto-increment-offset = 1
-
-
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
-
-
basedir = /home/mysql-5.7.25
-
datadir = /home/mysql-5.7.25/data
-
port = 3306
-
socket = /tmp/mysql.sock
-
character-set-server=utf8
-
-
log-error = /home/mysql-5.7.25/data/mysqld.log
-
pid-file = /home/mysql-5.7.25/data/mysqld.pid
第一台机器配置完毕。
第二台机器进行如下配置：
-
server-id=2
-
log-bin=mysql-bin
-
binlog_format=STATEMENT
-
auto-increment-increment = 2
-
auto-increment-offset = 2
和第一台基本一样，不过是increment不同，防止自增冲突。
整体配置文件如下：
-
[mysqld]
-
-
server-id=2
-
log-bin=mysql-bin
-
binlog_format=STATEMENT
-
auto-increment-increment = 2
-
auto-increment-offset = 2
-
-
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
-
-
basedir = /home/mysql-5.7.25
-
datadir = /home/mysql-5.7.25/data
-
port = 3306
-
socket = /tmp/mysql.sock
-
character-set-server=utf8
-
-
log-error = /home/mysql-5.7.25/data/mysqld.log
-
pid-file = /home/mysql-5.7.25/data/mysqld.pid
两台机器全部重启
service mysql restart
第二台机器复制第一台，第一台作为主机
第一台机器执行如下：
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
展示当前bin-log位置
show master status;
记住上方的file 和postion
切换到第二台机器执行如下
change master to master_host='15.1.1.46',master_user='root',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154;
开启复制
start slave;
展示从机复制情况
show slave status\G;
两个都是yes证明复制成功。
第一台机器复制第二台，第二台作为主机
第二台机器执行如下：
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
展示master的位置
show master status;
切换到第一台机器
执行如下命令
change master to master_host='15.1.1.50',master_user='root',master_password='123456',master_log_file='mysql-bin.000007',master_log_pos=154;
然后开启复制
start slave;
展示从机的复制情况
show slave status\G;
如果下图中两个都是yes则证明复制成功。
使用keepalived进行漂移保证高可用
(1)安装yum包
yum install -y gcc openssl-devel popt-devel
(2)上传keepalived并解压
tar -zxvf keepalived-1.2.15.tar.gz
(3)解压keepalived并进行编译
cd keepalived-1.2.15
./configure --prefix=/usr/local/keepalived
make && make install
(4)进行配置文件的初始化复制工作
cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d
cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig
mkdir /etc/keepalived
cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
(5)开机自启
chkconfig --add keepalived
chkconfig keepalived on
以上步骤第二台机器也如此执行
(6)进入主机 /etc/keepalived。修改里面的 keepalived.conf文件
-
! Configuration File for keepalived
-
-
global_defs {
-
router_id MYSQL-HA
-
}
-
-
vrrp_script check_mysql {
-
script "/home/mysql/mysql.sh"
-
interval 1
-
weight 2
-
}
-
-
vrrp_instance VI_1 {
-
state MASTER
-
interface ens33
-
virtual_router_id 51
-
priority 100
-
advert_int 1
-
authentication {
-
auth_type PASS
-
auth_pass 1111
-
}
-
track_script {
-
check_mysql
-
}
-
virtual_ipaddress {
-
15.1.1.99
-
}
-
}
(7)编写监测的自杀脚本
当mysql挂掉之后，keepalived监控到mysql挂掉，keepalived才能进行漂移。
-
#!/bin/bash
-
ps -ef | grep mysqld| grep -v grep &> /dev/null
-
if [ $? -eq 0 ]
-
then
-
echo "mysql服务正常运行！！！"
-
else
-
service keepalived stop
-
echo "mysql服务已经停止！请及时解决！！！"
-
fi
(8)编写从机的keepalived的脚本
上面的步骤里面的keepalived的conf按照下面的改下。其他不用动。
state MASTER 改为BACKUP
priority 100 改为90
(9)将keepalived的开启
service keepalived start
总结
至此mysql高可用已经搭建完毕。
评论（0）