?????? mysql數(shù)據(jù)備份從備份方式上可分為物理備份和邏輯備份，物理備份有mysql企業(yè)版自帶工具，或者選擇percona的開(kāi)源備份工具percona-xtra-backup，可以簡(jiǎn)稱(chēng)pxb。

?????? pxb主要對(duì)innodb引擎的表進(jìn)行備份，同時(shí)也會(huì)全備份myisam表數(shù)據(jù)，原理大概是這樣：開(kāi)啟讀取redo日志的線程，記錄備份期間數(shù)據(jù)的變化情況，同時(shí)記錄下此刻的lsn，然后以page為單位掃描出所有數(shù)據(jù)文件中page塊中l(wèi)sn號(hào)小于等于之前記錄的lsn號(hào)的page，即找出了所有在備份期間沒(méi)有修改的page塊，同時(shí)加上讀取的redo日志內(nèi)容，那么在數(shù)據(jù)恢復(fù)的時(shí)候，通過(guò)備份文件加上日志文件即可恢復(fù)出完整的innodb引擎數(shù)據(jù)。

?????? 本篇主要說(shuō)mysqldump，重新認(rèn)識(shí)下mysqldump這個(gè)常用的工具。mysqldump是mysql自帶的邏輯備份工具，多用于數(shù)據(jù)量較小的庫(kù)做整庫(kù)備份，把數(shù)據(jù)結(jié)構(gòu)和內(nèi)容以語(yǔ)句的形式備份成便于執(zhí)行的sql文件，數(shù)據(jù)遷移相對(duì)簡(jiǎn)單，數(shù)據(jù)量小的時(shí)候也非?？旖荨＃〝?shù)據(jù)量在百G以?xún)?nèi)可以使用）

??????? 這里不多介紹具體的命令，我們重點(diǎn)關(guān)注mysqldump如何在數(shù)據(jù)導(dǎo)出過(guò)程中保持某個(gè)時(shí)點(diǎn)的數(shù)據(jù)一致性，也就是說(shuō)在導(dǎo)出所有數(shù)據(jù)，均是這一時(shí)點(diǎn)的數(shù)據(jù)，不會(huì)因?yàn)楸碓趯?dǎo)出過(guò)程中，其他dml操作對(duì)導(dǎo)出數(shù)據(jù)造成影響。

?????? 直接給出生產(chǎn)中使用備份命令：

mysqldump -ubacker -p --default-character-set=utf8? --single-transaction --flush-logs --master-dat

a=2 --databases test1 test2 test3 | gzip > $backupdir/full_iTOU_$time.sql.gz

重點(diǎn)關(guān)注 --single-transaction --flush-logs --master-data=2 這三個(gè)選項(xiàng)

官方手冊(cè)中對(duì)single-transaction

--single-transaction

This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.

這個(gè)選項(xiàng)做了兩件事情，1是設(shè)置了事務(wù)的隔離級(jí)別為可重復(fù)讀，2是啟動(dòng)了事務(wù)；起到的作用就是：對(duì)于innoDB，保證了導(dǎo)出數(shù)據(jù)的一致性，并且不會(huì)阻塞應(yīng)用系統(tǒng)。

When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state.

這個(gè)選項(xiàng)能夠保證導(dǎo)出InnoDB引擎的表是一致性的狀態(tài)，其他引擎MyISAM或者M(jìn)EMORY表的導(dǎo)出數(shù)據(jù)仍然可能不是一致性狀態(tài)的。

While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqlpump to retrieve the table contents to obtain incorrect contents or fail.

在導(dǎo)出數(shù)據(jù)的過(guò)程中，需要保證dump文件的有效性；因?yàn)锳LTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE 這些DDL語(yǔ)句會(huì)改變表結(jié)構(gòu)，一致性讀并不會(huì)對(duì)隔離這些語(yǔ)句，那么這些語(yǔ)句會(huì)導(dǎo)致導(dǎo)出數(shù)據(jù)不正確或者失敗。

那我們使用--single-transaction，并開(kāi)啟general日志，看看具體都做了什么操作。

tips：在研究mysql后臺(tái)操作時(shí)，可以把詳細(xì)日志開(kāi)啟，以便獲取更細(xì)致的信息。

Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

設(shè)置隔離級(jí)別為可重復(fù)讀

Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */

開(kāi)啟事物并且設(shè)置一致性快照，在這個(gè)transaction中所有的查詢(xún)均能得到同一版本的數(shù)據(jù)，通過(guò)MVCC來(lái)實(shí)現(xiàn)。

注意：

（1）此刻打開(kāi)其他session對(duì)表做DML操作：例如，表中有100萬(wàn)行數(shù)據(jù)，delete這個(gè)表的所有數(shù)據(jù)，然后再插入100萬(wàn)行數(shù)據(jù)，正常情況下表空間大小應(yīng)該和原空間大小差不多；但這個(gè)時(shí)候的因?yàn)椴迦?00萬(wàn)數(shù)據(jù)，會(huì)增加幾乎一倍，也就是說(shuō)實(shí)際上物理上100萬(wàn)行數(shù)據(jù)因?yàn)橛衧ession需要，并未真正的在磁盤(pán)中刪除。

也就是通過(guò)mvcc的數(shù)據(jù)的快照版本會(huì)存放在自身的數(shù)據(jù)表空間里，在未真正失去作用之前，會(huì)一直保留。

另外與ORACLE數(shù)據(jù)庫(kù)做對(duì)比，已刪除數(shù)據(jù)的快照從UNDO表空間去獲取，如果UNDO表空間內(nèi)數(shù)據(jù)被覆蓋無(wú)法找到，則會(huì)報(bào)錯(cuò)到前端，ORA-01555快照過(guò)久

（2）此時(shí)并不會(huì)對(duì)表結(jié)構(gòu)做保護(hù)，如果對(duì)表做ddl操作，會(huì)導(dǎo)致導(dǎo)出報(bào)錯(cuò)。

mysqldump: Couldn't execute 'show create table `z_test`': Table 'test.z_test' doesn't exist (1146)

再看看--single-transaction --master-data=2

官方手冊(cè)中master-data 參數(shù)的解釋?zhuān)?/p>

Use this option to dump a master replication server to produce a dump file that can be used to set up another server as a slave of the master. It causes the dump output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and position) of the dumped server. These are the master server coordinates from which the slave should start replicating after you load the dump file into the slave.

If the option value is 2, the CHANGE MASTER TO statement is written as an SQL comment, and thus is informative only; it has no effect when the dump file is reloaded. If the option value is 1, the statement is not written as a comment and takes effect when the dump file is reloaded. If no option value is specified, the default value is 1.

This option requires the RELOAD privilege and the binary log must be enabled.

The --master-data option automatically turns off --lock-tables. It also turns on --lock-all-tables, unless --single-transaction also is specified, in which case, a global read lock is acquired only for a short time at the beginning of the dump (see the description for --single-transaction). In all cases, any action on logs happens at the exact moment of the dump.

It is also possible to set up a slave by dumping an existing slave of the master, using the --dump-slave option, which overrides --master-data and causes it to be ignored if both options are used.

使用這個(gè)選項(xiàng)備份出的文件可以做用來(lái)搭建slave，輸出的dump文件中有change master to的語(yǔ)句，包含二進(jìn)制日志文件的坐標(biāo)，用來(lái)告訴slave該從哪里開(kāi)始進(jìn)行日志的復(fù)制。

如果選項(xiàng)值是2，這條語(yǔ)句會(huì)被加注釋?zhuān)蝗绻x項(xiàng)值是1，則語(yǔ)句沒(méi)注釋?zhuān)€原的時(shí)候會(huì)被執(zhí)行；

使用此選項(xiàng)，會(huì)默認(rèn)關(guān)閉--lock-tables和--lock-all-tables選項(xiàng)。

為了確保備份出來(lái)的數(shù)據(jù)在某一個(gè)時(shí)點(diǎn)的一致性，可以通過(guò)兩種方式來(lái)實(shí)現(xiàn)：

（1）先對(duì)所有表加讀鎖，備份數(shù)據(jù)，等待備份數(shù)據(jù)結(jié)束后，解開(kāi)讀鎖。

主要通過(guò)flush table with read lock來(lái)實(shí)現(xiàn)，F(xiàn)TWRL三個(gè)步驟

1.上全局讀鎖(lock_global_read_lock)

2.清理表緩存(close_cached_tables)

3.上全局COMMIT鎖(make_global_read_lock_block_commit)

（2）先對(duì)所有表加讀鎖，設(shè)置隔離級(jí)別為可重復(fù)讀，開(kāi)啟事物，解鎖，備份數(shù)據(jù)。

因?yàn)殚_(kāi)啟了事務(wù)，查詢(xún)結(jié)果的一致性可以通過(guò)MVCC多版本并發(fā)控制來(lái)實(shí)現(xiàn)。

那么實(shí)際上持有讀鎖的時(shí)間會(huì)非常短，也就是通常 --single-transaction 加--master-data聯(lián)合使用的原因。

第一種方法會(huì)加MDL 元數(shù)據(jù)鎖，備份期間所有表的DML，DDL均無(wú)法執(zhí)行。

第二種方法通過(guò)MVCC來(lái)實(shí)現(xiàn)，備份期間可以對(duì)表執(zhí)行DDL操作，可能會(huì)導(dǎo)致整體數(shù)據(jù)結(jié)構(gòu)不一致。

值得注意的是 flush table 是個(gè)比較重的動(dòng)作，可能會(huì)引起問(wèn)題，例如：

SESSION a：一個(gè)大表的查詢(xún)正在執(zhí)行，

SESSION b:執(zhí)行flush table命令，需要等待查詢(xún)結(jié)束后做再表緩存的清理，此時(shí)等待狀態(tài)為（Waiting for table flush）

這時(shí)如果有第三個(gè)session查詢(xún)查詢(xún)此表，則同樣會(huì)被阻塞，等待狀態(tài)為（Waiting for table flush）