MySQL主主復制+Keepalived打造高可用MySQL集群的步驟

這篇文章主要介紹“主主復制+Keepalived打造高可用MySQL集群的步驟”，在日常操作中，相信很多人在MySQL主主復制+Keepalived打造高可用MySQL集群的步驟問(wèn)題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對大家解答”MySQL主主復制+Keepalived打造高可用MySQL集群的步驟”的疑惑有所幫助！接下來(lái)，請跟著(zhù)小編一起來(lái)學(xué)習吧！

以下內容均是實(shí)驗環(huán)境，請根據實(shí)際情況修改響應參數
實(shí)驗環(huán)境：
mysql1 ip：10.1.1.20
mysql2 ip：10.1.1.21
mysql vip：10.1.1.25

三臺機器均安裝centos 6.5_x64（虛擬機環(huán)境）
實(shí)驗開(kāi)始
一、安裝mysql，并打造主主同步。
相信主從同步大家都會(huì )做，一樣的道理，主主同步就是兩臺機器互為主的關(guān)系，在任何一臺機器上寫(xiě)入都會(huì )同步。
安裝mysql的過(guò)程不解釋?zhuān)瑈um就好啦

配置主主同步
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  #開(kāi)啟binlog功能
log-bin-index=binlog.index
sync_binlog=0
server_id = 1    #兩臺機器不能重復一個(gè)server_id=1一個(gè)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)
注:由于本文是實(shí)驗環(huán)境下編寫(xiě)，所以沒(méi)考慮任何安全性問(wèn)題，同步賬號也是最高權限，請根據實(shí)際情況設置響應權限??！

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  #執行這命令后 注意觀(guān)察下面這兩個(gè)參數，必須要都是yes才行
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

同樣的 反過(guò)來(lái)做相同操作
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  #執行這命令后 注意觀(guān)察下面這兩個(gè)參數，必須要都是yes才行
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
介此，主主同步打造完成，可以簡(jiǎn)單測試一下，分別在兩個(gè)機器上寫(xiě)數據 看看會(huì )不會(huì )同步到另一臺機器上
PS：如果報錯Slave_IO_Running: NO可以檢查同步的賬號是否創(chuàng )建正常！

二、安裝keepalived并設置監控
keepalived是安裝在兩臺MySQL上的
首先安裝keepalived過(guò)程不解釋就正常解壓安裝就好
安裝后配置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                           #優(yōu)先級，另一臺改為90
  advert_int 1
  nopreempt                              #不搶占，只在優(yōu)先級高的機器上設置即可，優(yōu)先級低的機器不設置
  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                 #會(huì )話(huà)保持時(shí)間
  protocol TCP
real_server 10.1.1.20 3306 {
      weight 3
      notify_down /tmp/killprocess.sh    #檢測到mysqld服務(wù)掛了執行此腳本（腳本要自己寫(xiě)）
      TCP_CHECK {
      connect_timeout 10                 #連接超時(shí)時(shí)間
      nb_get_retry 3                     #重連次數
      delay_before_retry 3               #重連間隔時(shí)間
      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
          }
      }
}

編寫(xiě)監控mysql服務(wù)是否掛了的腳本，按照上面配置文件的位置編寫(xiě)腳本。
# vim /tmp/killprocess.sh
#!/bin/sh
pkill keepalived
腳本很簡(jiǎn)單就一句，目的是當keepalived檢測到mysql服務(wù)掛了之后觸發(fā)該腳本，殺死keepalived進(jìn)程，讓另一臺服務(wù)器來(lái)接管

修改后啟動(dòng)keeplived服務(wù)
# service keeplived start
介此整個(gè)集群搭建完成

三、測試
找臺機器用虛擬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>
成功連上了，然后可以任意關(guān)閉某臺機器，或者某臺機器的mysql服務(wù)，看看還能不能連上??！