Mysql实现企业级日志管理、备份与恢复的实战教程

原作者: [db:作者] 来自: [db:来源] 收藏邀请

背景

随着业务的发展，公司业务和规模不断扩大，网站积累了大量的用户信息和数据，对于一家互联网公司来说，用户和业务数据是根基。一旦公司的数据错乱或者丢失，对于互联网公司而言就等于说是灭顶之灾，为防止系统出现操作失误或系统故障导致数据丢失，公司要求加强用户数据的可靠性，要求全面加强数据层面备份，并能在故障发生时第一时间恢复。

数据备份形式

文件备份：

通过Linux的备份命令把文件统一打个包存起来，可存在本地和远程服务器，等到要恢复时，再用这些文件恢复到指定位置。

数据库数据备份：

在一些对数据可靠性要求很高的行业如银行、证券、电信等，如果发生意外停机或数据丢失其损失会十分惨重。为此数据库管理员应针对具体的业务要求制定详细的数据库备份与灾难恢复策略，并通过模拟故障对每

种可能的情况进行严格测试，只有这样才能保证数据的高可用性。数据库的备份是一个长期的过程，而恢复只在发生事故后进行，恢复可以看作是备份的逆过程，恢复的程度的好坏很大程度上依赖于备份的情况。此外，

数据库管理员在恢复时采取的步骤正确与否也直接影响最终的恢复结果。

数据备份类型

按业务划分：可分为完全备份、增量备份、差异备份

1、完全备份：就是对整个数据库的数据和数据结构进行备份

优点：直观，容易被人理解

缺点：1.备份的数据有大量的数据都是重复的，占用了大量的空间，增加成本

2.备份的数据量大，所需时间较长

（Full Backup）所谓全备份就是对整个数据库的数据和数据结构进行备份。这种备份方式的好处就是很直观，容易被人理解。而且当发生数据丢失的灾难时，只要用灾难之前的备份文件，就可以恢复丢失的数据。

然而它也有不足之处：首先由于每天都对系统进行完全备份,因此在备份数据中有大量是重复的。这些重复的数据占用了大量的空间，这对用户来说就意味着增加成本；其次，由于需要备份的数据量相当大，因此备份所需时间较长。对于那些业务繁忙，备份窗口时间有限的单位来说，选择这种备份策略无疑是不明智的。

2、增量备份（Incremental Backup）：每次备份的数据只是相当于上次备份后增加和修改过的数据。

优点：没有重复备份的数据，节省空间

缺点：恢复数据比较麻烦，其中任何一个备份数据出了问题都会导致数据丢失

就是每次备份的数据只是相当于上一次备份后增加的和修改过的数据。这种备份的优点很明显：没有重复的备份数据，即节省空间，又缩短了备份时间。但它的缺点在于当发生灾难时，恢复数据比较麻烦。举例来说，如

果系统在星期四的早晨发生故障，丢失大批数据，那么现在就需要将系统恢复到星期三晚上的状态。

这时管理员需要首先找出星期一的那份完全备份数据进行系统恢复，然后再找出星期二的数据来恢复星期二的数据，然后在找出星期三的数据来恢复星期三的数据。很明显这比第一种策略要麻烦得多。另外这种备份可靠

性也差。在这种备份下，各备份数据间的关系就象链子一样，一环套一环，其中任何一个备份数据出了问题都会导致整条链子脱节。

3、差异备份（Differential Backup）：每次备份的数据相当于上一次全备份之后新增加和修改过的数据。

就是每次备份的数据是相对于上一次全备份之后新增加的和修改过的数据。管理员先在星期一进行一次系统完全备份；然后在接下来的几天里，管理员再将当天所有与星期一不同的数据（新的或经改动的）备份到磁带上。举例来说，在星期一，网络管理员按惯例进行系统完全备份；在星期二，假设系统内只多了一个资产清单，于是管理员只需将这份资产清单一并备份下来即可；在星期三，系统内又多了一份产品目录，于是管理员

不仅要将这份目录，还要连同星期二的那份资产清单一并备份下来。

如果在星期四系统内有多了一张工资表，那么星期四需要备份的内容就是：工资表+产品目录+资产清单。由此可以看出，全备份所需时间最长，但恢复时间最短，操作最方便，当系统中数据量不大时，采用全备份最可靠；差异备份可避免另外两种策略缺陷，但不同备份类型可以存在一定组合不同备份类型可以存在一定组合不同备份类型可以存在一定组合不同备份类型可以存在一定组合。

不同备份类型组合应用的示例

完全备份和差异备份

在星期一进行完全备份，在星期二至星期五进行差异备份。如果在星期五数据被破坏了，则你只需要还原星期一完全的备份和星期四的差异备份。这种策略备份数据需要较多的时间，但还原数据使用较少的时间。

完全备份和增量备份

在星期一进行完全备份，在星期二至星期五进行增量备份。如果在星期五数据被破坏了，则你需要还原星期一正常的备份和从星期二至星期五的所有增量备份。这种策略备份数据需要较少的时间，但还原数据使用较多的时间。

按方式划分：可分为热备、温备、冷备

热备份（Hot Backup）是指在数据库运行中直接备份，对正在运行的数据库没有任何影响。

冷备份（Cold Backup）是指在数据库停止的情况下进行备份，这种备份最为简单，一般只需要拷贝相关的数据库物理文件即可。

温备份（Warm Backup）备份同样是在数据库运行时进行，但是会对当前数据库的操作有所影响，例如加一个全局读锁以保证备份数据的一致性。（当你备份数据库中的一个表时，先把这个表锁起来，不让别人来增查删改表中的数据，这样你在备份的时候，该表中的数据就不会发生变化，保证了备份数据的一致性）

物理备份：直接复制数据文件进行的备份（直接复制备份的数据文件是二进制格式的）

优点：无需额外工具，直接copy即可，恢复直接复制备份文件即可

缺点：与存储引擎有关，跨平台能力较弱

逻辑备份：从数据库中"导出"数据另存而进行的备份（将sql语句给导出到一个文本中，比二进制格式的文件大）

优点：能使用编辑器处理，恢复简单，能基于网络恢复，有助于避免数据损坏

缺点：备份文件较大，备份较慢，无法保证浮点数的精度，使用逻辑备份数据恢复后，还需手动重建索引，十分消耗CPU资源

备份流程图

Mysql日志介绍

MySQL日志：

主要包含：错误日志、查询日志、慢查询日志、事务日志、二进制日志等；

日志是mysql数据库的重要组成部分。日志文件中记录着mysql数据库运行期间发生的变化；也就是说用来记

录mysql数据库的客户端连接状况、SQL语句的执行情况和错误信息等。当数据库遭到意外的损坏时，可以通

过日志查看文件出错的原因，并且可以通过日志文件进行数据恢复。

Mysql错误日志

在mysql数据库中，错误日志功能是默认开启的。并且，错误日志无法被禁止。默认情况下，错误日志存储在mysql数据库的数据文件中。错误日志文件通常的名称为hostname.err。其中，hostname表示服务器主机名。

错误日志信息可以自己进行配置的，错误日志所记录的信息是可以通过logerror和log-warnings来定义的，其中log-err是定义是否启用错误日志的功能和错误日志的存储位置，log-warnings是定义是否将警告信息也定义至错误日志中。默认情况下错误日志大概记录以下几个方面的信息：服务器启动和关闭过程中的信息（未必是错误信息，如mysql如何启动InnoDB的表空间文件的、如何初始化自己的存储引擎的等等）、服务器运行过程中的错误信息、事件调度器运行一个事件时产生的信息、在从服务器上启动服务器进程时产生的信息。

mysql -uroot -p

select globle variables like '%log%';

可以通过配置文件来修改log_error

vim /etc/my.cnf //如下图：我将错误日志的路径改为/var/log/mariadb/mariadb.err

log-error=/var/log/mariadb/mariadb.err

然后重启数据库服务连接数据库查看全局日志，修改成功

查看错误日志的内容

临时修改：

Mysql错误日志中，log_error可以直接定义为文件路径，也可以为ON|OFF；

log_warings只能使用1|0来定义开关启动。

永久修改：

更改错误日志位置可以使用log_error来设置形式如下：

[root@stu18 data]# vim /etc/my.cnf

[mysqld]

Log_error=DIR/[filename]

解析：其中，DIR参数指定错误日志的路径filename参数是错误日志的名称，没有指定该参数时默认为主机名。修改配置文件重启mysql服务器即可生效。

注意：在mysql5.5.7之前：数据库管理员可以删除很长时间之前的错误日志，以保证mysql服务器上的硬盘空间。mysql数据库中，可以使用mysqladmin命令开启新的错误日志。

mysqladmin命令的语法如下：

mysqladmin –u root –pflush-logs也可以使用登录mysql数据库中使用FLUSHLOGS语句来开启新的错误日志。

Mysql查询日志

默认情况下查询日志是关闭的。由于查询日志会记录用户的所有操作，其中还包含增删查改等信息，在并发操作大的环境下会产生大量的信息从而导致不必要的磁盘IO，会影响mysql的性能的。如若不是为了调试数据库的目的建议不要开启查询日志。

mysql

show global variables like '%log%';

Mysql慢查询日志

慢查询日志是用来记录执行时间超过指定时间的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低(有些查询语句的执行时间比较长，要把这些查询语句找出清除，来优化服务器性能)，以便进行优化。强烈建议开启，它对服务器性能的影响微乎其微，但是可以记录mysql服务器上执行了很长时间的查询语句。可以帮助我们定位性能问题的。

启动和设置慢查询日志：

1、通过配置文件my.cnf中的log-slow-queries选项可以开启慢查询日志；

形式如下：

vim /etc/my.cnf

[mysqld]

slow-query-log = ON

slow-query-log-file = /var/log/mariadb/slow.log

long-query-time = 0.01

其中，DIR参数指定慢查询日志的存储路径；filename参数指定日志的文件名，生成日志文件的完成名称为filename-slow.log。如果不指定存储路径，慢查询日志默认存储到mysql数据库的数据文件下，如果不指定文件名，默认文件名为hostname-slow.log

2、通过登录mysql服务器直接定义

方式如下：

首先要有全局权限；然后执行mysql>set global slow_query_log=1;（临时生效，sql语句执行时间超过1s就被成为慢查询日志）

时间默认超过多少的称为慢查询日志？

一般都是通过long_query_time选项来设置这个时间值，时间以秒为单位，可以精确到微秒。如果查询时间超过了这个时间值（默认为10秒），这个查询语句将被记录到慢查询日志中。查看服务器默认时间值方式如下：

注释：其中这个慢查询时间并不是只表示语句自身执行超过10秒还包含由于其他资源被征用造成阻塞的查询执行时间或其他原因等都被记录到慢查询中。所以这个慢查的时长表示从查询开始到查询结束中间包含可能的任何原因所经历的所有时间。

查看慢查询日志内容

Mysql事务日志

事务：事务就是一系列操作的集合，一系列操作之后需要提交，提交之后，这一系列操作才能被称为事务。(要么操作都执行，要么都不执行)

事务日志（InnoDB特有的日志）可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把改修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。事务日志持久以后，内存中被修改的数据在后台可以慢慢的刷回到磁盘。目前大多数的存储引擎都是这样实现的，我们通常称之为预写式日志，修改数据需要写两次磁盘。

mysql基于事务的操作，会直接把对应内存中的数据给改掉，改完之后，去查看，都已经生效，但是并没有网磁盘上去写，他是先写到事务日志里面，然后再定期的往磁盘上去刷（事务日志采用追加的方式，往磁盘上写，是按照顺序来写的，大大提高了事务的效率）

如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。具有的恢复方式则视存储引擎而定。

innodb引擎是支持事务的引擎

查看事务日志的定义

show global variables like '%log%';

Mysql二进制日志

二进制日志也叫作变更日志，主要用于记录修改数据或有可能引起数据改变的mysql语句，并且记录了语句发生时间、执行时长、操作的数据等等。所以说通过二进制日志可以查询mysql数据库中进行了哪些变化。一般大小体积上限为1G

show global variables like '%log%';

sql_log_bin ={ON|OFF} #用于控制会话级别（连上mysql执行一个操作语句，这就是会话级别的，比如说直接用一个文件导入mysql，这就不算会话级别的）二进制日志功能的开启或关闭。默认为ON，表示启用记录功能。用户可以在会话级别修改此变量的值，但其必须具有SUPER权限。

binlog_cache_size =32768 #默认值32768 Binlog Cache用于在打开了二进制日志（binlog）记录功能的环境，是MySQL 用来提高binlog的记录效率而设计的一个用于短时间内临时缓存binlog数据的内存区域。一般来说，如果我们的数据库中没有什么大事务，写入也不是特别频繁，2MB～4MB是一个合适的选择。但是如果我们的数据库大事务较多，写入量比较大，可与适当调高binlog_cache_size。同时，我们可以通过binlog_cache_use 以及binlog_cache_disk_use来分析设置的binlog_cache_size是否足够，是否有大量的binlog_cache由于内存大小不够而使用临时文件（binlog_cache_disk_use）来缓存了。

log_bin = mysql-bin #指定binlog的位置，默认在数据目录下。

binlog-format= {ROW|STATEMENT|MIXED} #指定二进制日志的类型，建议为MIXED。如果设定了二进制日志的格式，却没有启用二进制日志，则MySQL启动时会产生警告日志信息并记录于错误日志中。

row:不记录每条sql语句的上下文关系，而仅仅记录每条数据被修改了

statement：每一条会修改数据的sql语句都会被记录

mixed：表示前两者混合

sync_binlog = 10 #设定多久同步一次二进制日志至磁盘文件中，0表示不同步，任何正数值都表示对二进制每多少次写操作之后同步一次。当autocommit的值为1时，每条语句的执行都会引起二进制日志同步，否则，每个事务的提交会引起二进制日志同步

通过编辑my.cnf中的log-bin选项可以开启二进制日志；形式如下：

其中，DIR参数指定二进制文件的存储路径；filename参数指定二级制文件的文件名，其形式为filename.number，number的形式为000001、000002等。每次重启mysql服务或运行mysql> flush logs;都会生成一个新的二进制日志文件，这些日志文件的number会不断地递增。除了生成上述的文件外还会生成一个名为filename.index的文件。这个文件中存储所有二进制日志文件的清单又称为二进制文件的索引。

每重启一次数据库服务，就会生成一个二进制日志文件

查看二进制日志：

二进制日志的定义方式为二进制格式；使用此格式可以存储更多的信息，并且可以使写入二进制日志的效率更高。但是不能直接使用查看命令打开并查看二进制日志。

小扩展：二进制日志的记录位置,通常为上一个事件执行结束时间的位置，每一个日志文件本身也有自己的元数据所以说对于当前版本的mysql来说二进制的开始位置通常为107；

连接mysql，输入几条可以修改数据的sql语句，从而生成二进制日志

查看指定二进制日志信息

命令行下查看二进制日志：

由于无法使用cat等方式直接打开并查看二进制日志；所以必须使用mysqlbinlog命令。但是当正在执行mysql读写操作时建议不要使用此打开正在使用的二进制日志文件；若非要打开可flushlogs。mysqlbinlog命令的使用方式：

导出此数据库的信息：

[root@stu18 data]#mysqlbinlog mysql-bin.000017 > /tmp/a.sql

导入此数据库的信息：

[root@stu18 data]#mysql < a.sql

删除二进制日志信息：

二进制日志会记录大量的信息（其中包含一些无用的信息）。如果很长时间不清理二进制日志，将会浪费很多的磁盘空间。但是，删除之后可能导致数据库崩溃时无法进行恢复，所以若要删除二进制日志首先将其和数据库备份一份，其中也只能删除备份前的二进制日志，新产生的日志信息不可删(可以做即时点还原)。也不可在关闭mysql服务器之后直接删除因为这样可能会给数据库带来错误的。若非要删除二进制日志需要做如下操作：导出备份数据库和二进制日志文件进行压缩归档存储。删除二进制文件的方法如下：

使用RESET MASTER语句可以删除所有的二进制日志。该语句的形式如下：

mysql> reset master;

Query OK, 0 rowsaffected (0.17 sec)

mysql> show binary logs;

Mysql备份工具

mysqldump: 逻辑备份工具，适用于所有存储引擎，可用于温备，能实现完全备份，部分备份；对InnoDB存储引擎

支持热备；

cp, tar等文件系统工具：物理备份工具，适用于所有存储引擎；用于冷备，能实现完全备份，部分备份；

lvm2的快照：几乎热备；借助于文件系统工具实现物理备份；

mysqlhotcopy: 几乎冷备；仅适用于MyISAM存储引擎；

Mysql备份方案①mysqldump+binlog: （推荐）

完全备份，通过备份二进制日志实现增量备份

②xtrabackup:

对InnoDB：热备，支持完全备份和增量备份

对MyISAM：温备，只支持完全备份

③lvm2快照+binlog：

几乎热备，物理备份

mysqldump+binlog命令的语法格式

mysqldump [OPTIONS] database [tables]：备份单个库，或库指定的一个或多个表

mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2DB3...]：备份一个或多个库

mysqldump [OPTIONS] --all-databases [OPTIONS]：备份所有库

其他选项

-x, --lock-all-tables：锁定所有表

-l, --lock-tables：锁定备份的表

--single-transaction：启动一个大的单一事务实现备份

-C, --compress：压缩传输

-E, --events：备份指定库的事件调度器

-R, --routines：备份存储过程和存储函数

--triggers：备份触发器

--master-data={0|1|2}

0：不记录

1：记录CHANGE MASTER TO语句；此语句未被注释

2：记录为注释语句

-F，--flush-logs：锁定表之后执行flush logs命令

mysqldump+binlog备份与恢复1.修改mysql配置文件，开启二进制日志

vim /etc/my.cnf

log-bin = master-log

然后重启mysql

systemctl restart mariadb

在进入mysql查看是否生成二进制日志

2、准备备份目录

3、准备备份数据库及表

mysql

create database test;

use magedu;

create table m(id int,name char(20));

4、进行完整备份

mysqldump --all-databases --lock-all-tables --flush-log --master-data=2 > /backup/`date +%F_%T`-all.sql

5、向表中插入数据

mysql

use magedu;

show master status;

insert into m26 (id,name) values(1,'fuming'),(2,'zhangmeng');

6、进行增量备份，备份二进制日志

mysqlbinlog --start-position=245 --stop-position=479 /var/lib/mysql/master-log.000002 > /backup/binlog/binlog-`date +%F_%T`.sql

判断position的start和stop

show master logs;

show binlog events in 'master-log.000002';

结束要包含commit提交。

7、继续插入数据，在没备份的情况下删除数据库，模拟误操作

8、数据恢复，由于最后我们没有备份就删除了数据库，所以我们首先需要保护最后的二进制日志,如果这些二进制丢了，那就真的恢复不了了，查看删除操作之前的position值mysqlbinlog /var/lib/mysql/master-log.000002

9、将最后操作的二进制日志备份

mysqlbinlog --start-position=467 --stop-position=677 /var/lib/mysql/master-log.000002 > /backup/binlog/binlog-`date +%F_%T`.sql

ls /backup/binlog/

10.导入之前的所有备份

mysql < /backup/2017-12-07_20\:20\:04-all.sql 导入完整备份

mysql < /backup/binlog/binlog-2017-12-07_20\:45\:17.sql 导入增量备份

mysql < /backup/binlog/binlog-2017-12-07_21\:05\:42.sql 导入删掉数据库之前的增量备份

11.查看数据库及数据

xtrabackup

Xtrabackup是由percona提供的mysql数据库备份工具，据官方介绍，是一款开源能够对innodb和xtradb数据库进行热备的工具。

特点：

(1)备份过程快速、可靠

(2)备份过程不会打断正在执行的事务

(3)能够基于压缩等功能节约磁盘空间和流量

(4)自动实现备份检验

(5)还原速度快

实验步骤：（1）xtrabackup的安装

yum install percona-xtrabackup -y

（2）完全备份

innobackupex --user=root /backup

（3）添加数据

mysql -uroot

create database magedu;

use magedu

create table m26 (id int,name char(20));

insert into m26 values (007,'fuming'),(008,'zhangmeng')

（4）增量备份

innobackupex --incremental /backup/ --incremental-basedir=/backup/2017-11-16_16-53-4

（5）数据恢复准备
1.执行操作（完全备份）：

innobackupex --apply-log --redo-only BASE-DIR（BASE-DIR是完全备份的目录）

例如：innobackupex --apply-log --redo-only BASE-DIR --incrementaldir=/backup/2017-11-16_17-17-52/

2.接着执行（增量）：

innobackupex --apply-log --redo-only BASE-DIR --incrementaldir=INCREMENTAL-DIR-1（INCREMENTAL-DIR-1是增量备份的目录）

例如：innobackupex --apply-log --redo-only /backup/2017-11-16_16-53-43 --incrementaldir=/backup/2017-11-16_17-17-52/

（6）恢复阶段，还原数据

mv /var/lib/mysql /var/lib/mysql.bak 模拟删除数据库

mkdir /var/lib/mysql

cd /var/lib/mysql

innobackupex --copy-back /backup/2017-11-16_16-53-43 恢复完全备份

lvm2快照+binlog

做实验之前我们先回顾一下lvm2-snapshot的知识

LVM快照简单来说就是将所快照源分区一个时间点所有文件的元数据进行保存，如果源文件没有改变，那么访问快照卷的相应文件则直接指向源分区的源文件，如果源文件发生改变，则快照卷中与之对应的文件不会发生改变。快照卷主要用于辅助备份文件。

实验步骤：

1、添加硬盘，并划分磁盘类型为lvm类型

echo '- - -' > /sys/class/scsi_host/host2/scan

2.分区

t 8e 就是lvm

partx -a /dev/sdb 使内核识别新磁盘

3.pvcreate /dev/sdb1 添加物理卷

4.vgcreate myvg /dev/sdb1 添加卷组

5.lvcreate -n mydata -L 5G myvg 添加逻辑卷

6、mkfs.ext4 /dev/mapper/myvg-mydata 格式化逻辑卷

7、挂载mount /dev/mapper/myvg-mydata /lvm_data 使用

8、修改Mysql配置，使得数据文件在逻辑卷上 datadir=/lvm_data

9、service mysqld restart 启动Mysql服务

10、创建数据库，进行操作

11、mysql> FLUSH TABLES WITH READ LOCK; #锁定表

12、lvcreate -L 1G -n mydata-snap -p r -s /dev/mapper/myvgmydata

#创建快照卷 Logical volume "mydata-snap" created.

13、mysql> UNLOCK TABLES; #解锁所有表

14、 mount /dev/myvg/mydata-snap /lvm_snap/ #挂载snap

15、tar cvf /tmp/mysqlback.tar ./* #打包物理备份

16、umount /lvm_snap/ #卸载snap

17、lvremove myvg mydata-snap #删除snap

18、删除mysql数据 rm -rf /lvm_data/*

19、解压恢复删除数据 tar xvf /tmp/mysqlback.tar ./

20、验证数据库数据是否正确恢复

总结

备份方法	备份速度	回复速度	便捷性性	功能一般用于
Mysqldump	慢	慢	一般，可无视存储引擎的差异	一般中小型数据量备份
Lvm2快照	快	快	支持几乎热备，速度快	一般中小型数据量备份
Xtrabackup	较快	较快	实现innodb热备，对存储引擎要求强大	较大规模的本分
cp	快	快	一般，灵活性低	很弱少量数据备份