mysqldump+mysqlbinlog执行备份与还原

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

漯河ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景，ssl证书未来市场广阔！成为成都创新互联公司的ssl证书销售渠道，可以享受市场价格4-6折优惠！如果有意向欢迎电话联系或者加微信：13518219792（备注：SSL证书合作）期待与您的合作！

下面的命令用来显示名为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，所以服务器将不会断开连接。