mysql主主互备模式配置_配置MySQL实现主主互备模式并利用keepalived实现双主高可用...

每台主机均需安装keepalived，mysql，安装过程这里不多介绍，不懂的可自行百度。

本次配置的环境为

DB1：192.168.31.190                DB2：192.168.31.184        MySQL VIP：192.168.31.189

1、修改MySQL配置文件

首先修改DB1的/etc/my.cnf配置文件，在"[mysqld]"段中添加一下内容：

server-id = 1log-bin=mysql-bin

relay-log = mysql-relay-bin

replicate-wild-ignore-table=mysql.%replicate-wild-ignore-table=test.%replicate-wild-ignore-table=information_schema.%

然后修改DB2主机/etc/my.cnf配置文件,在"[mysqld]"段添加一下内容:

server-id = 2log-bin=mysql-bin

relay-log = mysql-relay-bin

replicate-wild-ignore-table=mysql.%replicate-wild-ignore-table=test.%replicate-wild-ignore-table=information_schema.%

2、手动同步数据库

DB1和DB2数据需要保持同步，在DB1上执行操作

mysql>FLUSH TABLES WITH READ LOCK;

Query OK,0 rows affected (0.00sec)

#然后不要退出终端，重开一个终端，执行一下命令

# cd/var/lib/#tar zcvf mysql.tar.gz mysql

#scp mysql.tar.gz DB2:/var/lib/

将数据传输到DB2后解压覆盖然后依次重启DB1和DB2上的MySQL

3、创建复制用户并授权

在DB1MySQL执行如下操作：

mysql> grant replication slave on *.* to 'repl_user'@'192.168.31.184' identified by 'repl_passwd';

mysql> show master status;

然后在DB2的MySQL将DB1设为自己的主服务器，操作如下：

mysql>change master to \-> master_host='192.168.31.190',-> master_user='repl_user',-> master_password='repl_passwd',-> master_log_file='mysql-bin.000001',-> master_log_pos=106;

#接着启动slave服务

mysql>start slave；

#查看slave运行状态

mysql> show slave status\G;

到这里，从DB1到DB2的MySQL主从复制已经完成，接下来配置DB2到DB1的mysql主从复制，过程和上面完全一样，在DB2的MySQL创建复制用户：

mysql> grant replication slave on *.* to 'repl_user'@'192.168.31.190' identified by 'repl_passwd';

mysql> show master status;

然后在DB1的MySQL将DB2设为自己的主服务器：

mysql>change master to \-> master_host='192.168.31.184',-> master_user='repl_user',-> master_password='repl_passwd',-> master_log_file='mysql-bin.000001',-> master_log_pos=436;

mysql>start slave；

mysql> show slave status\G;

接着启动slave服务。至此，MySQL双主模式的主从复制配置完毕。

4、配置keepalived实现MySQL双主高可用

首先在DB1上配置/etc/keepalived/keepalived.conf，内容如下

global_defs {

notification_email {

acassen@firewall.loc

failover@firewall.loc

sysadmin@firewall.loc

}

notification_email_from Alexandre.Cassen@firewall.loc

smtp_server192.168.200.1smtp_connect_timeout30router_id LVS_DEVEL

}

vrrp_script check_mysqld {

script"/etc/keepalived/mysqlcheck/check_slave.pl 127.0.0.1"#检测mysql复制状态脚本

interval2weight21}

vrrp_instance HA_1 {

state BACKUP #在DB1和DB2均为BACKUP

interface eth1

virtual_router_id80priority100advert_int2nopreempt #不抢占模式，只在优先级高的机器设置

authentication {

auth_type PASS

auth_pass qweasdzxc

track_script {

check_mysqld

}

virtual_ipaddress {192.168.31.189/24dev eth1 #mysql对外服务IP，即VIP

}

}

其中，/etc/keepalived/mysqlcheck/check_slave.pl文件的内容如下：

#!/usr/bin/perl -wuse DBI;

use DBD::mysql;

#CONFIG VARIABLES

$SBM= 120;

$db= "ixdba";

$host= $ARGV[0];

$port= 3306;

$user= "root";

$pw= "915389546";

#SQL query

$query= "show slave status";

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

exit1;

}

$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->{'Second_Behind_Master'};

}

$sqlQuery->finish;

$dbh->disconnect();if ( $Slave_IO_Running eq "NO" || $Slave_SQL_Running eq "NO") {

exit1;

}else{if ( $Seconds_Behind__Master >$SBM) {

exit1;

}else{

exit0;

}

}

接着将keepalived和check_slave.pl复制到DB2服务器上对应的位置，然后将keepalived文件中的priority值修改为90，还需去掉nopreempt选项，然后分别在DB1和DB2上启动keepalived服务。

5、测试主从同步功能

首先从第三台服务器远程登录VIP为“192.168.31.188“的数据库，操作过程如下：

[root@localhost~]#mysql -uroot -p -h 192.168.31.188

Enter password:

MySQL [(none)]> show variables like "%hostname%";+---------------+----------------+

| Variable_name | Value |

+---------------+----------------+

| hostname | DB1 |

+---------------+----------------+

1 row in set (0.01sec)

MySQL [(none)]> show variables like "%server_id%";+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 1 |

+---------------+-------+

1 row in set (0.01sec)

#可以看到成功通过VIP登录，并且登录了DB1服务器。

#接下来测试复制数据功能

MySQL [(none)]>create database repldb;

Query OK,1 row affected (0.12sec)

MySQL [(none)]>show databases;+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| repldb |

| test |

+--------------------+

5 rows in set (0.01sec)

MySQL [(none)]>use repldb;

Database changed

MySQL [repldb]> create table repl_table(id int,email varchar(80),password varchar(40) not null);

Query OK,0 rows affected (0.03sec)

MySQL [repldb]>show tables;+------------------+

| Tables_in_repldb |

+------------------+

| repl_table |

+------------------+

1 row in set (0.00sec)

MySQL [repldb]> insert into repl_table (id,email,password) values(1,"xiaoming@qq.com","123456");

Query OK,1 row affected (0.00 sec)

然后登录DB2主机的MySQL查看，可以发现数据实现了同步。

6、故障转移测试

首先远程通过VIP地址登录MySQL系统，不要退出这个连接，然后在DB1服务器的MySQL命令行执行如下操作：

mysql>slave stop;

然后回到远程连接的MySQL数据库执行如下操作：

MySQL [(none)]> show variables like "%hostname%";+---------------+----------------+

| Variable_name | Value |

+---------------+----------------+

| hostname | DB2 |

+---------------+----------------+

1 row in set (0.01sec)

MySQL [(none)]> show variables like "%server_id%";+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 2 |

+---------------+-------+

1 row in set (0.01 sec)

可以发现在DB1发现故障之后MySQL服务从DB1切换到了DB2服务器，至此，所有配置完成。