搜尋
首頁資料庫mysql教程Linux上透過binlog檔案恢復mysql資料庫詳細步驟_MySQL

 一、binlog 介紹

  伺服器的二進位日誌記錄著該資料庫的所有增刪改的操作日誌(前提是要在自己的伺服器上開啟binlog),也包含了這些操作的執行時間。為了顯示這些二進位內容,我們可以使用mysqlbinlog指令來查看。

  用途1:主從同步

  用途2:恢復資料庫(也是線上出現一次資料庫檔案遺失後,才對這個有所了解並學習的)

  mysqlbinlog指令用法:shell> mysqlbinlog [options] log_file ...

1) mysqlbinlog 選項範例

常見的選項有以下幾個:

--start-datetime

從二進位日誌中讀取指定等於時間戳記或晚於本機電腦的時間。取值如:="1470733768" 或="2016-08-09 5:09:28"

範例:

[root@hcloud ~]# mysqlbinlog --start-datetime="2016-08-09 5:05:27" /var/lib/mysql/mysql-bin.000001 
--stop-datetime

從二進位日誌中讀取指定小於時間戳記或等於本機電腦的時間 取值和上述一樣

--start-position

從二進位日誌中讀取指定position 事件位置作為開始。取值:="2698"

範例:

[root@hcloud ~]# mysqlbinlog --start-position="2698" /var/lib/mysql/mysql-bin.000001 
--stop-position

從二進位日誌中讀取指定position 事件位置作為事件截至。取值:="2698"

二、環境準備、備份還原

  1) 安裝mysql後,檢查開啟binlog

mysql> SHOW BINARY LOGS;

ERROR 1381 (HY000): You are not using binary logging :上面提示說明沒有伺服器開啟binlog

  修改/etc/my.cnf

  在mysqld選項中新增 一行內容如下:

  log-bin=mysql-bin

  預設如果不給值的話,log-bin 的會以mysqld-bin 為索引,建立mysqld-bin.00001等

  重啟mysqld即可。

  2) 檢查下binlog

mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 106 |
+------------------+-----------+
1 row in set (0.00 sec)

  3) 先建立一些原始資料。

mysql> create database Test_DB;
Query OK, 1 row affected (0.00 sec)
mysql> use Test_DB;
Database changed
mysql> CREATE TABLE OneTb(id INT(10) NOT NULL,name varchar(20),age INT(10));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into OneTb values (1,'user1',18);
mysql> insert into OneTb values (2,'user2',19);
insert into OneTb values (3,'user3',20);

  檢查下數據:

mysql> select * from OneTb;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | user1 | 18 |
| 2 | user2 | 19 |
| 3 | user3 | 20 |
+----+-------+------+
3 rows in set (0.00 sec)

  4) 備份還原 (完整備份以及還原)

  這裡我們模擬一下做下每天的完整備份資料庫任務。

[root@hcloud ~]# mysqldump -uroot -p Test_DB > /data/mysqlbackup/Test_DB_0809-16:50.sql
Enter password:

  模擬下操作失誤,將資料修改錯誤了。

mysql> update OneTb set age = 15;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from OneTb;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | user1 | 15 |
| 2 | user2 | 15 |
| 3 | user3 | 15 |
+----+-------+------+
3 rows in set (0.00 sec)

  現在我們使用傳統的方式來進行還原還原。

[root@hcloud ~]# mysql -uroot -p Test_DB < /data/mysqlbackup/Test_DB_0809-16\:50.sql 

再查詢一次:

mysql> select * from Test_DB.OneTb;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | user1 | 18 |
| 2 | user2 | 19 |
| 3 | user3 | 20 |
+----+-------+------+
3 rows in set (0.00 sec)

  可以看到資料都已經還原回來。

  5) 利用binlog模擬還原

  在原表的基礎上在建立幾個資料。

mysql> insert into Test_DB.OneTb 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 Test_DB.OneTb;
+----+-------+------+
| 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)

  如果這個時候我們把資料不小心修改了或把庫刪除掉了,導致資料全部遺失,這時候如果再用之前最新的備份檔Test_DB_0809-16:50.sql,去恢復資料的話,那麼將會丟掉備份之後新插入的資料。

  注意:如果真的使用最近的一次備份文件去做的話,一定是在萬不得已的情況(比如binlog 被刪除,整個硬碟掛掉、、、 想想都很可怕。。)。

  模擬誤操作,批量更改下用戶的名字。

mysql> update Test_DB.OneTb set name='user10';
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6 Changed: 6 Warnings: 0

  不行,上一步不夠狠,這裡再狠一點,把手錶都給刪除

mysql> drop table Test_DB.OneTb;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 3
Current database: *** NONE ***
Query OK, 0 rows affected (0.00 sec)

  由於之前我們一開始開啟了binlog 日誌選項,用binlog恢復資料庫。下面從binlog入手,先檢查一下binlog 文件,目前我的mysql 服務自開啟binlog 後重啟了兩次,所以有2個binlog文件(每重啟一次,便會重新生成一個binlog文件,還有一種情況就是運行了FLUSH LOGS指令也會重建一個);

  mysql-bin.index 文件中記錄的是:自log-bin選項開啟後,記錄的所有的二進位日誌清單清單。

  注意:在實際生產環境中,如果遇到需要恢復資料庫的情況,不要讓使用者能存取到資料庫,以避免新的資料插入進來,以及在主從的環境下,關閉主從。

  使用mysqlbinlog 指令可以查看binlog檔.我們看下最新的檔mysql-bin.00002

  從最後可以看出有刪除的操作。但是我們不能完全的恢復,因為最後還有刪除的操作。

  现在我的思路就是,先将第一个binlog 和第二个binlog 文件导出来à利用指定的position位置的方式(过滤掉删除表操作和update Test_DB.OneTb set name='user10';这条语句 ),导出2个sql 语句,最后我们将2个sql 合成一个sql,导入到数据库中即可。

  我们先用mysqlbinlog命令找到update 那条语句的位置,然后指定position 将mysql-bin.00001 导出来。

[root@hcloud ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000001
….
#160809 5:09:28 server id 1 end_log_pos 2698 Query thread_id=17 exec_time=0 error_code=0
SET TIMESTAMP=1470733768/*!*/;
SET @@session.foreign_key_checks=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=0/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
insert into Test_DB.OneTb values(4,'user4',21),(5,'user5',22),(6,'user6',23)
/*!*/;
# at 2698
#160809 5:19:49 server id 1 end_log_pos 2795 Query thread_id=17 exec_time=0 error_code=0
SET TIMESTAMP=1470734389/*!*/;
update Test_DB.OneTb set name='user10'
/*!*/;
# at 2795
#160809 5:30:38 server id 1 end_log_pos 2814 Stop
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

  从上面可以看到我们在做插入正常数据后的position 是2698,那么使用下面的命令导出sql

[root@hcloud ~]# mysqlbinlog --stop-position="2698" /var/lib/mysql/mysql-bin.000001 > Backup_1.sql 

  然后导出mysql-bin.00002的sql 语句(注:由于演示操作,该文件只有一个drop 表操作,所以不做处理,但是在实际环境中,由于中途可能会有重启数据库操作,那时就需要检测最新的binlog有没有业务需要的语句。)

  sql 语句已经导出来了。我们可以利用该语句直接恢复所有正常的数据。

  注:本次恢复没有利用到之前的完整备份,因为我是开启binlog后,然后才做的所有建库建表操作,第一个binlog文件里已经记录了所有的数据库操作,所以不需要使用之前的完整备份(另外:实际的生产环境,还是需要利用到完整备份的,因为线上环境可能会有N多个binlog文件,所以需要利用到完整备份和最新的binlog文件来结合恢复)

  开始恢复前,我们将原有的Test_DB数据库也给干掉吧。毕竟我们的binlog中有创建操作

mysql> DROP DATABASE Test_DB;
Query OK, 0 rows affected (0.03 sec)

  恢复数据库时还可以利用在登陆mysql 后,用source 命令导入sql语句,这里暂不介绍

[root@hcloud ~]# mysql -uroot -p < Backup_1.sql 

Enter password:

  恢复完成后,我们检查下表的数据是否完整

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| Test_DB |
| mysql |
+--------------------+
3 rows in set (0.00 sec)
mysql> select * from Test_DB.OneTb;
+----+-------+------+
| 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)

  Ok完整的都恢复过来了。

三、总结

  1) 恢复方式

    a) 利用最新一次的完整备份加binlog 指定事件起始时间和终止时间或者position恢复数据库

    b) 利用所有binlog指定事件起始位置和终止时间来合并sql文件恢复数据库(此方法要确保binlog文件的完整)

    c) 利用mysqldump 使用完整恢复。(在确保最新一次的完整备份后的数据不重要,允许丢掉的情况下,直接恢复。该方法最简单、效率最高)

  2) 附:官方建议的备份原则(为了能睡个好觉….嗯,是的)

    a) 在mysql安装好并运行时,就始终开启 log-bin选项,该日志文件位于datadir目录下,也要确保该目录所在存储介质是安全的。

    b) 定期做完整的mysql 备份。

    c) 定期使用 FlUSH LOGS 或者 mysqladmin flush-logs ,该操作会关闭当前的二进制日志文件,并新建一个binlog日志文件。(和重启mysql后新建的binlog操作一样)。以备份binlog日志,利用binlog日志也可以做增量备份。

以上所述是小编给大家介绍的Linux上通过binlog文件恢复mysql数据库详细步骤,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对网站的支持!

陳述
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
什么是linux设备节点什么是linux设备节点Apr 18, 2022 pm 08:10 PM

linux设备节点是应用程序和设备驱动程序沟通的一个桥梁;设备节点被创建在“/dev”,是连接内核与用户层的枢纽,相当于硬盘的inode一样的东西,记录了硬件设备的位置和信息。设备节点使用户可以与内核进行硬件的沟通,读写设备以及其他的操作。

Linux中open和fopen的区别有哪些Linux中open和fopen的区别有哪些Apr 29, 2022 pm 06:57 PM

区别:1、open是UNIX系统调用函数,而fopen是ANSIC标准中的C语言库函数;2、open的移植性没fopen好;3、fopen只能操纵普通正规文件,而open可以操作普通文件、网络套接字等;4、open无缓冲,fopen有缓冲。

linux中什么叫端口映射linux中什么叫端口映射May 09, 2022 pm 01:49 PM

端口映射又称端口转发,是指将外部主机的IP地址的端口映射到Intranet中的一台计算机,当用户访问外网IP的这个端口时,服务器自动将请求映射到对应局域网内部的机器上;可以通过使用动态或固定的公共网络IP路由ADSL宽带路由器来实现。

linux中eof是什么linux中eof是什么May 07, 2022 pm 04:26 PM

在linux中,eof是自定义终止符,是“END Of File”的缩写;因为是自定义的终止符,所以eof就不是固定的,可以随意的设置别名,linux中按“ctrl+d”就代表eof,eof一般会配合cat命令用于多行文本输出,指文件末尾。

什么是linux交叉编译什么是linux交叉编译Apr 29, 2022 pm 06:47 PM

在linux中,交叉编译是指在一个平台上生成另一个平台上的可执行代码,即编译源代码的平台和执行源代码编译后程序的平台是两个不同的平台。使用交叉编译的原因:1、目标系统没有能力在其上进行本地编译;2、有能力进行源代码编译的平台与目标平台不同。

linux怎么判断pcre是否安装linux怎么判断pcre是否安装May 09, 2022 pm 04:14 PM

在linux中,可以利用“rpm -qa pcre”命令判断pcre是否安装;rpm命令专门用于管理各项套件,使用该命令后,若结果中出现pcre的版本信息,则表示pcre已经安装,若没有出现版本信息,则表示没有安装pcre。

linux怎么查询mac地址linux怎么查询mac地址Apr 24, 2022 pm 08:01 PM

linux查询mac地址的方法:1、打开系统,在桌面中点击鼠标右键,选择“打开终端”;2、在终端中,执行“ifconfig”命令,查看输出结果,在输出信息第四行中紧跟“ether”单词后的字符串就是mac地址。

linux中rpc是什么意思linux中rpc是什么意思May 07, 2022 pm 04:48 PM

在linux中,rpc是远程过程调用的意思,是Reomote Procedure Call的缩写,特指一种隐藏了过程调用时实际通信细节的IPC方法;linux中通过RPC可以充分利用非共享内存的多处理器环境,提高系统资源的利用率。

See all articles

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

AI Hentai Generator

AI Hentai Generator

免費產生 AI 無盡。

熱門文章

R.E.P.O.能量晶體解釋及其做什麼(黃色晶體)
2 週前By尊渡假赌尊渡假赌尊渡假赌
倉庫:如何復興隊友
1 個月前By尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒險:如何獲得巨型種子
4 週前By尊渡假赌尊渡假赌尊渡假赌

熱工具

Dreamweaver CS6

Dreamweaver CS6

視覺化網頁開發工具

禪工作室 13.0.1

禪工作室 13.0.1

強大的PHP整合開發環境

EditPlus 中文破解版

EditPlus 中文破解版

體積小,語法高亮,不支援程式碼提示功能

SublimeText3 英文版

SublimeText3 英文版

推薦:為Win版本,支援程式碼提示!

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

強大的PHP整合開發環境