MySQL主主复制+Keepalived 打造高可用MySQL集群

为了响应公司需求，打造出更安全的mysql集群，能够实现mysql故障后切换，研究了几天终于有了成果，一起分享一下。首先介绍一下这套集群方案实现的功能1、mysql服务器故障后自动转移，修好后自动切回2、mysql服务故障自动转移，修好后自动切回3、可以实现在几秒钟内转移以下内容均是实验环境，请根据实际情况修改响应参数生产环境MySQL主主同步主键冲突处理 http://www.linuxidc.com/Linux/2013-07/86890.htmMySQL + KeepAlived + LVS 单点写入主主同步高可用架构实验 http://www.linuxidc.com/Linux/2013-05/84002.htmMySQL 主主同步配置 http://www.linuxidc.com/Linux/2013-05/83815.htmCentOS 6.3下MySQL主从复制笔记 http://www.linuxidc.com/Linux/2013-06/85983.htmLinux下的MySQL主主复制 http://www.linuxidc.com/Linux/2013-10/91683.htm实验环境：mysql1 ip：10.1.1.20mysql2 ip：10.1.1.21mysql vip：10.1.1.25三台机器均安装centos 6.5 32位（虚拟机环境）实验开始！！！一、安装mysql，并打造主主同步。相信主从同步大家都会做，一样的道理，主主同步就是两台机器互为主的关系，在任何一台机器上写入都会同步。安装mysql的过程不解释，yum就好啦配置主主同步1.配置 /etc/my.cnf[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0log-bin=binlog #开启binlog功能log-bin-index=binlog.indexsync_binlog=0server_id = 1 #两台机器不能重复，一个1 一个2 就好 [mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid
2.分别在两台机器上配置同步账号10.1.1.20机器上：[root@localhost ~]# mysqlWelcome to the MySQL monitor. Commands end with; or g.Your MySQL connection id is 2Server version: 5.0.77-log Sourcedistribution Type "help;" or "h" for help. Type "c" toclear the buffer. mysql> GRANT replication slave ON *.* TO"ab"@"%" identified by "123";Query OK, 0 rows affected （0.00 sec） mysql> flush privileges;Query OK, 0 rows affected （0.00 sec）10.1.1.21机器上：[root@localhost ~]# mysqlWelcome to the MySQL monitor. Commands end with; or g.Your MySQL connection id is 2Server version: 5.0.77-log Sourcedistribution Type "help;" or "h" for help. Type "c" toclear the buffer. mysql> GRANT replication slave ON *.* TO"ab"@"%" identified by "123";Query OK, 0 rows affected （0.00 sec） mysql> flush privileges;Query OK, 0 rows affected （0.00 sec）
注:由于本文是实验环境下编写，所以没考虑任何安全性问题，同步账号也是最高权限，请根据实际情况设置响应权限！！3.设置同步10.1.1.20机器上:mysql> flush tables with read lock;mysql> show master status;+---------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |+---------------+----------+--------------+------------------+| binlog.000003 | 365 | | |+---------------+----------+--------------+------------------+1 row in set （0.03 sec）mysql> unlock tables;Query OK, 0 rows affected （0.03 sec）10.1.1.21机器上：mysql> change master tomaster_host="10.1.1.20", master_port=3306, master_user="ab",master_password="123", master_log_file="binlog.000003",master_log_pos=365;Query OK, 0 rows affected （0.06 sec）mysql> start slave;Query OK, 0 rows affected （0.00 sec）mysql> show slave status G #执行这命令后注意观察下面这两个参数，必须要都是yes才行Slave_IO_Running: YesSlave_SQL_Running: Yes同样的反过来做相同操作10.1.1.21机器上:mysql> flush tables with read lock;mysql> show master status;+---------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |+---------------+----------+--------------+------------------+| binlog.000004 | 207 | | |+---------------+----------+--------------+------------------+1 row in set （0.03 sec）mysql> unlock tables;Query OK, 0 rows affected （0.03 sec）10.1.1.20机器上： mysql> change master tomaster_host="10.1.1.21", master_port=3306, master_user="ab",master_password="123", master_log_file="binlog.000004",master_log_pos=207;Query OK, 0 rows affected （0.06 sec）mysql> start slave;Query OK, 0 rows affected （0.00 sec）mysql> show slave status G #执行这命令后注意观察下面这两个参数，必须要都是yes才行Slave_IO_Running: YesSlave_SQL_Running: Yes介此，主主同步打造完成，可以简单测试一下，分别在两个机器上写数据看看会不会同步到另一台机器上PS：如果报错 Slave_IO_Running: NO 可以检查同步的账号是否创建正常！二、安装keepalived 并设置监控keepalived是安装在两台MySQL服务器上的首先安装keepalived 过程不解释就正常解压安装就好安装后配置 vim /etc/keepalived/keepalived.conf 内容如下10.1.1.20的配置文件！ Configuration File for keepalivedglobal_defs { notification_email { acassen@firewall.loc failover@firewall.loc sysadmin@firewall.loc } notification_email_from Alexandre.Cassen@firewall.loc smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id LVS_DEVEL} vrrp_instance VI_1 { state backup #两台配置此处均是BACKUP interface eth0 virtual_router_id 51 priority 100 #优先级，另一台改为90 advert_int 1 nopreempt #不抢占，只在优先级高的机器上设置即可，优先级低的机器不设置 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 10.1.1.25 }} virtual_server 10.1.1.25 3306 { delay_loop 6 lb_algo wrr lb_kind DR persistence_timeout 50 #会话保持时间 protocol TCP real_server 10.1.1.20 3306 { weight 3 notify_down /tmp/nimei.sh #检测到mysql服务挂了就执行这个脚本（脚本要自己写哈） TCP_CHECK { connect_timeout 10 #连接超时时间 nb_get_retry 3 #重连次数 delay_before_retry 3 #重连间隔时间 connect_port 3306 #健康检查端口 } }}
10.1.1.21 的配置文件！ Configuration File for keepalivedglobal_defs { notification_email { acassen@firewall.loc failover@firewall.loc sysadmin@firewall.loc } notification_email_from Alexandre.Cassen@firewall.loc smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id LVS_DEVEL} 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 { 10.1.1.25 }} virtual_server 10.1.1.25 3306 { delay_loop 6 lb_algo wrr lb_kind DR persistence_timeout 50 protocol TCP real_server 10.1.1.21 3306 { weight 3 notify_down /tmp/nimei.sh TCP_CHECK { connect_timeout 10 nb_get_retry 3 delay_before_retry 3 connect_port 3306 } }} 编写监控mysql服务是否挂了的脚本，按照上面配置文件的位置编写脚本。vim /tmp/nimei.sh#！/bin/sh pkill keepalived脚本很简单啊就一句，目的是当keepalived检测到mysql服务挂了之后触发这个脚本，杀死keepalived进程，让另一台机器接管好修改后启动keeplived服务介此整个集群搭建完成三、测试找一台机器用虚拟ip连接mysql[root@localhost html]# mysql -uab -h 10.1.1.25 -p123Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 736Server version: 5.1.66-log Source distribution Copyright （c） 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type "help;" or "h" for help. Type "c" to clear the current input statement.mysql>这样成功连上了，然后你可以任意关闭某台机器，或者某台机器的mysql服务，看看还能不能连上！！谢谢！！本文永久更新链接地址