首页 > 代码库 > mysql 数据库定时备份 增量/全备份

mysql 数据库定时备份 增量/全备份

2024-10-04 05:40:39   219人阅读

实验楼的 MySQL 数据库需要设计一个自动备份方案，能够每周执行一次全备份，每天执行一次增量备份。

数据库名称为 shiyanlou，管理的用户名为 shiyanlou，密码为 shiyanlou。注意需要先手动启动 MySQL 服务。

目标

设计并实现备份方案，任务完成后满足以下要求：

  1. MySQL 服务处于运行状态
  2. 需要为服务器中的 shiyanlou 用户设定计划任务
  3. 计划任务中设定每周的周一凌晨3点执行一次全备份
  4. 计划任务中设定每天凌晨3点执行一次增量备份，周一不执行
  5. 请不要编写额外的脚本，将备份命令直接写入 crontab -u shiyanlou 中

提示语

mysqldump + binary logs

crontab -u shiyanlou -l 再次确认下计划任务策略是否准确

知识点

  1. MySQL 数据库备份
  2. MySQL 全备份与增量备份
  3. crontab 计划任务的设定

来源

实验楼测试团队日常数据备份场景

 

 

# GRANT ALL PRIVILEGES  ON  *.*  TO ‘monty‘@‘localhost‘   IDENTIFIED BY ‘some_pass‘ WITH GRANT OPTION;

# GRANT ALL PRIVILEGES ON *.* TO ‘monty‘@‘%‘   IDENTIFIED BY ‘some_pass‘ WITH GRANT OPTION;

#

GRANT命令说明：

ALL PRIVILEGES 是表示所有权限，你也可以使用select、update等权限。

ON 用来指定权限针对哪些库和表。

*.* 中前面的*号用来指定数据库名，后面的*号用来指定表名。

TO 表示将权限赋予某个用户。

‘monty‘@‘localhost‘ 表示monty用户，@ 后面接限制的主机，可以是IP、IP段、域名以及%，%表示任何地方。（注意：这里%有的版本不包括本地，以前碰到过给某个用户设置了%允许任何地方登录，但是在本地登录不了，这个和版本有关系，遇到这个问题再加一个localhost的用户就可以了。）

IDENTIFIED BY指定用户的登录密码。

WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。（注意：经常有人在创建操作用户的时候不指定WITH GRANT OPTION选项导致后来该用户不能使用GRANT命令创建用户或者给其他用户授权。）

备注：可以使用GRANT重复给用户添加权限，权限叠加，比如你先给用户添加了一个SELECT权限，然后又给用户添加了一个INSERT权限，那么该用户就同时拥有了SELECT和INSERT权限。

 

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -> ON bankaccount.*
    -> TO ‘custom‘@‘localhost‘
    -> IDENTIFIED BY ‘obscure‘;

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -> ON expenses.*
    -> TO ‘custom‘@‘whitehouse.gov‘
    -> IDENTIFIED BY ‘obscure‘;

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -> ON customer.*
    -> TO ‘custom‘@‘server.domain‘
    -> IDENTIFIED BY ‘obscure‘;

这3个账户分别可以用于：

  • 第1个账户可以访问bankaccount数据库，但只能本机访问。

  • 第2个账户可以访问expenses数据库，但只能从主机访问whitehouse.gov。

  • 第3个账户可以访问customer数据库，但只能从主机访问server.domain。

 

   

2.3.1 数据库备份

由于MySQL表保存为文件方式会很容易备份。要想保持备份的一致性，需要对相关表执行LOCK TABLES操作，然后对表执行FLUSH TABLES。你只需要读锁定；这样当你复制数据库目录中的文件时，允许其它客户继续查询表。需要FLUSH TABLES语句来确保开始备份前将所有激活的索引页写入硬盘。

如果你想要进行SQL级别的表备份，你可以使用SELECT INTO ...OUTFILE或BACKUP TABLE。对于SELECT INTO ...OUTFILE， 输出的文件不能先存在。对于BACKUP TABLE也如此，因为覆盖完整的文件会有安全风险。

对于InnoDB表，可以进行在线备份，不需要对表进行锁定。

MySQL支持增量备份：需要用--log-bin选项来启动服务器以便启用二进制日志。当想要进行增量备份时(包含上一次完全备份或增量备份之后的所有更改)，应使用FLUSH LOGS回滚二进制日志。然后，你需要将从最后的完全或增量备份的某个时刻到最后某个点的所有二进制日志复制到备份位置。这些二进制日志为增量备份；恢复时，按照下面的解释应用。下次进行完全备份时，还应使用FLUSH LOGS或mysqlhotcopy --flushlogs回滚二进制日志。

如果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服务器从而启用二进制日志它才生效。

如果MySQL服务器启用了二进制日志，你可以使用mysqlbinlog工具来恢复从指定的时间点开始(例如，从你最后一次备份)直到现在或另一个指定的时间点的数据。

  1. 恢复原mysqldump备份，或二进制备份。

  2. 执行下面的命令重新更新二进制日志：

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

在某些情况下，你可能只想要从某个位置重新运行某些二进制日志。（通常你想要根据恢复备份的日期重新运行所有二进制日志）。

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

  • 要想复制表，使用SELECT * INTO OUTFILE ‘file_name‘ FROM tbl_name语句。

  • 要想重载表，使用LOAD DATA INFILE ‘file_name‘ REPLACE ...载入并恢复表。要避免复制记录，表必须有PRIMARY KEY或一个UNIQUE索引。当新记录复制唯一键值的旧记录时，REPLACE关键字可以将旧记录替换为新记录。

如果备份时遇到服务器性能问题，有用的一个策略是在子服务器而不是主服务器上建立复制并执行备份。

如果使用Veritas文件系统，可以这样备份：

  1. 从客户端程序执行FLUSH TABLES WITH READ LOCK语句。

  2. 从另一个shell执行mount vxfs snapshot命令。

  3. 从第一个客户端执行UNLOCK TABLES。

  4. 从快照复制文件。

  5. 卸载快照。

2.3.2 备份与恢复策略示例

1） 备份策略

我们都知道必须按计划定期进行备份。可以用一些工具(某个时间点的数据快照)完全备份MySQL。例如，InnoDB Hot Backup为InnoDB数据文件提供在线非数据块物理备份，mysqldump提供在线逻辑备份。

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

mysqldump -u root --single-transaction --all-databases > backup_sunday_1_PM.sql

以上方法是在线非数据块备份，不会干扰对表的读写。我们假定我们以前的表为InnoDB表，因此--single-transaction一致性地表，并且保证mysqldump所 看见的数据不会更改。(其它客户端对InnoDB表进行的更改不会被mysqldump进程看见）。如果我们还有其它类型的表，我们必须假定在备份过程中 它们不会更改。例如，对于mysql数据库中的MyISAM表，我们必须假定在备份过程中没有对MySQL账户进行管理更改。

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

进行完全备份的时候有时不方便，因为会产生大的备份文件并需要花时间来生成。从某个角度来看，完全备份并不理想，因为每个成功的完全备份都包括所有 数据，甚至包括自从上一次完全备份以来没有被更改的部分。完成了初始完全备份后，进行增量备份会更有效。这样备份文件要小得多，备份时间也较短。缺点是， 恢复时不能只重载完全备份来恢复数据。还必须要用增量备份来恢复增量更改。

要想进行增量备份，我们需要保存增量更改。应使用--log-bin选项启动MySQL服务器，以便更新数据时将这些更改保存到文件中。该选项启用二进制日志，因此服务器会将每个更新数据的SQL语句写入到MySQL二进制日志。让我们看看用--log-bin选项启动的已经运行多日的MySQL服务器的数据目录。找到以下MySQL二进制日志文件：

技术分享

每次重启，MySQL服务器都会使用以上序列中的下一个编号创建一个新的二进制日志文件。当服务器运行时，你还可以通过执行FLUSH LOGS SQL语句或mysqladmin flush-logs命令，告诉服务器关闭当前的二进制日志文件并创建一个新文件。mysqldump也有一个选项来清空日志。数据目录中的.index文件包含该目录下所有MySQL二进制日志的清单，该文件用于复制。

恢复时MySQL二进制日志很重要，因为它们是增量备份。如果进行完全备份时确保清空了日志，则后面创建的二进制日志文件包含了备份后的所有数据更改。让我们稍稍修改前面的mysqldump命令，让它在完全备份时能够清空 MySQL二进制日志，以便转储文件包含包含新的当前二进制日志：

mysqldump -u root --single-transaction --flush-logs --master-data=http://www.mamicode.com/2 --all-databases > backup_sunday_1_PM.sql

执行该命令后，数据目录则包含新的二进制日志文件。产生的.sql文件包含下列行：

-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE=‘gbichot2-bin.000007‘,MASTER_LOG_POS=4;

因为mysqldump命令可以执行完全备份，以上行代表两件事情：

  1. .sql文件包含所有写入gbichot2-bin.000007二进制日志文件或最新的文件之前的更改。

  2. 备份后所记录的所有数据更改不出现在.sql中，但会出现在gbichot2-bin.000007二进制日志文件或最新的文件中。

在星期一下午1点，我们可以清空日志并开始根据新的二进制日志文件来创建增量备份。例如，执行mysqladmin flush-logs命令创建gbichot2-bin.000008。星期日下午1点的完全备份和星期一下午1点之间的所有更改为文件gbichot2-bin.000007。该增量备份很重要，因此最好将它复制到安全的地方。（例如，备份到磁带或DVD上，或复制到另一台机器上）。在星期二下午1点，执行另一个mysqladmin flush-logs命令，这样星期一下午1点和星期二下午1点之间的所有更改为文件gbichot2-bin.000008(也应复制到某个安全的地方)。

MySQL二进制日志占据硬盘空间。要想释放空间，应随时清空。操作方法是删掉不再使用的二进制日志，例如进行完全备份时输入以下命令：

shell> mysqldump --single-transaction --flush-logs --master-data=http://www.mamicode.com/2
       --all-databases --delete-master-logs > backup_sunday_1_PM.sql

注释：如果你的服务器为复制主服务器，用mysqldump方法中的 --delete-master-logs选项删掉MySQL二进制日志很危险，因为从服务器可能还没有完全处理该二进制日志的内容。关于这一点，PURGE MASTER LOGS语句的描述中解释了为什么在删掉MySQL二进制日志之前应进行确认一下。

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文件，我们可以用它来恢复大部分最新的数据更改，而不会丢失星期二下午1点到崩溃时刻之间的数据。

3） 备份策略摘要

出现操作系统崩溃或电源故障时，InnoDB自己可以完成所有数据恢复工作。但为了确保你可以睡好觉，应遵从下面的指导：

  • 一定用--log-bin或甚至--log-bin=log_name选项启动MySQL服务器，其中日志文件名位于某个安全媒介上，不同于数据目录所在驱动器。如果你有这样的安全媒介，最好进行硬盘负载均衡(这样能够提高性能)。

  • 定期进行完全备份，使用mysqldump命令进行在线非数据块备份。

  • 用FLUSH LOGS或mysqladmin flush-logs清空日志进行定期增量备份。

2.3.3 自动恢复

注意：由于实验楼环境中默认的mysql配置文件中把log_bin等日志设置选项给注释了，因此需要更改文件my.cnf：

# 登陆到root账户
shell> sudo -s
shell> vi /etc/mysql/my.cnf

技术分享

修改后然后保存my.cnf文件，重启mysql服务器，并查看日志是否启动：

shell> service mysql restart --log-bin
shell> mysql -u root
sql> show variables like ‘log_%‘;

技术分享

继续~

要想确定当前的二进制日志文件的文件名，在命令行中加入下面的MySQL语句：

shell> mysql -u root -e ‘SHOW BINLOG EVENTS \G‘

技术分享

1） 指定恢复时间

对于MySQL 5，可以在mysqlbinlog语句中通过--start-date和--stop-date选项指定DATETIME格式的起止时间。举例说明，假设在今天上午10:00(今天是2015年8月6日)，执行SQL语句来删除一个大表。要想恢复表和数据，你可以恢复前一晚上的备份，并从命令行输入以下命令：

shell> mysqlbinlog --stop-date="2015-8-6 10:01:00" /var/log/mysql/bin.123456      | mysql -u root -p mypwd

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

在以上行中，从上午10:01登录的SQL语句将运行。结合执行前夜的转储文件和mysqlbinlog的两行命令可以将所有数据恢复到上午10:00前一秒钟。你应检查日志以确保时间确切。下一节介绍如何实现。

2） 指定恢复位置

也可以不指定日期和时间，而使用mysqlbinlog的选项--start-position和--stop-position来指定日志位置。它们的作用与起止日选项相同，不同的是给出了从日志起的位置号。使用日志位置是更准确的恢复方法，特别是当由于破坏性SQL语句同时发生许多事务的时候。要想确定位置号，可以运行mysqlbinlog寻找执行了不期望的事务的时间范围，但应将结果重新指向文本文件以便进行检查。操作方法为：

shell> mysqlbinlog --start-date="2014-10-29 9:55:00" --stop-date="2014-10-29 10:05:00"       /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

该命令将在/tmp目录创建小的文本文件，将显示执行了错误的SQL语句时的SQL语句。你 可以用文本编辑器打开该文件，寻找你不要想重复的语句。如果二进制日志中的位置号用于停止和继续恢复操作，应进行注释。用`log_pos加一个数字来标 记位置。使用位置号恢复了以前的备份文件后，你应从命令行输入下面内容：

shell> mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456     | mysql -u root -pmypwd 

shell> mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456     | mysql -u root -pmypwd

上面的第1行将恢复到停止位置为止的所有事务。第二行将恢复从给定的起始位置直到二进制日志结束的所有事务。因为mysqlbinlog的输出包括每个SQL语句记录之前的SET TIMESTAMP语句，恢复的数据和相关MySQL日志将反映事务执行的原时间。

2.4 日志文件

2.4.1 错误日志

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

如果mysqld莫名其妙地死掉并且需要mysqld_safe重新启动它，那么mysqld_safe在错误日志中写入一条restarted mysqld消息。如果mysqld注意到需要自动检查或着修复一个表，则错误日志中将写入这条消息。

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

可以用--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文件。

2.4.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中，服务器打开日志文件期间你不能重新命名日志文件，你必须先停止服务器然后重新命名日志文件，然后重启服务器来创建新的日志文件。

2.4.3 二进制日志

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

二进制日志包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。语句以“事件”的形式保存，它描述数据更改。

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

二进制日志还包含关于每个更新数据库的语句的执行时间信息。它不包含没有修改任何数据的语句。如果你想要记录所有语句（例如，为了识别有问题的查询），你应使用一般查询日志。

二进制日志的主要目的是在恢复使能够最大可能地更新数据库，因为二进制日志包含备份后进行的所有更新。

二进制日志还用于在主复制服务器上记录所有将发送给从服务器的语句。

运行服务器时若启用二进制日志则性能大约慢1%。但是，二进制日志的好处即用于恢复并允许设置复制超过了这个小小的性能损失。

当用--log-bin[=file_name]选项启动服务器时，mysqld写入包含所有更新数据的SQL命令的日志文件。如果未给出file_name值， 默认名为-bin后面所跟的主机名。如果给出了文件名，但没有包含路径，则文件被写入数据目录。

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

mysqld在每个二进制日志名后面添加一个数字扩展名。每次你启动服务器或刷新日志时该数字则增加。如果当前的日志大小达到max_binlog_size时，还会自动创建新的二进制日志。如果你正使用大的事务，二进制日志大小还会超过max_binlog_size。（事务要全写入一个二进制日志中，绝对不要写入不同的二进制日志中。）

为了能够知道还使用了哪个不同的二进制日志文件，mysqld还创建一个二进制日志索引文件，包含所有使用的二进制日志文件的文件名。默认情况下与二进制日志文件的文件名相同，扩展名为‘.index‘。你可以用--log-bin-index[=file_name]选项更改二进制日志索引文件的文件名。当mysqld在运行时，不应手动编辑该文件；如果这样做将会使mysqld变得混乱。

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

二进制日志格式有一些已知限制，会影响从备份恢复。

可以使用下面的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-ignore-db规则。当前的数据库是否匹配binlog-ignore-db规则?

    有：不要写入语句，并退出。

    没有：写入查询并退出。

例如，只用binlog-do-db=sales运行的服务器只将当前数据库为sales的语句写入二进制日志(换句话说，binlog-do-db有时可以表示“忽视其它数据库”)。

如果你正进行复制，应确保没有子服务器在使用旧的二进制日志文件时，方可删除它们。一种方法是每天一次执行mysqladmin flush-logs并删除三天前的所有日志。可以手动删除，或最好使用PURGE MASTER LOGS语句删除日志。

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

你可以用mysqlbinlog实用工具检查二进制日志文件。如果你想要重新处理日志止的语句，这很有用。例如，可以从二进制日志更新MySQL服务器，方法如下：

shell> mysqlbinlog log-file | mysql -h server_name

如果你正使用事务，必须使用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服务器)崩溃，有可能二进制日志中最后的语句丢失了。要想防止这种情况，你可以使用sync_binlog全局变量(设置该变量值为1是最安全的值，但也是最慢的)，使二进制日志在每N次二进制日志写入后与硬盘同步。

该选项可以提供更大程度的安全，还应对MySQL服务器进行配置，使每个事务的二进制日志(sync_binlog =1)和(默认情况为真)InnoDB日志与硬盘同步。该选项的效果是崩溃后重启时，在滚回事务后，MySQL服务器从二进制日志剪切 回滚的InnoDB事务。这样可以确保二进制日志反馈InnoDB表的确切数据等，并使从服务器保持与主服务器保持同步(不接收回滚的语句)。

请注意即使MySQL服务器更新其它存储引擎而不是InnoDB，也可以使用--innodb-safe-binlog选项启动服务。在InnoDB崩溃恢复时，只能从二进制日志中删除影响InnoDB表的语句或事务。如果崩溃恢复时MySQL服务器发现二进制日志变短了(即至少缺少一个成功提交的InnoDB事务)，如果sync_binlog =1并且硬盘或文件系统的确能根据需要进行同步(有些不需要)则不会发生，则输出错误消息 ("二进制日志<名>比期望的要小")。在这种情况下，二进制日志不准确，复制应从主服务器的数据快照开始。

写入二进制日志文件和二进制日志索引文件的方法与写入MyISAM表的相同。

2.4.4 慢速查询日志

用--log-slow-queries[=file_name]选项启动服务时，mysqld会写入一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。其中，获得初使表锁定的时间不算作执行时间。

如果没有给出file_name值，默认为主机名，后缀为-slow.log。如果给出了文件名，但不是绝对路径名，文件则写入数据目录。

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

慢查询日志可以用来找到执行时间长的查询，可以用于优化。但是，检查又长又慢的查询日志会很困难。要想容易些，你可以使用mysqldumpslow命令获得日志中显示的查询摘要来处理慢查询日志。

在MySQL 5.1的慢查询日志中，不使用索引的慢查询同使用索引的查询一样记录。要想防止不使用索引的慢查询记入慢查询日志，使用--log-short-format选项。

在MySQL 5.1中,通过--log-slow-admin-statements服务器选项，你可以请求将慢管理语句，例如将OPTIMIZE TABLE、ANALYZE TABLE和ALTER TABLE语句写入慢查询日志。

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

2.4.5 日志文件维护

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

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

在 Linux (Redhat)的安装上，你可为此使用mysql-log-rotate脚本。如果你在RPM上分发安装MySQL，脚本应该已经自动被安装。

在其它系统上，你必须自己安装短脚本，你可从镜像网站获得处理日志文件。

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

日志清空操作主要完成下列事情：

  • 如果使用标准日志(--log)或慢查询日志(--log-slow-queries)，关闭并重新打开日志文件。(默认为mysql.log和`hostname-slow.log`)。
  • 如果使用更新日志(--log-update)或二进制日志(--log-bin)，关闭日志并且打开有更高序列号的新日志文件。

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

    shell> cd mysql-data-directory
shell> mv mysql.log mysql.old
shell> mysqladmin flush-logs

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

 

mysql 数据库定时备份 增量/全备份


联系
我们