怎么使用mysql?binlog恢復(fù)數(shù)據(jù)

這篇文章主要介紹“怎么使用mysql binlog恢復(fù)數(shù)據(jù)”的相關(guān)知識(shí)，小編通過實(shí)際案例向大家展示操作過程，操作方法簡(jiǎn)單快捷，實(shí)用性強(qiáng)，希望這篇“怎么使用mysql binlog恢復(fù)數(shù)據(jù)”文章能幫助大家解決問題。

如果想通過 mysql 的 binlog 恢復(fù)數(shù)據(jù)，首先要開啟 binlog 。這里搭建一個(gè)測(cè)試的環(huán)境，了解一下 mysql binlog 是如何恢復(fù)數(shù)據(jù)庫的。原理比較簡(jiǎn)單，binlog 會(huì)存儲(chǔ)mysql中變化的數(shù)據(jù)，比如你創(chuàng)建了一個(gè)數(shù)據(jù)庫，寫入了一些數(shù)據(jù)，這些都會(huì)存儲(chǔ)在 mysql 的 binlog 中。

需要恢復(fù)的時(shí)候就找到，兩個(gè)位置，一個(gè)起始位置，一個(gè)結(jié)束的位置。結(jié)束的位置，一半是數(shù)據(jù)被破壞或者刪除前的位置。mysql 8 默認(rèn)已經(jīng)開啟了 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 已經(jīng)開啟， 同時(shí)可以看到存儲(chǔ)的位置在 /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

可以看到有好幾個(gè) binlog 日志文件， 因?yàn)檫@里是測(cè)試的數(shù)據(jù)庫（有歷史信息,之前的數(shù)據(jù)，沒什么用)，為了方便測(cè)試，直接進(jìn)行重置操作。(刪除了全部binlog文件) ，如果是生成環(huán)境 ，謹(jǐn)慎操作，數(shù)據(jù)無價(jià)。

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

執(zhí)行重置(reset master)后 ，可以看到之前的 binlog 文件已經(jīng)被刪除了，產(chǎn)生一個(gè)新的 binlog 文件。

可以查看一下這個(gè)文件的內(nèi)容

$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 /*!*/;
...

這個(gè)時(shí)候，我們添加一些數(shù)據(jù)

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 ('1','website', 'https://www.liuhaolin.com');
EOT

這個(gè)時(shí)候，不小心刪除了，數(shù)據(jù)庫 test_liuhaolin_com

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

現(xiàn)在就需要解決一個(gè)實(shí)際問題，怎么恢復(fù)這個(gè)數(shù)據(jù)庫

首先為了防止干擾，執(zhí)行 flush logs ，產(chǎn)生一個(gè)新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)

恢復(fù)數(shù)據(jù)，首先要找到數(shù)據(jù)在哪里被刪除了。

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

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

1. 數(shù)據(jù)需要恢復(fù)的起始位置

2. 數(shù)據(jù)需要恢復(fù)的結(jié)束位置

這里起始的位置就找 創(chuàng)建數(shù)據(jù)庫的位置，結(jié)束的位置就找 刪除數(shù)據(jù)庫的位置。

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

可以在文件 tmp.sql 中知道 開始和結(jié)束位置

binlog

可以看到開始的地方是 233

binlog

可以看到結(jié)束的位置在 1371 ，所以執(zhí)行一下數(shù)據(jù)的恢復(fù)。

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

作為驗(yàn)證,執(zhí)行前可以，看下 數(shù)據(jù)庫是否存在。

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

# 執(zhí)行恢復(fù)操作
mysqlbinlog -v  binlog.000001  --start-position=233  --stop-position=1371 | mysql 

# 再次檢查，可以發(fā)現(xiàn)數(shù)據(jù)已經(jīng)恢復(fù)
mysql>
mysql> use test_liuhaolin_com;
Database changed

關(guān)于“怎么使用mysql binlog恢復(fù)數(shù)據(jù)”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)，可以關(guān)注億速云行業(yè)資訊頻道，小編每天都會(huì)為大家更新不同的知識(shí)點(diǎn)。