lSky's blog

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

1
2
3
4
5
6
7
[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.%
1
2
3
4
5
6
7
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

1
2
3
4
5
6
7
[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.%
1
2
3
4
5
6
7
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

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
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

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
#!/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;
    }
}

service keepalived start

三．测试

1，以vip连接mysql

mysql -uamoeba -p1234 -h192.168.44.188

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

2，在master1

ip addr

1
2
3
4
5
6
7
8
9
10
11
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了，表示服务器已经切换了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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。