生產環境中mysql資料庫由主從關係切換為主主關係

2021 年 4 月 8 日
筆記
MySQL, 原創文章

一、清除原從資料庫數據及主從關係
- 1.1、關閉主從資料庫原有的主從關係
- 1.2、清除從資料庫原有數據
二、將主庫上的數據備份到從庫
- 2.1、備份主庫數據到從庫
- 2.2、在從庫使用tsc.sql文件恢復主庫數據
三、建立主主關係
四、開啟keepalived服務，為主主資料庫創建VIP
五、資料庫主從架構變更為主主架構的結果驗證
六、資料庫崩潰恢復方案

一、清除原從資料庫數據及主從關係

1.1、關閉主從資料庫原有的主從關係

從庫停止salve

mysql> stop salve;

查看主從關係連接狀態，確保IO執行緒和SQL執行緒停止運行

mysql> show slave status\G;
......
             Slave_IO_Running: NO
            Slave_SQL_Running: NO
......

1.2、清除從資料庫原有數據

從庫刪除原有tsc資料庫

mysql> drop database tsc;

從庫重新創建tsc資料庫

mysql> create database tsc;

二、將主庫上的數據備份到從庫

2.1、備份主庫數據到從庫

從庫備份主庫數據到本地，該過程主庫不會鎖表。

[root@mysql-2 ~]# mysqldump -umaster -pSenseTime#2020 -h 192.168.116.128 --single-transaction tsc > /root/tsc.sql;

查詢備份到從庫本地tsc.sql文件的大小,保證數據量充足。

[root@mysql-2 ~]# ll /root/tsc.sql -h

2.2、在從庫使用tsc.sql文件恢復主庫數據

查看是否存在tsc資料庫

mysql> show databases;

進入tsc資料庫

mysql> use tsc;

在從資料庫上開始備份數據

mysql> source /root/tsc.sql;

備份完成後，檢查從庫tsc庫中的數據表數量

mysql> show tables;

檢查各數據表中的數據條數

mysql> select count(*) from tb_fever_treatment;
+----------+
| count(*) |
+----------+
|    50056 |
+----------+
1 row in set (0.01 sec)

三、建立主主關係

3.1、修改資料庫配置文件並重啟生效

修改主庫mysql配置文件

[root@mysql-1 ~]# vi /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

log_bin=master-bin
binlog_format=MIXED
server_id=1
gtid-mode=on
enforce-gtid-consistency
skip-name-resolve
skip-host-cache
#在原有配置上增加以下內容
auto-increment-offset=2
auto-increment-increment=2
relay_log=mysql-relay-bin
log-slave-updates=on

修改從庫mysql配置文件

[root@mysql-2 ~]# vi /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

log_bin=master-bin
binlog_format=MIXED
server_id=2
gtid-mode=on
enforce-gtid-consistency
skip-name-resolve
skip-host-cache
#在原有配置上增加以下內容
auto-increment-offset=2
auto-increment-increment=2
relay_log=mysql-relay-bin
log-slave-updates=on

重啟主資料庫並驗證。

[root@mysql-1 ~]# systemctl restart mysqld
[root@mysql-1 ~]# systemctl status mysqld

重啟從資料庫並驗證。

[root@mysql-2 ~]# systemctl restart mysqld
[root@mysql-2 ~]# systemctl status mysqld

3.2、建立資料庫主主關係

以mysql-1為主、mysql2為從建立主從關係

查看mysql-1的master狀態，記錄下file和position的值

mysql> show master status\G;
*************************** 1. row ***************************
             File: master-bin.000522
         Position: 438
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 37f084e8-9161-11eb-abf6-000c29f882cb:12-13,
87366c38-9164-11eb-988a-000c293c71e0:1-14,
87366c38-9164-11eb-988a-000c293c71e1:1-10
1 row in set (0.00 sec)

在mysql-2上建立主從關係

mysql> change master to master_host='192.168.116.128',master_port=3306,master_user='master',master_password='SenseTime#2020',master_log_file='master-bin.000522',master_log_pos=438;

在mysql-2上打開主從關係

mysql> start slave;

在mysql-2上查看主從關係，當IO執行緒和SQL執行緒都在運行時則主從建立成功

mysql> show slave status\G;
......
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
......

以mysql-2為主、mysql-1為從建立主從關係

查看mysql-2的master狀態，記錄下file和position的值

mysql> show master status\G;
*************************** 1. row ***************************
             File: master-bin.002054
         Position: 1948
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 37f084e8-9161-11eb-abf6-000c29f882cb:12-13,
87366c38-9164-11eb-988a-000c293c71e0:1-13,
87366c38-9164-11eb-988a-000c293c71e1:1-10
1 row in set (0.00 sec)

在mysql-1上建立主從關係

mysql> change master to 
master_host='192.168.116.129',master_port=3306,master_user='master',master_password='SenseTime#2020',master_log_file='master-bin.002054',master_log_pos=1948;

在mysql-1上打開主從關係

mysql> start slave;

在mysql-1上查看主從關係，當IO執行緒和SQL執行緒都在運行時則主從建立成功

mysql> show slave status\G;

3.3、主主關係建立失敗回退方案

若主主關係建立失敗，則撤銷主主關係，mysql-1對外提供服務不受影響，user用戶無感知。

在mysql-1上運行:

mysql> stop slave;

在mysql-2上運行:

mysql> stop slave;

四、開啟keepalived服務，為主主資料庫創建VIP

4.1、配置mysql-1 keepalived服務的配置文件

[root@mysql-1 ~]# vi /etc/keepalived/keepalived.conf 

! configuration File for keepalived

global_defs {
    router_id master
}


vrrp_script check_mysql {
    script "/etc/keepalived/check_mysql_second.sh"
    interval 2
}

vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 51
    priority 150
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.116.150
    }
    track_script {
        check_mysql
    }
}

配置mysql-1的mysqld健康狀態腳本並賦予許可權

[root@mysql-1 ~]# vi /etc/keepalived/check_mysql_second.sh
#!/bin/bash
counter=$(ss -antlp | grep 3306 | wc -l)
if [ "${counter}" -eq 0 ]; then
    /usr/bin/systemctl stop keepalived.service
fi

[root@mysql-1 ~]# chmod 755 /etc/keepalived/check_mysql_second.sh

4.2、配置mysql-2的 keepalived服務的配置文件

[root@mysql-2 ~]# vi /etc/keepalived/keepalived.conf

! configuration File for keepalived

global_defs {
    router_id master
}


vrrp_script check_mysql {
    script "/etc/keepalived/check_mysql_second.sh"
    interval 2
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 50
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.116.150
    }
    track_script {
        check_mysql
    }
}

配置mysql-2的mysqld健康狀態腳本並賦予許可權

[root@mysql-2 ~]# vi /etc/keepalived/check_mysql.sh
#!/bin/bash
counter=$(ss -antlp | grep 3306 | wc -l)
if [ "${counter}" -eq 0 ]; then
    /usr/bin/systemctl stop keepalived.service
fi

[root@mysql-1 ~]# chmod 755 /etc/keepalived/check_mysql_second.sh

4.3、分別在mysql-1、mysql-2上啟動keepalived服務

在mysql-1上啟動keepalived服務並檢查狀態

[root@mysql-1 ~]# systemctl start keepalived
[root@mysql-1 ~]# systemctl status keepalived

在mysql-2上啟動keepalived服務並檢查狀態

[root@mysql-2 ~]# systemctl start keepalived
[root@mysql-2 ~]# systemctl status keepalived

4.4、檢查mysql-1、mysql-2的VIP使用情況

正常情況下VIP要求落在mysql-1上，當mysql-1的mysql退出後，VIP落到mysql-2上，即使mysql-1恢復後也補搶佔VIP。

檢查mysql-1的VIP使用情況

[root@mysql-1 ~]# ip address

檢查mysql-2的VIP使用情況

[root@mysql-2 ~]# ip address

4.5、切換客戶端訪問資料庫的IP地址為VIP

需改需要訪問mysql程式的配置文件，訪問地址由mysql-1的IP（本例中為192.168.116.128）改為VIP（本例中為192.168.116.150）。

五、資料庫主從架構變更為主主架構的結果驗證

5.1、高可用特性驗證

數據主主架構的高可用，是指當有client連接mysql集群時，一台mysql主機停服後，另一台mysql能夠無縫切換對外提供服務，client側無感知或感知不強烈。

模擬mysql client用戶連接mysql，創建伺服器虛擬機（主機名為）user。

在user上訪問資料庫的VIP，並使用mysql服務。

[root@user ~]# mysql -umaster -pSenseTime#2020 -h 192.168.116.150
mysql> select UUID();
+--------------------------------------+
| UUID()                               |
+--------------------------------------+
| 8aa2591c-94ef-11eb-93de-000c29309533 |
+--------------------------------------+
1 row in set (0.01 sec)

在mysql-1上停止mysqld服務,並查看IP，發現mysql-1上已經沒有VIP

[root@mysql-1 ~]# systemctl stop mysqld
[root@mysql-1 ~]# ip address

此時mysql-1上的mysqld已經停止運行，但在user伺服器上仍然能夠正常訪問mysql服務，查看UUID，發現UUID已經變化，說明keepalived實現了VIP在mysql-1和mysql-2上的無縫切換。

mysql> select UUID();
+--------------------------------------+
| UUID()                               |
+--------------------------------------+
| f78cfe1b-94ef-11eb-9622-000c293c71e0 |
+--------------------------------------+
1 row in set (0.02 sec)

恢復mysql-1上的mysqld服務後，發現VIP仍然在mysql-2上，實現了VIP的非搶佔特性。

5.2、一致性特徵驗證

一致性特徵是指，mysql client在一個資料庫上進行數據操作後，其他資料庫上能夠同步發生相應的數據變化。

在mysql-1上，對資料庫進行數據插入操作。

mysql> INSERT INTO tb_fever_treatment(`id`,`temp`,`device_id`) VALUES(123293,35.3,'123213213213');
Query OK, 1 row affected (0.00 sec)

在mysql-2上，能夠查詢到對應的數據。

mysql> select id,temp,device_id from tb_fever_treatment where id=123293;
+--------+------+--------------+
| id     | temp | device_id    |
+--------+------+--------------+
| 123293 | 35.3 | 123213213213 |
+--------+------+--------------+
1 row in set (0.00 sec)

5.3、數據完整性驗證

數據完整性是指在mysql主主建立的過程中，mysql-2是否能夠百分百的同步到mysql-1上的數據。

在mysql-1上查詢表中的行數：

mysql> use tsc;select count(*) from tb_fever_treatment;
Database changed
+----------+
| count(*) |
+----------+
|    50056 |
+----------+
1 row in set (0.04 sec)

在mysql-2上查詢表中的行數:

mysql> use tsc;select count(*) from tb_fever_treatment;
Database changed
+----------+
| count(*) |
+----------+
|    50053 |
+----------+
1 row in set (0.03 sec)

發現mysql-2上的數據比mysql-1上的少，即mysql-2上的數據和mysql-1上的數據同步不完整。

由於在生產環境上進行主主改造，需要盡量保證mysql-1服務的連續性，故在2.1、備份主庫數據到從庫章節中，備份主庫數據時，加入了「–single-transaction」選項，即備份過程不鎖表，此時當有數據在主庫寫入時，無法備份到文件中去，因此在從庫使用備份文件同步主庫數據時，存在部分數據丟失。

六、資料庫崩潰恢復方案

生產環境對資料庫操作存在一定風險，儘管本文已經對操作步驟進行了一定的規範和操作示例，但為了最大程度保證數據安全，需要預備一套資料庫崩潰後的數據恢復方案。

6.1、數據恢復前置條件

mysqld服務可以正常啟動
資料庫備份文件完整，假設備份文件路徑為/root/mysqlDump/databaseDump.sql。

6.2、資料庫恢復操作

mysql安裝並正常啟動後（次試資料庫沒有數據），根據原有數據文件對資料庫進行恢復。

創建資料庫:

mysql> create database tsc;

對資料庫進行備份:

mysql> use tsc;
mysql> source /root/mysqlDump/databaseDump.sql;

檢查數據條數，出現如下結果說明數據恢復成功。

mysql> use tsc;select count(*) from tb_fever_treatment;
Database changed
+----------+
| count(*) |
+----------+
|    50053 |
+----------+
1 row in set (0.03 sec)

6.3驗證資料庫恢復後的可用性

在需要使用mysql服務的客戶端上（本文為user伺服器），連接mysql服務並查詢。

[root@user ~]# mysql -umaster -pSenseTime#2020 -h192.168.116.150 --execute='use tsc;select count(*) from tb_fever_treatment';
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+
| count(*) |
+----------+
|    50053 |
+----------+

正常顯示數據條數，說明客戶端可以正常查詢資料庫，資料庫恢復成功。

Tags: MySQL 原創文章