測試一次binlog 刪除或異動資料復原
#建立數據庫binlogtest
mysql> create database binlogtest;
Query OK, 1 row affected (0.02 sec)
#使用數據庫binlogtest
mysql> use binlogtest;
Database changed
#顯示目前binlog最新的位置，也是目前創建DB的位置
mysql> SHOW BINARY LOGS;
+------------------+------------+
| Log_name | File_size |
+------------------+------------+
| mysql-bin.000020 | 344636396 |
+------------------+------------+
6 rows in set (0.03 sec)
#新增test1表
mysql> CREATE TABLE test1(id INT(10) NOT NULL,name varchar(20),age INT(10));
Query OK, 0 rows affected (0.15 sec)
#插入幾筆數據
mysql> insert into test1 (id,name,age) values (1,'user1',18),(2,'user2',19),(3,'user3',20);
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
#檢查一下數據
mysql> select * from test1;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | user1 | 18 |
| 2 | user2 | 19 |
| 3 | user3 | 20 |
+----+-------+------+
3 rows in set (0.00 sec)
#準備測試----誤下錯指令
mysql> update test1 set age = 20;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
#數據已變動
mysql> select * from test1;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | user1 | 20 |
| 2 | user2 | 20 |
| 3 | user3 | 20 |
+----+-------+------+
3 rows in set (0.00 sec)
#若當下又有使用者插入資料
mysql> insert into test1 values(4,'user4',21),(5,'user5',22),(6,'user6',23);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test1;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | user1 | 20 |
| 2 | user2 | 20 |
| 3 | user3 | 20 |
| 4 | user4 | 21 |
| 5 | user5 | 22 |
| 6 | user6 | 23 |
+----+-------+------+
6 rows in set (0.00 sec)
復原方法：
#再開一個視窗在LINUX下先把binlog文件導出，只有binlogtest這個DB
mysqlbinlog mysql-bin.000020 --database=binlogtest > binlogtest.sql
#接著編輯把錯誤的指令刪除update test1 set age = 20（用FIND搜尋字串）
vi binlogtest.sql
如下字串把下面字段全部刪除後存檔
# at 344637142
#190510 10:40:49 server id 1 end_log_pos 344637253 CRC32 0xad712e55 Query thread_id=1590 exec_time=0 error_code=0
SET TIMESTAMP=1557456049/*!*/;
update test1 set age = 20
/*!*/;
#接著切換到剛MYSQL視窗把DB內把binlogtest的DB刪除
mysql> drop database binlogtest;
Query OK, 1 row affected (0.06 sec)
#然後在LINUX下把binlogtest.sql就剛匯出的binlog 導入
因為剛在使用binlog匯出時，只有針對binlogtest這個DB匯出，所以匯入時不用再指定DB
mysql -uroot -p < binlogtest.sql
#接著進入查看，數據都回來了
mysql> use binlogtest
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------+
| Tables_in_binlogtest |
+----------------------+
| test1 |
+----------------------+
1 row in set (0.00 sec)
mysql> select * from test1;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | user1 | 18 |
| 2 | user2 | 19 |
| 3 | user3 | 20 |
| 4 | user4 | 21 |
| 5 | user5 | 22 |
| 6 | user6 | 23 |
+----+-------+------+
6 rows in set (0.00 sec)
同樣的，如果特定某個語句，應該都可以用這種方式復原，例如讓DROP database ...等
