【MYSQL】測試一次binlog 刪除或異動資料復原

測試一次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 ...等