Mysql主主复制故障自动切换解决方案

  1. MySQL-VIP：192.168.0.50  
   2. MySQL-master1：192.168.0.42 
   3. MySQL-master2：192.168.0.49
   
   5. OS版本：CentOS 5.5  
   6. MySQL版本：5.5.13 
   7. Keepalived版本：keepalived-1.2.7.tar.gz 


一、MySQL master-master配置
　1、修改MySQL配置文件
　　两台MySQL均如要开启binlog日志功能，开启方法：在MySQL配置文件[MySQLd]段中加上log-bin=MySQL-bin选项
　　两台MySQL的server-ID不能一样，默认情况下两台MySQL的serverID都是1，需将其中一台修改为2即可
　　这里省略掉创建主主过程，简单描述如下，在2太服务器上分别创建账号，然后实例化彼此的从服务器！即可！
二、keepalived安装及配置
安装keepalived
   1. #tar zxvf keepalived-1.1.20.tar.gz  
   2. #cd keepalived-1.1.20  
   3. #./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.18-164.el5-i686  
   4. #make && make install 
[root@slave01 keepalived-1.2.7]# ./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.18-164.el5-i686
checking for gcc... gcc
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables... 
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking for a BSD-compatible install... /usr/bin/install -c
checking for strip... strip
checking how to run the C preprocessor... gcc -E
checking for grep that handles long lines and -e... /bin/grep
checking for egrep... /bin/grep -E
checking for ANSI C header files... yes
checking for sys/wait.h that is POSIX.1 compatible... yes
checking for sys/types.h... yes
checking for sys/stat.h... yes
checking for stdlib.h... yes
checking for string.h... yes
checking for memory.h... yes
checking for strings.h... yes
checking for inttypes.h... yes
checking for stdint.h... yes
checking for unistd.h... yes
checking fcntl.h usability... yes
checking fcntl.h presence... yes
checking for fcntl.h... yes
checking syslog.h usability... yes
checking syslog.h presence... yes
checking for syslog.h... yes
checking for unistd.h... (cached) yes
checking sys/ioctl.h usability... yes
checking sys/ioctl.h presence... yes
checking for sys/ioctl.h... yes
checking sys/time.h usability... yes
checking sys/time.h presence... yes
checking for sys/time.h... yes
checking openssl/ssl.h usability... yes
checking openssl/ssl.h presence... yes
checking for openssl/ssl.h... yes
checking openssl/md5.h usability... yes
checking openssl/md5.h presence... yes
checking for openssl/md5.h... yes
checking openssl/err.h usability... yes
checking openssl/err.h presence... yes
checking for openssl/err.h... yes
checking whether ETHERTYPE_IPV6 is declared... no
checking for MD5_Init in -lcrypto... yes
checking for SSL_CTX_new in -lssl... yes
checking for poptGetContext in -lpopt... yes
checking for nl_socket_modify_cb in -lnl... no
configure: WARNING: keepalived will be built without libnl support.
checking for kernel version... 2.6.18
checking for IPVS syncd support... yes
checking for kernel macvlan support... no
checking for an ANSI C-conforming const... yes
checking for pid_t... yes
checking whether time.h and sys/time.h may both be included... yes
checking whether gcc needs -traditional... no
checking for working memcmp... yes
checking return type of signal handlers... void
checking for gettimeofday... yes
checking for select... yes
checking for socket... yes
checking for strerror... yes
checking for strtol... yes
checking for uname... yes
configure: creating ./config.status
config.status: creating Makefile
config.status: creating genhash/Makefile
config.status: creating keepalived/core/Makefile
config.status: creating keepalived/include/config.h
config.status: creating keepalived.spec
config.status: creating keepalived/Makefile
config.status: creating lib/Makefile
config.status: creating keepalived/vrrp/Makefile
config.status: creating keepalived/check/Makefile
config.status: creating keepalived/libipvs-2.6/Makefile


Keepalived configuration
------------------------
Keepalived version       : 1.2.7
Compiler                 : gcc
Compiler flags           : -g -O2 -DETHERTYPE_IPV6=0x86dd
Extra Lib                : -lpopt -lssl -lcrypto 
Use IPVS Framework       : Yes
IPVS sync daemon support : Yes
IPVS use libnl           : No
Use VRRP Framework       : Yes
Use VRRP VMAC            : No
SNMP support             : No
Use Debug flags          : No
[root@slave01 keepalived-1.2.7]# make && make install
make -C lib || exit 1;
make[1]: Entering directory `/usr/local/keepalived-1.2.7/lib'
make[1]: Nothing to be done for `all'.
make[1]: Leaving directory `/usr/local/keepalived-1.2.7/lib'
make -C keepalived
make[1]: Entering directory `/usr/local/keepalived-1.2.7/keepalived'
make[2]: Entering directory `/usr/local/keepalived-1.2.7/keepalived/core'
gcc -g -O2 -DETHERTYPE_IPV6=0x86dd  -I/usr/src/kernels/2.6.18-164.el5-i686/include -I/usr/src/kernels/2.6.18-164.el5-i686/include -I../include -I../../lib -Wall -Wunused -Wstrict-prototypes -D_KRNL_2_6_ -D_WITH_LVS_ -D_WITH_VRRP_ -D_WITHOUT_SNMP_  -c main.c
make[2]: Leaving directory `/usr/local/keepalived-1.2.7/keepalived/core'
make[2]: Entering directory `/usr/local/keepalived-1.2.7/keepalived/check'
make[2]: Nothing to be done for `all'.
make[2]: Leaving directory `/usr/local/keepalived-1.2.7/keepalived/check'
make[2]: Entering directory `/usr/local/keepalived-1.2.7/keepalived/vrrp'
make[2]: Nothing to be done for `all'.
make[2]: Leaving directory `/usr/local/keepalived-1.2.7/keepalived/vrrp'
make[2]: Entering directory `/usr/local/keepalived-1.2.7/keepalived/libipvs-2.6'
gcc -g -O2 -DETHERTYPE_IPV6=0x86dd  -I/usr/src/kernels/2.6.18-164.el5-i686/include -I/usr/src/kernels/2.6.18-164.el5-i686/include -DLIBIPVS_DONTUSE_NL -Wall -Wunused -c -o libipvs.o libipvs.c
gcc -g -O2 -DETHERTYPE_IPV6=0x86dd  -I/usr/src/kernels/2.6.18-164.el5-i686/include -I/usr/src/kernels/2.6.18-164.el5-i686/include -DLIBIPVS_DONTUSE_NL -Wall -Wunused -c -o ip_vs_nl_policy.o ip_vs_nl_policy.c
ar rv libipvs.a libipvs.o ip_vs_nl_policy.o
r - libipvs.o
r - ip_vs_nl_policy.o
rm libipvs.o ip_vs_nl_policy.o
make[2]: Leaving directory `/usr/local/keepalived-1.2.7/keepalived/libipvs-2.6'
Building ../bin/keepalived
strip ../bin/keepalived


Make complete
make[1]: Leaving directory `/usr/local/keepalived-1.2.7/keepalived'
make -C genhash
make[1]: Entering directory `/usr/local/keepalived-1.2.7/genhash'
strip ../bin/genhash


Make complete
make[1]: Leaving directory `/usr/local/keepalived-1.2.7/genhash'


Make complete
make -C keepalived install
make[1]: Entering directory `/usr/local/keepalived-1.2.7/keepalived'
install -d /usr/local/keepalived/sbin
install -m 700 ../bin/keepalived /usr/local/keepalived/sbin/
install -d /usr/local/keepalived/etc/rc.d/init.d
install -m 755 etc/init.d/keepalived.init /usr/local/keepalived/etc/rc.d/init.d/keepalived
install -d /usr/local/keepalived/etc/sysconfig
install -m 755 etc/init.d/keepalived.sysconfig /usr/local/keepalived/etc/sysconfig/keepalived
install -d /usr/local/keepalived/etc/keepalived/samples
install -m 644 etc/keepalived/keepalived.conf /usr/local/keepalived/etc/keepalived/
install -m 644 ../doc/samples/* /usr/local/keepalived/etc/keepalived/samples/
install -d /usr/local/keepalived/share/man/man5
install -d /usr/local/keepalived/share/man/man8
install -m 644 ../doc/man/man5/keepalived.conf.5 /usr/local/keepalived/share/man/man5
install -m 644 ../doc/man/man8/keepalived.8 /usr/local/keepalived/share/man/man8
make[1]: Leaving directory `/usr/local/keepalived-1.2.7/keepalived'
make -C genhash install
make[1]: Entering directory `/usr/local/keepalived-1.2.7/genhash'
install -d /usr/local/keepalived/bin
install -m 755 ../bin/genhash /usr/local/keepalived/bin/
install -d /usr/local/keepalived/share/man/man1
install -m 644 ../doc/man/man1/genhash.1 /usr/local/keepalived/share/man/man1
make[1]: Leaving directory `/usr/local/keepalived-1.2.7/genhash


配置keepalived
默认情况下keepalived启动时会去/etc/keepalived目录下找配置文件，我们在新创建一个keepalived.conf文件（这两台服务器都要配置同样的文件有几个参数有差异请仔细查阅）；
192.168.0.42中的；
 vi /etc/keepalived/keepalived.conf
global_defs {
     notification_email {
     1042372071@qq.com
     }
     notification_email_from 1042372071@qq.com
     smtp_server 127.0.0.1
     smtp_connect_timeout 30
     router_id MySQL-ha
     }


vrrp_instance VI_1 {
     state BACKUP
     interface eth0
     virtual_router_id 51
     priority 90
     advert_int 1
     authentication {
     auth_type PASS
     auth_pass 1111
     }
     virtual_ipaddress {
     192.168.0.50
     }
     }


virtual_server 192.168.0.50 3306 {
     delay_loop 2
     lb_algo wrr
     lb_kind DR
     persistence_timeout 60
     protocol TCP
     real_server 192.168.0.42 3306 {
     weight 3
     notify_down /usr/local/mysql/bin/MySQL.sh
     TCP_CHECK {
     connect_timeout 10
     nb_get_retry 3
     delay_before_retry 3
     connect_port 3306
     }
     }
192.168.0.49中的；
global_defs {
     notification_email {
     1
     interface eth0
     virtual_router_id 51
     priority 100  
     advert_int 1                       
     nopreempt  
     authentication {
     auth_type PASS
     auth_pass 1111
     
bal_defs {
     notification_email {
     1042372071@qq.com
     }
     notification_email_from 1042372071@qq.com
     smtp_server 127.0.0.1
     smtp_connect_timeout 30
     router_id MySQL-ha
     }
 
vrrp_instance VI_1 {
     state BACKUP
     interface eth0
     virtual_router_id 51
     priority 100
     advert_int 1
     nopreempt
     authentication {
     auth_type PASS
     auth_pass 1111
     }
     virtual_ipaddress {
     192.168.0.50
     }
     }
 
virtual_server 192.168.0.50 3306 {
     delay_loop 2
     lb_algo wrr
     lb_kind DR
     persistence_timeout 60
     protocol TCP
     real_server 192.168.0.49 3306 {
     weight 3
     notify_down /usr/local/mysql/bin/MySQL.sh
     TCP_CHECK {
     connect_timeout 10
     nb_get_retry 3
     delay_before_retry 3
     connect_port 3306
     }
     }
这里创建我们检测服务down后所要执行的脚本
 #vi /usr/local/mysql/bin/MySQL.sh  
 #!/bin/sh  
 pkill keepalived  
 #chmod +x /usr/local/mysql/bin/MySQL.sh
要知道如果是root创建的还要做权限变更；
如下操作；
chmod +x /usr/local/mysql
chown -R mysql.mysql /usr/local/mysql
chmod +x /usr/local/mysql/bin/MySQL.sh
启动keepalived
   1.#/usr/local/keepalived/sbin/keepalived –D  
   2. #ps -aux | grep keepalived
（停止进程的话，直接kill，哈哈）
至此配置工作已经做完，记下来就是测试工作，可一个远程msyql服务可以ping我们的50端口，然后再另启动一个会话，在链接到50端口上
如下；
D:\Program Files\uptime software\uptime\mysql\bin>mysql.exe -uroot -p -h1492.168.0.50 -P3306
Enter password:
ERROR 2005 (HY000): Unknown MySQL server host '1492.168.0.50' (11004)
D:\Program Files\uptime software\uptime\mysql\bin>mysql.exe -uroot -proot -h192.
168.0.50 -P3306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3275
Server version: 5.5.13-log Source distribution


Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema    |
| inno                         |
| mysql                       |
| performance_schema |
| test                 |
| test_db            |
+--------------------+
6 rows in set (0.07 sec)
然后进行故障测试，分别对keepalived和，mysql服务分别停掉看看不同服务切换的效果！以下是我测试的；


mysql>
mysql> show databases;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    601
Current database: *** NONE ***


+--------------------+
| Database           |
+--------------------+
| information_schema |
| inno               |
| mysql              |
| performance_schema |
| test               |
| test_db            |
+--------------------+
6 rows in set (0.01 sec)


mysql> show databases;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    3665
Current database: *** NONE ***


+--------------------+
| Database           |
+--------------------+
| information_schema |
| inno               |
| mysql              |
| performance_schema |
| test               |
| test_db            |
+--------------------+
6 rows in set (0.00 sec)


mysql> show databases;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1033
Current database: *** NONE ***


+--------------------+
| Database           |
+--------------------+
| information_schema |
| inno               |
| mysql              |
| performance_schema |
| test               |
| test_db            |
+--------------------+
6 rows in set (0.01 sec)