怎麼使用mysql binlog恢復數據

在要使用 mysql 的 binlog 進行資料復原前，必須先啟用 binlog。在這裡建立一個測試環境，以了解如何使用mysql binlog恢復資料庫。原理比較簡單，binlog 會儲存mysql中變化的數據，例如你建立了一個資料庫，寫入了一些數據，這些都會儲存在 mysql 的 binlog 中。

在需要恢復時，要找出兩個位置：一個起始位置和一個結束位置。結束的位置，一半是資料被破壞或刪除前的位置。 mysql 8 預設已經開啟了binlog

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------+
| Variable_name                   | Value                    |
+---------------------------------+--------------------------+
| log_bin                         | ON                       |
| log_bin_basename                | /data/mysql/binlog       |
| log_bin_index                   | /data/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                      |
| log_bin_use_v1_row_events       | OFF                      |
| sql_log_bin                     | ON                       |
+---------------------------------+--------------------------+

可以看到log_bin 已經開啟， 同時可以看到儲存的位置在/daba/mysql 目錄, 前綴是binlog

ls /data/mysql/binlog.*
/data/mysql/binlog.000143  /data/mysql/binlog.000144  /data/mysql/binlog.000145  /data/mysql/binlog.000146  /data/mysql/binlog.index

可以看到有好幾個binlog 日誌文件， 因為這裡是測試的資料庫（有歷史資訊,之前的數據，沒什麼用)，為了方便測試，直接進行重置操作。刪除所有binlog檔案需謹慎，若為生產環境，資料價值不可估量。

show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000143 |       156 | No        |
| binlog.000144 |       200 | No        |
| binlog.000145 |       156 | No        |
| binlog.000146 |       156 | No        |
+---------------+-----------+-----------+
4 rows in set (0.01 sec)

mysql> reset master;
Query OK, 0 rows affected (0.02 sec)

mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       156 | No        |
+---------------+-----------+-----------+
1 row in set (0.00 sec)


ls /data/mysql/binlog.*
/data/mysql/binlog.000001  /data/mysql/binlog.index

執行重置(reset master)後 ，可以看到先前的 binlog 檔案已經被刪除了，產生一個新的 binlog 檔案。

可以查看這個檔案的內容

$mysqlbinlog binlog.000001
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
...

這個時候，我們加入一些資料

mysql <<EOT
create database test_liuhaolin_com;
select now();
EOT

mysql <<EOT
use test_liuhaolin_com;

create table if not exists test(
	\`id\` int unsigned not null auto_increment primary key,
	\`key\` varchar(100),
	\`val\` varchar(255)
) engine=myisam charset=utf8mb4;
EOT

mysql <<EOT
use test_liuhaolin_com;
insert into test  values (&#39;1&#39;,&#39;website&#39;, &#39;https://www.liuhaolin.com&#39;);
EOT

這個時候，不小心刪除了，資料庫test_liuhaolin_com

mysql> drop database test_liuhaolin_com;
Query OK, 1 row affected (0.09 sec)

現在就需要解決一個實際問題，怎麼恢復這個資料庫

首先為了防止幹擾，執行flush logs ，產生一個新binlog 檔案。

mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |      1594 | No        |
+---------------+-----------+-----------+
1 row in set (0.00 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |     1594 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.08 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |      156 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

恢復數據，首先要找到數據在哪裡被刪除了。

mysqlbinlog binlog.000001 | grep -n  "drop database"
113:drop database test_liuhaolin_com

可以看到在 113 行的地方有個 刪除語句。終可以找到兩個地方

1. 資料需要恢復的起始位置

2. 資料需要恢復的結束位置

#這裡起始的位置就找建立資料庫的位置，結束的位置就找刪除資料庫的位置。

mysqlbinlog --set-charset=utf8  binlog.000001 > tmp.sql

可以在檔案tmp.sql 中知道開始和結束位置

#binlog

可以看到開始的地方是233

binlog

可以看到結束的位置在1371 ，所以執行資料的復原。

mysqlbinlog -v binlog.000001 --start-position=233 --stop-position=1371 | mysql

作為驗證,執行前可以，看下 資料庫是否存在。

mysql> use test_liuhaolin_com;
ERROR 1049 (42000): Unknown database 'test_liuhaolin_com'

# 执行恢复操作
mysqlbinlog -v  binlog.000001  --start-position=233  --stop-position=1371 | mysql 

# 再次检查，可以发现数据已经恢复
mysql>
mysql> use test_liuhaolin_com;
Database changed

以上是怎麼使用mysql binlog恢復數據的詳細內容。更多資訊請關注PHP中文網其他相關文章！