Keepalived +Mysql 主主同步

2332323

一、配置Mysql主主同步
1，修改/etc/my.cnf，创建同步用户
Master104
[iyunv@104 ~]# vim /etc/my.cnf
[mysqld]
server-id = 1                    #backup这台设置2
log-bin = mysql-bin
binlog-ignore-db = mysql,information_schema       #忽略写入binlog日志的库
auto-increment-increment = 2             #字段变化增量值
auto-increment-offset = 1              #初始字段ID为1
slave-skip-errors = all                       #忽略所有复制产生的错误
[iyunv@104 ~]# service mysqld restart
#查看log bin日志和pos值位置
[iyunv@104 ~]# mysql -uroot -p
mysql> grant replication slave on *.* to 'replication'@'192.168.9.%' identified by 'replication';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)



Backup106
#106配置Mysql主主同步
[iyunv@106 ~]# vim /etc/my.cnf
[mysqld]
server-id = 2                    
log-bin = mysql-bin
binlog-ignore-db = mysql,information_schema      
auto-increment-increment = 2            
auto-increment-offset = 1              
slave-skip-errors = all   
[iyunv@106 ~]# service mysqld restart
#查看log bin日志和pos值位置
[iyunv@106 ~]# mysql -uroot -p
mysql> grant replication slave on *.* to 'replication'@'192.168.9.%' identified by 'replication';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)


2、配置Mysql主主同步
Master 104
[iyunv@104 ~]# mysql -uroot -p
mysql> show master status;
+------------------+----------+--------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+--------------------------+
| mysql-bin.000002 | 625 | | msyql,information_schema |
+------------------+----------+--------------+--------------------------+
1 row in set (0.00 sec)

mysql> change master to
-> master_host='192.168.9.106',
-> master_user='replication',
-> master_password='replication',
-> master_log_file='mysql-bin.000002',
-> master_log_pos=356;
Query OK, 0 rows affected (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.9.106
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 356
Relay_Log_File: 104-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Backup 106
[iyunv@106 ~]# mysql -uroot -p
mysql> show master status;
+------------------+----------+--------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+--------------------------+
| mysql-bin.000002 | 356 | | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
1 row in set (0.00 sec)

mysql> change master to
-> master_host='192.168.9.104',
-> master_user='replication',
-> master_password='replication',
-> master_log_file='mysql-bin.000002',
-> master_log_pos=625;
Query OK, 0 rows affected (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.9.104
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 625
Relay_Log_File: 106-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

#如果Slave_IO_Running和Slave_SQL_Running为Yes，则配置主主正常，可以创建测试下

二、配置keepalived实现双主热备
1，#下载安装keepalived
[iyunv@104 ~]# cd /usr/local/src/
[iyunv@104 src]# wget http://www.keepalived.org/software/keepalived-1.2.13.tar.gz
[iyunv@104 src]# yum -y install gcc gcc-c++ gcc-g77 ncurses-devel bison libaio-devel cmake libnl* libpopt* popt-static openssl-devel
[iyunv@104 src]# tar xf keepalived-1.2.13.tar.gz
[iyunv@104 src]# cd keepalived-1.2.13
[iyunv@104 keepalived-1.2.13]# ./configure --prefix=/usr/local/keepalived  --disable-fwmark
[iyunv@104 keepalived-1.2.13]# make
[iyunv@104 keepalived-1.2.13]# make install
2，#配置keepalived配置成系统服务
[iyunv@104 keepalived-1.2.13]# cd /usr/local/keepalived/
[iyunv@104 keepalived]# cp etc/rc.d/init.d/keepalived /etc/init.d/
[iyunv@104 keepalived]# cp etc/sysconfig/keepalived /etc/sysconfig/
[iyunv@104 keepalived]# mkdir /etc/keepalived
[iyunv@104 keepalived]# cp etc/keepalived/keepalived.conf /etc/keepalived/
[iyunv@104 keepalived]# cp sbin/keepalived /usr/sbin/
[iyunv@104 keepalived]# chkconfig keepalived on
[iyunv@104 keepalived]# chkconfig --list keepalived
keepalived 0:off 1:off 2:on 3:on 4:on 5:on 6:off
  3，#配置mysql热备
Mater 104
[iyunv@104 keepalived]# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived
global_defs {
        router_id Mysql_HA    #标识，双主相同
}

vrrp_instance zabbix_mysql {
        state Backup               #俩台都设置Backup
        interface eth0
        virtual_router_id 53     #主备相同
        priority 100                 #优先级，backup设置90
        advert_int 1
        nopreempt                  #不主动抢占资源，只在master这台优先级高的设置，backup不设置
        authentication {
                auth_type PASS
                auth_pass sina
        }
        virtual_ipaddress {
                192.168.9.123
         }
   }
   virtual_server 192.168.9.123 3306 {
         delay_loop 6
         nat_mask 255.255.255.0
         persistence_timeout 50
         protocol TCP
         real_server 192.168.9.104 3306 {
                  weight 3
                  notify_down "/etc/keepalived/mysql.sh"
                  TCP_CHECK {
                          connect_timeout 5
                          nb_get_retry 2
                          delay_before_retry 3
                  }
         }
}
[iyunv@104 keepalived]# vim /etc/keepalived/mysql.sh
#!/bin/bash
/etc/init.d/keepalived stop

#启动keepalived
[iyunv@104 keepalived]# /etc/init.d/keepalived start
[iyunv@104 keepalived]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:ff:a5:92 brd ff:ff:ff:ff:ff:ff
inet 192.168.9.104/24 brd 192.168.9.255 scope global eth0
inet 192.168.9.123/32 scope global eth0
inet6 fe80::20c:29ff:feff:a592/64 scope link
valid_lft forever preferred_lft forever


Backup106
[iyunv@106 keepalived]# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived
global_defs {
        router_id Mysql_HA   
}

vrrp_instance zabbix_mysql {
        state Backup               
        interface eth0
        virtual_router_id 53     
        priority 90                 
        advert_int 1
        authentication {
                auth_type PASS
                auth_pass sina
        }
        virtual_ipaddress {
                192.168.9.123
         }
   }
   virtual_server 192.168.9.123 3306 {
         delay_loop 6
         nat_mask 255.255.255.0
         persistence_timeout 50
         protocol TCP
         real_server 192.168.9.106 3306 {
                  weight 3
                  notify_down "/etc/keepalived/mysql.sh"
                  TCP_CHECK {
                          connect_timeout 5
                          nb_get_retry 2
                          delay_before_retry 3
                  }
         }
}
[iyunv@104 keepalived]# vim /etc/keepalived/mysql.sh
#!/bin/bash
/etc/init.d/keepalived stop
[iyunv@104 keepalived]# chmod +x /etc/keepalived/mysql.sh
#启动keepalived
[iyunv@104 keepalived]# /etc/init.d/keepalived start
[iyunv@104 keepalived]# ip addr

4，验证
104停止mysqld
[iyunv@104 keepalived]# service mysqld stop
Shutting down MySQL.... SUCCESS!
[iyunv@104 keepalived]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:ff:a5:92 brd ff:ff:ff:ff:ff:ff
inet 192.168.9.104/24 brd 192.168.9.255 scope global eth0
inet6 fe80::20c:29ff:feff:a592/64 scope link
valid_lft forever preferred_lft forever
106
[iyunv@106 keepalived]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:c1:76:6b brd ff:ff:ff:ff:ff:ff
inet 192.168.9.106/24 brd 192.168.9.255 scope global eth0
inet 192.168.9.123/32 scope global eth0
inet6 fe80::20c:29ff:fec1:766b/64 scope link
valid_lft forever preferred_lft forever