mysql-HA
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