mysqldump+mysqlbinlog执行备份与还原

2025-04-12 技术教程

服务器的二进制日志文件由用来描述修改数据库内容的事件组成。服务器以二进制方式来写这些文件。为了以文本格式来显示这些内容，可以使用mysqlbinlog工具。也可以使用mysqlbinlog来显示在复制环境中由从从slave服务器所写入中relay日志文件中的内容，因为其格式与二进制日志文件格式一样。
mysqlbinlog的使用语法如下:
Usage: mysqlbinlog [options] log-files

下面的命令用来显示名为binlog.000001的二进制日志文件的内容:

[mysql@localhost~]$mysqlbinlog/mysqldata/mysql/binlog.000001/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER/*!*/;#at4#19111515:39:01serverid1end_log_pos123CRC320x2d9d7b4fStart:binlogv4,serverv5.7.26-logcreated19111515:39:01atstartup#Warning:thisbinlogiseitherinuseorwasnotclosedproperly.ROLLBACK/*!*/;BINLOG'FVbOXQ8BAAAAdwAAAHsAAAABAAQANS43LjI2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAVVs5dEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQAAU97nS0='/*!*/;#at123#19111515:39:01serverid1end_log_pos154CRC320x42dcd61cPrevious-GTIDs#[empty]#at154#19111515:51:15serverid1end_log_pos219CRC320x5bc0b021Anonymous_GTIDlast_committed=0sequence_number=1rbr_only=noSET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;#at219#19111515:51:15serverid1end_log_pos308CRC320x7261eacbQuerythread_id=2exec_time=0error_code=0use`mysql`/*!*/;SETTIMESTAMP=1573804275/*!*/;SET@@session.pseudo_thread_id=2/*!*/;SET@@session.foreign_key_checks=1,@@session.sql_auto_is_null=0,@@session.unique_checks=1,@@session.autocommit=1/*!*/;SET@@session.sql_mode=1436549152/*!*/;SET@@session.auto_increment_increment=1,@@session.auto_increment_offset=1/*!*/;/*!\Cgb2312*//*!*/;SET@@session.character_set_client=24,@@session.collation_connection=24,@@session.collation_server=45/*!*/;SET@@session.lc_time_names=0/*!*/;SET@@session.collation_database=DEFAULT/*!*/;truncatetableperson/*!*/;#at308#19111515:51:38serverid1end_log_pos373CRC320x6d2e39aaAnonymous_GTIDlast_committed=1sequence_number=2rbr_only=noSET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;#at373#19111515:51:38serverid1end_log_pos454CRC320x7871c2eaQuerythread_id=2exec_time=0error_code=0SETTIMESTAMP=1573804298/*!*/;BEGIN/*!*/;#at454#at486#19111515:51:38serverid1end_log_pos486CRC320xb746cd30IntvarSETINSERT_ID=1/*!*/;#19111515:51:38serverid1end_log_pos654CRC320x0e926042Querythread_id=2exec_time=0error_code=0SETTIMESTAMP=1573804298/*!*/;insertintoperson(last_name,first_name,birth,death)values('yong','jing','1985-02-28',null)/*!*/;#at654#19111515:51:38serverid1end_log_pos736CRC320xc5450308Querythread_id=2exec_time=0error_code=0SETTIMESTAMP=1573804298/*!*/;COMMIT/*!*/;#at736#19111515:51:45serverid1end_log_pos801CRC320xc2c892b8Anonymous_GTIDlast_committed=2sequence_number=3rbr_only=noSET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;#at801#19111515:51:45serverid1end_log_pos882CRC320x51a9cd5cQuerythread_id=2exec_time=0error_code=0SETTIMESTAMP=1573804305/*!*/;BEGIN/*!*/;#at882#at914#19111515:51:45serverid1end_log_pos914CRC320x40a98faeIntvarSETINSERT_ID=2/*!*/;#19111515:51:45serverid1end_log_pos1082CRC320x3396c40dQuerythread_id=2exec_time=0error_code=0SETTIMESTAMP=1573804305/*!*/;insertintoperson(last_name,first_name,birth,death)values('yan','huang','1990-08-25',null)/*!*/;#at1082#19111515:51:45serverid1end_log_pos1164CRC320xf6f6efadQuerythread_id=2exec_time=0error_code=0SETTIMESTAMP=1573804305/*!*/;COMMIT/*!*/;#at1164#19111515:51:53serverid1end_log_pos1229CRC320x55b50dbeAnonymous_GTIDlast_committed=3sequence_number=4rbr_only=noSET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;#at1229#19111515:51:53serverid1end_log_pos1310CRC320xd0f6a335Querythread_id=2exec_time=0error_code=0SETTIMESTAMP=1573804313/*!*/;BEGIN/*!*/;#at1310#at1342#19111515:51:53serverid1end_log_pos1342CRC320xfad94bafIntvarSETINSERT_ID=3/*!*/;#19111515:51:53serverid1end_log_pos1508CRC320x26c5b3bbQuerythread_id=2exec_time=0error_code=0SETTIMESTAMP=1573804313/*!*/;insertintoperson(last_name,first_name,birth,death)values('yali','ye','1994-12-23',null)/*!*/;#at1508#19111515:51:53serverid1end_log_pos1590CRC320xbb6a2b4cQuerythread_id=2exec_time=0error_code=0SETTIMESTAMP=1573804313/*!*/;COMMIT/*!*/;SET@@SESSION.GTID_NEXT='AUTOMATIC'/*addedbymysqlbinlog*//*!*/;DELIMITER;#Endoflogfile/*!50003SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=0*/;

上面输出了binlog.000001二进制日志文件中所包含的内容。对于基于语句的日志，事件信息包括SQL语句，执行语句的服务器ID，语句被执行的时间戳，执行时间等。对于基于行记录的日志，事件信息指示行的改变而不是SQL语句。

#at486#19111515:51:38serverid1end_log_pos486CRC320xb746cd30IntvarSETINSERT_ID=1/*!*/;#19111515:51:38serverid1end_log_pos654CRC320x0e926042Querythread_id=2exec_time=0error_code=0SETTIMESTAMP=1573804298/*!*/;

第一行，at后面的数字指示事件在二进制日志文件中的偏移量或开始位置。

第二行是以日期和时间开始指示语句开始执行的时间。对于复制来说，这个时间戳将传播到从属服务器。server id是事件起源服务器的server_id值。end_log_pos指示下一个事件开始的位置(它是当前事件的终止位置+1)。thread_id那个线程来执行这个事件。exec_time是在主服务器上执行事件所花费的时间。在从属服务器上，它是从属服务器上执行结束时间减去主服务器上的执行开始时间的差值。这种差值可以作为一种指示来表示复制进程落后于主服务器多长时间。error_code指示执行事件的结果。零意味着没有出现错误。

mysqlbinglog的输出可以用来重新执行日志文件中的语句(例如，通过使用mysql工具)。这在服务器崩溃时用来恢复是很有用的。

正常来说，使用mysqlbinlog直接读取二进制日志文件并应用它们到本地MySQL服务器。它也可以通过使用--read-from-remote-server选项来从远程服务器上读取二进制日志文件。为了读取远程二进制日志文件，连接参数选项可以被指定用来指示如何连接服务器。这些选项有--host,--password,--port,--protocol,--socket和--user，除非使用了--read-from-remote-server选项否则它们会被忽略。

当对一个大的二进制日志文件执行mysqlbinlog时，要小心注意文件系统是否有足够的空间来存储结果文件。为了配置目录给mysqlbinlog临时使用存储文件，使用TMPDIR环境变量。

使用mysqlbinlog备份二进制日志文件
缺省情况下，mysqlbinlog读取二进制日志文件并以文本格式来显示它的内容。这能让你更容易使用文件来检查发生的事件和重新执行它们(例如，通过使用输出作为mysql的输入)。mysqlbinlog可以直接从本地文件系统中读取日志文件或者使用--read-from-remote-server选项来连接远程服务器并从远程服务器上读取二进制日志文件。mysqlbinlog以文本格式将内容输出到标准输出，或者如果指定了--result-file=file_name选项会将内容写入文件。

mysqlbinlog可以读了二进制日志文件并将其包含的内容以二进制格式而不是文本格式写入新文件。这种能力可以让你以原来的格式来备份二进制日志文件。mysqlbinlog可以生成静态备份，在备份一组日志文件时当备份完最后的文件时而停止。它也可以生成一种连续(live)备份，当备份到最后的日志文件时仍然保持对服务器的连接并当生成新的事件时继续复制新的事件。在连续备份操作时，mysqlbinlog会运行到连接中断为止(比如，服务器退出)或mysqlbinlog被强制中断为止。当连接中断，mysqlbinlog不会进行等待并重新进行连接，不像从属复制服务器那样。为了在服务器重启之后继续一个live备份，必须重新启动mysqlbinlog。

二进制日志文件备份要求在调用mysqlbinlog时最少要使用两个选项:
.--read-from-remote-server(或-R)选项来告诉mysqlbinlog连接到一个服务器并读取它的二进制日志文件(这类似于一个从属复制服务器连接到它的主服务器).

.--raw选项告诉mysqlbinlog以原始(二进制)格式输出，而不是文本格式。

与--read-from-remote-server一起通常还指定其它选项:--host指示服务器运行在哪里，并且可能需要指定连接选项--user和password。

与--raw联合使用的几个其它选项:.--stop-never:在读取到最后日志文件后保持对服务器的连接并继续读取新的事件。

.--stop-never-slave-server-id=id:当--stop-never被使用时mysqlbinlog报告的服务器ID，缺省值65535。这可以避免与从属服务器或其它的mysqlbinlog进程的ID冲突。

.--result-file:输出文件名的前缀

为了使用mysqlbinlog来备份服务器的二进制日志文件，你必须指定在服务器上真实存在的文件名。如果你不知道文件名，连接到服务器并使用show binary logs语句来查看当前的日志文件名。

使用这些信息可以使用mysqlbinlog来备份二进制日志文件到当前目录:
为了对binlog.000130到binlog.000132的日志文件进行静态备份，使用以下命令:
mysqlbinlog --read-from-remote-server --host=host_name --raw
binlog.000130 binlog.000131 binlog.000132

mysqlbinlog --read-from-remote-server --host=host_name --raw
--to-last-log binlog.000130

第一个命令显式指定每个文件名。第二个只指定了第一个日志文件并使用了--to-last-log来读取到最后一个日志文件。在这些命令之间的差异是在mysqlbinlog到达binlog.000132的末尾之前如果服务器打开了binlog.000133文件，第一个命令将不会读取，但第二个命令会读取。

为了进行live备份mysqlbinlog从binlog.000130开始备份现有的日志文件，然后保持对服务器的连接来复制生成的新事件:
mysqlbinlog --read-from-remote-server --host=host_name --raw
--stop-never binlog.000130

使用--stop-never选项，不需要指定--to-last-log来读取最后的日志文件因为这个选项是隐含的

输出文件名
在没有使用--raw选项时，mysqlbinlog会生成文本格式的输出，如果指定--result-file选项，指定将所有输出写入一个文件中。使用--raw选项时，mysqlbinlog会将服务器的每个日志文件转换成一个二进制输出文件。缺省情况下，mysqlbinlog会在当前目录中生成与源日志文件同名的文件。为了修改输出文件名，使用--result-file选项。与--raw联合使用，--result-file选项值将作为前缀来命名输出文件名。

现在对远程服务器中的binlog.000001日志文件进行备份

[mysql@localhost~]$mysqlbinlog--read-from-remote-server--host=192.168.1.250--rawbinlog.000001--result-file=jy_[mysql@localhost~]$ls-lrt-rw-r-----.1mysqlmysql253011月2210:24jy_binlog.000001

可以看到备份的日志文件为以jy_为前缀，其文件名为jy_binlog.000001

使用mysqldump与mysqlbinlog执行备份与还原操作
下面将介绍一个简单的例子显示如何使用mysqldump与mysqlbinlog一起来备份MySQL服务器的数据和二进制日志文件以及在数据丢失时如何使用备份来还原数据。

现在主机上的MySQL服务器的第一个二进制日志文件为binlog.000001

使用mysqlbinlog来对二进制日志文件执行连续备份:

[mysql@localhost~]$mysqlbinlog--read-from-remote-server--host=192.168.1.250--raw--stop-neverbinlog.000001[mysql@localhost~]$ls-lrt-rw-r-----.1mysqlmysql253011月2210:38binlog.000001

创建了一个名为t的测试表并插入了三行记录

mysql>select*fromt;+----+----------+------+|id|name|date|+----+----------+------+|1|jingyong|NULL||2|yeyali|NULL||3|huangyan|NULL|+----+----------+------+3rowsinset(0.00sec)

使用mysqldump来创建一个dump文件作为对MySQL服务器的数据快照。使用--all-databases,--events和--routines来备份所有的数据，--master-data=2用来指示在dump文件中包括当前的二进制日志文件。

[mysql@localhost~]$mysqldump--host=192.168.1.250--port=3306-uroot-pabcd--all-databases--events--routines--master-data=2>dump_mysql.sqlmysqldump:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[mysql@localhost~]$ls-lrt-rw-r--r--.1mysqlmysql329049711月2210:51dump_mysql.sql

现在删除mysql库中的表t

mysql>droptablet;QueryOK,0rowsaffected(0.18sec)mysql>desct;ERROR1146(42S02):Table'mysql.t'doesn'texist

现在假设mysql库中的表t丢失了，使用最近的dump文件来还原数据:

[mysql@localhost~]$mysql--host=192.168.1.250--port=3306-uroot-pabcdmysql

还原数据后mysql.t表就恢复了

mysql>desct;+-------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------+-------------+------+-----+---------+-------+|id|int(11)|NO||NULL|||name|varchar(20)|NO||NULL|||date|date|YES||NULL||+-------+-------------+------+-----+---------+-------+3rowsinset(0.01sec)mysql>select*fromt;+----+----------+------+|id|name|date|+----+----------+------+|1|jingyong|NULL||2|yeyali|NULL||3|huangyan|NULL|+----+----------+------+3rowsinset(0.00sec)

现在向mysql.t表中插入一条记录并删除这条记录,然后使用备份的二进制日志文件来重新执行事件来恢复这条记录

mysql>insertintotvalue(4,'wenyao',NULL);QueryOK,1rowaffected(0.03sec)mysql>select*fromt;+----+----------+------+|id|name|date|+----+----------+------+|1|jingyong|NULL||2|yeyali|NULL||3|huangyan|NULL||4|wenyao|NULL|+----+----------+------+4rowsinset(0.00sec)mysql>deletefromtwhereid=4;QueryOK,1rowaffected(0.13sec)mysql>select*fromt;+----+----------+------+|id|name|date|+----+----------+------+|1|jingyong|NULL||2|yeyali|NULL||3|huangyan|NULL|+----+----------+------+3rowsinset(0.01sec)

我们需要找到插入这条记录在日志文件中的开始与结束的位置

#at3306211#19112211:04:34serverid1end_log_pos3306323CRC320x88f89864Querythread_id=11exec_time=0error_code=0SETTIMESTAMP=1574391874/*!*/;insertintotvalue(4,'wenyao',NULL)/*!*/;#at3306323#19112211:04:34serverid1end_log_pos3306354CRC320x966500deXid=1041COMMIT/*!*/;#at3306354#19112211:07:26serverid1end_log_pos3306419CRC320x1f3e6e28Anonymous_GTIDlast_committed=160sequence_number=161rbr_only=noSET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;#at3306419#19112211:07:26serverid1end_log_pos3306500CRC320x883ecef4Querythread_id=11exec_time=0error_code=0SETTIMESTAMP=1574392046/*!*/;BEGIN/*!*/;#at3306500#19112211:07:26serverid1end_log_pos3306600CRC320xecae0a57Querythread_id=11exec_time=0error_code=0SETTIMESTAMP=1574392046/*!*/;deletefromtwhereid=4

从上面的日志文件内容可以看到插入的开始位置为3306211，结束位置为3306323

现在当前备份的二进制日志文件名为binlog.000001，重新执行事件的命令如下:

[mysql@localhost~]$mysqlbinlog--start-position=3306211--stop-position=3306323binlog.000001|mysql--host=192.168.1.250--port=3306-uroot-pabcdmysqlmysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.

现在检查mysql.t表中的记录，可以看到被删除的这条记录恢复了。

mysql>select*fromt;+----+----------+------+|id|name|date|+----+----------+------+|1|jingyong|NULL||2|yeyali|NULL||3|huangyan|NULL||4|wenyao|NULL|+----+----------+------+4rowsinset(0.00sec)

设置msyqlbinlog 服务器ID
在使用--read-from-remote-server选项来调用mysqlbinlog时，mysqlbinlog会连接到一个MySQL服务器，指定了一个服务器ID来标识它并且从该服务器获取所需要的二进制日志文件。可以使用mysqlbinlog以以下几种方式来从服务器中获取日志文件:
.对文件集指定显式的名字。对每个文件，mysqlbinlog会执行连接操作并执行binlog dump命令。服务器会发送文件并断开连接。每个文件都有一个连接。

.指定开始文件与--to-last-log选项,mysqlbinlog会执行连接并对所有的日志文件执行binlog dump命令。服务器会发送所有日志文件并断开连接

.指定开始文件与--stop-never选项(隐式实现--to-last-log选项的功能),mysqlbinlog会执行连接并对所有日志文件执行binlog dump命令。服务器会发送所有日志文件，但在发送最后一个日志文件后不会断开与服务器的连接。

只有使用--read-from-remote-server选项时，mysqlbinlog使用一个为0的server ID进行连接，它将告诉服务器在发送所请求的日志文件后断开连接。

使用--read-from-remote-server与--stop-never选项时，mysqlbinlog将使用一个非0的server ID进行连接，因此在最后的日志文件发送之后服务器不会断开连接。缺省的server ID为65535，但这个可以通过使用--stop-never-slave-server-id选项来修改。

因此，对于使用前两种方式来获取日志文件人，因为mysqlbinlog指定的server ID为0，所有服务器会断开连接，如果--stop-never选项被指定因为mysqlbinlog指定一个非0的server ID，所以服务器将不会断开连接。