【mysqldump】mysqldump及備份恢復示例

mysqldump 邏輯備份

一、  mysqldump 介紹

1.  mysqldump 基本介紹

mysqldump 是基於 SQL 的邏輯備份方式，專門用於備份 mysql 資料庫。 它的備份原理是，通過協議連線到mysql 資料庫，將需要備份的資料查詢出來，將查詢出的資料轉換成對應的 SQL 語句，當我們需要還原這些資料時，只要執行這些 SQL 語句，即可將對應的資料還原。

2.  mysqldump 的優點

可以直接使用文字處理工具處理對應的備份資料，因為備份資料已經被mysqldump 轉換為了對應的 SQL 語句，所以，我們可以藉助檔案系統中的文字處理工具對備份資料進行直接處理。

mysqldump 對 innodb 儲存引擎支援熱備， innodb 支援事務，我們可以基於事務通過 mysqldump 對資料庫進行熱備。

二、  mysqldump 語法

# mysqldump -h 主機名 -u 使用者名稱 -p 密碼 資料庫名 > 備份檔案 .sql

也可以使用本地socket 連線

# mysqldump -Ssocketname -u 使用者名稱 -p 密碼 資料庫名 > 備份檔案 .sql

注： 資料庫名選項

-A, --all-database # 所有庫

db1       # 資料庫名

db1 tab1 tab2   #db1 庫的表 tab1,tab2

-B --database db1 db2 # 多個資料庫

其他常用引數說明：

 

三、  備份及恢復示例

思路清晰

step1: 插入資料“ 1 ”“ 2 ”

step2: 執行 mysqldump 備份

step3: 再次插入資料“ 3 ”“ 4 ”

step4: 通過 dump 備份執行恢復，只能恢復“ 1 ”“ 2 ”

step5: 通過 binlog 繼續恢復，可以恢復“ 3 ”“ 4 ”

1.  模擬資料準備

mysql> create database test_db;

mysql> create table test_tab(id int);

mysql> insert into test_tab values(1);

mysql> insert into test_tab values(2);

mysql> select * from test_tab;

+------+

| id   |

+------+

|    1 |

|    2 |

+------+

2 rows in set (0.00 sec)

2.  執行第一次備份

執行以下備份語句

# mysqldump -uroot -p'P@ssw0rd' \

-S/usr/local/mysql/data/mysql.sock \

--all-databases \

--single-transaction \

--master-data=1 \

--flush-logs > /mysqldump/`date +%F-%H`-mysql-all.sql

在備份完成後，在/mysqldump 資料夾中會生成一個 sql 結尾的檔案，是 mysqldump 命令產生的邏輯備份檔案。

需要注意的是，“ --flush-logs ”引數會截斷日誌，並且產生新的 binlog 日誌。“ --master-data=1 ”引數，會在生成的檔案中，加入一句話“ CHANGE MASTER TO MASTER_LOG_FILE='slave.000008', MASTER_LOG_POS=154; ”。這句話告訴我們這次 dump 備份，截止的日誌為“ slave.000007 ”， position 為“ 154 ”。產生了新的 binlog 日誌“ slave.000008 ”。在執行恢復的時候，通過產生的 dump 檔案恢復至“ slave.000007 ”日誌結束， position 為“ 154 ”的位置，剩下的資料需要通過 binlog “ slave.000008 ”進行恢復。

3.  再次插入資料

這次的插入資料是在mysqldump 備份後進行，意在模擬新的資料通過 binlog 恢復。

mysql> use test_db;

mysql> insert into test_tab values( 3 );

mysql> insert into test_tab values( 4 );

mysql> select * from test_tab;

------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

+------+

4 rows in set (0.00 sec)

4.  執行恢復

在進行恢復的時候，思路一定要清晰。由於我們dump 備份的臨界值為“ slave.000007 ”，那我們想要進行完全恢復，就需要保留“ slave.000008 ”。此時，我們通過 dump 檔案將資料庫恢復至“ slave.000007 ”，剩下的資料需要藉助 binlog “ slave.000008 ”恢復。

1）  保留新的binlog

這裡需要將新產生的“ slave.000008 ”進行儲存到其他位置

# cp /usr/local/mysql/mysql-bin/slave.000008 /root/mysql_bak/

2）  停止資料庫

# service mysqld stop

3）  清理環境

由於我這裡是在本機恢復，需要清理一下環境。mysqldump 的恢復需要資料庫在啟動時進行，所以這裡我清理環境的方法是重新初始化資料庫，相當於重建新庫。

# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

4）  啟動資料庫

( 初始密碼 /var/log/mysqld.log)

# grep password /var/log/mysqld.log

A temporary password is generated for root@localhost: U/y1skwOfB,(

5）  重置密碼

# mysqladmin -u'root' -p' U/y1skwOfB,( ' password "P@sswrd"

6）  mysql 匯入資料

執行以下命令

# mysql -uroot -p'P@ssw0rd' -S/usr/local/mysql/data/mysql.sock < /mysqldump/2021-01-09-22-mysql-all.sql

此時資料已經匯入成功

7）  驗證資料

mysql> select * from test_db.test_tab;

+------+

| id   |

+------+

|    1 |

|    2 |

+------+

2 rows in set (0.00 sec)

注意，此時匯入的資料只有“ 1 ”“ 2 ”兩個結果，這個是我們執行 mysqldump 時的資料。後面新增的“ 3 ”“ 4 ”並沒有在 dump 檔案中，這兩個資料需要通過儲存的 binlog “ slave.000008 ”恢復。

8）  恢復binlog 資料

# mysqlbinlog slave.000008 | mysql -uroot -p'P@ssw0rd' -S/usr/local/mysql/data/mysql.sock

如果要從“ slave.000007 ”的 position 為“ 154 ”進行恢復，需要執行以下語句

# mysqlbinlog slave.000007 --start-position=154 | mysql -uroot -p'P@ssw0rd' -S/usr/local/mysql/data/mysql.sock

9）  再次查詢資料

mysql> select * from test_db.test_tab;

+------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

+------+

4 rows in set (0.00 sec)

此時，所有資料已經找到。

5.  其他問題

1）  建議在恢復的時候暫停binlog

mysql> set sql_log_bin=0

或

將set sql_log_bin=0 寫進 dump 檔案中

2）  執行恢復語句有兩種方式

方式一：在作業系統層面執行

# mysql -u -p -S < dump 檔案

方式二：進入mysql 客戶端執行

mysql> source dump 檔案

 

 

---- end ----