MySQL基操---深入浅出增量断点备份与日志管理

2025-04-08 技术教程

-----------------------------日志-----------------------------------

MySQL的日志类型有以下几种：

1.错误日志（error）,MySQL服务实例启动、运行或者停止等相关信息。2.普通查询日志（general）,MySQL服务实例运行的所有SQL语句或者MySQL命令。3.二进制日志（binary）,对数据库执行的所有更新语句，不包括select和show语句。4.慢查询日志（slow），执行时间超过long_query_time设置值的SQL语句，或者没有使用索引的SQL语句。

默认情况下，所有的MySQL日志以文件的方式存放在数据库根目录下：

[root@localhostdata]#pwd/usr/local/mysql/data[root@localhostdata]#lsauto.cnfibdata1ib_logfile1mysqlsysib_buffer_poolib_logfile0ibtmp1performance_schema

这里主要讲到两种日志

1. 错误日志

错误日志记录着mysqld启动和停止,以及服务器在运行过程中发生的错误的相关信息。在默认情况下，系统记录错误日志的功能是关闭的，错误信息被输出到标准错误输出。需要在启动的时候开启 log-error选项

如果没有指定文件名默认hostname.err，默认路径为datadir目录

开启错误日志的操作：

[root@localhostdata]#vim/etc/my.cnf//编辑配置文件在[mysqld]下添加：log-error=/usr/local/mysql/data/mysql_error.log[root@localhostdata]#systemctlrestartmysqld.service//重启服务[root@localhostdata]#ls//查看文件是否产生日志文件auto.cnfibdata1ib_logfile1mysqlperformance_schemaib_buffer_poolib_logfile0ibtmp1mysql_error.logsysmysql>showvariableslike'%err%';//查看错误日志状态+---------------------+---------------------------------------+|Variable_name|Value|+---------------------+---------------------------------------+|binlog_error_action|ABORT_SERVER||error_count|0||log_error|/usr/local/mysql/data/mysql_error.log||log_error_verbosity|3||max_connect_errors|100||max_error_count|64||slave_skip_errors|OFF|+---------------------+---------------------------------------+7rowsinset(0.01sec)

错误日志的清理：[root@localhostdata]#lsauto.cnfibdata1ib_logfile1mysqlperformance_schemaib_buffer_poolib_logfile0ibtmp1mysql_error.logsys[root@localhostdata]#mvmysql_error.logmysql_olderror.log[root@localhostdata]#mysqladmin-uroot-pabc123flush-logsmysqladmin:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.

2.二进制日志(重点!)

二进制日志不会记录select ，show 等不修改数据的语句。打开二进制日志会消耗一些系统系能，但是对于复制和系统恢复，所带来的好处大于减少的性能，它用来实现复制的基本凭据。也就是说，你可以将生成环境中的MySQL的二进制文件拿到线下的服务器上运行一下，理论上你会拿到和生成环境中一样的数据，因此，二进制日志也叫复制日志。二进制日志文件默认在数据目录下，通常情况下为mysql-bin#（例如：mysql-bin.000001，mysql-bin000002）。二进制日志即binlog日志记录数据定义语言(DDL)和数据控制语言(DML) 但不包括数据查询语言。

二进制日志的主要功能

1、恢复（recovery）

2、复制（replication）

二进制日志文件内容格式

1.事件发生的日期和时间（会在关键字“at”）
2.服务器ID（server id）
3.事件结束位置（end_log_pos）
4.事件的类型（如：Query，Stop等等）
5.原服务器生成此事件时的线程ID号（thead_id，可以通过“show processlist;”进行查询）
6.语句时间戳和写入二进制文件的时间差，单位为秒（exec_time，表示记录日志所用的时间戳，当他等于0时表示没有用到1秒钟。）
7.错误代码，0表示正常执行（error_code,排查方法就得查看官方文档。）
8.事件内容（修改的SQL语句）
9.事件位置（相当于下一事件的开始位置，还是用“at”关键字标志）

开启二进制日志

[root@localhostdata]#vim/etc/my.cnf//编辑配置文件再[mysqld]下添加：log_bin=mysql-bin[root@localhostdata]#systemctlrestartmysqld.service//重启服务

二进制日志状态查看

系统变量log_bin的值为OFF表示没有开启二进制日志（binary log）。ON表示开启了二进制日志（binary log）

查看当前服务器所有的二进制日志文件

查看当前二进制日志文件状态

为了避免一个文件过大，我们可以适当的将文件的内容分开存储，这就是日志滚动，比如：当超过1G，日志会滚动。当然，你也可以按照文件大小自定义，时间定义。想要手动滚动日志，执行“flush logs;”即可。

查看第一个binlog文件的内容

mysql>showbinlogevents;+------------------+-----+----------------+-----------+-------------+---------------------------------------+|Log_name|Pos|Event_type|Server_id|End_log_pos|Info|+------------------+-----+----------------+-----------+-------------+---------------------------------------+|mysql-bin.000001|4|Format_desc|1|123|Serverver:5.7.17-log,Binlogver:4||mysql-bin.000001|123|Previous_gtids|1|154|||mysql-bin.000001|154|Rotate|1|201|mysql-bin.000002;pos=4|+------------------+-----+----------------+-----------+-------------+---------------------------------------+3rowsinset(0.00sec)

查看某个特定binglog文件的内容

mysql>showmasterstatus;+------------------+----------+--------------+------------------+-------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|+------------------+----------+--------------+------------------+-------------------+|mysql-bin.000002|154||||+------------------+----------+--------------+------------------+-------------------+1rowinset(0.00sec)mysql>flushlogs;QueryOK,0rowsaffected(0.01sec)mysql>createdatabasemood;QueryOK,1rowaffected(0.00sec)mysql>usemood;Databasechangedmysql>createtableinfo(idintprimarykeyauto_increment,namechar(10)notnull);QueryOK,0rowsaffected(0.00sec)mysql>insertintoinfo(name)values('lisi');QueryOK,1rowaffected(0.01sec)mysql>select*frominfo;+----+------+|id|name|+----+------+|1|lisi|+----+------+1rowinset(0.00sec)mysql>showmasterstatus;+------------------+----------+--------------+------------------+-------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|+------------------+----------+--------------+------------------+-------------------+|mysql-bin.000003|791||||+------------------+----------+--------------+------------------+-------------------+1rowinset(0.00sec)mysql>showbinlogeventsin'mysql-bin.000003';+------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------------+|Log_name|Pos|Event_type|Server_id|End_log_pos|Info|+------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------------+|mysql-bin.000003|4|Format_desc|1|123|Serverver:5.7.17-log,Binlogver:4||mysql-bin.000003|123|Previous_gtids|1|154|||mysql-bin.000003|154|Anonymous_Gtid|1|219|SET@@SESSION.GTID_NEXT='ANONYMOUS'||mysql-bin.000003|219|Query|1|313|createdatabasemood||mysql-bin.000003|313|Anonymous_Gtid|1|378|SET@@SESSION.GTID_NEXT='ANONYMOUS'||mysql-bin.000003|378|Query|1|528|use`mood`;createtableinfo(idintprimarykeyauto_increment,namechar(10)notnull)||mysql-bin.000003|528|Anonymous_Gtid|1|593|SET@@SESSION.GTID_NEXT='ANONYMOUS'||mysql-bin.000003|593|Query|1|665|BEGIN||mysql-bin.000003|665|Table_map|1|715|table_id:219(mood.info)||mysql-bin.000003|715|Write_rows|1|760|table_id:219flags:STMT_END_F||mysql-bin.000003|760|Xid|1|791|COMMIT/*xid=23*/|+------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------------+11rowsinset(0.00sec)

使用mysqlbinlog命令如何查看二进制日志文件中的的内容

[root@localhost~]#cd/usr/local/mysql/data///想进入data目录下[root@localhostdata]#mysqlbinlog--no-defaults--base64-output=decode-rows–vmysql-bin.000003//64位解码查看二进制日志/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER/*!*/;#at4#18090321:35:36serverid1end_log_pos123CRC320x42461cf1Start:binlogv4,serverv5.7.17-logcreated18090321:35:36#Warning:thisbinlogiseitherinuseorwasnotclosedproperly.#at123#18090321:35:36serverid1end_log_pos154CRC320xec3d8563Previous-GTIDs#[empty]#at154#18090321:37:18serverid1end_log_pos219CRC320xbc79d089Anonymous_GTIDlast_committed=0sequence_number=1SET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;#at219#18090321:37:18serverid1end_log_pos313CRC320xf7bec480Querythread_id=3exec_time=0error_code=0SETTIMESTAMP=1535981838/*!*/;SET@@session.pseudo_thread_id=3/*!*/;SET@@session.foreign_key_checks=1,@@session.sql_auto_is_null=0,@@session.unique_checks=1,@@session.autocommit=1/*!*/;SET@@session.sql_mode=1437073414/*!*/;SET@@session.auto_increment_increment=1,@@session.auto_increment_offset=1/*!*/;/*!\Cutf8*//*!*/;SET@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;SET@@session.lc_time_names=0/*!*/;SET@@session.collation_database=DEFAULT/*!*/;createdatabasemood/*!*/;#at313#18090321:37:26serverid1end_log_pos378CRC320x23338adaAnonymous_GTIDlast_committed=1sequence_number=2SET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;#at378#18090321:37:26serverid1end_log_pos528CRC320x2abda936Querythread_id=3exec_time=0error_code=0use`mood`/*!*/;SETTIMESTAMP=1535981846/*!*/;createtableinfo(idintprimarykeyauto_increment,namechar(10)notnull)/*!*/;#at528#18090321:37:51serverid1end_log_pos593CRC320xfb0a8540Anonymous_GTIDlast_committed=2sequence_number=3SET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;#at593#18090321:37:51serverid1end_log_pos665CRC320x092ccb69Querythread_id=3exec_time=0error_code=0SETTIMESTAMP=1535981871/*!*/;BEGIN/*!*/;#at665#18090321:37:51serverid1end_log_pos715CRC320x7bc30dc3Table_map:`mood`.`info`mappedtonumber219#at715#18090321:37:51serverid1end_log_pos760CRC320x634de617Write_rows:tableid219flags:STMT_END_F###INSERTINTO`mood`.`info`###SET###@1=1###@2='lisi'#at760#18090321:37:51serverid1end_log_pos791CRC320x2b8944b4Xid=23COMMIT/*!*/;SET@@SESSION.GTID_NEXT='AUTOMATIC'/*addedbymysqlbinlog*//*!*/;DELIMITER;#Endoflogfile/*!50003SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=0*/;

增量备份

增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加或者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。这种备份方式最显著的优点就是：没有重复的备份数据，因此备份的数据量不大，备份所需的时间很短。但增量备份的数据恢复是比较麻烦的。您必须具有上一次全备份和所有增量备份磁带（一旦丢失或损坏其中的一个增量，就会造成恢复的失败），并且它们必须沿着从全备份到依次增量备份的时间顺序逐个反推恢复，因此这就极大地延长了恢复时间。

MYSQL数据库在使用过程中，一般都需要对使用的数据库进行备份处理，对于数据量较小时可以通过mysqldump命令进行数据库全备份，但是当数据库数据量达到一定程度之后，显然增量备份更加适合。假如我们有一个数据库，有20G的数据，每天会增加10M的数据，数据库每天都要全量备份一次，这样的话服务器的压力比较大，因此我们只需要备份增加的这部分数据，这样减少服务器的负担。

2、binlog简介

binlog日志由配置文件的 log-bin 选项负责启用，MySQL服务器将在数据根目录创建两个新文件XXX-bin.001和xxx-bin.index，若配置选项没有给出文件名，Mysql将使用主机名称命名这两个文件，其中.index文件包含一份全体日志文件的清单。

Mysql会把用户对所有数据库的内容和结构的修改情况记入XXX-bin.n文件，而不会记录 SELECT和没有实际更新的UPDATE语句。
当MySQL数据库停止或重启时，服务器会把日志文件记入下一个日志文件，Mysql会在重启时生成一个新的binlog日志文件，文件序号递增，此外，如果日志文件超过max_binlog_size系统变量配置的上限时，也会生成新的日志文件。

mysqlbinlog的常用的[options]：1--start-time#起始时间2--stop-time#结束时间3--start-position#基于起始位置来显示信息4--stop-position#指定结束位置来显示

下面做一个简单的断点恢复增量备份实例：

先看下基本环境

mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||mood||mysql||performance_schema||sys|+--------------------+5rowsinset(0.00sec)mysql>usemoodDatabasechangedmysql>showtables;+----------------+|Tables_in_mood|+----------------+|info|+----------------+1rowinset(0.00sec)mysql>select*frominfo;+----+------+|id|name|+----+------+|1|lisi|+----+------+1rowinset(0.00sec)mysql>showbinarylogs;+------------------+-----------+|Log_name|File_size|+------------------+-----------+|mysql-bin.000001|201||mysql-bin.000002|201||mysql-bin.000003|791|+------------------+-----------+3rowsinset(0.00sec)

首先进行一个完全备份[root@localhostdata]#mysqldump-uroot-pmood>/opt/mood.sqlEnterpassword:

刷新日志生成尾号为000004的新二进制日志

[root@localhostdata]#mysqladmin-uroot-pflush-logsEnterpassword:[root@localhostdata]#lsauto.cnfib_logfile1mysql-bin.000001mysql-bin.indexsysib_buffer_poolibtmp1mysql-bin.000002mysql_error.logibdata1moodmysql-bin.000003mysql_olderror.logib_logfile0mysqlmysql-bin.000004performance_schema

进入数据库模拟一个误操作，这里误操作为删除lisi这个条目。

mysql>usemoodDatabasechangedmysql>insertintoinfo(name)values('test01');QueryOK,1rowaffected(0.00sec)mysql>deletefrominfowherename='lisi';QueryOK,1rowaffected(0.01sec)mysql>insertintoinfo(name)values('test02');QueryOK,1rowaffected(0.00sec)mysql>select*frominfo;+----+--------+|id|name|+----+--------+|2|test01||3|test02|+----+--------+2rowsinset(0.00sec)

再次刷新日志

[root@localhostdata]#mysqladmin-uroot-pflush-logsEnterpassword:

看到data下生产了新的000005日志，但是我们刚刚的操作是写入了000004日志中，我们对00004日志进行64位解码并转存位一个txt文件便于我们查看。

[root@localhostdata]#lsauto.cnfibtmp1mysql-bin.000003mysql_olderror.logib_buffer_poolmoodmysql-bin.000004performance_schemaibdata1mysqlmysql-bin.000005sysib_logfile0mysql-bin.000001mysql-bin.indexib_logfile1mysql-bin.000002mysql_error.log[root@localhostdata]#mysqlbinlog--no-defaults--base64-output=decode-rows-vmysql-bin.000004>/opt/info.txt

再opt下vim查看生成出来的txt文件，也就是刚刚的000004日志。

找到那条误操作的命令，记录下它以及前后位置的时间和位置数据。

操作时间位置类型2018-09-03 22:15:31341正确操作一2018-09-03 22:18:03606误操作2018-09-03 22:18:09869正确操作二

寻找位置如图：

下面示范两种跳过方式

1.通过时间识别

首先还原到完全备份时的数据表状态。

mysql>usemoodDatabasechangedmysql>droptableinfo;QueryOK,0rowsaffected(0.01sec)mysql>showtables;Emptyset(0.00sec)mysql>source/opt/mood.sqlQueryOK,0rowsaffected(0.00sec)mysql>select*frominfo;+----+------+|id|name|+----+------+|1|lisi|+----+------+1rowinset(0.00sec)

跳过删除lisi那一步误操作进行增量备份还原。

第一条命令：恢复到22.18.03（错误发生时间点）为止！不执行错误时间点写入的命令，后面一切命令也不执行。

第二条命令：从22.18.09 （下一条正确命令时间点）开始！前面一切命令不执行。

这样我们就跳过了哪一条delete命令

[root@localhostopt]#mysqlbinlog--no-defaults--stop-datetime='2018-09-0322:18:03'/usr/local/mysql/data/mysql-bin.000004|mysql-uroot-pEnterpassword:[root@localhostopt]#mysql-uroot-pabc123-e'usemood;select*frominfo;'mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+----+--------+|id|name|+----+--------+|1|lisi||2|test01|+----+--------+[root@localhostopt]#mysqlbinlog--no-defaults--start-datetime='2018-09-0322:18:09'/usr/local/mysql/data/mysql-bin.000004|mysql-uroot-pEnterpassword:[root@localhostopt]#mysql-uroot-pabc123-e'usemood;select*frominfo;'mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+----+--------+|id|name|+----+--------+|1|lisi||2|test01||3|test02|+----+--------+

2.通过位置操作

还是还原到完全备份时的数据表状态。

回头看一下上面的表格，我们得知：

错误操作的位置号是606，它的上一个操作位置号是556，下一个操作位置号是651

即：

556-606-651

那我们只要跳过606即可！

操作如下：

[root@localhostopt]#mysqlbinlog--no-defaults--stop-position='556'/usr/local/mysql/data/mysql-bin.000004|mysql-uroot-pabc123mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@localhostopt]#mysqlbinlog--no-defaults--start-position='651'/usr/local/mysql/data/mysql-bin.000004|mysql-uroot-pabc123mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@localhostopt]#mysql-uroot-pabc123-e'usemood;select*frominfo;'mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+----+--------+|id|name|+----+--------+|1|lisi||2|test01||3|test02|+----+--------+

完成！

ps:warning提示的是把密码写在命令中并不安全，这里模拟试验图省事，大家实际操作还是不要写在命令中哦！