為響應公司需求，打造出更安全的mysql叢集，能夠實現mysql故障後切換，研究了幾天終於有了成果，一起分享一下。
首先介紹一下這套叢集方案實現的功能
1、mysql伺服器故障後自動轉移，修好後自動切回
2、mysql服務故障自動轉移，修好後自動切回
3、可以實現在幾秒鐘內轉移

以下內容均是實驗環境，請根據實際情況修改響應引數
實驗環境：
mysql1 ip：10.1.1.20
mysql2 ip：10.1.1.21
mysql vip：10.1.1.25

三臺機器均安裝centos 6.5_x64（虛擬機器環境）
實驗開始
一、安裝mysql，並打造主主同步。
相信主從同步大家都會做，一樣的道理，主主同步就是兩臺機器互為主的關係，在任何一臺機器上寫入都會同步。
安裝mysql的過程不解釋，yum就好啦

配置主主同步
1.配置 /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-bin=binlog #開啟binlog功能
log-bin-index=binlog.index
sync_binlog=0
server_id = 1    #兩臺機器不能重複一個server_id=1一個server_id=2

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

2.分別在兩臺機器上配置同步賬號
10.1.1.20機器上：
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with; or \g.
Your MySQL connection id is 2
Server version: 5.0.77-log Sourcedistribution

Type 'help;' or '\h' for help. Type '\c' toclear the buffer.
mysql> GRANT replication slave ON *.* TO'ab'@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

10.1.1.21機器上：
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with; or \g.
Your MySQL connection id is 2
Server version: 5.0.77-log Sourcedistribution

Type 'help;' or '\h' for help. Type '\c' toclear the buffer.
mysql> GRANT replication slave ON *.* TO'ab'@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
注:由於本文是實驗環境下編寫，所以沒考慮任何安全性問題，同步賬號也是最高許可權，請根據實際情況設定響應許可權！！

3.設定同步
10.1.1.20機器上:
mysql> flush tables with read lock;
mysql> show master status;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB |Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000003 | 365      |              |                  |
+---------------+----------+--------------+------------------+
1 row in set (0.03 sec)

mysql> unlock tables;
Query OK, 0 rows affected (0.03 sec)

10.1.1.21機器上：
mysql> change master tomaster_host='10.1.1.20', master_port=3306, master_user='ab',master_password='123', master_log_file='binlog.000003',master_log_pos=365;
Query OK, 0 rows affected (0.06 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G #執行這命令後注意觀察下面這兩個引數，必須要都是yes才行
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

同樣的反過來做相同操作
10.1.1.21機器上:
mysql> flush tables with read lock;
mysql> show master status;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB |Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000004 | 207      |              |                  |
+---------------+----------+--------------+------------------+
1 row in set (0.03 sec)

mysql> unlock tables;
Query OK, 0 rows affected (0.03 sec)

10.1.1.20機器上：
mysql> change master tomaster_host='10.1.1.21', master_port=3306, master_user='ab',master_password='123', master_log_file='binlog.000004',master_log_pos=207;
Query OK, 0 rows affected (0.06 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G #執行這命令後注意觀察下面這兩個引數，必須要都是yes才行
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
介此，主主同步打造完成，可以簡單測試一下，分別在兩個機器上寫資料看看會不會同步到另一臺機器上
PS：如果報錯Slave_IO_Running: NO可以檢查同步的賬號是否建立正常！

二、安裝keepalived並設定監控
keepalived是安裝在兩臺MySQL伺服器上的
首先安裝keepalived過程不解釋就正常解壓安裝就好
安裝後配置vim /etc/keepalived/keepalived.conf內容如下
10.1.1.20的配置檔案
! Configuration File for keepalived
global_defs {
notification_email {
    acassen@firewall.loc
    failover@firewall.loc
    sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id LVS_DEVEL
}

vrrp_instance VI_1 {
state backup                           #兩臺配置此處均是BACKUP
interface eth0
virtual_router_id 51
priority 100                           #優先順序，另一臺改為90
advert_int 1
nopreempt                              #不搶佔，只在優先順序高的機器上設定即可，優先順序低的機器不設定
authentication {
      auth_type PASS
      auth_pass 1111
}
virtual_ipaddress {
      10.1.1.25

}
}

virtual_server 10.1.1.25 3306 {
delay_loop 6
lb_algo wrr
lb_kind DR
persistence_timeout 50                 #會話保持時間
protocol TCP
real_server 10.1.1.20 3306 {
      weight 3
      notify_down /tmp/killprocess.sh    #檢測到mysqld服務掛了執行此指令碼（指令碼要自己寫）
      TCP_CHECK {
      connect_timeout 10                 #連線超時時間
      nb_get_retry 3                     #重連次數
      delay_before_retry 3               #重連間隔時間
      connect_port 3306                  #健康檢查埠
        }
      }
}

10.1.1.21的配置檔案
! Configuration File for keepalived
global_defs {
notification_email {
    acassen@firewall.loc
    failover@firewall.loc
    sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id LVS_DEVEL
}
vrrp_instance VI_1 {
state backup
interface eth0
virtual_router_id 51
priority 90
advert_int 1
authentication {
      auth_type PASS
      auth_pass 1111
}
virtual_ipaddress {
      10.1.1.25
}
}

virtual_server 10.1.1.25 3306 {
delay_loop 6
lb_algo wrr
lb_kind DR
persistence_timeout 50
protocol TCP
real_server 10.1.1.21 3306 {
      weight 3
      notify_down /tmp/killprocess.sh
      TCP_CHECK {
      connect_timeout 10
      nb_get_retry 3
      delay_before_retry 3
      connect_port 3306
          }
      }
}

編寫監控mysql服務是否掛了的指令碼，按照上面配置檔案的位置編寫指令碼。
# vim /tmp/killprocess.sh
#!/bin/sh
pkill keepalived
指令碼很簡單就一句，目的是當keepalived檢測到mysql服務掛了之後觸發該指令碼，殺死keepalived程式，讓另一臺伺服器來接管

修改後啟動keeplived服務
# service keeplived start
介此整個叢集搭建完成

三、測試
找臺機器用虛擬ip連線mysql
[root@localhost html]# mysql -uab -h 10.1.1.25 -p123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 736
Server version: 5.1.66-log Source distribution
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
成功連上了，然後可以任意關閉某臺機器，或者某臺機器的mysql服務，看看還能不能連上！！

MySQL主主複製+Keepalived打造高可用MySQL叢集

相關文章