Keepalived+mysql Master Master Interop mode for high availability

MySQL has many high-availability scenarios, a highly available solution that is simple to configure and useful.

Experimental architecture diagram:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/57/79/wKioL1SbeqHyGF7WAAEGk3sODA4346.jpg "title=" Qq20141225104057.png "alt=" Wkiol1sbeqhygf7waaegk3soda4346.jpg "/>

Modify the DB1 configuration file to add the following items:

[Mysqld]

Log-bin=mysql-bin

Relay-log=mysql-relay-bin

Server-id=1

Replicate-wild-ignore-table=mysql.%

replicate-wild-ignore-table=test.%

replicate-wild-ignore-table=information_schema.%

Modify the DB2 configuration file to add the following items:

[Mysqld]

Log-bin=mysql-bin

Relay-log=mysql-relay-bin

server-id=2

Replicate-wild-ignore-table=mysql.%

replicate-wild-ignore-table=test.%

replicate-wild-ignore-table=information_schema.%

It is recommended to use the replicate-wild-ignore-table and replicate_wild_do_table two options on master and slave libraries to solve the problem of replication filtering.

Ensure data synchronization

If you already have MySQL data on the DB1, you need to synchronize the MySQL on the DB1 and the DB2 before performing the primary master Interop.

First, the lock table is executed on DB1 and then the data is exported and applied on the DB2.

Lock Table command: FLUSH TABLES with READ lock;

Create a replication user and authorize

First create a replication user in DB1

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/57/79/wKioL1SbgXOzyPF5AAB3PKq6Os0325.jpg "title=" 1.png " alt= "Wkiol1sbgxozypf5aab3pkq6os0325.jpg"/>

Then set DB1 as your home server in DB2 's MySQL library

mysql> change Master to \

Master_host= ' 192.168.0.254 ',

Master_user= ' Repl_user ',

-master_password= ' 123456 ',

Master_log_file= ' mysql-bin.000002 ',

master_log_pos=106;

Note: The values of the two options for Master_log_file and Master_log_pos correspond to the values that are queried by the SQL statement "Show Master Status" on DB1.

Start the slave service on DB2

mysql> start slave;

Viewing the running status of slave on DB2

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/57/79/wKioL1SbhH3QdWdOAAJBhRl9pYM736.jpg "title=" Qq20141225112557.png "alt=" Wkiol1sbhh3qdwdoaajbhrl9pym736.jpg "/>

This way the master-slave copy of DB1 to DB2 has been completed, followed by a master-slave copy from DB2 to DB1, which is exactly the same as before. First create a replication user in the MySQL library of DB2

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/57/79/wKioL1SbhRaxVtc5AAB3PKq6Os0925.jpg "title=" 1.png " alt= "Wkiol1sbhraxvtc5aab3pkq6os0925.jpg"/>

Then set DB2 as your home server in DB1 's MySQL library

mysql> change Master to \

Master_host= ' 192.168.0.251 ',

Master_user= ' Repl_user ',

-master_password= ' 123456 ',

Master_log_file= ' mysql-bin.000002 ',

master_log_pos=106;

Start the replication thread

mysql> start slave;

Viewing the running status of slave on DB1

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/57/7C/wKiom1SbhjPxUXBjAAKZv1_WjLU955.jpg "title=" 2.png " alt= "Wkiom1sbhjpxuxbjaakzv1_wjlu955.jpg"/>

As seen from the state, the Replication service is functioning properly, and the master-slave replication configuration for MySQL dual master mode is complete.

Configuring keepalived for MySQL dual master high Availability

Need to install keepalived on both servers, here take DB1 installation process example, DB2 installation process is the same here does not repeat.

Tar XF keepalived-1.2.12.tar.gz

CD keepalived-1.2.12

./configure--sysconf=/etc--with-kernel-dir=/usr/src/kernels/2.6.32-504.el6.x86_64/

Make

Make install

DB1 Server configuration file/etc/keepalived/keepalived.conf

Global_defs {

Notification_email {

[Email protected]

}

Notification_email_from keepalived.example.com

router_id Mysql_ha

}

Vrrp_script Check_mysqld {

Script "/etc/keepalived/check_slave.pl 127.0.0.1"

Interval 2

Weight 21

}

Vrrp_instance ha_1 {

Configured as backup on state backup//DB1 and DB2

Interface eth0

VIRTUAL_ROUTER_ID 80

Priority 100

Advert_int 2

Nopreempt//No preemption mode, only set on the priority machine, the low priority machine can not be set.

Authentication {

Auth_type PASS

Auth_pass 23B14455CD

}

Track_script {

Check_mysqld

}

virtual_ipaddress {

192.168.0.150

}

}

Where the/etc/keepalived/check_slave.pl script content is:

#!/usr/bin/perl-w

Use DBI;

Use Dbd::mysql;

# CONFIG VARIABLES

$SBM = 120;

$db = "wpdb";

$host = $ARGV [0];

$port = 3306;

$user = "root";

$PW = "123456";

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

}

}

This is written in Perl to detect the MySQL replication status of the script, modify the file in the MySQL database port, user name, password can be used directly.

If the following error is performed on this foot of this newspaper:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/57/7D/wKiom1Sbn_GiqrKRAAEysQybBKs065.jpg "title=" 5.png " alt= "Wkiom1sbn_giqrkraaeysqybbks065.jpg"/>

Use the following command to resolve:

# yum Install perl-dbi perl-dbd-mysql-y

DB2 keepalived.conf files and DB1 Basically, just change the priority value to 90, because the configuration is not preemption mode, you also need to remove the nopreempt option.

Then start the Keepalived service on both hosts separately

High-availability capabilities for testing services

We are connecting with the MySQL client on the 192.168.0.3 host computer vip:192.168.0.150

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/57/7D/wKiom1SbknvxkZ91AAIARvzmG7A712.jpg "title=" 3.png " alt= "Wkiom1sbknvxkz91aaiarvzmg7a712.jpg"/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/57/7D/wKiom1SbktvS-2H9AAFtIG96YV8466.jpg "title=" 4.png " alt= "Wkiom1sbktvs-2h9aaftig96yv8466.jpg"/>

from the above two graphs can be seen, is currently connected to the 192.168.0.254 server, also represents the cursor IP On this server.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/57/7A/wKioL1SbnSmxn-3_AAHGuMxOX0Q529.jpg "title=" 33.png "alt=" Wkiol1sbnsmxn-3_aahgumxox0q529.jpg "/>

Test failover

Failure Simulation , we then manually stop 192.168.0.254 of the MySQL replication thread

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/57/7D/wKiom1Sbngrx37kTAAFswoBFAF8765.jpg "title=" rr.png "alt=" Wkiom1sbngrx37ktaafswobfaf8765.jpg "/>

As you can see here, when the replication thread is stopped, the connection is interrupted once when the query is executed, the query is immediately connected again, and the displayed server_id has become 2, indicating that the server has switched.

Check the IP address of the DB2 server to verify that the cursor IP is over.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/57/7D/wKiom1SbnyHDj4vzAAItp-PDPKE779.jpg "title=" 11.png "alt=" Wkiom1sbnyhdj4vzaaitp-pdpke779.jpg "/>

Experiment Done!

This article from "Breakthrough Comfort zone" blog, reproduced please contact the author!

Keepalived+mysql Master Master Interop mode for high availability