Mysql+Keepalived實現雙主熱備方式

cd /usr/local/src/
tar -zxvf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
mkdir -p /usr/local/mysql/
mv mysql-5.7.28-linux-glibc2.12-x86_64 /usr/local/mysql/mysql-5.7.28

cd /usr/local
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
chown -R mysql:mysql mysql

cd /usr/local/mysql/mysql-5.7.28
bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/mysql-5.7.28/ --datadir=/data1/mysql/data --lc_messages_dir=/usr/local/mysql/mysql-5.7.28/share --lc_messages=en_US
bin/mysql_ssl_rsa_setup --datadir=/data1/mysql/data

vi /etc/my.cnf
basedir=/usr/local/mysql/mysql-5.7.28
datadir=/data1/mysql/data

#編輯環境變數 .bash_profile表示當前使用者個人設定 只對當前使用者生效
vi ~/.bash_profile
 
#將mysql的bin目錄設定到環境變數中
PATH=$PATH:$HOME/bin:/usr/local/mysql/mysql-5.7.28/bin
export PATH
 
#使設定修改後立即生效
source ~/.bash_profile

#啟動前先設定免密登入
vi /etc/my.cnf
#新增如下設定並儲存 跳過許可權驗證
skip-grant-tables=1
 
#安全啟動
cd /usr/local/mysql/mysql-5.7.28/bin
./mysqld_safe --user=mysql &
 
#修改密碼
#進入mysql
mysql
use mysql
update user set authentication_string=password("123456") where user="root";
#重新整理許可權表
flush privileges;
 
#設定遠端存取
use mysql
#允許所有機器存取root使用者 %代表所有機器
update user set host='%' where user='root';
#授權所有機器的root使用者擁有所有資料庫的所有許可權 遠端存取密碼root %代表所有機器
grant all privileges on *.* to root@'%' identified by 'root';
#重新整理許可權表
flush privileges;
 
#退出mysql
exit
 
#安全關閉
./mysqladmin -uroot -p shutdown
 
#刪除免密登入設定
vi /etc/my.cnf
#刪除skip-grant-tables=1並儲存
 
#安全啟動
./mysqld_safe --user=mysql &

#授權IP192.168.0.102的從資料庫replication使用者擁有所有資料庫的同步許可權 遠端存取密碼replication
grant replication slave on *.* to 'replication'@'192.168.0.102' identified by 'replication';
#重新整理許可權表
flush privileges;

#授權IP192.168.0.101的從資料庫replication使用者擁有所有資料庫的同步許可權 遠端存取密碼replication
grant replication slave on *.* to 'replication'@'192.168.0.101' identified by 'replication';
#重新整理許可權表
flush privileges;

[mysqld]
basedir=/usr/local/mysql/mysql-5.7.28
datadir=/data1/mysql/data
 
server-id = 101
 
skip-external-locking
 
log_bin = mysql-binlog
binlog_do_db = sample
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema
 
binlog_group_commit_sync_delay = 100000 # 單位微妙
binlog_group_commit_sync_no_delay_count = 20 # 單位每組事物數量
 
#設定複製型別
slave-parallel-type=LOGICAL_CLOCK
#設定並行數量
slave-parallel-workers=4
 
#主主需要加的部分
replicate-do-db = sample
 
log-slave-updates=true
#sync_binlog=1
auto_increment_offset=1
auto_increment_increment=2
 
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_chunk_size = 128M
innodb_log_file_size = 256M
innodb_log_buffer_size = 32M
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 120
 
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/log/slow.log
long_query_time = 5
 
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 
secure_file_priv=/usr/local/mysqlcsvfiles/
 
expire_logs_days=7
max_binlog_size=1024M
 
!includedir /etc/my.cnf.d

mkdir -p /usr/local/mysql/log
mkdir -p /usr/local/mysqlcsvfiles

./mysqladmin -uroot -p shutdown

./mysqld_safe --user=mysql &

#進入mysql執行
show master statusG;
 
#分別記錄主的binlog位置，在101的角度上102為主，在102的角度上，101為主
File:mysql-bin.000001
Position: 154
 
#設定同步關係（兩個主都要設定）
#其中master_log_file和master_log_pos要登入到對方機器執行show master statusG;去檢視
stop slave;
（在192.168.0.101）
change master to master_host='192.168.0.102',master_user='replication',master_password='replication',master_log_file='mysql-bin.000001',master_log_pos=154;
（在192.168.0.102）
change master to master_host='192.168.0.101',master_user='replication',master_password='replication',master_log_file='mysql-bin.000001',master_log_pos=154;
 
#分別啟動slave
start slave;

yum install -y gcc openssl-devel libnl libnl-devel libnfnetlink-devel

#解壓keepalived壓縮包放到/usr/local/並重新命名為keepalived
cd /usr/local/src
tar -zxvf keepalived-2.0.7.tar.gz
mv keepalived-2.0.7 /usr/local/keepalived
 
#設定keepalived 得到一個Makefile的資料夾
#--prefix：keepalived安裝目錄
#--sysconf：keepalived的核心組態檔，必須要在/etc目錄下面，改為其他位置會導致啟動不了，不設定在該目錄下的話，啟動keepalived時紀錄檔檔案裡面會報錯，顯示找不到/etc這個資料夾
cd /usr/local/keepalived
./configure --prefix=/usr/local/keepalived/ --sysconf /etc
 
#編譯和安裝keepalived
make && make install
 
#建立keepalived軟連結 /usr/sbin/如果存在keepalived就先刪除
ln -s  /usr/local/keepalived/sbin/keepalived /usr/sbin/
 
#複製keepalived指令碼檔案到/etc/init.d/目錄下
cd keepalived
cp /usr/local/keepalived/etc/init.d/keepalived /etc/init.d/
 
#設定Keepalived開機自啟動
chkconfig --add keepalived
chkconfig keepalived on
 
#啟動keepalived
service keepalived start

vi /etc/keepalived/keepalived.conf

global_defs {
	router_id LVS_LEVEL1	#主伺服器名稱
}
 
vrrp_script check_run {
   script "/etc/keepalived/mysql_check.sh"
   interval 5				#5秒執行一次指令碼
}
 
vrrp_instance VI_1 {
    state MASTER			#主伺服器
    interface eth0			#承載VIP地址到物理介面
    virtual_router_id 51	#虛擬路由器ID號，每個熱播組保持一致
    priority 100			#優先順序，數值越大優先順序越高
    advert_int 1			#檢查間隔，預設為1s
    authentication {		#認證資訊，每個熱播組保持一致
        auth_type PASS      #認證型別
        auth_pass 1111		#密碼字串
    }
    virtual_ipaddress {
        192.168.0.144		#VIP地址（內網地址）
    }
    track_script {
        check_run
    }
}

global_defs {
	router_id LVS_LEVEL2	#備份伺服器名稱
}
vrrp_script check_run {
	script "/etc/keepalived/mysql_check.sh"
	interval 5				#5秒執行一次指令碼
}
vrrp_instance VI_1 {
    state BACKUP			#備份伺服器
    interface eth0			#承載VIP地址到物理介面
    virtual_router_id 51	#虛擬路由器ID號，每個熱播組保持一致
    priority 50				#優先順序，數值越大優先順序越高
    advert_int 1			#檢查間隔，預設為1s
    authentication {		#認證資訊，每個熱播組保持一致
        auth_type PASS      #認證型別
        auth_pass 1111		#密碼字串
    }
    virtual_ipaddress {
        192.168.0.144       #VIP地址（和主伺服器設定一樣）
    }
    track_script {
        check_run
    }
}

#!/bin/bash
/usr/bin/mysql -uroot -p'你自己的資料庫密碼' -e "show status" &>/dev/null 
if [ $? -ne 0 ] ;then
    systemctl stop keepalived
fi

chmod +x /etc/keepalived/mysql_check.sh