簡介
Binlog日志，即二進制日志文件，用于記錄用戶對數據庫操作的SQL語句信息，當發生數據誤刪除的時候我們可以通過binlog日志來還原已經刪除的數據，還原數據的方法分為傳統二進制文件還原數據和基于GTID的二進制文件還原數據
前期準備
準備一臺Centos7虛擬機，關閉防火墻和selinux，配置IP地址,同步系統時間,安裝MySQL數據庫
傳統二進制日志還原數據
修改配置文件
|
1
2
3
4
5
6
|
[root@localhost ~]# vi /etc/my.cnfserver-id=1log-bin=binlog#重啟數據庫服務[root@localhost ~]# systemctl restart mysqld |
操作數據庫
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
mysql> create database mydb charset utf8mb4;mysql> use mydb;mysql> create table test(id int)engine=innodb charset=utf8mb4;mysql> insert into test values(1);mysql> insert into test values(2);mysql> insert into test values(3);mysql> insert into test values(4);mysql> commit;mysql> update test set id=10 where id=4;mysql> commit;mysql> select * from test;+------+| id |+------+| 1 || 2 || 3 || 10 |+------+4 rows in set (0.00 sec)mysql> drop database mydb; |
查看二進制日志信息
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mysql> show master status\G;*************************** 1. row *************************** File: binlog.000001 Position: 1960 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec) #查找創庫和刪庫的點,為219和1868mysql> show binlog events in 'binlog.000001';+---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |+---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+| binlog.000001 | 219 | Query | 1 | 329 | create database mydb charset utf8mb4 || binlog.000001 | 1868 | Query | 1 | 1960 | drop database mydb |+---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+ |
另存為二進制日志信息
|
1
|
[root@localhost ~]# mysqlbinlog --start-position=219 --stop-position=1868 /var/lib/mysql/binlog.000001 > /tmp/binlog.sql |
恢復數據
|
1
2
3
4
5
6
|
#臨時關閉二進制日志記錄以免重復記錄mysql> set sql_log_bin=0;#恢復數據mysql> source /tmp/binlog.sql#重啟二進制日志記錄mysql> set sql_log_bin=1; |
查看數據恢復情況
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mydb || mysql || performance_schema || sys |+--------------------+5 rows in set (0.00 sec)mysql> use mydb;Database changedmysql> select * from test;+------+| id |+------+| 1 || 2 || 3 || 10 |+------+4 rows in set (0.00 sec)、 |
基于GTID二進制日志還原數據
修改配置文件
|
1
2
3
4
5
6
7
8
9
|
[root@localhost ~]# vi /etc/my.cnfserver-id=1log-bin=binloggtid_mode=ONenforce_gtid_consistency=truelog_slave_updates=1#重啟數據庫服務[root@localhost ~]# systemctl restart mysqld |
操作數據庫
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
mysql> create database mydb1;mysql> use mydb1;Database changedmysql> create table t1(id int)engine=innodb charset=utf8mb4;mysql> insert into t1 values(1);mysql> insert into t1 values(2);mysql> insert into t1 values(3);mysql> insert into t1 values(11);mysql> insert into t1 values(12);mysql> commit;mysql> select * from t1;+------+| id |+------+| 1 || 2 || 3 || 11 || 12 |+------+5 rows in set (0.00 sec)mysql> drop database mydb1; |
查看二進制日志信息
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mysql> show master status\G;*************************** 1. row *************************** File: binlog.000003 Position: 1944 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 51d3db57-bf69-11ea-976c-000c2911a022:1-81 row in set (0.00 sec)mysql> show binlog events in 'binlog.000003';+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+| binlog.000003 | 154 | Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= '51d3db57-bf69-11ea-976c-000c2911a022:1' || binlog.000003 | 219 | Query | 1 | 316 | create database mydb1 || binlog.000003 | 1784 | Gtid | 1 | 1849 | SET @@SESSION.GTID_NEXT= '51d3db57-bf69-11ea-976c-000c2911a022:8' || binlog.000003 | 1849 | Query | 1 | 1944 | drop database mydb1 |+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+ |
另存為二進制日志信息
|
1
2
|
#8號事務記錄為刪除數據庫，因此只需恢復1-7號事務記錄即可[root@localhost ~]# mysqlbinlog --skip-gtids --include-gtids='51d3db57-bf69-11ea-976c-000c2911a022:1-7' /var/lib/mysql/binlog.000003 > /tmp/gtid.sql |
參數說明:
--include-gtids:包含事務
--exclude-gtids:排除事務
--skip-gtids:跳過事務
恢復數據
|
1
2
3
|
mysql> set sql_log_bin=0;mysql> source /tmp/gtid.sqlmysql> set sql_log_bin=1; |
查看數據恢復情況
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mydb || mydb1 || mysql || performance_schema || sys |+--------------------+6 rows in set (0.00 sec)mysql> use mydb1;Database changedmysql> select * from t1;+------+| id |+------+| 1 || 2 || 3 || 11 || 12 |+------+5 rows in set (0.00 sec) |
到此這篇關于Centos7實現MySQL基于日志還原數據的示例代碼的文章就介紹到這了,更多相關Centos7 MySQL日志還原數據內容請搜索服務器之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持服務器之家！
原文鏈接：https://blog.51cto.com/14832653/2509121
