Configure MySQL to implement Master Master Interop mode and leverage keepalived for dual master high availability

Each host needs to install KEEPALIVED,MYSQL, the installation process is not introduced here, do not understand the Baidu can be self-.

The environment for this configuration is

db1:192.168.31.190 db2:192.168.31.184 MySQL vip:192.168.31.189

1. Modify the MySQL configuration file

First modify the DB1/etc/my.cnf configuration file and add the content in the "[Mysqld]" section:

              server-ID1              log-bin=mysql-bin              relay-log = mysql-relay-bin              Replicate-wild-ignore-table=mysql.%              replicate-wild-ignore-table=test.%              replicate -wild-ignore-table=information_schema.%

Then modify the DB2 host/ETC/MY.CNF configuration file and add the content in the "[Mysqld]" section:

               server-ID2               log-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. Manually synchronize the database

DB1 and DB2 data need to be kept in sync to perform operations on DB1

Mysql> FLUSH TABLES with READ LOCK; Query OK,0 rows affected (0.00  sec)#然后不要退出终端, re-open a terminal and execute the command  /var/lib/   tar zcvf MySQL. Tar  SCP MySQL. tar. GZ db2:/var/lib/

Unzip the overlay after data transfer to DB2 and restart MySQL on DB1 and DB2 in turn

3. Create a replication user and authorize

In Db1mysql, do the following:

' Repl_user ' @'192.168.31.184'repl_passwd'; MySQL > Show master Status;

Then in DB2 MySQL set DB1 as its own master server, the operation is as follows:

Mysql>Change Master tomaster_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;

Here, MySQL master-slave replication from DB1 to DB2 has been completed, and the next configuration is DB2 to DB1 MySQL master-slave replication, the process is exactly the same as above, in the DB2 of MySQL to create the replication user:

' Repl_user ' @'192.168.31.190'repl_passwd'; MySQL > Show master Status;

Then in DB1 MySQL set DB2 as its primary server:

 mysql> change master to , Master_host= '  192.168.31.184   '   

Then start the slave service. At this point, the master-slave replication configuration for MySQL dual master mode is complete.

4. Configure keepalived to achieve MySQL dual master high Availability

First configure the/etc/keepalived/keepalived.conf on the DB1, as follows:

Global_defs {notification_email {[email protected][email protected][email protected]} notification_email_from [Email protected] smtp_server192.168.200.1Smtp_connect_timeout -router_id lvs_devel}vrrp_script check_mysqld {script"/etc/keepalived/mysqlcheck/check_slave.pl 127.0.0.1"#检测mysql复制状态脚本interval2Weight +}vrrp_instance ha_1 {State BACKUP #在DB1和DB2均为BACKUP interface eth1 virtual_router_id the Priority -Advert_int2nopreempt #不抢占模式, only in high priority machine settings authentication {auth_type PASS auth_pass qweasdzxc TRACK_SCR IPT {Check_mysqld} virtual_ipaddress {192.168.31.189/ -Dev eth1 #mysql对外服务IP, VIP}}

Where the contents of the/etc/keepalived/mysqlcheck/check_slave.pl file are as follows:

#!/usr/bin/Perl-WUse dbi;use dbd::mysql; #CONFIG VARIABLES$SBM= -; $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= $DBHprepare ($query); $sqlQuery-execute; $Slave _io_running=""; $Slave _sql_running=""; $Seconds _behind_master=""; while(My $ref = $sqlQueryFetchrow_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; }}

Then the keepalived and Check_ The slave.pl is copied to the corresponding location on the DB2 server, then the priority value in the Keepalived file is modified to 90, the nopreempt option is removed, and the DB1 service is started on DB2 and keepalived respectively.

5, test master-Slave synchronization function

First Telnet from the third server to the VIP "192.168.31.188" database, the operation process is as follows:

[Email protected]~] #mysql-uroot-p-H 192.168.31.188
Enter Password:

MySQL [(None)]> show variables like"%hostname%";+---------------+----------------+| variable_name | Value |+---------------+----------------+|hostname| DB1 |+---------------+----------------+1RowinchSet (0.01sec) MySQL [(none)]> Show variables like"%server_id%";+---------------+-------+| variable_name | Value |+---------------+-------+| server_id |1|+---------------+-------+1RowinchSet (0.01sec)
#可以看到成功通过VIP登录 and logged on to the DB1 server .
#接下来测试复制数据功能 MySQL [(none)]>CREATE DATABASE Repldb; Query OK,1Row affected (0.12sec) MySQL [(none)]>show databases;+--------------------+| Database |+--------------------+|               Information_schema | | MySQL | | Repldb | | Test |+--------------------+5RowsinchSet (0.01sec) MySQL [(none)]>Use repldb;database changedmysql [repldb]> CREATE TABLE Repl_table (ID int, Email varchar ( the), Password varchar ( +) notNULL); Query OK,0Rows Affected (0.03sec) MySQL [Repldb]>show tables;+------------------+| Tables_in_repldb |+------------------+| Repl_table |+------------------+1RowinchSet (0.00sec) MySQL [Repldb]> INSERT INTO Repl_table (ID, Email,password) VALUES (1,"[email protected]","123456"); Query OK,1Row affected (0.00Sec

Then log in to the DB2 host MySQL view, you can find the data to achieve synchronization.

6. Fail-over Test

First, remotely log in to the MySQL system via the VIP address, do not exit this connection, and then do the following on the MySQL command line of the DB1 server:

Mysql>slave stop;

Then go back to the remote connected MySQL database and do the following:

MySQL [(None)]> show variables like"%hostname%";+---------------+----------------+| variable_name | Value |+---------------+----------------+|hostname| DB2 |+---------------+----------------+1RowinchSet (0.01sec) MySQL [(none)]> Show variables like"%server_id%";+---------------+-------+| variable_name | Value |+---------------+-------+| server_id | 2 |+---------------+-------+1RowinchSet (0.01Sec

It can be found that the MySQL service switched from DB1 to the DB2 server after the DB1 discovery failure, and all configurations are complete.

Configure MySQL to implement Master Master Interop mode and leverage keepalived for dual master high availability