GreatSQL社區原創內容未經授權不得隨意使用，轉載請聯繫小編並註明來源。 GreatSQL是MySQL的國產分支版本，使用上與MySQL一致。 作者： 楊延昭 文章來源：GreatSQL社區投稿 在進行資料庫備份的時候主要分為了邏輯備份和物理備份這兩種方式。在數據遷移和備份恢復中使用mysqld ...
- GreatSQL社區原創內容未經授權不得隨意使用，轉載請聯繫小編並註明來源。
- GreatSQL是MySQL的國產分支版本，使用上與MySQL一致。
- 作者： 楊延昭
- 文章來源：GreatSQL社區投稿
在進行資料庫備份的時候主要分為了邏輯備份和物理備份這兩種方式。在數據遷移和備份恢復中使用mysqldump將數據生成sql進行保存是最常用的方式之一。
本文將圍繞著mysqldump的使用，工作原理，以及對於InnoDB和MyISAM兩種不同引擎如何實現數據一致性這三個方面進行介紹。
一.mysqldump 簡介
mysqldump是MySQL自帶的邏輯備份工具。
它的備份原理是通過協議連接到 MySQL資料庫，將需要備份的數據查詢出來，
將查詢出的數據轉換成對應的insert語句，當我們需要還原這些數據時，
只要執行這些insert語句，即可將對應的數據還原。
二.備份的命令
2.1命令的格式
1.mysqldump [選項] 資料庫名 [表名] > 腳本名
2.mysqldump [選項] --資料庫名 [選項 表名] > 腳本名
3.mysqldump [選項] --all-databases [選項] > 腳本名
2.2選項說明
| 參數名 | 縮寫 | 含義 |
|---|---|---|
| --host | -h | 伺服器IP地址 |
| --port | -P | 伺服器埠號 |
| --user | -u | MySQL 用戶名 |
| --pasword | -p | MySQL 密碼 |
| --databases | 指定要備份的資料庫 | |
| --all-databases | 備份mysql伺服器上的所有資料庫 | |
| --compact | 壓縮模式，產生更少的輸出 | |
| --comments | 添加註釋信息 | |
| --complete-insert | 輸出完成的插入語句 | |
| --lock-tables | 備份前，鎖定所有資料庫表 | |
| --no-create-db/--no-create-info | 禁止生成創建資料庫語句 | |
| --force | 當出現錯誤時仍然繼續備份操作 | |
| --default-character-set | 指定預設字元集 | |
| --add-locks | 備份資料庫表時鎖定資料庫表 |
三.還原的命令
3.1系統行命令
mysqladmin -uroot -p create db_name
mysql -uroot -p db_name < /backup/mysqldump/db_name.db
註：在導入備份資料庫前，db_name如果沒有，是需要創建的； 而且與db_name.db中資料庫名是一樣的才可以導入。
3.2source方式
mysql > use db_name;
mysql > source /backup/mysqldump/db_name.db;
四.mysqldump實現的原理
4.1備份流程如下
1.調用FWRL（flush tables with read lock）,全局禁止讀寫
2.開啟快照讀，獲取此期間的快照（僅僅對innodb起作用）
3.備份非innodb表數據（*.frm,*.myi,*.myd等）
4.非innodb表備份完畢之後，釋放FTWRL
5.逐一備份innodb表數據
6.備份完成
4.2執行mysqldump，分析備份日誌
# 執行語句
[root@localhost backup]# mysqldump -uroot -proot -h127.0.0.1 --all-databases --single-transaction --routines --events --triggers --master-data=2 --hex-blob --default-character-set=utf8mb4 --flush-logs --quick > all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# tail -f /var/lib/mysql/localhost.log
第一步：
FLUSH /*!40101 LOCAL */ TABLES
# 這裡是刷新表
第二步：
FLUSH TABLES WITH READ LOCK
# 因為開啟了--master-data=2，這時就需要flush tables with read lock鎖住全庫，
記錄當時的master_log_file和master_log_pos點
這裡有一個疑問？
執行flush tables操作，並加一個全局讀鎖，那麼以上兩個命令貌似是重覆的，
為什麼不在第一次執行flush tables操作的時候加上鎖呢？
簡而言之，是為了避免較長的事務操作造成FLUSH TABLES WITH READ LOCK操作遲遲得不到
鎖，但同時又阻塞了其它客戶端操作。
第三步：
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
# --single-transaction參數的作用，設置事務的隔離級別為可重覆讀，
即REPEATABLE READ，這樣能保證在一個事務中所有相同的查詢讀取到同樣的數據，
也就大概保證了在dump期間，如果其他innodb引擎的線程修改了表的數據並提交，
對該dump線程的數據並無影響，然而這個還不夠，還需要看下一條
第四步：
START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
# 獲取當前資料庫的快照，這個是由mysqldump中--single-transaction決定的。
# WITH CONSISTENT SNAPSHOT能夠保證在事務開啟的時候，第一次查詢的結果就是
事務開始時的數據A，即使這時其他線程將其數據修改為B，查的結果依然是A。簡而言之，就是開啟事務並對所有表執行了一次SELECT操作，這樣可保證備份時，
在任意時間點執行select * from table得到的數據和
執行START TRANSACTION WITH CONSISTENT SNAPSHOT時的數據一致。
【註意】，WITH CONSISTENT SNAPSHOT只在RR隔離級別下有效。
第五步：
SHOW MASTER STATUS
# 這個是由--master-data決定的，記錄了開始備份時，binlog的狀態信息，
包括MASTER_LOG_FILE和MASTER_LOG_POS
這裡需要特別區分一下master-data和dump-slave
master-data:
--master-data=2表示在dump過程中記錄主庫的binlog和pos點，併在dump文件中註釋掉這一行；
--master-data=1表示在dump過程中記錄主庫的binlog和pos點，併在dump文件中不註釋掉這一行，即恢復時會執行；
dump-slave
--dump-slave=2表示在dump過程中，在從庫dump，mysqldump進程也要在從庫執行，
記錄當時主庫的binlog和pos點，併在dump文件中註釋掉這一行；
--dump-slave=1表示在dump過程中，在從庫dump，mysqldump進程也要在從庫執行，
記錄當時主庫的binlog和pos點，併在dump文件中不註釋掉這一行；
第六步：
UNLOCK TABLES
# 釋放鎖。
五.mysqldump對InnoDB和MyISAM兩種存儲引擎進行備份的差異。
5.1對於支持事務的引擎如InnoDB，參數上是在備份的時候加上 –single-transaction 保證數據一致性
–single-transaction 實際上通過做了下麵兩個操作 ：
① 在開始的時候把該 session 的事務隔離級別設置成 repeatable read ；
② 然後啟動一個事務（執行 begin ），備份結束的時候結束該事務（執行 commit ）
有了這兩個操作，在備份過程中，該 session 讀到的數據都是啟動備份時的數據（同一個點）。可以理解為對於 InnoDB 引擎來說加了該參數，備份開始時就已經把要備份的數據定下來了，
備份過程中的提交的事務時是看不到的，也不會備份進去。
5.2對於不支持事務的引擎如MyISAM，只能通過鎖表來保證數據一致性，這裡分兩種情況：
1）導出全庫：加 –lock-all-tables 參數，這會在備份開始的時候啟動一個全局讀鎖
（執行 flush tables with read lock），其他 session 可以讀取但不能更新數據，
備份過程中數據沒有變化，所以最終得到的數據肯定是完全一致的；
2）導出單個庫：加 –lock-tables 參數，這會在備份開始的時候鎖該庫的所有表，
其他 session 可以讀但不能更新該庫的所有表，該庫的數據一致；
Enjoy GreatSQL
