生産環境中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 |
+----------+

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