MySQL進階：主主複製+Keepalived高可用

不羈的羅恩發表於2021-06-18
MySql

Blog：部落格園 個人

概述

mysql主主複製

所謂主主複製，即雙主備份，或者叫互作主從複製，每臺master既是master，又是slave。這種方案，既做到了訪問量的壓力分流，同時也解決了單點故障問題。

Keepalived

VRRP協議

VRRP可以將兩臺或者多臺物理路由器裝置虛擬成一個虛擬路由，這個虛擬路由器通過虛擬IP（一個或者多個)對外提供服務，而在虛擬路由器內部十多個物理路由器協同工作，同一時間只有一臺物理路由器對外提供服務，這臺物理路由裝置被成為：主路由器（Master角色)，一般情況下Master是由選舉演算法產生，它擁有對外服務的虛擬IP，提供各種網路功能，如：ARP請求，ICMP 資料轉發等，而且其它的物理路由器不擁有對外的虛擬IP，也不提供對外網路功能，僅僅接收MASTER的VRRP狀態通告資訊，這些路由器被統稱為“BACKUP的角色”，當主路由器失敗時，處於BACKUP角色的備份路由器將重新進行選舉，產生一個新的主路由器進入MASTER角色，繼續提供對外服務，整個切換對使用者來說是完全透明的。

Keepalived工作原理

Keepalived的作用是檢測伺服器的狀態，如果有一臺web伺服器當機，或工作出現故障，Keepalived將檢測到，並將有故障的伺服器從系統中剔除，同時使用其他伺服器代替該伺服器的工作，當伺服器工作正常後Keepalived自動將伺服器加入到伺服器群中，這些工作全部自動完成，不需要人工干涉，需要人工做的只是修復故障的伺服器。

伺服器資訊

IP 主機名
192.168.10.21 master-1
192.168.10.22 master-2

架構圖：

MySQL進階：主主複製+Keepalived高可用

安裝mysql

配置映象源

配置映象源建立檔案/etc/yum.repos.d/mysql57.repo：

# Enable to use MySQL 5.7
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

更新快取：

yum makecache

安裝

master-1和master-2都按照以下步驟安裝mysql。

yum -y install mysql mysql-server mysql-devel
systemctl start mysqld
systemctl enable mysqld

修改密碼：

root@master-1 ~]#grep "temporary" /var/log/mysqld.log
2021-06-18T08:55:10.565308Z 1 [Note] A temporary password is generated for root@localhost: pFa)ajuqQ1J5
2021-06-18T08:55:12.785391Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
[root@master-1 ~]#mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
 Your MySQL connection id is 2
Server version: 5.7.33
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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> set GLOBAL validate_password_policy=LOW;
mysql> set password for 'root'@'localhost'=password('XXX');       
Query OK, 0 rows affected, 1 warning (0.00 sec)

建立相關目錄：

mkdir /var/log/mysql
chown -R mysql.mysql /var/log/mysql
mkdir -p /data/mysql
cp -fr /var/lib/mysql/* /data/mysql/
chown -R mysql.mysql /data/mysql/

修改配置檔案/etc/my.cnf：

master-1的server-id為1，master-2的server-id為2，其餘配置不變。

[client] port = 3306
socket = /tmp/mysql.sock
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
log-bin = mysql-bin
binlog_format = ROW
log_slave_updates = 1
expire_logs_days = 7
max_binlog_size = 100M

server-id = 1 

log_timestamps = SYSTEM
log_error=/var/log/mysql/mysql.err
character-set-server=utf8
port = 3306
socket = /tmp/mysql.sock
datadir = /data/mysql/
default-storage-engine=INNODB
max_connections=200
validate_password_policy=LOW
pid-file=/var/run/mysqld/mysqld.pid

重啟：

systemctl restart mysqld

配置主主

建立主從複製賬號

# master-1
GRANT REPLICATION SLAVE ON *.*  TO  'rep'@'192.168.10.22'  identified by  'Rep@rep123';


# master-2
GRANT REPLICATION SLAVE ON *.*  TO  'rep'@'192.168.10.21'  identified by  'Rep@rep123';

啟動主主

master-1查詢master狀態：

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      599 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

master-2啟動主從複製：

# 啟動主從複製
change master to master_host='192.168.10.21',master_user='rep',master_password='Rep@rep123',master_log_file='mysql-bin.000002',master_log_pos=599;
start slave;

# 檢視slave是否正常啟動
show slave status\G;

master-2檢視狀態：

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      599 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

master-1啟動主從複製：

# 啟動主從複製
change master to master_host='192.168.10.22',master_user='rep',master_password='Rep@rep123',master_log_file='mysql-bin.000002',master_log_pos=599;
start slave;

# 檢視slave是否正常啟動
show slave status\G;

配置keepalived

安裝keepalived

master-1和master-2安裝keepalived：

yum install keepalived -y

配置

master-1的/etc/keepalived/keepalived.conf：

! Configuration File for keepalived

global_defs {
   router_id DB-HA
}

vrrp_script chk_mysql_port {    
    script "/scripts/check_mysql.sh"   
    interval 2                   
    weight -5                   
    fall 2                    
    rise 1                    
}

vrrp_instance VI_1 {
    state MASTER
    interface eth0
    virtual_router_id 110
    mcast_src_ip 192.168.10.21
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.10.20
    }
    track_script {               
        chk_mysql_port             
}
}

master-2的/etc/keepalived/keepalived.conf：

! Configuration File for keepalived

global_defs {
   router_id DB-HA
}

vrrp_script chk_mysql_port {    
    script "/scripts/check_mysql.sh"   
    interval 2                   
    weight -5                   
    fall 2                    
    rise 1                    
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 110
    mcast_src_ip 192.168.10.22
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.10.20
    }
    track_script {               
        chk_mysql_port             
}
}

檢查mysql狀態的指令碼/scripts/check_mysql.sh：

#!/bin/bash
counter=$(netstat -lntp|grep 3306|wc -l)
if [ "${counter}" -eq 0 ]; then
    systemctl stop keepalived
fi

授權：

chmod +x /scripts/check_mysql.sh

兩臺主機都啟動keepalived：

systemctl start keepalived

此時，可以通過ip a檢視vip在192.168.10.21的網路卡上：

[root@master-1 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP qlen 1000
    link/ether bc:95:e1:s9:2G:3g brd ff:ff:ff:ff:ff:ff
    inet 192.168.10.12/21 brd 192.168.10.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet 192.168.10.20/32 scope global eth0
       valid_lft forever preferred_lft forever
    inet6 fe80::9714:an6b:43g3:c3i5/64 scope link
       valid_lft forever preferred_lft forever

相關文章