MySQL HA

備註：

準備兩臺伺服器

以root使用者安裝

關閉selinux

一、安裝MySQL

1.解除安裝mariadb

#查詢mariadb 安裝包
rpm -qa|grep mariadb
mariadb-libs-5.5.60-1.el7_5.x86_64
#刪除centos自帶的安裝包
rpm -e --nodeps mariadb-libs-5.5.60-1.el7_5.x86_64
#查詢是否解除安裝乾淨
rpm -qa|grep mariadb

2.安裝必要的外掛

#我的伺服器是最小化安裝 如果已安裝請省略
yum install -y net-tools autoconf

3.安裝服務端

rpm -ivh MySQL-server-5.6.24-1.el6.x86_64.rpm

4.安裝客戶端

rpm -ivh MySQL-client-5.6.24-1.el6.x86_64.rpm

5.登入MySQL進行相關配置

#啟動MySQL服務
service mysql start
#檢視MySQL安裝完成生成的隨機密碼
cat /root/.mysql_secret
#登陸MySQL
mysql -uroot -pB3uRVTBXWdAoZjlX
#修改密碼
SET PASSWORD=PASSWORD('要修改成的密碼');
#退出MySQL
exit;
#用新密碼重新登陸
mysql -uroot -p修改後的密碼
#使用mysql資料庫
use mysql;
#設定遠端訪問
update user set host='%' where host='localhost';
#重新整理
flush privileges;
#退出
exit;

6.第二臺伺服器執行同樣的操作

二、主從複製

A伺服器為主B伺服器為從

1.修改A伺服器的my.cnf檔案

#修改MySQL的配置檔案 MySQL版本不同 my.cnf 可能不在/usr下
vim /usr/my.cnf 
#在[mysqld]下新增如下內容
	[mysqld]
	log_bin = mysql-bin # log_bin
	binlog_format = row #binlog日誌型別
	server_id = 1 #MySQL伺服器唯一id
	relay_log=mysql-relay #開啟slave中繼日誌

2.重啟A伺服器MySQL

service mysql restart
mysql -uroot -p你的密碼
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      432 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3.修改B伺服器的my.cnf檔案

#修改MySQL的配置檔案 MySQL版本不同 my.cnf 可能不在/usr下
vim /usr/my.cnf 
#在[mysqld]下新增如下內容
[mysqld]
	log_bin = mysql-bin # log_bin
	binlog_format = row #binlog日誌型別
	server_id = 2 #MySQL伺服器唯一id
	relay_log=mysql-relay #開啟slave中繼日誌

4.重啟B伺服器MySQL

#重啟B伺服器MySQL
service mysql restart
mysql -uroot -p你的密碼
mysql>
CHANGE MASTER TO 
MASTER_HOST='A伺服器的IP',
MASTER_USER='root',
MASTER_PASSWORD='A伺服器的MySQL密碼',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=120;
#啟動slave
mysql> start slave;
#檢視slave狀態
mysql>show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: hadoop103
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000018
          Read_Master_Log_Pos: 432
               Relay_Log_File: mysql-relay.000067
                Relay_Log_Pos: 595
        Relay_Master_Log_File: mysql-bin.000018
             Slave_IO_Running: Yes      
            Slave_SQL_Running: Yes 
#Slave_IO_Running和Slave_SQL_Running均為yes表示配置成功

B伺服器為主A伺服器為從

1.登陸B伺服器檢視master狀態

#登陸伺服器
mysql -uroot -p你的密碼
#檢視master狀態
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2.重啟A伺服器MySQL

#重啟A伺服器mysql
service mysql restart
#登陸MySQL
mysql -uroot -p你的密碼
mysql>
CHANGE MASTER TO 
MASTER_HOST='B伺服器的IP',
MASTER_USER='root',
MASTER_PASSWORD='B伺服器的MySQL密碼',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=120;
#啟動slave
mysql> start slave;
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: hadoop104
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000019
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysql-relay.000066
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000019
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
#Slave_IO_Running和Slave_SQL_Running均為yes表示配置成功

三、安裝keepalived

yum install -y keepalived

1.解決包衝突

#安裝過程出現瞭如下的錯誤 keepalived 需要的mariadb-libs-1:5.5.68-1.el7.x86_64和MySQL-server-5.6.24-1.el6.x86_64出現了衝突
Transaction check error:
  file /usr/share/mysql/charsets/README from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.6.24-1.el6.x86_64
  file /usr/share/mysql/charsets/Index.xml from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.6.24-1.el6.x86_64
#查詢到剛剛安裝的MySQL
rpm -qa |grep -i mysql
MySQL-client-5.6.24-1.el6.x86_64
MySQL-server-5.6.24-1.el6.x86_64
#刪除MySQL服務端
rpm -e --nodeps MySQL-server-5.6.24-1.el6.x86_64
#重新安裝keepalived
yum install -y keepalived
#刪除mariadb
rpm -qa |grep mariadb
rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64
#再次安裝MySQL服務端
cd /安裝包目錄
rpm -ivh MySQL-server-5.6.24-1.el6.x86_64.rpm 
#開啟MySQL服務
service mysql start
#不需要再做任何配置用剛開始的密碼就能登陸
mysql -uroot -p之前修改的密碼

2.第二臺伺服器執行同樣的操作

3.修改A伺服器keepalived的配置檔案

vim /etc/keepalived/keepalived.conf
#在該檔案下新增如下內容
! Configuration File for keepalived
global_defs {
    router_id MySQL-ha
}
vrrp_instance VI_1 {
    state master #初始狀態
    interface ens33 #網路卡 centos 6 為 eth0
    virtual_router_id 51 #虛擬路由id
    priority 100 #優先順序
    advert_int 1 #Keepalived心跳間隔
    nopreempt #只在高優先順序配置，原master恢復之後不重新上位
    authentication {
        auth_type PASS #認證相關
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.138.100 #虛擬ip
    }
} 

#宣告虛擬伺服器
virtual_server 192.168.138.100 3306 {
    delay_loop 6
    persistence_timeout 30
    protocol TCP
    #宣告真實伺服器 192.168.138.101為A伺服器的IP
    real_server 192.168.138.101 3306 {
        notify_down /var/lib/mysql/killkeepalived.sh #真實服務故障後呼叫指令碼
        TCP_CHECK {
            connect_timeout 3 #超時時間
            nb_get_retry 1 #重試次數
            delay_before_retry 1 #重試時間間隔
        }
    }
}

4.編寫指令碼檔案

vim /var/lib/mysql/killkeepalived.sh
#新增如下內容
#! /bin/bash
sudo service keepalived stop
#新增執行許可權
chmod +x /var/lib/mysql/killkeepalived.sh
#修改所屬使用者和使用者組
cd  /etc/keepalived/
chown mysql:mysql keepalived.conf
#開啟keepalived
service keepalived start
#設定開機自起
systemctl enable keepalived
#如果開機自啟不成功嘗試下面的命令
systemctl daemon-reload 

5.修改B伺服器keepalived的配置檔案同步驟3、4，注意將A伺服器IP（192.168.138.101）替換B伺服器的真實IP