Configuring keepalived for MySQL dual master high Availability

This article is for the South African ant book " Step linux-Second Edition " -8.3.6 of Reading notes

Installing keepalived Software on DB1 and DB2

Install the Build tool first

# yum Install gcc* gcc-c++ autoconf automake

Upload keepalived-1.2.19.tar.gz to/server/tools directory

# Cd/server/tools

# tar ZXVF keepalived-1.2.19.tar.gz

# CD keepalived-1.2.19

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

# make

# make Install

# ln-s/usr/local/sbin/keepalived/sbin/

# chkconfig--add keepalived

# chkconfig--level keepalived on

# CD: /

After the installation is complete, enter the DB1 configuration process

DB1 configuration content on server/etc/keepalived/keepalived.conf, where green font is new

! Configuration File for Keepalived

Global_defs {

Notification_email {

[Email protected]

[Email protected]

[Email protected]

}

Notification_email_from [email protected]

Smtp_server 192.168.200.1

Smtp_connect_timeout 30

router_id Lvs_devel

}

Vrrp_scripts Check_mysqld {

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

Interval 2

Weight 21

}

Vrrp_instance ha_1 {

State BACKUP #在DB1和DB2上均配置为BACKUP

Interface eth0

VIRTUAL_ROUTER_ID 80

Priority 90

Advert_int 2

Nopereempt #不抢占模式, set only on high priority machines, not on low priority machines

Authentication {

Auth_type PASS

Auth_pass QWEASDZXC

}

Track_script {

Check_mysqld

}

virtual_ipaddress {

10.24.24.110/24 Dev eth0 #mysql的对外服务IP, VIP

}

}

Vrrp_instance Vi_1 {

State MASTER

Interface eth0

VIRTUAL_ROUTER_ID 51

Priority 100

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}

virtual_ipaddress {

192.168.200.16

192.168.200.17

192.168.200.18

}

}

-----------------------------------------

# mkdir/etc/keepalived/mysqlcheck/

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

# vim/etc/keepalived/mysqlcheck/check_slave.pl

#!/usr/bin/perl-w

Use DBI;

Use Dbd::mysql;

# CONFIG VARIABLES

$SBM = 120;

$db = "ywadmin";

$host = $ARGV [0];

$port = 3306;

$user = "root";

$PW = "jzh0024";

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

}

}

Just modify the red database name \ database port \ Username and password to

Add executable permissions

# chmod +x/etc/keepalived/mysqlcheck/check_slave.pl

Copy the keepalived.conf and check_slave.pl files to the location of the DB2 server, modify the priority value in keepalived.conf on DB2 to 90, and remove the nopreempt option

DB2 on

[Email protected] keepalived]# mkdir/etc/keepalived/mysqlcheck/

[Email protected] keepalived]# cd/etc/keepalived/mysqlcheck/

Uploading check_slave.pl Scripts

[Email protected] mysqlcheck]# RZ

[email protected] mysqlcheck]# LL

Total 4

-rw-r--r--1 root root 919 Sep 14:57 check_slave.pl

[Email protected] mysqlcheck]# chmod +x check_slave.pl

After all configurations are completed, start the keepalived service on DB1 and DB2 respectively, and normally the VIP address should be running on the DB1 server

[[email protected] tools]#/etc/init.d/keepalived start

Starting keepalived: [OK]

[[email protected] keepalived]#/etc/init.d/keepalived start

Starting keepalived: [OK]

======================================================

8.3.7 test MySQL master-slave synchronization function

First, add the remote access authorization on the DB1,DB2;

Authorized on DB1

Mysql> Grant All on * * to ' root ' @ ' 10.24.24.% ' identified by ' jzh0024 ';

Query OK, 0 rows Affected (0.00 sec)

mysql> flush Privileges;

Query OK, 0 rows Affected (0.00 sec)

Mysql> select User,host from Mysql.user;

Authorized on DB2

Mysql> Grant All on * * to ' root ' @ ' 10.24.24.% ' identified by ' jzh0024 ';

Query OK, 0 rows Affected (0.00 sec)

mysql> flush Privileges;

Query OK, 0 rows Affected (0.00 sec)

Mysql> select User,host from Mysql.user;

1. Log on to the remote client through the VIP login test

[Email protected] ~]# mysql-uroot-p-H 10.24.24.110

Enter Password:

Mysql> Show variables like "%hostname%";

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

| variable_name | Value |

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

| hostname | DB1 |

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

1 row in Set (0.00 sec)

Mysql> Show variables like "%server_id%";

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

| variable_name | Value |

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

| server_id | 1 |

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

1 row in Set (0.00 sec)

From the results of SQL output, you can log in via VIP and log in to the DB1 server

2. Data copy function test

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/88/1F/wKiom1fqEvnCZV8XAAD3yI_Y_cw753.jpg "title=" 5546e1c6-feb8-4516-8106-595f250f76cf.jpg "alt=" Wkiom1fqevnczv8xaad3yi_y_cw753.jpg "/>

[Email protected] ~]# mysql-uroot-p-H 10.24.24.110

Enter Password:

mysql> CREATE DATABASE Repldb;

Query OK, 1 row Affected (0.00 sec)

mysql> use REPLDB;

Database changed

Mysql> CREATE TABLE repl_table (ID int,email varchar (), password varchar (+) not NULL);

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show tables;

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

| Tables_in_repldb |

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

| repl_table |

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

1 row in Set (0.00 sec)

mysql> INSERT INTO repl_table (Id,email,password) VALUES (1, "[email protected]", "QWESSD");

Query OK, 1 row Affected (0.00 sec)

Mysql> select * from Repl_table;

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

| ID | email | password |

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

| 1 | [Email protected] | QWESSD |

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

1 row in Set (0.00 sec)

Querying whether data is synchronized in the DB2 database

mysql> show databases;

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

| Database |

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

| Information_schema |

| DB01 |

| DB02 |

| MySQL |

| Repldb |

| Ywadmin |

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

6 rows in Set (0.00 sec)

mysql> use REPLDB;

Database changed

Mysql> Show tables;

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

| Tables_in_repldb |

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

| repl_table |

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

1 row in Set (0.00 sec)

Mysql> select * from Repl_table;

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

| ID | email | password |

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

| 1 | [Email protected] | QWESSD |

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

1 row in Set (0.00 sec)

Prove that the data that is written to the VIP in other MySQL client logins has been synchronized to the DB2 database.

======================================================

8.3.8 test keepalived for MySQL failover

In order to test the failover function implemented by the keepalived, some faults need to be simulated.

For example, you can disconnect the DB1 host network, shut down the DB1 host, turn off the MySQL service on the DB1, and other operations to achieve;

This stops the DB1 server network connection, simulates the DB1 MySQL fault;

Since the script check_slave.pl to monitor the running state of MySQL is added on the DB1, DB2 server, keepalived is detected immediately after the DB1 MySQL log receive function is turned off, then the switchover operation is performed

The test process is as follows:

1. Stop the DB1 server's network connection

In the remote MySQL client log in to the MySQL system with the VIP address, do not exit this connection;

Interrupt the network connection of the DB1 server;

[[email protected] ~]#/etc/init.d/network stop

2. In MySQL remote client test

Continue to execute commands in the remote MySQL connection that you just opened

Mysql> Show variables like "%hostname%";

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

| variable_name | Value |

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

| hostname | DB2 |

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

1 row in Set (0.00 sec)

Mysql> Show variables like "%server_id%";

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

| variable_name | Value |

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

| server_id | 2 |

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

1 row in Set (0.00 sec)

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/88/1C/wKioL1fqEwmgtA8KAACaUDJOKfU047.jpg "title=" Fd0f5a59-5583-4e06-afbd-b9ca0922bd1a.jpg "alt=" Wkiol1fqewmgta8kaacaudjokfu047.jpg "/>

Then reopen the DB1 network connection and discover that keepalived will no longer perform the switchover because the keepalived is configured as preemption mode.

At this point, the MySQL service will continue to run on the DB2 server, the cost of each switchover is very high, thus shutting down the active preemption mode of keepalived.

This article is from the "Linux operations self-cultivation" blog, please be sure to keep this source http://ywliyq.blog.51cto.com/11433965/1856964

Configuring keepalived for MySQL dual master high Availability