mysqldump+mysqlbinlog执行备份与还原

举报
eric0435 发表于 2022/02/17 10:15:12 2022/02/17
【摘要】 服务器的二进制日志文件由用来描述修改数据库内容的事件组成。服务器以二进制方式来写这些文件。为了以文本格式来显示这些内容，可以使用mysqlbinlog工具。也可以使用mysqlbinlog来显示在复制环境中由从从slave服务器所写入中relay日志文件中的内容，因为其格式与二进制日志文件格式一样。mysqlbinlog的使用语法如下:Usage: mysqlbinlog [options]...

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

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

[mysql@localhost ~]$ mysqlbinlog /mysqldata/mysql/binlog.000001
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1/;
/!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/;
DELIMITER /!/;

at 4

#191115 15:39:01 server id 1 end_log_pos 123 CRC32 0x2d9d7b4f Start: binlog v 4, server v 5.7.26-log created 191115 15:39:01 at startup

Warning: this binlog is either in use or was not closed properly.

ROLLBACK/!/;
BINLOG ’
FVbOXQ8BAAAAdwAAAHsAAAABAAQANS43LjI2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAVVs5dEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AU97nS0=
'/!/;

at 123

#191115 15:39:01 server id 1 end_log_pos 154 CRC32 0x42dcd61c Previous-GTIDs

[empty]

at 154

#191115 15:51:15 server id 1 end_log_pos 219 CRC32 0x5bc0b021 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’/!/;

at 219

#191115 15:51:15 server id 1 end_log_pos 308 CRC32 0x7261eacb Query thread_id=2 exec_time=0 error_code=0
use mysql/!/;
SET TIMESTAMP=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/!/;
/!\C gb2312 //!/;
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/!/;
truncate table person
/!/;

at 308

#191115 15:51:38 server id 1 end_log_pos 373 CRC32 0x6d2e39aa Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’/!/;

at 373

#191115 15:51:38 server id 1 end_log_pos 454 CRC32 0x7871c2ea Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1573804298/!/;
BEGIN
/!/;

at 454

at 486

#191115 15:51:38 server id 1 end_log_pos 486 CRC32 0xb746cd30 Intvar
SET INSERT_ID=1/!/;
#191115 15:51:38 server id 1 end_log_pos 654 CRC32 0x0e926042 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1573804298/!/;
insert into person(last_name,first_name,birth,death) values(‘yong’,‘jing’,‘1985-02-28’,null)
/!/;

at 654

#191115 15:51:38 server id 1 end_log_pos 736 CRC32 0xc5450308 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1573804298/!/;
COMMIT
/!/;

at 736

#191115 15:51:45 server id 1 end_log_pos 801 CRC32 0xc2c892b8 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’/!/;

at 801

#191115 15:51:45 server id 1 end_log_pos 882 CRC32 0x51a9cd5c Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1573804305/!/;
BEGIN
/!/;

at 882

at 914

#191115 15:51:45 server id 1 end_log_pos 914 CRC32 0x40a98fae Intvar
SET INSERT_ID=2/!/;
#191115 15:51:45 server id 1 end_log_pos 1082 CRC32 0x3396c40d Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1573804305/!/;
insert into person(last_name,first_name,birth,death) values(‘yan’,‘huang’,‘1990-08-25’,null)
/!/;

at 1082

#191115 15:51:45 server id 1 end_log_pos 1164 CRC32 0xf6f6efad Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1573804305/!/;
COMMIT
/!/;

at 1164

#191115 15:51:53 server id 1 end_log_pos 1229 CRC32 0x55b50dbe Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=no
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’/!/;

at 1229

#191115 15:51:53 server id 1 end_log_pos 1310 CRC32 0xd0f6a335 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1573804313/!/;
BEGIN
/!/;

at 1310

at 1342

#191115 15:51:53 server id 1 end_log_pos 1342 CRC32 0xfad94baf Intvar
SET INSERT_ID=3/!/;
#191115 15:51:53 server id 1 end_log_pos 1508 CRC32 0x26c5b3bb Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1573804313/!/;
insert into person(last_name,first_name,birth,death) values(‘yali’,‘ye’,‘1994-12-23’,null)
/!/;

at 1508

#191115 15:51:53 server id 1 end_log_pos 1590 CRC32 0xbb6a2b4c Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1573804313/!/;
COMMIT
/!/;
SET @@SESSION.GTID_NEXT= ‘AUTOMATIC’ /* added by mysqlbinlog / /!*/;
DELIMITER ;

End of log file

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

at 486

#191115 15:51:38 server id 1 end_log_pos 486 CRC32 0xb746cd30 Intvar SET INSERT_ID=1/!/;
#191115 15:51:38 server id 1 end_log_pos 654 CRC32 0x0e926042 Query thread_id=2
exec_time=0 error_code=0 SET TIMESTAMP=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语句来查看当前的日志文件名。

mysql> show binary logs;
±--------------±----------+
| Log_name | File_size |
±--------------±----------+
| binlog.000001 | 2530 |
±--------------±----------+
1 row in set (0.00 sec)
使用这些信息可以使用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 --raw binlog.000001 --result-file=jy_
[mysql@localhost ~]$ ls -lrt
-rw-r-----. 1 mysql mysql 2530 11月 22 10:24 jy_binlog.000001
可以看到备份的日志文件为以jy_为前缀，其文件名为jy_binlog.000001

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

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

mysql> show binary logs;
±--------------±----------+
| Log_name | File_size |
±--------------±----------+
| binlog.000001 | 2530 |
±--------------±----------+
1 row in set (0.01 sec)
使用mysqlbinlog来对二进制日志文件执行连续备份:

[mysql@localhost ~]$ mysqlbinlog --read-from-remote-server --host=192.168.1.250 --raw --stop-never binlog.000001

[mysql@localhost ~]$ ls -lrt

-rw-r-----. 1 mysql mysql 2530 11月 22 10:38 binlog.000001
创建了一个名为t的测试表并插入了三行记录

mysql> select * from t;
±—±---------±-----+
| id | name | date |
±—±---------±-----+
| 1 | jingyong | NULL |
| 2 | yeyali | NULL |
| 3 | huangyan | NULL |
±—±---------±-----+
3 rows in set (0.00 sec)
使用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.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[mysql@localhost ~]$ ls -lrt
-rw-r–r--. 1 mysql mysql 3290497 11月 22 10:51 dump_mysql.sql
现在删除mysql库中的表t

mysql> drop table t;
Query OK, 0 rows affected (0.18 sec)

mysql> desc t;
ERROR 1146 (42S02): Table ‘mysql.t’ doesn’t exist
现在假设mysql库中的表t丢失了，使用最近的dump文件来还原数据:

[mysql@localhost ~]$ mysql --host=192.168.1.250 --port=3306 -uroot -pabcd mysql
还原数据后mysql.t表就恢复了

mysql> desc t;
±------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| id | int(11) | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
| date | date | YES | | NULL | |
±------±------------±-----±----±--------±------+
3 rows in set (0.01 sec)

mysql> select * from t;
±—±---------±-----+
| id | name | date |
±—±---------±-----+
| 1 | jingyong | NULL |
| 2 | yeyali | NULL |
| 3 | huangyan | NULL |
±—±---------±-----+
3 rows in set (0.00 sec)
现在向mysql.t表中插入一条记录并删除这条记录,然后使用备份的二进制日志文件来重新执行事件来恢复这条记录

mysql> insert into t value(4,‘wenyao’,NULL);
Query OK, 1 row affected (0.03 sec)

mysql> select * from t;
±—±---------±-----+
| id | name | date |
±—±---------±-----+
| 1 | jingyong | NULL |
| 2 | yeyali | NULL |
| 3 | huangyan | NULL |
| 4 | wenyao | NULL |
±—±---------±-----+
4 rows in set (0.00 sec)

mysql> delete from t where id=4;
Query OK, 1 row affected (0.13 sec)

mysql> select * from t;
±—±---------±-----+
| id | name | date |
±—±---------±-----+
| 1 | jingyong | NULL |
| 2 | yeyali | NULL |
| 3 | huangyan | NULL |
±—±---------±-----+
3 rows in set (0.01 sec)
我们需要找到插入这条记录在日志文件中的开始与结束的位置

at 3306211

#191122 11:04:34 server id 1 end_log_pos 3306323 CRC32 0x88f89864 Query thread_id=11 exec_time=0 error_code=0
SET TIMESTAMP=1574391874/!/;
insert into t value(4,‘wenyao’,NULL)
/!/;

at 3306323

#191122 11:04:34 server id 1 end_log_pos 3306354 CRC32 0x966500de Xid = 1041
COMMIT/!/;

at 3306354

#191122 11:07:26 server id 1 end_log_pos 3306419 CRC32 0x1f3e6e28 Anonymous_GTID last_committed=160 sequence_number=161 rbr_only=no
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’/!/;

at 3306419

#191122 11:07:26 server id 1 end_log_pos 3306500 CRC32 0x883ecef4 Query thread_id=11 exec_time=0 error_code=0
SET TIMESTAMP=1574392046/!/;
BEGIN
/!/;

at 3306500

#191122 11:07:26 server id 1 end_log_pos 3306600 CRC32 0xecae0a57 Query thread_id=11 exec_time=0 error_code=0
SET TIMESTAMP=1574392046/!/;
delete from t where id=4
从上面的日志文件内容可以看到插入的开始位置为3306211，结束位置为3306323

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

[mysql@localhost ~]$ mysqlbinlog --start-position=3306211 --stop-position=3306323 binlog.000001 | mysql --host=192.168.1.250 --port=3306 -uroot -pabcd mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
现在检查mysql.t表中的记录，可以看到被删除的这条记录恢复了。

mysql> select * from t;
±—±---------±-----+
| id | name | date |
±—±---------±-----+
| 1 | jingyong | NULL |
| 2 | yeyali | NULL |
| 3 | huangyan | NULL |
| 4 | wenyao | NULL |
±—±---------±-----+
4 rows in set (0.00 sec)
设置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，所以服务器将不会断开连接。

【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
MySQL
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入