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

转载地址https://www.zifangsky.cn/997.html

首先准备两台MySQL服务器：

• 192.168.1.4
• 192.168.1.31

一 分别修改两台服务器的MySQL的my.cnf配置文件

（1）修改主机192.168.1.4的/etc/my.cnf文件：

添加以下内容：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

server - id = 1 auto - increment - increment = 2 auto - increment - offset = 1 explicit_defaults_for_timestamp = 1 character_set_server = utf8 interactive_timeout = 57600 log - bin = mysql - bin   expire - logs - days = 100 replicate - do - db = test123    #需要同步的数据库 binlog - ignore - db = mysql binlog - ignore - db = information_schema   slave - skip - errors = all log - slave - updates symbolic - links = 0 skip - name - resolve

注：auto-increment-offset参数是用来设置数据库中自动增长的起点。因为这两台服务器都设置了一次自动增长2，所以它们的起点必须得不同，这样才能避免两台服务器数据同步时出现主键冲突的问题。一般情况下auto-increment-increment设置为集群中MySQL实例的总数，而auto-increment-offset设置为该服务器的序列号

（2）修改主机192.168.1.31的/etc/my.cnf文件：

添加以下内容：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

server - id = 2 auto - increment - increment = 2 auto - increment - offset = 2 explicit_defaults_for_timestamp = 1 character_set_server = utf8 interactive_timeout = 57600 log - bin = mysql - bin expire - logs - days = 100   replicate - do - db = test123 binlog - ignore - db = mysql binlog - ignore - db = information_schema   slave - skip - errors = all log - slave - updates symbolic - links = 0 skip - name - resolve

二 分别重启两台服务器上面的MySQL

1	[ root @ backup ~ ] # service mysqld restart

三 互相授权

（1）在主机192.168.1.4执行以下MySQL命令：

1 2	mysql > GRANT REPLICATION SLAVE ON *.* TO 'backup'@ '192.168.1.31' IDENTIFIED BY 'pass.word'; mysql > flush privileges;

（2）在主机192.168.1.31执行以下MySQL命令：

1 2	mysql > GRANT REPLICATION SLAVE ON *.* TO 'backup'@ '192.168.1.4' IDENTIFIED BY 'pass.word'; mysql > flush privileges;

四 互告bin-log信息

（1）在主机192.168.1.4执行以下MySQL命令：

1 2	mysql > flush tables with read lock; mysql > show master status;

+——————+———-+————–+————————–+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+————————–+——————-+
| mysql-bin.000001 | 612 | | mysql,information_schema | |
+——————+———-+————–+————————–+——————-+

（2）在主机192.168.1.31执行以下MySQL命令：

1 2	mysql > flush tables with read lock; mysql >    show master status;

+——————+———-+————–+————————–+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+————————–+——————-+
| mysql-bin.000001 | 611 | | mysql,information_schema | |
+——————+———-+————–+————————–+——————-+

（3）在主机192.168.1.4执行以下MySQL命令：

change master to master_host=’192.168.1.31‘,master_user=’backup‘,master_password=’pass.word‘,master_log_file=’mysql-bin.000001‘,master_log_pos=611;

（4）在主机192.168.1.31执行以下MySQL命令：

change master to master_host=’192.168.1.4‘,master_user=’backup‘,master_password=’pass.word‘,master_log_file=’mysql-bin.000001‘,master_log_pos=612;

（5）在两台主机上分别执行以下MySQL命令：

1 2	mysql > start slave; mysql > show slave status \G

注：执行以上命令之后需要观察：

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes

如果出现上面提示则说明已经配置成功了

（6）解锁：

1	mysql > unlock tables;

五 安装keepalived 并设置监控

（1）在两台服务器上面分别安装keepalived，安装步骤如下：

1 2 3 4 5 6 7 8 9 10 11 12

[ root @ backup mysql ] # cd /usr/local/src [ root @ backup src ] # tar -zxvf keepalived-1.3.6.tar.gz [ root @ backup src ] # cd keepalived-1.3.6 [ root @ backup keepalived - 1.3.6 ] # ./configure [ root @ backup keepalived - 1.3.6 ] # make && make install   [ root @ backup ~ ] # cp /usr/local/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/ [ root @ backup ~ ] # cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig [ root @ backup ~ ] # mkdir /etc/keepalived [ root @ backup ~ ] # cp /usr/local/etc/keepalived/keepalived.conf /etc/keepalived/ [ root @ backup ~ ] # cp /usr/local/sbin/keepalived /usr/sbin [ root @ backup ~ ] # chkconfig keepalived on

注：默认keepalived日志文件地址：/var/log/messages

（2）修改/etc/keepalived/keepalived.conf配置文件：

1	[ root @ backup ~ ] # vim /etc/keepalived/keepalived.conf

主机192.168.1.4的配置文件修改之后内容如下：

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 51 52 53

! Configuration File for keepalived   global_defs {    notification_email {      acassen @ firewall . loc      failover @ firewall . loc      sysadmin @ firewall . loc    }    notification_email_from Alexandre . Cassen @ firewall . loc    smtp _server 192.168.200.1    smtp_connect _timeout 30    router_id LVS_DEVEL    vrrp_skip_check_adv_addr    vrrp_strict    vrrp_garp _interval 0    vrrp_gna _interval 0 }   vrrp_instance VI_1 {      state BACKUP        #两台配置此处均是BACKUP      interface eth0      virtual_router_id 58      priority 100        #优先级，另一台改为80      advert_int 1      nopreempt            #不抢占，只在优先级高的机器上设置即可，优先级低的机器不设置      authentication {          auth_type PASS          auth _pass 1111      }      virtual_ipaddress {          192.168.1.7      } }   virtual _server 192.168.1.7 3306 {      delay _loop 6      lb_algo rr      lb_kind DR      persistence _timeout 50      protocol TCP      real _server 192.168.1.4 3306 {          weight 3          notify_down / etc / keepalived / kill_keepalived . sh          TCP_CHECK {              connect _timeout 10              nb_get _retry 3              delay_before _retry 3              connect _port 3306          }      } }

主机192.168.1.31的配置文件修改之后内容如下：

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

! Configuration File for keepalived   global_defs {    notification_email {      acassen @ firewall . loc      failover @ firewall . loc      sysadmin @ firewall . loc    }    notification_email_from Alexandre . Cassen @ firewall . loc    smtp _server 192.168.200.1    smtp_connect _timeout 30    router_id LVS _DEVEL }   vrrp_instance VI_1 {      state BACKUP        #两台配置此处均是BACKUP      interface eth0      virtual_router_id 58      priority 80      advert_int 1      authentication {          auth_type PASS          auth _pass 1111      }      virtual_ipaddress {          192.168.1.7      } }   virtual _server 192.168.1.7 3306 {      delay _loop 6      lb_algo rr      lb_kind DR      persistence _timeout 50      protocol TCP        real _server 192.168.1.31 3306 {          weight 3          notify_down / etc / keepalived / kill_keepalived . sh          TCP_CHECK {              connect _timeout 10              nb_get _retry 3              delay_before _retry 3              connect _port 3306          }      } }

编写kill_keepalived.sh脚本：

1	[ root @ backup keepalived ] # vim /etc/keepalived/kill_keepalived.sh

内容如下：

1 2 3

#!/bin/bash   pkill keepalived

注：目的很简单，当keepalived检测到mysql服务挂了之后触发这个脚本，杀死keepalived进程，让另一台机器接管

六 测试

使用客户端连接192.168.1.7的MySQL服务，然后关闭此时虚IP所在的MySQL，观察虚IP是否已经自动切换到另一台服务器上面，并且MySQL服务是否仍然可用

 

参考：

• http://www.cnblogs.com/wclwcw/p/6281608.html
• http://www.linuxidc.com/Linux/2014-09/106570.htm