使用keepalived实现mysql双机热备

master:192.168.15.154
slave:192.168.15.150
vip:192.168.15.200
操作系统版本:CentOS 6.3
mysql版本:5.6.37
keepalived版本:1.2.7

1、在master的/etc/my.cnf的[mysqld]下面添加如下配置

server-id=1
auto-increment-offset=1
auto-increment-increment=2
log-bin=mysql-bin
binlog_format=MIXED
binlog-do-db=minar
sync_binlog=1
log-bin-trust-function-creators=1
innodb_flush_log_at_trx_commit=1
relay_log=mysql-relay-bin
replicate-do-db=minar
log_slave_updates=ON
slave-skip-errors=all

2、在slave的/etc/my.cnf的[mysqld]下面添加如下配置

server-id=2
auto-increment-offset=2
auto-increment-increment=2
log-bin=mysql-bin
binlog_format=MIXED
binlog-do-db=minar
sync_binlog=1
log-bin-trust-function-creators=1
innodb_flush_log_at_trx_commit=1
relay_log=mysql-relay-bin
replicate-do-db=minar
log_slave_updates=ON
slave-skip-errors=all

3、在master的mysql控制台执行如下命令

grant replication slave on *.* to 'replicate'@'192.168.15.150' identified by '123456';
flush privileges;

4、在slave的mysql控制台执行如下命令

grant replication slave on *.* to 'replicate'@'192.168.15.154' identified by '123456';
flush privileges;

5、在master的mysql控制台执行命令:flush tables with read lock;(锁过所有表的写,此时可读),执行命令:show master status;复制File和Position这两个值,等会将会用到,保持会话打开状态,不能退出会话,如果退出会话将会自动解锁。打开一个新的master的ssh终端,执行命令:mysqldump -uroot -p -R minar > ~/minar.sql,输入密码开始备份数据,备份结束后,回到master之前锁表的mysql控制台,执行命令:unlock tables;(解锁),然后在master的ssh终端执行命令:scp ~/minar.sql 192.168.15.150:~,将master的~/minar.sql复制到slave中的~目录下。

6、在slave的mysql控制台执行如下命令:

CREATE DATABASE IF NOT EXISTS minar DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
GRANT ALL ON `minar`.* TO 'minar'@'%' IDENTIFIED BY 'minar' WITH GRANT OPTION;
flush privileges;

7、在slave的ssh终端中执行命令:mysql -uroot -p minar < ~/minar.sql,输入密码开始恢复数据,恢复结束后,进入slave的mysql控制台,执行命令:show master status;复制File和Position这两个值,等会将会用到。

8、在master的mysql控制台执行如下命令:
注意:master_log_file是slave中复制的File的值,master_log_pos是slave中复制的Position的值,请自行替换。

stop slave;
change master to master_host='192.168.15.150',master_user='replicate',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=23771053;
start slave;

show slave status;查看Slave_IO_Running和Slave_SQL_Running都是Yes表示成功

9、在slave的mysql控制台执行如下命令:
注意:master_log_file是master中复制的File的值,master_log_pos是master中复制的Position的值,请自行替换。

stop slave;
change master to master_host='192.168.15.154',master_user='replicate',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=12085;
start slave;

show slave status;查看Slave_IO_Running和Slave_SQL_Running都是Yes表示成功

10、master和slave安装keepalived
下载:wget http://www.keepalived.org/software/keepalived-1.2.7.tar.gz
解压:tar -zxvf keepalived-1.2.7.tar.gz -C ~
安装:

cd ~/keepalived-1.2.7/
./configure --prefix=/usr/local/keepalived
#如果出现错误:configure: error: libnfnetlink headers missing,执行命令:yum install -y libnfnetlink-devel,然后再执行上一条configure命令
make && make install
cd ~
cp -rf /usr/local/keepalived/etc /
cp -rf /usr/local/keepalived/bin /usr/local/keepalived/sbin /usr/
cp -rf /usr/local/keepalived/share /usr/local/
rm -rf /usr/local/keepalived/ ~/keepalived-1.2.7/
chkconfig --add keepalived
chkconfig keepalived on

11、master的/etc/keepalived/keepalived.conf,配置如下内容

global_defs {
    router_id MYSQL_DEVEL
}

vrrp_script chk_mysql {
    script "</dev/tcp/127.0.0.1/3306"
    interval 2
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth9
    virtual_router_id 51
    priority 100
    nopreempt
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.15.200
    }
    track_script {
       chk_mysql
    }
}

12、slave的/etc/keepalived/keepalived.conf,配置如下内容

global_defs {
    router_id MYSQL_DEVEL
}

vrrp_script chk_mysql {
    script "</dev/tcp/127.0.0.1/3306"
    interval 2
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth1
    virtual_router_id 51
    priority 99
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.15.200
    }
    track_script {
       chk_mysql
    }
}

13、master和slave启动/关闭/重启keepalived

service keepalived start
service keepalived stop
service keepalived restart

14、keepalived配置注意事项
nopreempt表示当前节点的运行state(不是配置文件中的初始state)为BACKUP时且priority比当前运行state为MASTER大的情况下,不抢占为MASTER,nopreempt只在较大的priority的节点上配置。
master节点和slave节点的初始state都设置为BACKUP,且master节点的priority比slave大,master节点设置为nopreempt,防止master节点恢复正常后抢占为MASTER。

标签: 使用keepalived实现mysql双机热备

添加新评论