mysql双机热备

通常，为了简化逻辑，其中一个Master会设置为只读，正常只通过另外一个Master进行读写。 若要两边都写，为了避免自增id冲突，一般会设置奇偶错开，即一台的自增ID均为奇数，另一台均为偶数。

保持两个数据库的状态自动同步。对任何一个数据库的操作都自动应用到另外一个数据库，始终保持两个数据库数据一致。 这样做的好处是： 1. 可以做灾备，其中一个坏了可以切换到另一个。 2. 可以做负载均衡，可以将请求分摊到其中任何一台上，提高网站吞吐量。 对于异地热备，尤其适合灾备。

一．主-主复制

Master1 ip：192.168.44.137
Master2 ip：192.168.44.139
Virtual ip：192.168.44.188
原理：简单的说就是把 一个服务器上执行过的sql语句在别的服务器上也重复执行一遍， 这样只要两个数据库的初态是一样的，那么它们就能一直同步。其实只要做互为主从备份

1.Master1

# vim /etc/my.cnf

[mysqld]
log-bin=/tmp/binlog
relay-log=/tmp/mysql-relay-bin  #配置中继日志
server-id=1
replicate-wild-ignore-table=mysql.%  #不对这些数据表进行镜像处理
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

mysql> change master to 
    -> master_host='192.168.44.139',
    -> master_user='salveuser',
    -> master_password='redhat',
    -> master_log_file='binlog.000001',
-> master_log_pos=328；
mysql> slave start;

2.Master2

# vim /etc/my.cnf

[mysqld]
server-id=2
log-bin=/tmp/binlog
relay_log=/tmp/mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

mysql> change master to 
    -> master_host='192.168.44.137',
    -> master_user='salveuser',
    -> master_password='redhat',
    -> master_log_file='binlog.000001',
-> master_log_pos=106；
mysql> slave start;

二．Keepalive安装部署（实现MySQL双主高可用，故障转移）

# yum install -y gcc make openssl openssl-devel ipvsadm perl-DBI perl-DBD-MySQL
# wget http://www.keepalived.org/software/keepalived-1.2.12.tar.gz
# tar -xzvf keepalived-1.2.12.tar.gz
# cd keepalived-1.2.12
# ./configure –prefix=/usr/local/keepalived
# make && make install

# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
# cp -rf /usr/local/keepalived/etc/keepalived/ /etc/

Slave1

# vim /etc/keepalived/keepalived.conf

global_defs {                   #全局配置标识
   notification_email {         #邮箱地址，切换操作时会的发邮件到配置上的邮箱
        root@localhost
   }
   notification_email_from www.nginx2.com   #邮件源地址
   smtp_server 127.0.0.1                    #smtp服务器地址
   smtp_connect_timeout 30              #连接smtp超时时间
   router_id MySQL_HA                   #设置VRID
}

vrrp_script check_mysqld {                  #定义一个名称为check_mysqld的检查脚本  
        script "/etc/keepalived/check_slave.pl 127.0.0.1"
        interval 2                          #运行间隔
        weight 21
}

vrrp_instance VI_1 {
    state BACKUP            
    interface eth0                      #绑定虚拟IP的网络接口 
    virtual_router_id 51                #VRRP组名，两个节点的设置必须一样，以指明各个节点属于同一VRRP组
    priority 100                        #主节点的优先级
advert_int 2                        # 组播信息发送间隔，两个节点设置必须一样
nopreempt                       #不抢占模式,只在主master上设置
    authentication {                    #设置验证信息，两个节点必须一致  
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {                  #指定虚拟IP, 两个节点设置必须一样 
        192.168.44.188
    }
    track_script {                      #使用上面定义的检测脚本
        check_mysqld
    }
}

在slave2中
修改priority为90，并去掉nopreempt使之抢占

检查的pl脚本（也可为shell脚本）

# vim check_slave.pl

#!/usr/bin/perl -w
use DBI;
use DBD::mysql;

# CONFIG VARIABLES
$SBM = 120;
$db = "ylyg";
$host = $ARGV[0];
$port = 3306;
$user = "root";
$pw = "redhat";

# SQL query
$query = "show slave status";

$dbh = DBI->connect("DBI:mysql:$db:$host:$port",$user,$pw,{ RaiseError => 0,PrintError => 0 });

if (!defined($dbh)) {
    exit 1;
}

$sqlQuery = $dbh->prepare($query);
$sqlQuery->execute;

$Slave_IO_Running =  "";
$Slave_SQL_Running = "";
$Seconds_Behind_Master = "";

while (my $ref = $sqlQuery->fetchrow_hashref()) {
    $Slave_IO_Running = $ref->{'Slave_IO_Running'};
    $Slave_SQL_Running = $ref->{'Slave_SQL_Running'};
    $Seconds_Behind_Master = $ref->{'Seconds_Behind_Master'};
}

$sqlQuery->finish;
$dbh->disconnect();

if ( $Slave_IO_Running eq "No" || $Slave_SQL_Running eq "No" ) {
    exit 1;
} else {
    if ( $Seconds_Behind_Master > $SBM ) {
        exit 1;
    } else {
        exit 0;
    }
}

shell脚本

/usr/bin/mysqladmin  -u root -pXXX -h 127.0.0.1 -P 3306 extended-status | grep -q Slave_running
RET=`echo $?`
exit ${RET}

# service keepalived start

三．测试
1，以vip连接mysql

# mysql -uamoeba -p1234 -h192.168.44.188

mysql> show global variables like 'server%';
mysql> show slave status\G;

2，在master1

# 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:88:07:d5 brd ff:ff:ff:ff:ff:ff
    inet 192.168.44.137/24 brd 192.168.44.255 scope global eth0
    inet 192.168.44.188/32 scope global eth0
    inet6 fe80::20c:29ff:fe88:7d5/64 scope link 
       valid_lft forever preferred_lft forever

故障模拟，上关闭slave，停止MySQL复制线程

3，以vip连接mysql
会发现当停掉复制线程后，执行查询时连接中断了一次，马上再次连接上完成查询，显示的server_id已经变成2了，表示服务器已经切换了。

mysql> show global variables like 'server%';
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> show global variables like 'server%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    351
Current database: *** NONE ***

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
1 row in set (0.12 sec)

Tips：在 双主（多主循环）互相备份。 因为每台数据库服务器都可能在同一个表中插入数据，如果表有一个自动增长的主键，那么就会在多服务器上出现主键冲突。 解决这个问题的办法就是让每个数据库的自增主键不连续。 上图说是， 我假设需要将来可能需要10台服务器做备份， 所以auto-increment-increment 设为10. 而 auto-increment-offset=1 表示这台服务器的序号。 从1开始， 不超过auto-increment-increment。这样做之后， 我在这台服务器上插入的第一个id就是 1， 第二行的id就是 11了， 而不是2，同理，在第二台服务器上插入的第一个id就是2， 第二行就是12。