企業(yè) - MySQL主從備份

發(fā)布時(shí)間：2020-07-09 02:34:57 來(lái)源：網(wǎng)絡(luò) 閱讀：537 作者：huanzi2017 欄目：MySQL數(shù)據(jù)庫(kù)

一、mysql主從備份原理

一、雙機(jī)熱備的概念簡(jiǎn)單說一下，就是要保持兩個(gè)數(shù)據(jù)庫(kù)的狀態(tài)自動(dòng)同步。對(duì)任何一個(gè)數(shù)據(jù)庫(kù)的操作都自動(dòng)應(yīng)用到另外一個(gè)數(shù)據(jù)庫(kù)，始終保持兩個(gè)數(shù)據(jù)庫(kù)中的數(shù)據(jù)一致。這樣做有如下幾點(diǎn)好處：

1. 可以做災(zāi)備，其中一個(gè)壞了可以切換到另一個(gè)。

2. 可以做負(fù)載均衡，可以將請(qǐng)求分?jǐn)偟狡渲腥魏我慌_(tái)上，提高網(wǎng)站吞吐量。對(duì)于異地?zé)醾?，尤其適合災(zāi)備。

二、mysql 主從備份工作原理

簡(jiǎn)單的說就是把一個(gè)服務(wù)器上執(zhí)行過的sql語(yǔ)句在別的服務(wù)器上也重復(fù)執(zhí)行一遍，這樣只要兩個(gè)數(shù)據(jù)庫(kù)的初態(tài)是一樣的，那么它們就能一直同步。

二、實(shí)現(xiàn)方式

MYSQL主從同步是在MySQL主從復(fù)制（Master-Slave Replication）基礎(chǔ)上實(shí)現(xiàn)的，通過設(shè)置在Master上的binlog，使其處于打開狀態(tài)；Slave通過一個(gè)I/O線程從Master上讀取binlog，然后傳輸?shù)?/span>Slave的中繼日志中，然后使用SQL線程讀取中繼日志，并應(yīng)用到自身數(shù)據(jù)庫(kù)中，從而實(shí)現(xiàn)主從數(shù)據(jù)同步功能。

有兩個(gè)服務(wù)器，演示了從一個(gè)主服務(wù)器（master）把數(shù)據(jù)同步到從服務(wù)器（slave）的過程。

對(duì)于一個(gè)mysql服務(wù)器，一般有兩個(gè)線程來(lái)負(fù)責(zé)復(fù)制和被復(fù)制。當(dāng)開啟復(fù)制這個(gè)開關(guān)之后（start slave）

1. 作為主服務(wù)器Master，會(huì)把自己的每一次改動(dòng)都記錄到二進(jìn)制日志 Binarylog 中。（從服務(wù)器會(huì)負(fù)責(zé)來(lái)讀取這個(gè)log，然后在自己那里再執(zhí)行一遍。）

2. 作為從服務(wù)器Slave，會(huì)用master上的賬號(hào)登陸到master上，去讀取master的Binarylog, 然后寫入到自己的中繼日志Relaylog，然后自己的sql線程會(huì)負(fù)責(zé)讀取這個(gè)中繼日志，并執(zhí)行一遍。到這里主服務(wù)器上的更改就同步到從服務(wù)器上了。

在mysql上可以查看當(dāng)前服務(wù)器的主，從狀態(tài)。其實(shí)就是當(dāng)前服務(wù)器的 Binary（作為主服務(wù)器角色）狀態(tài)和位置。以及其RelayLog（作為從服務(wù)器）的復(fù)制進(jìn)度。

三、復(fù)制的過程

該過程的第一部分就是master記錄二進(jìn)制日志。在每個(gè)事務(wù)更新數(shù)據(jù)完成之前，master在二日志記錄這些改變。MySQL將事務(wù)串行的寫入二進(jìn)制日志，即使事務(wù)中的語(yǔ)句都是交叉執(zhí)行的。在事件寫入二進(jìn)制日志完成后，master通知存儲(chǔ)引擎提交事務(wù)。下一步就是slave將master的binary log拷貝到它自己的中繼日志。首先，slave開始一個(gè)工作線程——I/O線程。I/O線程在master上打開一個(gè)普通的連接，然后開始binlog dump process。Binlog dump process從master的二進(jìn)制日志中讀取事件，如果已經(jīng)跟上master，它會(huì)睡眠并等待master產(chǎn)生新的事件。I/O線程將這些事件寫入中繼日志。 SQL slave thread（SQL從線程）處理該過程的最后一步。SQL線程從中繼日志讀取事件，并重放其中的事件而更新slave的數(shù)據(jù)，使其與master中的數(shù) 據(jù)一致。只要該線程與I/O線程保持一致，中繼日志通常會(huì)位于OS的緩存中，所以中繼日志的開銷很小。此外，在master中也有一個(gè)工作線程：和其它MySQL的連接一樣，slave在master中打開一個(gè)連接也會(huì)使得master開始一個(gè)線程。復(fù)制過程有一個(gè)很重要的限制——復(fù)制在slave上是串行化的，也就是說master上的并行更新操作不能在slave上并行操作。

實(shí)驗(yàn)

實(shí)驗(yàn)環(huán)境

server2 master

server3 slave

master上下載包

mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar

安裝數(shù)據(jù)庫(kù)

[root@server2~]# yum install -y mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm

[root@server3 ~]# yum install -y *

修改mysql的配置文件

[root@server2 ~]# vim /etc/my.cnf

server-id = n

給服務(wù)器分配一個(gè)唯一的ID編號(hào)

log-bin [= filename]

把對(duì)數(shù)據(jù)進(jìn)行修改的所有SQL命令(也就是INSERT、UPDATE和DELETE命令)以二進(jìn)制格式記入日志(二進(jìn)制變更日志，binary update log)。這種日志的文件名是filename.n或默認(rèn)的hostname.n，其中n是一個(gè)6位數(shù)字的整數(shù)(日志文件按順序編號(hào))。

開啟服務(wù)

修改slave配置文件

server-id = n

給服務(wù)器分配一個(gè)唯一的ID編號(hào)

[root@server3 ~]# vim /etc/my.cnf

開啟服務(wù)

企業(yè) - MySQL主從備份

查看密碼

安全配置向?qū)?/span>

如下方法修改slave密碼

mysql> alter user root@localhost identified by 'LH=redhat123';

master上進(jìn)行授權(quán)

mysql> grant replication slave on *.* to cara@'192.168.122.13' identified by 'LH@redhat123' ；用戶授權(quán)

mysql> flush privileges; 刷新

master授權(quán)后，slave可以遠(yuǎn)程登錄

master端查看

使 slave 與 master 建立連接，從而同步：
mysql> change master to master_host='192.168.122.12',master_user='cara',master_password='LH=redhat123',master_log_file='mysql-bin.000003',master_log_pos=1706;

slave端 mysql -p 登錄

查看

[root@server3 mysql]# pwd
/var/lib/mysql
[root@server3 mysql]# cat master.info

[root@server3 mysql]# cat server3-relay-bin.index

mysql> show slave status\G; 查看slave狀態(tài)

mysql> start slave; 開啟slave

創(chuàng)建庫(kù)westos,創(chuàng)建表usertb

在表中插入數(shù)據(jù)

更改密碼

刪除表中數(shù)據(jù)

[root@server2 mysql]# mysqlbinlog mysql-bin.000003 可查看master所做的操作

在slave上也可查看master上數(shù)據(jù)

深入了解復(fù)制-全局事務(wù)標(biāo)識(shí)符（GTID）

1）什么是GTID

GTID(Global Transaction ID)是對(duì)于一個(gè)已提交事務(wù)的編號(hào)，并且是一個(gè)全局唯一的編號(hào)。GTID實(shí)際上是由UUID+TID組成的。其中UUID是一個(gè)MySQL實(shí)例的唯一標(biāo) 識(shí)，保存在mysql數(shù)據(jù)目錄下的auto.cnf文件里。TID代表了該實(shí)例上已經(jīng)提交的事務(wù)數(shù)量，并且隨著事務(wù)提交單調(diào)遞增。下面是一個(gè)GTID的具體形式：3E11FA47-71CA-11E1-9E33-C80AA9429562:23。