mysql备份与恢复

学习笔记 沙发 观看82 次 2月月 5日, 2018

Linux 专题

下面文章摘录的主题:mysql 日志文件,使用 mysqld 加相应选项来启用某种日志。Mysql

完全备份及恢复:mysqldump 对 MyISAM 或 InnoDB 完全备份,mysqlhotcopy 对 MyISAM 完全

备份。增量备份:使用二进制日志增量备份,使用 mysqlbinlog 命令恢复二进制日志。

SQL 语法备份及恢复。拷贝数据文件备份(对 Innodb 还需拷贝日志文件)。MyISAM 表的检

查与修复(另见《MySql 存储引擎》)。Innodb 表的碎片整理和模糊检查点。

MySQL 备份和恢复

作/译者:叶金荣

本文讨论 MySQL 的备份和恢复机制,以及如何维护数据表,包括最主要的两种表类型:MyISAM 和

Innodb,文中设计的 MySQL 版本为 5.0.22。

目前 MySQL 支持的免费备份工具有:mysqldump、mysqlhotcopy,还可以用 SQL 语法进行备份:BACKUP

TABLE 或者 SELECT INTO OUTFILE,又或者备份二进制日志(binlog),还可以是直接拷贝数据文件和相

MyISAM 表是保存成文件的形式,因此相对比较容易备份,上面提到的几种方法都可以

关的配置文件。MyISAM

使用。Innodb 所有的表都保存在同一个数据文件 ibdata1 中(也可能是多个文件,或者是独立的表空间

文件),相对来说比较不好备份,免费的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump。

1、mysqldump

1.1 备份

mysqldump 是采用 SQL 级别的备份机制,它将数据表导成 SQL 脚本文件,在不同的 MySQL 版本之

间升级时相对比较合适,这也是最常用的备份方法。

现在来讲一下 mysqldump 的一些主要参数:

1.

–compatible=name

它告诉 mysqldump,导出的数据将和哪种数据库或哪个旧版本的 MySQL 服务器相兼容。值可以为

ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、

no_field_options 等,要使用几个值,用逗号将它们隔开。当然了,它并不保证能完全兼容,而是尽量兼

容。

2.

–complete-insert,-c

导出的数据采用包含字段名的完整 INSERT 方式,也就是把所有的值都写在一行。这么做能提高插入效率,

但是可能会受到 max_allowed_packet 参数的影响而导致插入失败。因此,需要谨慎使用该参数,至少我

不推荐。

3.

–default-character-set=charset

指定导出数据时采用何种字符集,如果数据表不是采用默认的 latin1 字符集的话,那么导出时必须指定

该选项,否则再次导入数据后将产生乱码问题。

4.

–disable-keys

告诉 mysqldump 在 INSERT 语句的开头和结尾增加 /*!40000 ALTER TABLE table DISABLE KEYS */; 和

/*!40000 ALTER TABLE table ENABLE KEYS */; 语句,这能大大提高插入语句的速度,因为它是在插入

完所有数据后才重建索引的。该选项只适合 MyISAM 表。

5.

–extended-insert = true|false

默认情况下,mysqldump 开启 –complete-insert 模式,因此不想用它的的话,就使用本选项,设定它的

值为 false 即可。

6.

–hex-blobLinux 专题

使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用本选项 。影响到的字段类型有

BINARY、VARBINARY、BLOB。

7.

–lock-all-tables,-x

在开始导出之前,提交请求锁定所有数据库中的所有表,以保证数据的一致性。这是一个全局读锁,并且

自动关闭 –single-transaction 和 –lock-tables 选项。

8.

–lock-tables

它和 –lock-all-tables 类似,不过是锁定当前导出的数据表,而不是一下子锁定全部库下的表。本选项

只适用于 MyISAM 表,如果是 Innodb 表可以用 –single-transaction 选项。

9.

–no-create-info,-t

只导出数据,而不添加 CREATE TABLE 语句。

10.

–no-data,-d

不导出任何数据,只导出数据库表结构。

11.

–opt

这只是一个快捷选项,等同于同时添加 –add-drop-tables –add-locking –create-option

–disable-keys –extended-insert –lock-tables –quick –set-charset 选项。本选项能让

mysqldump 很快的导出数据,并且导出的数据能很快导回。该选项默认开启,但可以用 –skip-opt 禁用。

注意,如果运行 mysqldump 没有指定 –quick 或 –opt 选项,则会将整个结果集放在内存中。如果导

出大数据库的话可能会出现问题。

12.

–quick,-q

该选项在导出大表时很有用,它强制 mysqldump 从服务器查询取得记录直接输出而不是取得所有记录后将

它们缓存到内存中。

13.

–routines,-R

导出存储过程以及自定义函数。

14.

–single-transaction

该选项在导出数据之前提交一个 BEGIN SQL 语句,BEGIN 不会阻塞任何应用程序且能保证导出时数据库

的一致性状态。它只适用于事务表,例如 InnoDB 和 BDB。

本选项和 –lock-tables 选项是互斥的,因为 LOCK TABLES 会使任何挂起的事务隐含提交。

要想导出大表的话,应结合使用 –quick 选项。

15.

–triggers

同时导出触发器。该选项默认启用,用 –skip-triggers 禁用它。

其他参数详情请参考手册,我通常使用以下 SQL 来备份 MyISAM 表:

/usr/local/mysql/bin/mysqldump -uyejr -pyejr \

–default-character-set=utf8 –opt –extended-insert=false \

–triggers -R –hex-blob -x db_name > db_name.sql

使用以下 SQL 来备份 Innodb 表:

/usr/local/mysql/bin/mysqldump -uyejr -pyejr \

–default-character-set=utf8 –opt –extended-insert=false \

–triggers -R –hex-blob –single-transaction db_name > db_name.sql

另外,如果想要实现在线备份,还可以使用 –master-data 参数来实现,如下:Linux 专题

/usr/local/mysql/bin/mysqldump -uyejr -pyejr \

–default-character-set=utf8 –opt –master-data=1 \

–single-transaction –flush-logs db_name > db_name.sql

它只是在一开始的瞬间请求锁表,然后就刷新 binlog 了,而后在导出的文件中加入 CHANGE MASTER 语句

来指定当前备份的 binlog 位置,如果要把这个文件恢复到 slave 里去,就可以采用这种方法来做。

1.2 还原

用 mysqldump 备份出来的文件是一个可以直接倒入的 SQL 脚本,有两种方法可以将数据导入。

直接用 mysql 客户端

1.

例如:

/usr/local/mysql/bin/mysql -uyejr -pyejr db_name < db_name.sql

用 SOURCE 语法

2.

其实这不是标准的 SQL 语法,而是 mysql 客户端提供的功能,例如:

SOURCE /tmp/db_name.sql;

这里需要指定文件的绝对路径,并且必须是 mysqld 运行用户(例如 nobody)有权限读取的文件。

2、 mysqlhotcopy

2.1 备份

mysqlhotcopy 是一个 PERL 程序,最初由 Tim Bunce 编写。它使用 LOCK TABLES、FLUSH TABLES 和 cp

或 scp 来快速备份数据库。它是备份数据库或单个表的最快的途径,但它只能运行在数据库文件(包括数

据表定义文件、数据文件、索引文件)所在的机器上。mysqlhotcopy 只能用于备份 MyISAM,并且只能运

行在 类 Unix 和 NetWare 系统上。

mysqlhotcopy 支持一次性拷贝多个数据库,同时还支持正则表达。以下是几个例子:

root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr \

db_name /tmp (把数据库目录 db_name 拷贝到 /tmp 下)

root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr \

db_name_1 … db_name_n /tmp

root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr \

db_name./regex/ /tmp

更详细的使用方法请查看手册,或者调用下面的命令来查看 mysqlhotcopy 的帮助:

perldoc /usr/local/mysql/bin/mysqlhotcopy

注意,想要使用 mysqlhotcopy,必须要有 SELECT、RELOAD(要执行 FLUSH TABLES) 权限,并且还必须要

能够有读取 datadir/db_name 目录的权限。

2.2 还原

mysqlhotcopy 备份出来的是整个数据库目录,使用时可以直接拷贝到 mysqld 指定的 datadir (在这里是

/usr/local/mysql/data/)目录下即可,同时要注意权限的问题,如下例:

root#cp -rf db_name /usr/local/mysql/data/Linux 专题

root#chown -R nobody:nobody /usr/local/mysql/data/ (将 db_name 目录的属主改成 mysqld 运行用户)

3、 SQL 语法备份

3.1 备份

BACKUP TABLE 语法其实和 mysqlhotcopy 的工作原理差不多,都是锁表,然后拷贝数据文件。它能实现在

线备份,但是效果不理想,因此不推荐使用。它只拷贝表结构文件和数据文件,不同时拷贝索引文件,因

此恢复时比较慢。

例子:

BACK TABLE tbl_name TO ‘/tmp/db_name/’;

注意,必须要有 FILE 权限才能执行本 SQL,并且目录 /tmp/db_name/ 必须能被 mysqld 用户可写,导

出的文件不能覆盖已经存在的文件,以避免安全问题。

SELECT INTO OUTFILE 则是把数据导出来成为普通的文本文件,可以自定义字段间隔的方式,方便处理这

些数据。

例子:

SELECT * INTO OUTFILE ‘/tmp/db_name/tbl_name.txt’ FROM tbl_name;

注意,必须要有 FILE 权限才能执行本 SQL,并且文件 /tmp/db_name/tbl_name.txt 必须能被 mysqld 用

户可写,导出的文件不能覆盖已经存在的文件,以避免安全问题。

3.2 恢复

用 BACKUP TABLE 方法备份出来的文件,可以运行 RESTORE TABLE 语句来恢复数据表。

例子:

RESTORE TABLE FROM ‘/tmp/db_name/’;

权限要求类似上面所述。

用 SELECT INTO OUTFILE 方法备份出来的文件,可以运行 LOAD DATA INFILE 语句来恢复数据表。

例子:

LOAD DATA INFILE ‘/tmp/db_name/tbl_name.txt’ INTO TABLE tbl_name;

权限要求类似上面所述。导入数据之前,数据表要已经存在才行。如果担心数据会发生重复,可以增加

REPLACE 关键字来替换已有记录或者用 IGNORE 关键字来忽略他们。

4、 启用二进制日志(binlog)

采用 binlog 的方法相对来说更灵活,省心省力,而且还可以支持增量备份。

启用 binlog 时必须要重启 mysqld。首先,关闭 mysqld,打开 my.cnf,加入以下几行:

server-id = 1

log-bin = binlog

log-bin-index = binlog.indexLinux 专题

然后启动 mysqld 就可以了。运行过程中会产生 binlog.000001 以及 binlog.index,前面的文件是

mysqld 记录所有对数据的更新操作,后面的文件则是所有 binlog 的索引,都不能轻易删除。关于 binlog

的信息请查看手册。

需要备份时,可以先执行一下 SQL 语句,让 mysqld 终止对当前 binlog 的写入,就可以把文件直接备

份,这样的话就能达到增量备份的目的了:

FLUSH LOGS;

如果是备份复制系统中的从服务器,还应该备份 master.info 和 relay-log.info 文件。

备份出来的 binlog 文件可以用 MySQL 提供的工具 mysqlbinlog 来查看,如:

/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001

该工具允许你显示指定的数据库下的所有 SQL 语句,并且还可以限定时间范围,相当的方便,详细的请

查看手册。

恢复时,可以采用类似以下语句来做到:

/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001 | mysql -uyejr -pyejr db_name

把 mysqlbinlog 输出的 SQL 语句直接作为输入来执行它。

如果你有空闲的机器,不妨采用这种方式来备份。由于作为 slave 的机器性能要求相对不是那么高,因此

成本低,用低成本就能实现增量备份而且还能分担一部分数据查询压力,何乐而不为呢?

5、 直接备份数据文件

相较前几种方法,备份数据文件[即直接拷贝数据文件]最为直接、快速、方便,缺点是基本上不能实现增

量备份。为了保证数据的一致性,需要在拷贝文件前,执行以下 SQL 语句:

FLUSH TABLES WITH READ LOCK;

也就是把内存中的数据都刷新到磁盘中,同时锁定数据表,以保证拷贝过程中不会有新的数据写入。这种

方法备份出来的数据恢复也很简单,直接拷贝回原来的数据库目录下即可。

注意,对于 Innodb 类型表来说,还需要备份其日志文件,即 ib_logfile* 文件。因为当 Innodb 表损

坏时,就可以依靠这些日志文件来恢复。

6、 备份策略

对于中等级别业务量的系统来说,备份策略可以这么定:第一次全量备份,每天一次增量备份,每周再做

一次全量备份,如此一直重复。而对于重要的且繁忙的系统来说,则可能需要每天一次全量备份,每小时

一次增量备份,甚至更频繁。为了不影响线上业务,实现在线备份,并且能增量备份,最好的办法就是采

( replication)

) ,在 slave 机器上做备份。

用主从复制机制(

7、 数据维护和灾难恢复

作为一名 DBA(我目前还不是,呵呵),最重要的工作内容之一是保证数据表能安全、稳定、高速使用。因

此,需要定期维护你的数据表。以下 SQL 语句就很有用:

CHECK TABLE 或 REPAIR TABLE,检查或维护 MyISAM 表

OPTIMIZE TABLE,优化 MyISAM 表

ANALYZE TABLE,分析 MyISAM 表

当然了,上面这些命令起始都可以通过工具 myisamchk 来完成,在这里不作详述。

Innodb 表则可以通过执行以下语句来整理碎片,提高索引速度:Linux 专题

ALTER TABLE tbl_name ENGINE = Innodb;

这其实是一个 NULL 操作,表面上看什么也不做,实际上重新整理碎片了。

通常使用的 MyISAM 表可以用上面提到的恢复方法来完成。如果是索引坏了,可以用 myisamchk 工具来重

建索引。而对于 Innodb 表来说,就没这么直接了,因为它把所有的表都保存在一个表空间了。不过 Innodb

有一个检查机制叫 模糊检查点,只要保存了日志文件,就能根据日志文件来修复错误。可以在 my.cnf 文

件中,增加以下参数,让 mysqld 在启动时自动检查日志文件:

innodb_force_recovery

= 4

关于该参数的信息请查看手册。

8、 总结

做好数据备份,定只好合适的备份策略,这是一个 DBA 所做事情的一小部分,万事开头难,就从现在开始

吧!

来源:http://imysql.cn/mysql_backup_and_recover

备份与恢复

本节讨论如何进行数据库备份(完全备份和增量备份),以及如何执行表的维护。本节描述的 SQL 语句语法

参见第 5 章:数据库管理。此处提供的大多数信息主要适合 MyISAM 表。

5.9.1. 数据库备份

因为 MySQL 表保存为文件方式,很容易备份。要想保持备份的一致性,对相关表执行 LOCK TABLES 操作,

然后对表执行 FLUSH TABLES。参见 13.4.5 节,“LOCK TABLES 和 UNLOCK TABLES 语法”和 13.5.5.2 节,

“FLUSH 语法”。你只需要读锁定;这样当你复制数据库目录中的文件时,允许其它客户继续查询表。需

要 FLUSH TABLES 语句来确保开始备份前将所有激活的索引页写入硬盘。

如果你想要进行 SQL 级别的表备份,你可以使用 SELECT INTO …OUTFILE 或 BACKUP TABLE。对于 SELECT

INTO …OUTFILE, 输出的文件不能先存在。对于 BACKUP TABLE 也如此,因为覆盖完整的文件会有安全风

险。

备份数据库的另一个技术是使用 mysqldump 程序或 mysqlhotcopy 脚本。

完全备份数据库:

shell> mysqldump –tab= /path/to/some/dir –opt db_name

或:

shell> mysqlhotcopy db_name /path/to/some/dir

只要服务器不再进行更新,还可以只复制所有表文件(*.frm、*.MYD 和*.MYI 文件)。mysqlhotcopy 脚本使

用该方法。(但请注意如果数据库包含 InnoDB 表,这些方法不工作。InnoDB 不将表的内容保存到数据库目

录中,mysqlhotcopy 只适合 MyISAM 表)。

如果 mysqld 在运行则停止,然后用–log-bin[=file_name]选项来启动。参见 5.11.3 节,“二进制日

志”。二进制日志文件中提供了执行 mysqldump 之后对数据库的更改进行复制所需要的信息。

对于 InnoDB 表,可以进行在线备份,不需要对表进行锁定;参见 8.8 节,“mysqldump:数据库备份程序”。Linux 专题

MySQL 支持增量备份:需要用–log-bin 选项来启动服务器以便启用二进制日志;参见 5.11.3 节,“二进制

日志”。当想要进行增量备份时(包含上一次完全备份或增量备份之后的所有更改),应使用 FLUSH LOGS 回

滚二进制日志。然后,你需要将从最后的完全或增量备份的某个时刻到最后某个点的所有二进制日志复制

到备份位置。这些二进制日志为增量备份;恢复时,按照下面的解释应用。下次进行完全备份时,还应使

用 FLUSH LOGS 或 mysqlhotcopy –flushlogs 回滚二进制日志。参见 8.8 节,“mysqldump:数据库备份程

序”和 8.9 节,“mysqlhotcopy:数据库备份程序”。

如果 MySQL 服务器为从复制服务器,则无论选择什么备份方法,当备份从机数据时,还应备份 master.info

和 relay-log.info 文件。恢复了从机数据后,需要这些文件来继续复制。如果从机执行复制 LOAD DATA

INFILE 命令,你应还备份用–slave-load-tmpdir 选项指定的目录中的 SQL_LOAD-*文件。(如果未指定,

该位置默认为 tmpdir 变量值)。从机需要这些文件来继续复制中断的 LOAD DATA INFILE 操作。

如果必须恢复 MyISAM 表,先使用 REPAIR TABLE 或 myisamchk -r 来恢复。99.9%的情况下该方法可以工作。

如果 myisamchk 失败,试试下面的方法。请注意只有用–log-bin 选项启动了 MySQL 从而启用二进制日志

它才工作;参见 5.11.3 节,“二进制日志”。

  1. 恢复原 mysqldump 备份,或二进制备份。
  2. 执行下面的命令重新更新二进制日志:

shell> mysqlbinlog hostname-bin.[0-9]* | mysql

在某些情况下,你可能只想要从某个位置重新运行某些二进制日志。(通常你想要从恢复备份的日期重新运

行所有二进制日志,查询不正确时例外)。关于 mysqlbinlog 工具和如何使用它的详细信息参见 8.6 节,

“mysqlbinlog:用于处理二进制日志文件的实用工具”。

还可以对具体文件进行选择备份:

  • 要想复制表,使用 SELECT * INTO OUTFILE ‘file_name’ FROM tbl_name。
  • 要想重载表,使用 LOAD DATA INFILE ‘file_name’ REPLACE …并恢复。要避免复制记录,

表必须有 PRIMARY KEY 或一个 UNIQUE 索引。当新记录复制唯一键值的旧记录时,REPLACE 关键字可以将旧

记录替换为新记录。

如果备份时遇到服务器性能问题,可以有帮助的一个策略是在从服务器而不是主服务器上建立复制并执行

备份。参见 6.1 节,“复制介绍”。

如果使用 Veritas 文件系统,可以这样备份:

  1. 从客户端程序执行 FLUSH TABLES WITH READ LOCK。
  2. 从另一个 shell 执行 mount vxfs snapshot。
  3. 从第一个客户端执行 UNLOCK TABLES。
  4. 从快照复制文件。
  5. 卸载快照。

5.9.2. 示例用备份与恢复策略

5.9.2.1. 备份策略

5.9.2.2. 为恢复进行备份

5.9.2.3. 备份策略摘要

本节讨论进行备份的程序,在出现崩溃后,可以恢复数据:

  • 操作系统崩溃
  • 电源故障
  • 文件系统崩溃·

Linux 专题

硬件问题(硬盘、母板等等)

该命令不包括 mysqldump 和 mysql 程序的–user 和—password 等选项。应包括必要的选项让 MySQL 服务器

允许你连接它。

我们假定数据保存在 MySQL 的 InnoDB 存储引擎中,支持事务和自动崩溃恢复。我们假定崩溃时 MySQL 服务

器带负载。如果不带负载,则不需要恢复。

出现操作系统崩溃或电源故障时,我们可以假定重启后硬盘上的 MySQLś数据仍可用。由于崩溃,InnoDB

数据文件中的数据可能不再保持一致性,但 InnoDB 读取它的日志并会查到挂起的提交的和未提交的事务清

单,它们没有清空到数据文件中。InnoDB 自动卷回未提交的事务,并清空到它的数据文件中。通过 MySQL

错误日志将该恢复过程相关信息传达给用户。下面的例子为日志摘录:

InnoDB: Database was not shut down normally.

InnoDB: Starting recovery from log files…

InnoDB: Starting log scan based on checkpoint at

InnoDB: log sequence number 0 13674004

InnoDB: Doing recovery: scanned up to log sequence number 0 13739520

InnoDB: Doing recovery: scanned up to log sequence number 0 13805056

InnoDB: Doing recovery: scanned up to log sequence number 0 13870592

InnoDB: Doing recovery: scanned up to log sequence number 0 13936128

InnoDB: Doing recovery: scanned up to log sequence number 0 20555264

InnoDB: Doing recovery: scanned up to log sequence number 0 20620800

InnoDB: Doing recovery: scanned up to log sequence number 0 20664692

InnoDB: 1 uncommitted transaction(s) which must be rolled back

InnoDB: Starting rollback of uncommitted transactions

InnoDB: Rolling back trx no 16745

InnoDB: Rolling back of trx no 16745 completed

InnoDB: Rollback of uncommitted transactions completed

InnoDB: Starting an apply batch of log records to the database…

InnoDB: Apply batch completed

InnoDB: Started

mysqld: ready for connections

如果文件系统崩溃或出现硬件问题,我们可以假定重启后硬盘上的 MySQLś数据不可用。这说明 MySQL 未能

成功启动,因为一些硬盘数据块不再可读。在这种情况下,需要重新格式化硬盘,安装一个新的,或纠正

问题。然后需要从备份中恢复 MySQL 数据,这说明我们必须先做好备份。要想确保,应及时返回并设计备

份策略。

5.9.2.1. 备份策略

我们都知道必须按计划定期进行备份。可以用几个工具完全备份(在某个时间点的数据快照)MySQL。例如,

InnoDB Hot Backup 为 InnoDB 数据文件提供在线非数据块物理备份,mysqldump 提供在线逻辑备份。这里

使用 mysqldump。

假定我们在星期日下午 1 点进行了备份,此时负荷较低。下面的命令可以完全备份所有数据库中的所有

InnoDB 表:

shell> mysqldump –single-transaction –all-databases > backup_sunday_1_PM.sql

这是在线非块备份,

不会干扰对表的读写。我们以前假定我们的表为 InnoDB 表,因此–single-transactionLinux 专题

使用一致性地读,并且保证 mysqldump 所看见的数据不会更改。(其它客户端对 InnoDB 表进行的更改不会

被 mysqldump 进程看见)。如果我们还有其它类型的表,我们必须假定在备份过程中它们不会更改。例如,

对于 mysql 数据库中的 MyISAM 表,我们必须假定在备份过程中没有对 MySQL 账户进行管理更改。

mysqldump 命令产生的.sql 文件包含一系列 SQL INSERT 语句,可以用来重载转储的表。

需要进行完全备份,但有时不方便。会产生大的备份文件并需要花时间来生成。从某个角度,完全备份并

不理想,因为每个成功的完全备份包括所有数据,甚至自从上一次完全备份以来没有更改的部分。完成了

初使完全备份后,进行增量备份会更有效。这样备份文件要小得多,备份时间也较短。不利之处是,恢复

时不能只重载完全备份来恢复数据。还必须要用增量备份来恢复增量更改。

要想进行增量备份,我们需要保存增量更改。应使用–log-bin 选项启动 MySQL 服务器,以便更新数据时

将这些更改保存到文件中。该选项启用二进制日志,因此服务器写将每个更新数据的 SQL 语句写入 MySQL

二进制日志。让我们看看用–log-bin 选项启动的已经运行多日的 MySQL 服务器的数据目录。我们找到以

下 MySQL 二进制日志文件:

-rw-rw—- 1 guilhem guilhem 1277324 Nov 10 23:59 gbichot2-bin.000001

-rw-rw—- 1 guilhem guilhem 4 Nov 10 23:59 gbichot2-bin.000002

-rw-rw—- 1 guilhem guilhem 79 Nov 11 11:06 gbichot2-bin.000003

-rw-rw—- 1 guilhem guilhem 508 Nov 11 11:08 gbichot2-bin.000004

-rw-rw—- 1 guilhem guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005

-rw-rw—- 1 guilhem guilhem

-rw-rw—- 1 guilhem guilhem

998412 Nov 14 10:08 gbichot2-bin.000006

361 Nov 14 10:07 gbichot2-bin.index

每次重启,MySQL 服务器用序列中的下一个编号创建一个新的二进制日志文件。当服务器运行时,你还可

以通过执行 FLUSH LOGS SQL 语句或 mysqladmin flush-logs 命令,告诉服务器关闭当前的二进制日志文件

并创建一个新文件。mysqldump 也有一个选项来清空日志。数据目录中的.index 文件包含该目录下所有

MySQL 二进制日志的清单。该文件用于复制。

恢复时 MySQL 二进制日志很重要,因为它们是增量备份。如果进行完全备份时确保清空了日志,则后面创

建的二进制日志文件包含了备份后的所有数据更改。让我们稍稍修改前面的 mysqldump 命令,让它在完全

备份时能够清空 MySQL 二进制日志,以便转储文件包含包含新的当前的二进制日志:

shell> mysqldump –single-transaction –flush-logs –master-data=2

–all-databases > backup_sunday_1_PM.sql

执行该命令后,数据目录则包含新的二进制日志文件,gbichot2-bin.000007。结果.sql 文件包含下列行:

— Position to start replication or point-in-time 恢复时 y from

— CHANGE MASTER TO MASTER_LOG_FILE=’gbichot2-bin.000007′,MASTER_LOG_POS=4;

因为 mysqldump 命令可以执行完全备份,这些行表示两件事情:

  • .sql 文件包含所有写入 gbichot2-bin.000007 二进制日志文件或最新的文件之前的更改。
  • 备份后所记录的所有数据更改不出现在.sql 中,但出现在 gbichot2-bin.000007 二进制日志

文件或最新的文件中。

在星期一下午 1 点,我们可以清空日志开始新的二进制日志文件来创建增量备份。例如,执行 mysqladmin

flush-logs 命令创建 gbichot2-bin.000008。星期日下午 1 点的完全备份和星期一下午 1 点之间的所有更

改为文件 gbichot2-bin.000007。该增量备份很重要,因此最好将它复制到安全的地方。(例如,备份到磁

带或 DVD 上,或复制到另一台机器上)。在星期二下午 1 点,执行另一个 mysqladmin flush-logs 命令。Linux 专题

星期一下午 1 点和星期二下午 1 点之间的所有所有更改为文件 gbichot2-bin.000008(也应复制到某个安全

的地方)。

MySQL 二进制日志占据硬盘空间。要想释放空间,应随时清空。操作方法是删掉不再使用的二进制日志,

例如进行完全备份时:

shell> mysqldump –single-transaction –flush-logs –master-data=2

–all-databases –delete-master-logs > backup_sunday_1_PM.sql

注释:如果你的服务器为复制主服务器,用 mysqldump –delete-master-logs 删掉 MySQL 二进制日志很危

险,因为从服务器可能还没有完全处理该二进制日志的内容。

PURGE MASTER LOGS 语句的描述中解释了为什么在删掉 MySQL 二进制日志之前应进行确认。参见 13.6.1.1

节,“PURGE MASTER LOGS 语法”。

5.9.2.2. 为恢复进行备份

现在假设在星期三上午 8 点出现了灾难性崩溃,需要使用备份文件进行恢复。恢复时,我们首先恢复最后

的完全备份(从星期日下午 1 点开始)。完全备份文件是一系列 SQL 语句,因此恢复它很容易:

shell> mysql < backup_sunday_1_PM.sql

在该点,数据恢复到星期日下午 1 点的状态。要想恢复从那时起的更改,我们必须使用增量备份,也就是,

gbichot2-bin.000007 和 gbichot2-bin.000008 二进制日志文件。根据需要从备份处取过这些文件,然后

按下述方式处理:

shell> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql

我们现在将数据恢复到星期二下午 1 点的状态,但是从该时刻到崩溃之间的数据仍然有丢失。要想恢复,

我们需要 MySQL 服务器将 MySQL 二进制日志保存到安全的位置(RAID disks, SAN, …),应为与数据文件

的保存位置不同的地方,保证这些日志不在毁坏的硬盘上。(也就是,我们可以用–log-bin 选项启动服务

器,指定一个其它物理设备上的与数据目录不同的位置。这样,即使包含该目录的设备丢失,日志也不会

丢失)。如果我们执行了这些操作,我们手头上会有 gbichot2-bin.000009 文件,我们可以用它来恢复大

部分最新的数据更改,而不会丢失到崩溃时的数据。

5.9.2.3. 备份策略摘要

出现操作系统崩溃或电源故障时,InnoDB 自己可以完成所有数据恢复工作。但为了确保你可以睡好觉,应

遵从下面的指导:

一定用–log-bin 或甚至–log-bin=log_name 选项运行 MySQL 服务器,其中日志文件名位于

某个安全媒介上,不同于数据目录所在驱动器。如果你有这样的安全媒介,最好进行硬盘负载均衡(这样能

够提高性能)。

  • 定期进行完全备份,使用 mysqldump 命令进行在线非块备份。
  • 用 FLUSH LOGS 或 mysqladmin flush-logs 清空日志进行定期增量备份。

5.9.3. 自动恢复

5.9.3.1. 指定恢复时间

5.9.3.2. 指定恢复位置

如果 MySQL 服务器启用了二进制日志,你可以使用 mysqlbinlog 工具来恢复从指定的时间点开始 (例如,

从你最后一次备份)直到现在或另一个指定的时间点的数据。关于启用二进制日志的信息,参见 5.11.3 节,Linux 专题

“二进制日志”。对于 mysqlbinlog 的详细信息,参见 8.6 节,“mysqlbinlog:用于处理二进制日志文件

的实用工具”。

要想从二进制日志恢复数据,你需要知道当前二进制日志文件的路径和文件名。一般可以从选项文件(即

my.cnf or my.ini,取决于你的系统)中找到路径。如果未包含在选项文件中,当服务器启动时,可以在命

令行中以选项的形式给出。启用二进制日志的选项为–log-bin。要想确定当前的二进制日志文件的文件名,

输入下面的 MySQL 语句:

SHOW BINLOG EVENTS \G

你还可以从命令行输入下面的内容:

mysql –user=root -pmy_pwd -e ‘SHOW BINLOG EVENTS \G’

将密码 my_pwd 替换为服务器的 root 密码。

5.9.3.1. 指定恢复时间

对于 MySQL 4.1.4,可以在 mysqlbinlog 语句中通过–start-date 和–stop-date 选项指定 DATETIME 格式

的起止时间。举例说明,假设在今天上午 10:00(今天是 2005 年 4 月 20 日),执行 SQL 语句来删除一个大

表。要想恢复表和数据,你可以恢复前晚上的备份,并输入:

mysqlbinlog –stop-date=”2005-04-20 9:59:59″ /var/log/mysql/bin.123456 | mysql -u root –pmypwd

[stop-date 稍早于重大删除时间即可]

该命令将恢复截止到在–stop-date 选项中以 DATETIME 格式给出的日期和时间的所有数据。如果你没有检

测到几个小时后输入的错误的 SQL 语句,可能你想要恢复后面发生的活动。根据这些,你可以用起始日期

和时间再次运行 mysqlbinlog:

mysqlbinlog –start-date=”2005-04-20 10:01:00″ /var/log/mysql/bin.123456 | mysql -u root

-pmypwd [恢复重大删除语句后的所有更改]

在该行中,从上午 10:01 登录的 SQL 语句将运行。组合执行前夜的转储文件和 mysqlbinlog 的两行可以将

所有数据恢复到上午 10:00 前一秒钟。你应检查日志以确保时间确切。下一节介绍如何实现。

5.9.3.2. 指定恢复位置

也可以不指定日期和时间,而使用 mysqlbinlog 的选项–start-position 和–stop-position 来指定日志

位置。它们的作用与起止日选项相同,不同的是给出了从日志起的位置号。使用日志位置是更准确的恢复

方法,特别是当由于破坏性 SQL 语句同时发生许多事务的时候。要想确定位置号,可以运行 mysqlbinlog

寻找执行了不期望的事务的时间范围,但应将结果重新指向文本文件以便进行检查。操作方法为:

mysqlbinlog –start-date=”2005-04-20 9:55:00″ –stop-date=”2005-04-20 10:05:00″ \

/var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

该命令将在/tmp 目录创建小的文本文件,将显示执行了错误的 SQL 语句时的 SQL 语句。你可以用文本编辑

器打开该文件,寻找你不要想重复的语句。如果二进制日志中的位置号用于停止和继续恢复操作,应进行

注释。用 log_pos 加一个数字来标记位置。使用位置号恢复了以前的备份文件后,你应从命令行输入下面

内容:

mysqlbinlog –stop-position=”368312″ /var/log/mysql/bin.123456 \

| mysql -u root -pmypwd

mysqlbinlog –start-position=”368315″ /var/log/mysql/bin.123456 \

| mysql -u root -pmypwd \

上面的第 1 行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的Linux 专题

所有事务。因为 mysqlbinlog 的输出包括每个 SQL 语句记录之前的 SET TIMESTAMP 语句,恢复的数据和相

关 MySQL 日志将反应事务执行的原时间。

来源:http://blog.sina.com.cn/s/blog_4d22b9720100c4he.html

来源:http://blog.sina.com.cn/s/blog_4d22b9720100c4hd.html

MySQL 日志文件

5.11.1. 错误日志

5.11.2. 通用查询日志

5.11.3. 二进制日志

5.11.4. 慢速查询日志

5.11.5. 日志文件维护

MySQL 有几个不同的日志文件,可以帮助你找出 mysqld 内部发生的事情:

日志文件 记入文件中的信息类型

错误日志 记录启动、运行或停止 mysqld 时出现的问题。

查询日志 记录建立的客户端连接和执行的语句。

更新日志 记录更改数据的语句。不赞成使用该日志。(在 MySQL 5.1 中不再使用)

二进制日志 记录所有更改数据的语句。还用于复制。

慢日志

记录所有执行时间超过 long_query_time 秒的所有查询或不使用索引的查询。

默认情况下,所有日志创建于 mysqld 数据目录中。通过刷新日志,你可以强制 mysqld 来关闭和重新打开

日志文件(或者在某些情况下切换到一个新的日志)。当你执行一个 FLUSH LOGS 语句或执行 mysqladmin

flush-logs 或 mysqladmin refresh 时,出现日志刷新。参见 13.5.5.2 节,“FLUSH 语法”。

如果你正使用 MySQL 复制功能,从复制服务器将维护更多日志文件,被称为接替日志。相关讨论参见第 6

章:MySQL 中的复制。

5.11.1. 错误日志

错误日志文件包含了当 mysqld 启动和停止时,以及服务器在运行过程中发生任何严重错误时 的相关信息。

如果 mysqld 莫名其妙地死掉并且 mysqld_safe 需要重新启动它,mysqld_safe 在错误日志中写入一条

restarted mysqld 消息。如果 mysqld 注意到需要自动检查或者修复一个表,则错误日志中写入一条消息。

在一些操作系统中,如果 mysqld 死掉,错误日志包含堆栈跟踪信息。跟踪信息可以用来确定 mysqld 死掉

的地方。参见 E.1.4 节,“使用堆栈跟踪”。

可以用–log-error[=file_name]选项来指定 mysqld 保存错误日志文件的位置。如果没有给定 file_name

值,mysqld 使用错误日志名 host_name.err 并在数据目录中写入日志文件。如果你执行 FLUSH LOGS,错

误日志用-old 重新命名后缀并且 mysqld 创建一个新的空日志文件。(如果未给出–log-error 选项,则不

会重新命名)。

如果不指定–log-error,或者(在 Windows 中)如果你使用–console 选项,错误被写入标准错误输出

stderr。通常标准输出为你的终端。

在 Windows 中,如果未给出–console 选项,错误输出总是写入.err 文件。Linux 专题

5.11.2. 通用查询日志

如果你想要知道 mysqld 内部发生了什么,你应该用–log[=file_name]或-l [file_name]选项启动它。如

果没有给定 file_name 的值,默认名是 host_name.log。所有连接和语句被记录到日志文件。当你怀疑在

客户端发生了错误并想确切地知道该客户端发送给 mysqld 的语句时,该日志可能非常有用。

mysqld 按照它接收的顺序记录语句到查询日志。这可能与执行的顺序不同。这与更新日志和二进制日志不

同,它们在查询执行后,但是任何一个锁释放之前记录日志。(查询日志还包含所有语句,而二进制日志不

包含只查询数据的语句)。

服务器重新启动和日志刷新不会产生新的一般查询日志文件 (尽管刷新关闭并重新打开一般查询日志文

件)。在 Unix 中,你可以通过下面的命令重新命名文件并创建一个新文件:

shell> mv hostname.log hostname-old.log

shell> mysqladmin flush-logs

shell> cp hostname-old.log to-backup-directory

shell> rm hostname-old.log

在 Windows 中,服务器打开日志文件期间你不能重新命名日志文件。你必须先停止服务器然后重新命名日

志文件。然后,重启服务器来创建新的日志文件。

5.11.3. 二进制日志

二进制日志以一种更有效的格式,并且是事务安全的方式包含更新日志中可用的所有信息。

二进制日志包含了所有更新了数据或者已经潜在更新了数据(例如,没有匹配任何行的一个 DELETE)的所

有语句。语句以“事件”的形式保存,它描述数据更改。

注释:二进制日志已经代替了老的更新日志,更新日志在 MySQL 5.1 中不再使用。

二进制日志还包含关于每个更新数据库的语句的执行时间信息。它不包含没有修改任何数据的语句。如果

你想要记录所有语句(例如,为了识别有问题的查询),你应使用一般查询日志。参见 5.11.2 节,“通用

查询日志”。

二进制日志的主要目的是在恢复时能够最大可能地更新数据库,因为二进制日志包含 备份后[ 指完全备份

后,如使用 mysqldump。二进制日志可作为增量备份 ] 进行的所有更新。

二进制日志还用于在主复制服务器上记录所有将发送给从服务器的语句 。参见第 6 章:MySQL 中的复制。

运行服务器时若启用二进制日志则性能大约慢 1%。但是,二进制日志的好处,即用于恢复并允许设置复制

超过了这个小小的性能损失。

当用–log-bin[=file_name]选项启动时,mysqld 写入包含所有更新数据的 SQL 命令的日志文件。如果未

给出 file_name 值,默认名为-bin 后面所跟的主机名。如果给出了文件名,但没有包含路径,则文件被写

入数据目录。建议指定一个文件名,原因参见 A.8.1 节,“MySQL 中的打开事宜”。

如果你在日志名中提供了扩展名(例如,–log-bin=file_name.extension),则扩展名被悄悄除掉并忽略。

mysqld 在每个二进制日志名后面添加一个数字扩展名。每次你启动服务器或刷新日志时该数字则增加。如

果当前的日志大小达到 max_binlog_size,还会自动创建新的二进制日志。如果你正使用大的事务,二进

制日志还会超过 max_binlog_size:事务全写入一个二进制日志中,绝对不要写入不同的二进制日志中 。

为了能够知道还使用了哪个不同的二进制日志文件,mysqld 还创建一个二进制日志索引文件,包含所有使

用的二进制日志文件的文件名。默认情况下与二进制日志文件的文件名相同,扩展名为’.index’。你可以Linux 专题

用–log-bin-index[=file_name]选项更改二进制日志索引文件的文件名。当 mysqld 在运行时,不应手动

编辑该文件;如果这样做将会使 mysqld 变得混乱。

可以用 RESET MASTER 语句删除所有二进制日志文件,或用 PURGE MASTER LOGS 只删除部分二进制文件。参

见 13.5.5.5 节,“RESET 语法”和 13.6.1 节,“用于控制主服务器的 SQL 语句”。

二进制日志格式有一些已知限制,会影响从备份恢复。参见 6.7 节,“复制特性和已知问题”。

保存程序和触发器的二进制日志的描述参见 20.4 节,“存储子程序和触发程序的二进制日志功能”。

可以使用下面的 mysqld 选项来影响记录到二进制日志的内容。又见选项后面的讨论。

–binlog-do-db=db_name

告诉主服务器,如果当前的数据库(即 USE 选定的数据库)是 db_name,应将更新记录到二进制日志中。其

它所有没有明显指定的数据库

被忽略。如果使用该选项,你应确保只对当前的数据库进行更新。

对于 CREATE DATABASE、ALTER DATABASE 和 DROP DATABASE 语句,有一个例外,即通过操作的数据库来决

定是否应记录语句,而不是用当前的数据库。

一个不能按照期望执行的例子:如果用 binlog-do-db=sales 启动服务器,并且执行 USE prices; UPDATE

sales.january SET amount=amount+1000;,该语句不写入二进制日志。

–binlog-ignore-db=db_name

告诉主服务器,如果当前的数据库(即 USE 选定的数据库)是 db_name,不应将更新保存到二进制日志中。

如果你使用该选项,你应确保只对当前的数据库进行更新。

一个不能按照你期望的执行的例子:如果服务器用 binlog-ignore-db=sales 启动,并且执行 USE prices;

UPDATE sales.january SET amount=amount+1000;,该语句不写入二进制日志。

类似于–binlog-do-db,对于 CREATE DATABASE、ALTER DATABASE 和 DROP DATABASE 语句,有一个例外,

即通过操作的数据库来决定是否应记录语句,而不是用当前的数据库。

要想记录或忽视多个数据库,使用多个选项,为每个数据库指定相应的选项。

服务器根据下面的规则对选项进行评估,以便将更新记录到二进制日志中或忽视。请注意对于

CREATE/ALTER/DROP DATABASE 语句有一个例外。在这些情况下,根据以下规则,所创建、修改或删除的数

据库将代替当前的数据库。

  1. 是否有 binlog-do-db 或 binlog-ignore-db 规则?
  • 没有:将语句写入二进制日志并退出。
  • 有:执行下一步。

2.

有一些规则(binlog-do-db 或 binlog-ignore-db 或二者都有)。当前有一个数据库(USE 是否选择了

数据库?)?

  • 没有:不要写入语句,并退出。
  • 有:执行下一步。

3.

有当前的数据库。是否有 binlog-do-db 规则?

有:当前的数据库是否匹配 binlog-do-db 规则?

o 有:写入语句并退出。

o 没有:不要写入语句,退出。

4.

No:执行下一步。

有一些 binlog-ignore-db 规则。当前的数据库是否匹配 binlog-ignore-db 规则?

  • 有:不要写入语句,并退出。
  • 没有:写入查询并退出。Linux 专题

例如,只用 binlog-do-db=sales 运行的服务器不将当前数据库不为 sales 的语句写入二进制日志(换句话

说,binlog-do-db 有时可以表示“忽视其它数据库”)。

如果你正进行复制,应确保没有从服务器在使用旧的二进制日志文件,方可删除它们。一种方法是每天一

次执行 mysqladmin flush-logs 并删除三天前的所有日志。可以手动删除,或最好使用 PURGE MASTER

LOGS(参见 13.6.1 节,“用于控制主服务器的 SQL 语句”),该语句还会安全地更新二进制日志索引文件(可

以采用日期参数)。

具有 SUPER 权限的客户端可以通过 SET SQL_LOG_BIN=0 语句禁止将自己的语句记入二进制记录。

参见 13.5.3

节,“SET 语法”。

你可以用 mysqlbinlog 实用工具检查二进制日志文件。如果你想要重新处理日志止的语句,这很有用。例

如,可以从二进制日志更新 MySQL 服务器,方法如下:

shell> mysqlbinlog log-file | mysql -h server_name

关于 mysqlbinlog 实用工具的详细信息以及如何使用它,参见 8.6 节,“mysqlbinlog:用于处理二进制日

志文件的实用工具”。

如果你正使用事务,必须使用 MySQL 二进制日志进行备份,而不能使用旧的更新日志。

查询结束后、锁定被释放前或提交完成后则立即记入二进制日志。这样可以确保按执行顺序记入日志。

对非事务表的更新执行完毕后立即保存到二进制日志中。对于事务表,例如 BDB 或 InnoDB 表,所有更改表

的更新(UPDATE、DELETE 或 INSERT) 被缓存起来,直到服务器接收到 COMMIT 语句。在该点,执行完 COMMIT

之前,mysqld 将整个事务写入二进制日志。当处理事务的线程启动时,它为缓冲查询分配

binlog_cache_size(全局变量)大小的内存。如果语句大于该值,线程则打开临时文件来保存事务。线程

结束后临时文件被删除。

Binlog_cache_use 状态变量显示了使用该缓冲区(也可能是临时文件)保存语句的事务的数量。

Binlog_cache_disk_use 状态变量显示了这些事务中实际上有多少必须使用临时文件。这两个变量可以用

于将 binlog_cache_size 调节到足够大的值,以避免使用临时文件。

max_binlog_cache_size(默认 4GB)可以用来限制用来缓存多语句事务的缓冲区总大小。如果某个事务大于

该值,将会失败并回滚。

如果你正使用更新日志或二进制日志,当使用 CREATE … SELECT or INSERT … SELECT 时,并行插入被

转换为普通插入。这样通过在备份时使用日志可以确保重新创建表的备份。

请注意 MySQL 5.1 值的二进制日志格式与以前版本的 MySQL 不同,因为复制改进了。参见 6.5 节,“不同

MySQL 版本之间的复制兼容性”。

默认情况下,并不是每次写入时都将二进制日志与硬盘同步。因此如果操作系统或机器(不仅仅是 MySQL

服务器)崩溃,有可能二进制日志中最后的语句丢失了。要想防止这种情况,你可以使用 sync_binlog 全局

变量(1 是最安全的值,但也是最慢的),使二进制日志在每 N 次二进制日志写入后与硬盘同步。参见 5.3.3

节,“服务器系统变量”。即使 sync_binlog 设置为 1,出现崩溃时,也有可能表内容和二进制日志内容之

间存在不一致性。例如,如果使用 InnoDB 表,MySQL 服务器处理 COMMIT 语句,它将整个事务写入二进制

日志并将事务提交到 InnoDB 中。如果在两次操作之间出现崩溃,重启时,事务被 InnoDB 回滚,但仍然存

在二进制日志中。可以用–innodb-safe-binlog 选项解决该问题,可以增加 InnoDB 表内容和二进制日志

之间的一致性。(注释:在 MySQL 5.1 中不需要–innodb-safe-binlog;由于引入了 XA 事务支持,该选项

作废了)。

该选项可以提供更大程度的安全,

还应对 MySQL 服务器进行配置,

使每个事务的二进制日志(sync_binlog =1)

和(默认情况为真)InnoDB 日志与硬盘同步。该选项的效果是崩溃后重启时,在滚回事务后,MySQL 服务器Linux 专题

从二进制日志剪切回滚的 InnoDB 事务。这样可以确保二进制日志反馈 InnoDB 表的确切数据等,并使从服

务器保持与主服务器保持同步(不接收回滚的语句)。

请注意即使 MySQL 服务器更新其它存储引擎而不是 InnoDB,也可以使用–innodb-safe-binlog。在 InnoDB

崩溃恢复时,只从二进制日志中删除影响 InnoDB 表的语句/事务。如果崩溃恢复时 MySQL 服务器发现二进

制日志变短了(即至少缺少一个成功提交的 InnoDB 事务),如果 sync_binlog =1 并且硬盘/文件系统的确能

根据需要进行同步(有些不需要)则不会发生,则输出错误消息 (“二进制日志<名>比期望的要小”)。在这种

情况下,二进制日志不准确,复制应从主服务器的数据快照开始。

写入二进制日志文件和二进制日志索引文件的方法与写入 MyISAM 表相同。参见 A.4.3 节,“MySQL 处理磁

盘满的方式”。

5.11.4. 慢速查询日志

用–log-slow-queries[=file_name]选项启动时,mysqld 写一个包含所有执行时间超过 long_query_time

秒的 SQL 语句的日志文件。获得初使表锁定的时间不算作执行时间。

如果没有给出 file_name 值,默认为主机名,后缀为-slow.log。如果给出了文件名,但不是绝对路径名,

文件则写入数据目录。

语句执行完并且所有锁释放后记入慢查询日志。记录顺序可以与执行顺序不相同。

慢查询日志可以用来找到执行时间长的查询,可以用于优化。但是,检查又长又慢的查询日志会很困难。

要想容易些,你可以使用 mysqldumpslow 命令获得日志中显示的查询摘要来处理慢查询日志。

在 MySQL 5.1 的慢查询日志中,不使用索引的慢查询同使用索引的查询一样记录。要想防止不使用索引的

慢查询记入慢查询日志,使用–log-short-format 选项。参见 5.3.1 节,“mysqld 命令行选项”。

在 MySQL 5.1 中,通过–log-slow-admin-statements 服务器选项,

你可以请求将慢管理语句,例如 OPTIMIZE

TABLE、ANALYZE TABLE 和 ALTER TABLE 写入慢查询日志。

用查询缓存处理的查询不加到慢查询日志中,因为表有零行或一行而不能从索引中受益的查询也不写入慢

查询日志。

5.11.5. 日志文件维护

MySQL 服务器可以创建各种不同的日志文件,从而可以很容易地看见所进行的操作。参见 5.11 节,“MySQL

日志文件”。但是,你必须定期清理这些文件,确保日志不会占用太多的硬盘空间。

当启用日志使用 MySQL 时,你可能想要不时地备份并删除旧的日志文件,并告诉 MySQL 开始记入新文件。

参见 5.9.1 节,“数据库备份”。

在 Linux (Redhat)的安装上,你可为此使用 mysql-log-rotate 脚本。如果你从 RPM 分发安装 MySQL,脚

本应该自动被安装了。

在其它系统上,你必须自己安装短脚本,你可从 cron 等入手处理日志文件。

你可以通过 mysqladmin flush-logs 或 SQL 语句 FLUSH LOGS 来强制 MySQL 开始使用新的日志文件。

日志清空操作做下列事情:

如果使用标准日志(–log)或慢查询日志(–log-slow-queries),关闭并重新打开日志文件。(默

认为 mysql.log 和hostname-slow.log)。

如果使用更新日志(–log-update)或二进制日志(–log-bin),关闭日志并且打开有更高序列号

的新日志文件。

如果使用错误日志,如使用了–log-error 选项启动 mysqld,则以-old 重命名旧日志并打开一个

新的空日志;否则,关闭并打开原来的日志。

如果你只使用更新日志,你只需要重新命名日志文件,然后在备份前清空日志。例如,你可以这样做:Linux 专题

shell> cd mysql-data-directory

shell> mv mysql.log mysql.old

shell> mysqladmin flush-logs

然后做备份并删除“mysql.old”。

来源:http://blog.sina.com.cn/s/blog_4d22b9720100c5b6.html (到该博客阅读其他文章)

使用 MYSQLBINLOG 来恢复数据

今天在家里做了一下试验,终于搞明白了以前做复制的时候没有搞明白的问题。原来 BINLOG 就是一个记

录 SQL 语句的过程,和普通的 LOG 一样。不过只是她是二进制存储,普通的是十进制存储罢了。

1、配置文件里要写的东西:

[mysqld]

log-bin=yueliangdao_binglog(名字可以改成自己的,如果不改名字的话,默认是以主机名字命名)

重新启动 MSYQL 服务。

二进制文件里面的东西显示的就是执行所有语句的详细记录,当然一些语句不被记录在内,要了解详细的,

见手册页。

2、查看自己的 BINLOG 的名字是什么。

show binlog events;

query result(1 records)

Log_name

Po

s

Event_type

Server_i

d

yueliangdao_binglog.0000 4 Format_des 1

01 c

End_log_pos Info

106 Server ver:

5.1.22-rc-community

-log, Binlog ver: 4

3、我做了几次操作后,它就记录了下来。

又一次 show binlog events 的结果。

query result(4 records)

Log_name

Pos Event_type

Server_i End_log_po

d

yueliangdao_binglog.0000 4 Format_des 1

01 c

s

106

Info

Server ver:

5.1.22-rc-community-l

og, Binlog ver: 4

yueliangdao_binglog.0000 10 Intvar

01

134 INSERT_ID=1

1 254 use test; create table

6

yueliangdao_binglog.0000 13 Query

01

1

4

a1(id int not null

auto_increment

primary key, str

varchar(1000))Linux 专题

engine=myisam

yueliangdao_binglog.0000 25 Query

01

1

330

use test; insert into

4

a1(str) values (‘I love

you’),(‘You love me’)

yueliangdao_binglog.0000 33 Query

01

1

485

use test; drop table

0

a1

4、用 mysqlbinlog 工具来显示记录的二进制结果,然后导入到文本文件,为了以后的恢复。

详细过程如下:

D:\LAMP\MYSQL5\data>mysqlbinlog –start-position=4 –stop-position=106

yueliangdao_binglog.000001 > c:\\test1.txt

test1.txt 的文件内容:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#7122 16:9:18 server id 1 end_log_pos 106

Start: binlog v 4, server v 5.1.22-rc-community-log created 7122 16:9:18 at

startup

# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.

ROLLBACK/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

第二行的记录:

D:\LAMP\MYSQL5\data>mysqlbinlog –start-position=106 –stop-position=134 yuelian

gdao_binglog.000001 > c:\\test1.txt

test1.txt 内容如下:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 106

#7122 16:22:36 server id 1 end_log_pos 134

Intvar

SET INSERT_ID=1/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

第三行记录:

D:\LAMP\MYSQL5\data>mysqlbinlog –start-position=134 –stop-position=254 yuelian

gdao_binglog.000001 > c:\\test1.txt

内容:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 134

#7122 16:55:31 server id 1 end_log_pos 254

Query

thread_id=1

exec_time=0

error_code=0

use test/*!*/;

SET TIMESTAMP=1196585731/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;

SET @@session.sql_mode=1344274432/*!*/;Linux 专题

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;

create table a1(id int not null auto_increment primary key,

str varchar(1000)) engine=myisam/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

第四行的记录:

D:\LAMP\MYSQL5\data>mysqlbinlog –start-position=254 –stop-position=330 yuelian

gdao_binglog.000001 > c:\\test1.txt

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 254

#7122 16:22:36 server id 1 end_log_pos 330

Query

thread_id=1

exec_time=0

error_code=0

use test/*!*/;

SET TIMESTAMP=1196583756/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;

SET @@session.sql_mode=1344274432/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;

use test; insert into a1(str) values (‘I love you’),(‘You love me’)/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

5、查看这些东西是为了恢复数据,而不是为了好玩。所以我们最中还是为了要导入结果到 MYSQL 中。

D:\LAMP\MYSQL5\data>mysqlbinlog –start-position=134 –stop-position=330 yuelian

gdao_binglog.000001 | mysql -uroot – p [ 管道命令,登录 mysql 服务器,将日志内容传入,实

]

现恢复]

或者

D:\LAMP\MYSQL5\data>mysqlbinlog –start-position=134 –stop-position=330 yuelian

gdao_binglog.000001 >test1.txt

进入 MYSQL 导入

mysql> source c:\\test1.txt

[linux 命令,source FileName 作用:在当前 bash 环境下读取并执行

FileName 中的命令。注:该命令通常用命令“.”来替代。]

Query OK, 0 rows affected

Query OK, 0 rows affected

Database changed

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Charset changed

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

6、查看数据:

mysql> show tables;

(0.00 sec)

(0.00 sec)

(0.00 sec)

(0.00 sec)

(0.00 sec)

(0.00

(0.03

(0.00

(0.00

sec)

sec)

sec)

sec)Linux 专题

+—————-+

| Tables_in_test |

+—————-+

| a1

|

+—————-+

1 row in set (0.01 sec)

mysql> select * from a1;

+—-+————-+

| id | str

|

+—-+————-+

| 1 | I love you |

| 2 | You love me |

+—-+————-+

2 rows in set (0.00 sec)

问:我从本地新建了一个和服务器相同的表,但是忘记备份服务器的表,结果,上传到服务器后就覆盖了

原来的表,结果里面的信息都丢了,有办法恢复吗?

答:如果你开启了二进制日志,当然可以恢复。

来源: http://blog.chinaunix.net/u/29134/showart_434296.html

使用 mysqlbinlog 恢复数据

mysqlbinlog 工具的使用,大家可以看 MySQL 的帮助手册。里面有详细的用,

在这个例子中,重点是–start-position 参数和–stop-position 参数的使用。

  • –start-position=N

从二进制日志中第 1 个位置等于 N 参量时的事件开始读。

  • –stop-position=N

从二进制日志中第 1 个位置等于和大于 N 参量时的事件起停止读。

1、OK,现在开始,要启动二进制日志记录,

要先在 my.cnf / my.ini 文件的 mysqld 里添加

log-bin=日志名

在这里,偶是的设置是 log-bin=liangck

然后再启动 mysql 服务,因为偶是用 windows 系统,

所以执行 net start mysql 命令即可。

2、然后在一测试数据库里,创建一个表,并添加记录。

SQL codemysql> create table test(id int auto_increment not null primary key,

val int,data varchar(20));

mysql> insert into test(val,data) values(10,’liang’);

Query OK, 1 row affected (0.03 sec)

mysql> insert into test(val,data) values(20,’jia’);

Query OK, 1 row affected (0.08 sec)

mysql> insert into test(val,data) values(30,’hui’);

Query OK, 1 row affected (0.03 sec)

mysql> flush logs;

–产生第二个日志文件

Query OK, 0 rows affected (0.09 sec)Linux 专题

mysql> insert into test(val,data) values(40,’aaa’);

Query OK, 1 row affected (0.05 sec)

mysql> insert into test(val,data) values(50,’bbb’);

Query OK, 1 row affected (0.03 sec)

mysql> insert into test(val,data) values(60,’ccc’);

Query OK, 1 row affected (0.03 sec)

mysql> delete from test where id between 4 and 5;

–删除记录

Query OK, 2 rows affected (0.05 sec)

mysql> insert into test(val,data) values(70,’ddd’);

Query OK, 1 row affected (0.03 sec)

mysql> flush logs;

–产生第三个文件文件

Query OK, 0 rows affected (0.11 sec)

mysql> insert into test(val,data) values(80,’dddd’);

Query OK, 1 row affected (0.05 sec)

mysql> insert into test(val,data) values(90,’eeee’);

Query OK, 1 row affected (0.03 sec)

mysql> drop table test;

–删除表

Query OK, 0 row affected (0.05 sec)

――――――――――――――――――――――――――――――――――

3、OK,现在测试数据已经建好了,要求是什么呢? 就是将 test 表的数据全部恢复出来。

先用 mysqlbinlog 工具将日志文件生成 txt 文件出来分析。

F:\Program Files\MySQL_Data\data\log> mysqlbinlog liangck.000001 > G:\001.txt

F:\Program Files\MySQL_Data\data\log> mysqlbinlog liangck.000002 > G:\002.txt

F:\Program Files\MySQL_Data\data\log> mysqlbinlog liangck.000003 > G:\003.txt

通过这三个命令,可以在 G 盘下生成 3 个文件,里面分别记录了日志文件的内容, 也就是用户操作的步骤。

因为我们需要重做第一个日志文件的所有操作,所以这里只需要将第一个日志文件全恢复就行了。

F:\Program Files\MySQL_Data\data\log> mysqlbinlog liangck.000001 | mysql -uroot –p

Ok,接着,我们需要分析的是第二个日志文件。为什么要分析它呢, 因为它中途执行了一个操作是 DELETE,

因为我们要做的是恢复全部数据,也就是我们不希望去重做这个语句。所以在这里我们要想办法去绕开它。

我们先打开 002.txt 文件来分析一下。 在这个文件中,我们可以看到 DELETE 的操作的起始位置是 875,

终止位置是 1008. 那么我们只要重做第二个日志文件的开头到 875 的操作,然后再从 1008 到末尾的操作,

我们就可以把数据给恢复回来,而不会 DELETE 数据。所以执行两个命令:

F:\Program Files\MySQL_Data\data\log>mysqlbinlog liangck.000002 –stop-pos=875| mysql -uroot -p

F:\Program Files\MySQL_Data\data\log> liangck.000002 –start-pos=1008 | mysql -uroot -p mytest

OK,现在第二个日志文件的数据恢复好了。Linux 专题

第三个日志文件也是同理,只要找到 DROP TABLE 的位置,就可以了。

F:\Prog..\MySQL_Data\data\log> mysqlbinlog liangck.000003 –stop-pos=574|mysql -uroot –p

4、现在我们再查一下数据看看:

SQL codemysql> select * from test;

+—-+——+——-+

| id | val | data |

+—-+——+——-+

| 1 |

10 | liang |

| 2 |

20 | jia

|

| 3 |

30 | hui

|

| 4 |

40 | aaa

|

| 5 |

50 | bbb

|

| 6 |

60 | ccc

|

| 7 |

70 | ddd

|

| 8 |

80 | dddd |

| 9 |

90 | eeee |

+—-+——+——-+

9 rows in set (0.00 sec)

可以看到,全部数据都回来了。

来源:http://bbs.zbitedu.com/thread-21259-1-1.html

mysqlbinlog 怎么将数据库恢复到指定的时间?

来源:http://www.javaeye.com/problems/6634

发表评论

昵称*

邮箱*

网址