MySQL主主互备结合keepalived实现高可用
原文：http://7424593.blog.51cto.com/7414593/1741717
试验环境：
master：192.168.1.210（CentOS6.5）
slave：192.168.1.211（CentOS6.5）
VIP：192.168.1.208
MySQL主主互备模式配置
step1：Master服务的/etc/my.cnf配置
|
1
2
3
4
5
6
7
8
9
10
11
12
|
[mysqld]basedir = /usr/local/mysqldatadir = /var/lib/mysqlport = 3306socket = /var/lib/mysql/mysql.sockserver_id = 1log-bin = mysql-binrelay-log = mysql-relay-binreplicate-wild-ignore-table=mysql.% #指定不需要复制的库，mysql.%表示mysql库下的所有对象replicate-wild-ignore-table=test.%replicate-wild-ignore-table=information_schema.% |
step2：Slave服务的/etc/my.cnf配置
|
1
2
3
4
5
6
7
8
9
10
11
12
|
[mysqld]basedir = /usr/local/mysqldatadir = /var/lib/mysqlport = 3306socket = /var/lib/mysql/mysql.sockserver_id = 2log-bin = mysql-binrelay-log = mysql-relay-binreplicate-wild-ignore-table=mysql.%replicate-wild-ignore-table=test.%replicate-wild-ignore-table=information_schema.% |
step3：重启两台主从mysql服务
|
1
2
3
4
5
6
|
[root@master ~]# service mysqld restartShutting down MySQL.. [ OK ]Starting MySQL. [ OK ][root@slave ~]# service mysqld restartShutting down MySQL.. [ OK ]Starting MySQL. [ OK ] |
step4：查看主从的log-bin日志状态
记录File和Position的值
|
1
2
3
4
5
6
7
|
[root@master ~]# mysql -uroot -ppasswd -e 'show master status'Warning: Using a password on the command line interface can be insecure.+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 | 414 | | | |+------------------+----------+--------------+------------------+-------------------+ |
|
1
2
3
4
5
6
7
|
[root@slave ~]# mysql -uroot -ppasswd -e 'show master status'Warning: Using a password on the command line interface can be insecure.+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 | 414 | | | |+------------------+----------+--------------+------------------+-------------------+ |
step5：创建主从同步replication用户
1、master
|
1
2
3
4
5
6
7
8
9
10
|
mysql> grant replication slave on *.* to 'replication'@'192.168.1.211' identified by 'replication';mysql> flush privileges;mysql> change master to -> master_host='192.168.1.211', -> master_user='replication', -> master_password='replication', -> master_port=3306, -> master_log_file='mysql-bin.000001', -> master_log_pos=414;mysql> start slave; |
2、slave
|
1
2
3
4
5
6
7
8
9
10
|
mysql> grant replication slave on *.* to 'replication'@'192.168.1.210' identified by 'replication';mysql> flush privileges;mysql> change master to -> master_host='192.168.1.210', -> master_user='replication', -> master_password='replication', -> master_port=3306, -> master_log_file='mysql-bin.000001', -> master_log_pos=414;mysql> start slave; |
同步失败可能需要停止或重设slave
mysql> stop slave;
mysql> reset slave;
step6：分别在master和slave上查看slave状态，验证是否成功配置主主复制模式
1、master
2、slave
slave状态同步过程可能需要重启MySQL服务
[root@master ~]# service mysqld restart
[root@slave ~]# service mysqld restart
step7：验证，在master上创建test1数据库，slave上查看是否同步
1、master上创建test1数据库
|
1
|
[root@master ~]# mysql -uroot -ppasswd -e 'create database test1' |
2、slave上查看是否同步创建test1
|
1
2
3
4
5
6
7
8
9
|
[root@slave ~]# mysql -uroot -ppasswd -e 'show databases'+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test1 |+--------------------+ |
安装和配置keepalived实现MySQL双主高可用
step1：安装keepalived
方法一：使用yum安装keepalived，需要安装epel-release源
[root@master ~]# rpm -ivh http://mirrors.opencas.cn/epel/6/i386/epel-release-6-8.noarch.rpm
[root@slave ~]# rpm -ivh http://mirrors.opencas.cn/epel/6/i386/epel-release-6-8.noarch.rpm
[root@slave ~]# yum -y install keepalived
查看keepalived相关目录
|
1
2
3
4
5
6
|
[root@slave ~]# ls /usr/sbin/keepalived /usr/sbin/keepalived[root@slave ~]# ls /etc/init.d/keepalived /etc/init.d/keepalived[root@slave ~]# ls /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf |
方法二：从keepalived官方网站http://www.keepalived.org下载源代码包编译安装
1、下载keepalived最新版
[root@master ~]# wget http://www.keepalived.org/software/keepalived-1.2.19.tar.gz
[root@slave ~]# wget http://www.keepalived.org/software/keepalived-1.2.19.tar.gz
2、安装keepalived依赖软件包
[root@master ~]# yum install pcre-devel openssl-devel popt-devel libnl-devel
3、解压并安装keepalived
|
1
2
3
4
5
|
[root@master ~]# tar zxf keepalived-1.2.19.tar.gz [root@master ~]# cd keepalived-1.2.19[root@master keepalived-1.2.19]# ./configure --prefix=/usr/local/keepalived --sysconf=/etc --with-kernel-dir=/usr/src/kernels/2.6.32-431.el6.x86_64 |
|
1
2
|
[root@master keepalived-1.2.19]# make[root@master keepalived-1.2.19]# make install |
查看keepalived相关的文件
|
1
2
3
4
|
[root@master keepalived-1.2.19]# ls /etc/keepalived/keepalived.conf samples[root@master keepalived-1.2.19]# ls /etc/init.d/keepalived /etc/init.d/keepalived |
链接/usr/local/keepalived/sbin/keepalived到/sbin/目录
|
1
|
[root@master keepalived-1.2.19]# ln -s /usr/local/keepalived/sbin/keepalived /sbin/ |
设置keepalived启动级别
|
1
2
|
[root@master keepalived-1.2.19]# chkconfig --add keepalived[root@master keepalived-1.2.19]# chkconfig --level 35 keepalived on |
step2：配置keepalived
1、Master的keepalived.conf配置文件
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
! Configuration File for keepalivedglobal_defs { notification_email { root@huangmingming.cn 741616710@qq.com } notification_email_from keepalived@localhost smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id LVS_DEVEL}vrrp_instance HA_1 { state BACKUP #master和slave都配置为BACKUP interface eth0 #指定HA检测的网络接口 virtual_router_id 80 #虚拟路由标识，主备相同 priority 100 #定义优先级，slave设置90 advert_int 1 #设定master和slave之间同步检查的时间间隔 nopreempt #不抢占模式。只在优先级高的机器上设置即可 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { #设置虚拟IP，可以设置多个，每行一个 192.168.1.208/24 dev eth0 #MySQL对外服务的IP，即VIP }}virtual_server 192.168.1.208 3306 { delay_loop 2 #每隔2秒查询real server状态 lb_algo wrr #lvs 算法 lb_kinf DR #LVS模式（Direct Route） persistence_timeout 50 protocol TCP real_server 192.168.1.210 3306 { #监听本机的IP weight 1 notify_down /usr/local/keepalived/bin/mysql.sh TCP_CHECK { connect_timeout 10 #10秒无响应超时 bingto 192.168.1.208 nb_get_retry 3 delay_before_retry 3 connect_port 3306 } }} |
keepalived检测脚本，当其中一台MySQL服务出现故障down掉时，实现自动切换到正常的MySQL服务器继续提供服务
|
1
2
3
|
[root@master ~]# vim /usr/local/keepalived/bin/mysql.sh#!/bin/bashpkill keepalived |
2、Slave的keepalived.conf配置文件
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
|
! Configuration File for keepalivedglobal_defs { notification_email { root@huangmingming.cn 741616710@qq.com } notification_email_from keepalived@localhost smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id LVS_DEVEL}vrrp_instance HA_1 { state BACKUP #master和slave都配置为BACKUP interface eth0 #指定HA检测的网络接口 virtual_router_id 80 #虚拟路由标识，主备相同 priority 90 #定义优先级，slave设置90 advert_int 1 #设定master和slave之间同步检查的时间间隔 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { #设置虚拟IP，可以设置多个，每行一个 192.168.1.208/24 dev eth0 #MySQL对外服务的IP，即VIP }}virtual_server 192.168.1.208 3306 { delay_loop 2 lb_algo wrr lb_kinf DR persistence_timeout 50 protocol TCP real_server 192.168.1.211 3306 { #监听本机的IP weight 1 notify_down /usr/local/mysql/bin/mysql.sh TCP_CHECK { connect_timeout 10 bingto 192.168.1.208 nb_get_retry 3 delay_before_retry 3 connect_port 3306 } }} |
step3：授权VIP的root用户权限
授权远程主机可以通过VIP登录MySQL，并测试数据复制功能
|
1
2
|
mysql> grant all on *.* to root@'192.168.1.208' identified by '741616710';mysql> flush privileges; |
step4：测试keepalived高可用功能
1、远程主机登录通过VIP192.168.1.208登录MySQL，查看MySQL连接状态
|
1
2
3
4
5
6
7
|
mysql> show variables like 'hostname%';+---------------+--------+| Variable_name | Value |+---------------+--------+| hostname | master |+---------------+--------+1 row in set (0.00 sec) |
从上面查看的结果看样看出在正常情况下连接的是master
2、故障测试，停止master的MySQL服务，再次查看是否转移至slave服务器上
|
1
2
|
[root@master ~]# service mysqld stopShutting down MySQL.... SUCCESS! |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> show variables like 'hostname%';ERROR 2013 (HY000): Lost connection to MySQL server during querymysql> show variables like 'hostname%';ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 1268Current database: *** NONE ***+---------------+-------+| Variable_name | Value |+---------------+-------+| hostname | slave |+---------------+-------+1 row in set (0.01 sec) |
由测试结果可以看出，keepalived成功转移MySQL服务