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

目錄

• 一、清除原從資料庫資料及主從關係
  • 1.1、關閉主從資料庫原有的主從關係
  • 1.2、清除從資料庫原有資料
• 二、將主庫上的資料備份到從庫
  • 2.1、備份主庫資料到從庫
  • 2.2、在從庫使用tsc.sql檔案恢復主庫資料
• 三、建立主主關係
  • 3.1、修改資料庫配置檔案並重啟生效
  • 3.2、建立資料庫主主關係
    • 以mysql-1為主、mysql2為從建立主從關係
    • 以mysql-2為主、mysql-1為從建立主從關係
  • 3.3、主主關係建立失敗回退方案
• 四、開啟keepalived服務，為主主資料庫建立VIP
  • 4.1、配置mysql-1 keepalived服務的配置檔案
  • 4.2、配置mysql-2的 keepalived服務的配置檔案
  • 4.3、分別在mysql-1、mysql-2上啟動keepalived服務
  • 4.4、檢查mysql-1、mysql-2的VIP使用情況
  • 4.5、切換客戶端訪問資料庫的IP地址為VIP
• 五、資料庫主從架構變更為主主架構的結果驗證
  • 5.1、高可用特性驗證
  • 5.2、一致性特徵驗證
  • 5.3、資料完整性驗證
• 六、資料庫崩潰恢復方案
  • 6.1、資料恢復前置條件
  • 6.2、資料庫恢復操作
  • 6.3驗證資料庫恢復後的可用性

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

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 |
+----------+

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