配置Keepalived實現mysql雙主高可用

標籤：keepalived mysql 雙主高可用

本文為南非螞蟻的書籍《循序漸進linux-第二版》-8.3.6的讀筆記

在DB1和DB2上安裝Keepalived軟體

先安裝編譯工具

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

上傳keepalived-1.2.19.tar.gz至/server/tools目錄下

# 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 35 keepalived on

# cd ../

安裝完成後，進入DB1的配置過程

DB1伺服器上/etc/keepalived/keepalived.conf的配置內容，其中綠色字型為新增部分

! 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         #不搶佔模式，只在優先順序高的機器上設定即可，優先順序低的機器上不設定

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/

其中，/etc/keepalived/mysqlcheck/check_slave.pl檔案的內容如下：

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

    }

}

只需要修改紅色的資料庫名\資料庫連接埠\使用者名稱和密碼即可

添加可執行許可權

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

將keepalived.conf和check_slave.pl檔案複製到DB2伺服器對應的位置，將DB2上的keepalived.conf中的priority值修改為90，同時去掉nopreempt選項

DB2上

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

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

上傳check_slave.pl指令碼

[[email protected] mysqlcheck]# rz

[[email protected] mysqlcheck]# ll

total 4

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

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

完成所有配置後，分別啟動DB1和DB2上啟動keepalived服務，正常情況下VIP地址應該運行在DB1伺服器上

[[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 測試mysql主從同步功能

首先在DB1,DB2上添加遠端存取授權;

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;

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.在遠程用戶端通過VIP登入測試

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

從SQL輸出結果看,可以通過VIP登入,並且登入到了DB1伺服器

2.資料複製功能測試

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(86),password varchar(40) 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)

在DB2資料庫中查詢資料是否已經同步

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)

證明在其他mysql用戶端登入寫入VIP的資料已經同步到DB2資料庫中。

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

8.3.8 測試keepalived實現mysql故障切換

為了測試keepalived實現的故障切換功能，需要類比一些故障。

比如，可以斷開DB1主機的網路、關閉DB1主機、關閉DB1上mysql服務等各種操作實現；

這裡停止DB1伺服器的網路連接，類比DB1的mysql故障；

由於在DB1、DB2伺服器上都添加了監控MYSQL運行狀態的指令碼 check_slave.pl，因此當關閉DB1的MYSQL日誌接收功能後，keepalived會立刻檢測到，接著執行切換操作

測試過程如下：

1.停止DB1伺服器的網路連接

在遠程mysql用戶端以VIP地址登入到mysql系統中，不要退出這個串連；

中斷DB1伺服器的網路連接;

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

2.在mysql遠程用戶端測試

繼續在剛才開啟的遠程mysql串連中執行命令

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

接著重新開啟DB1的網路連接，發現keepalived將不再執行切換操作了，因為上面將keepalived配置為搶佔模式了。

此時，mysql服務將一直在DB2伺服器上運行，每次切換的代價很大，因而關閉了keepalived的主動搶佔模式。

本文出自 “Linux營運的自我修養” 部落格，請務必保留此出處http://ywliyq.blog.51cto.com/11433965/1856964

配置Keepalived實現mysql雙主高可用