MySQL的邏輯備份和恢複（含binlog恢複）

在MySQL中，邏輯備份的最大優點是對于各種存儲引擎都可以用同樣的方法來備份；

備份

MSQL 中的邏輯備份是将資料庫中的資料備份為一個文本檔案，備份的檔案可以被檢視和編輯。在MySQL中，可以使用 mysqldump工具來完成邏輯備份。

備份指令：

-- 備份指定的資料庫或者此資料庫中的某些表
shel1> mysqldump [options] db_name [tables]

-- 備份指定的一個或多個資料庫
shel1> mysqldump [options] ---database DBl [DB2 DB3...]

-- 備份所有資料庫。
she11> mysqldump [options] --a11-database           

如果沒有指定資料庫中的任何表，預設導出所有資料庫中的所有表。

舉例：

-- 備份所有資料庫:
[zzxelocalhost ~]$ mysqldump -uroot -p123456 --all-database > al1.sql

-- 備份資料庫 test:
[zzxelocalhost ~]$ mysqldump -uroot -p123456 test > test.sql

-- 備份資料庫 test 下的表emp:
[zzxelocalhost ~]$ mysqldump -uroot -p123456 test emp > emp.sql

-- 備份資料庫 test 下的表emp和dept
[zzxelocalhost ~]$ mysqldump -uroot -p123456 test emp dept > emp_dept.sql

           

為了保證資料備份的一緻性，MyISAM 存儲引整在備份時需要加上-l參數表示将所有表加上讀鎖，在備份期間，所有表将隻能讀而不能進行資料更新。

但是對于事務存儲引擎(InnoDB和BDB)來說，可以采用更好的選項--single-transaction，此選項将使得 InnoDB存儲引擎得到一個快照，使得備份的資料能夠保證一緻性。

完全恢複

mysqldump 的恢複也很簡單，将備份作為輸入執行即可，具體文法如下：

mysql-urout p dbname < bakfile           

注意，将備份恢複後資料并不完整，還需要将備份後執行的日志（binlog）進行重做，文法如下：

mysqlbinlog binlog-file | mysql -u root -p -l -F test > test.dmp           

其中-l參數表示給所有表加讀鎖，-F 表示生成一個新的日志檔案。

基于時間點恢複

由于誤操作，比如誤删除了一張表，這時使用完全恢複是沒有用的，因為日志裡面還存在誤操作的語句，我們需要的是恢複到誤操作之前的狀态，然後跳過誤操作語句，再恢複後面執行的語句，完成我們的恢複。這種恢複叫不完全恢複，在 MySQL 中，不完全恢複分為基于時間點的恢複和基于位置的恢複。

（1）如果上午10點發生了誤操作，可以用以下語句用備份和 binlog 将資料複到故障前：

shell> mysqlbinlog --stop-date='2023-04-20 9:59:59' /var/log/mysql/bin.123456 | mysql -u root -pmypwd             

(2)跳過故障時的時間點，繼續執行後面的binlog，完成恢複。

shell> mysqlbinlog --stop-date='2023-04-20 10:01:00' /var/log/mysql/bin.123456 | mysql -u root -pmypwd             

基于位置恢複

和基于時間點的恢複類似，但是更精确，因為同一個時間點可能有很多條 SQL 語句同時執行。恢複的操作步驟如下。

(1)在 shell 下執行如下指令:

she11>mysqlbinlog --start-date="2023-04-20 9:55:00" \
--stop-date="2023-04-20 10:05:00 /var/1og/mysq1/bin.123456 > /tmp/mysql_restore.sg1           

該指令将在/tmp 目錄建立小的文本檔案，編輯此檔案，找到出錯語句前後的位置号，例如前後位置号分别是368312和368315。

(2)恢複了以前的備份檔案後，應從指令行輸入下面的内容：

she11s> mysq1binlog --stop-position="368312" /var/log/mysql/bin.123456 \ 
| mysql-u root -pmypwd

she1l>mysqlbinlog --start-position-"368315" /var/1og/mysql/bin.123456 \
| mysql -u root -pmypwd           

上面的第一行将恢複到停止位置為止的所有事務。下一行将恢複從給定的起始位置直到binlog日志結束的所有事務。