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

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

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

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

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

[root@localhost data]# pwd/usr/local/mysql/data[root@localhost data]# lsauto.cnf        ibdata1      ib_logfile1  mysql               sysib_buffer_pool  ib_logfile0  ibtmp1       performance_schema

这里主要讲到两种日志

1. 错误日志

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

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

开启错误日志的操作：

[root@localhost data]# vim /etc/my.cnf       //编辑配置文件在[mysqld]下添加：log-error=/usr/local/mysql/data/mysql_error.log[root@localhost data]# systemctl restart mysqld.service        //重启服务[root@localhost data]# ls      //查看文件是否产生日志文件auto.cnf        ibdata1      ib_logfile1  mysql            performance_schemaib_buffer_pool  ib_logfile0  ibtmp1       mysql_error.log  sysmysql> show variables like '%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                                   |+---------------------+---------------------------------------+7 rows in set (0.01 sec)

错误日志的清理：[root@localhost data]# lsauto.cnf        ibdata1      ib_logfile1  mysql            performance_schemaib_buffer_pool  ib_logfile0  ibtmp1       mysql_error.log  sys[root@localhost data]# mv mysql_error.log mysql_olderror.log[root@localhost data]# mysqladmin -uroot -pabc123 flush-logsmysqladmin: [Warning] Using a password on the command line interface can be insecure.

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@localhost data]# vim /etc/my.cnf       //编辑配置文件再[mysqld]下添加：log_bin=mysql-bin[root@localhost data]# systemctl restart mysqld.service        //重启服务

二进制日志状态查看

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

mysql> show variables like 'log_bin';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin       | ON    |+---------------+-------+1 row in set (0.00 sec)

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

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

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

mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 |      154 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

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

mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 |      154 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)mysql> flush logs;Query OK, 0 rows affected (0.01 sec)mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000002 |      154 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

查看第一个binlog文件的内容

mysql> show binlog events;+------------------+-----+----------------+-----------+-------------+---------------------------------------+| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |+------------------+-----+----------------+-----------+-------------+---------------------------------------+| mysql-bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.17-log, Binlog ver: 4 || mysql-bin.000001 | 123 | Previous_gtids |         1 |         154 |                                       || mysql-bin.000001 | 154 | Rotate         |         1 |         201 | mysql-bin.000002;pos=4                |+------------------+-----+----------------+-----------+-------------+---------------------------------------+3 rows in set (0.00 sec)

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

mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000002 |      154 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)mysql> flush logs;Query OK, 0 rows affected (0.01 sec)mysql> create database mood;Query OK, 1 row affected (0.00 sec)mysql> use mood;Database changedmysql> create table info (id int primary key auto_increment,name char(10) not null);Query OK, 0 rows affected (0.00 sec)mysql> insert into info (name)values('lisi');Query OK, 1 row affected (0.01 sec)mysql> select *from info;+----+------+| id | name |+----+------+|  1 | lisi |+----+------+1 row in set (0.00 sec)mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000003 |      791 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)mysql> show binlog events in 'mysql-bin.000003';+------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------------+| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                     |+------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------------+| mysql-bin.000003 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.17-log, Binlog ver: 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 | create database mood                                                                     || mysql-bin.000003 | 313 | Anonymous_Gtid |         1 |         378 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                     || mysql-bin.000003 | 378 | Query          |         1 |         528 | use `mood`; create table info (id int primary key auto_increment,name char(10) not null) || 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: 219 flags: STMT_END_F                                                          || mysql-bin.000003 | 760 | Xid            |         1 |         791 | COMMIT /* xid=23 */                                                                      |+------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------------+11 rows in set (0.00 sec)

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

[root@localhost ~]# cd /usr/local/mysql/data/          //想进入data目录下[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -vmysql-bin.000003 //64位解码查看二进制日志/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4#180903 21:35:36 server id 1  end_log_pos 123 CRC32 0x42461cf1     Start: binlog v 4, server v 5.7.17-log created 180903 21:35:36# Warning: this binlog is either in use or was not closed properly.# at 123#180903 21:35:36 server id 1  end_log_pos 154 CRC32 0xec3d8563     Previous-GTIDs# [empty]# at 154#180903 21:37:18 server id 1  end_log_pos 219 CRC32 0xbc79d089     Anonymous_GTID last_committed=0    sequence_number=1SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 219#180903 21:37:18 server id 1  end_log_pos 313 CRC32 0xf7bec480     Query    thread_id=3    exec_time=0    error_code=0SET TIMESTAMP=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/*!*/;/*!\C utf8 *//*!*/;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/*!*/;create database mood/*!*/;# at 313#180903 21:37:26 server id 1  end_log_pos 378 CRC32 0x23338ada     Anonymous_GTID last_committed=1    sequence_number=2SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 378#180903 21:37:26 server id 1  end_log_pos 528 CRC32 0x2abda936     Query    thread_id=3    exec_time=0    error_code=0use `mood`/*!*/;SET TIMESTAMP=1535981846/*!*/;create table info (id int primary key auto_increment,name char(10) not null)/*!*/;# at 528#180903 21:37:51 server id 1  end_log_pos 593 CRC32 0xfb0a8540     Anonymous_GTID last_committed=2    sequence_number=3SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 593#180903 21:37:51 server id 1  end_log_pos 665 CRC32 0x092ccb69     Query    thread_id=3    exec_time=0    error_code=0SET TIMESTAMP=1535981871/*!*/;BEGIN/*!*/;# at 665#180903 21:37:51 server id 1  end_log_pos 715 CRC32 0x7bc30dc3     Table_map: `mood`.`info` mapped to number 219# at 715#180903 21:37:51 server id 1  end_log_pos 760 CRC32 0x634de617     Write_rows: table id 219 flags: STMT_END_F### INSERT INTO `mood`.`info`### SET###   @1=1###   @2='lisi'# at 760#180903 21:37:51 server id 1  end_log_pos 791 CRC32 0x2b8944b4     Xid = 23COMMIT/*!*/;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*/;

增量备份

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

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> show databases;+--------------------+| Database           |+--------------------+| information_schema || mood               || mysql              || performance_schema || sys                |+--------------------+5 rows in set (0.00 sec)mysql> use moodDatabase changedmysql> show tables;+----------------+| Tables_in_mood |+----------------+| info           |+----------------+1 row in set (0.00 sec)mysql> select *from info;+----+------+| id | name |+----+------+|  1 | lisi |+----+------+1 row in set (0.00 sec)mysql> show binary logs;+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000001 |       201 || mysql-bin.000002 |       201 || mysql-bin.000003 |       791 |+------------------+-----------+3 rows in set (0.00 sec)

首先进行一个完全备份[root@localhost data]# mysqldump -uroot -p mood > /opt/mood.sqlEnter password:

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

[root@localhost data]# mysqladmin -uroot -p flush-logsEnter password: [root@localhost data]# lsauto.cnf        ib_logfile1  mysql-bin.000001  mysql-bin.index     sysib_buffer_pool  ibtmp1       mysql-bin.000002  mysql_error.logibdata1         mood         mysql-bin.000003  mysql_olderror.logib_logfile0     mysql        mysql-bin.000004  performance_schema

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

mysql> use moodDatabase changedmysql> insert into info (name)values('test01');Query OK, 1 row affected (0.00 sec)mysql> delete from info where name='lisi';Query OK, 1 row affected (0.01 sec)mysql> insert into info (name)values('test02');Query OK, 1 row affected (0.00 sec)mysql> select *from info;+----+--------+| id | name   |+----+--------+|  2 | test01 ||  3 | test02 |+----+--------+2 rows in set (0.00 sec)

再次刷新日志

[root@localhost data]# mysqladmin -u root -p flush-logsEnter password:

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

[root@localhost data]# lsauto.cnf        ibtmp1            mysql-bin.000003  mysql_olderror.logib_buffer_pool  mood              mysql-bin.000004  performance_schemaibdata1         mysql             mysql-bin.000005  sysib_logfile0     mysql-bin.000001  mysql-bin.indexib_logfile1     mysql-bin.000002  mysql_error.log[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000004 > /opt/info.txt

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

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

寻找位置如图：

下面示范两种跳过方式

1.通过时间识别

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

mysql> use moodDatabase changedmysql> drop table info;Query OK, 0 rows affected (0.01 sec)mysql> show tables;Empty set (0.00 sec)mysql> source /opt/mood.sqlQuery OK, 0 rows affected (0.00 sec)mysql> select *from info;+----+------+| id | name |+----+------+|  1 | lisi |+----+------+1 row in set (0.00 sec)

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

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

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

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

[root@localhost opt]# mysqlbinlog --no-defaults --stop-datetime='2018-09-03 22:18:03' /usr/local/mysql/data/mysql-bin.000004 | mysql -uroot -pEnter password: [root@localhost opt]# mysql -uroot -pabc123 -e 'use mood;select *from info;'mysql: [Warning] Using a password on the command line interface can be insecure.+----+--------+| id | name   |+----+--------+|  1 | lisi   ||  2 | test01 |+----+--------+[root@localhost opt]# mysqlbinlog --no-defaults --start-datetime='2018-09-03 22:18:09' /usr/local/mysql/data/mysql-bin.000004 | mysql -uroot -pEnter password: [root@localhost opt]# mysql -uroot -pabc123 -e 'use mood;select *from info;'mysql: [Warning] Using a password on the command line interface can be insecure.+----+--------+| id | name   |+----+--------+|  1 | lisi   ||  2 | test01 ||  3 | test02 |+----+--------+

2.通过位置操作

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

mysql> use moodDatabase changedmysql> drop table info;Query OK, 0 rows affected (0.01 sec)mysql> show tables;Empty set (0.00 sec)mysql> source /opt/mood.sqlQuery OK, 0 rows affected (0.00 sec)mysql> select *from info;+----+------+| id | name |+----+------+|  1 | lisi |+----+------+1 row in set (0.00 sec)

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

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

即：

556-606-651

那我们只要跳过606即可！

操作如下：

[root@localhost opt]# mysqlbinlog --no-defaults --stop-position='556' /usr/local/mysql/data/mysql-bin.000004 | mysql -uroot -pabc123mysql: [Warning] Using a password on the command line interface can be insecure.[root@localhost opt]# mysqlbinlog --no-defaults --start-position='651' /usr/local/mysql/data/mysql-bin.000004 | mysql -uroot -pabc123mysql: [Warning] Using a password on the command line interface can be insecure.[root@localhost opt]# mysql -uroot -pabc123 -e 'use mood;select *from info;'mysql: [Warning] Using a password on the command line interface can be insecure.+----+--------+| id | name   |+----+--------+|  1 | lisi   ||  2 | test01 ||  3 | test02 |+----+--------+

完成！

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