马尼拉做网站,商城县,如何建设一个自己的网站,西安app开发echo 开始:$Begin 结束:$Last $GZDumpFile succ $LogFilecd $BakDir/daily/bin/rm -f * 2#xff09;增量备份脚本#xff08;脚本中mysql的数据存放路径是/home/mysql/data#xff0c;具体根据自己的实际情况进行调整#xff09;[roottest-huanqiu ~]# vim /root…echo 开始:$Begin 结束:$Last $GZDumpFile succ $LogFilecd $BakDir/daily/bin/rm -f * 2增量备份脚本脚本中mysql的数据存放路径是/home/mysql/data具体根据自己的实际情况进行调整[roottest-huanqiu ~]# vim /root/Mysql-DailyBak.sh#!/bin/bash# Program# use cp to backup mysql data everyday!# History# PathBakDir/home/mysql/backup/daily //增量备份时复制mysql-bin.00000*的目标目录提前手动创建这个目录BinDir/home/mysql/data //mysql的数据目录LogFile/home/mysql/backup/bak.logBinFile/home/mysql/data/mysql-bin.index //mysql的index文件路径放在数据目录下的/usr/local/mysql/bin/mysqladmin -uroot -p123456 flush-logs#这个是用于产生新的mysql-bin.00000*文件Counterwc -l $BinFile |awk {print $1}NextNum0#这个for循环用于比对$Counter,$NextNum这两个值来确定文件是不是存在或最新的for file in cat $BinFiledo basebasename $file #basename用于截取mysql-bin.00000*文件名去掉./mysql-bin.000005前面的./ NextNumexpr $NextNum 1 if [ $NextNum -eq $Counter ] then echo $base skip! $LogFile else dest$BakDir/$base if(test -e $dest) #test -e用于检测目标文件是否存在存在就写exist!到$LogFile去 then echo $base exist! $LogFile else cp $BinDir/$base $BakDir echo $base copying $LogFile fi fidoneecho date %Y年%m月%d日 %H:%M:%S $Next Bakup succ! $LogFile 3设置crontab任务执行备份脚本。先执行的是增量备份脚本然后执行的是全量备份脚本[roottest-huanqiu ~]# crontab -e#每个星期日凌晨3:00执行完全备份脚本0 3 * * 0 /bin/bash -x /root/Mysql-FullyBak.sh /dev/null 21#周一到周六凌晨3:00做增量备份0 3 * * 1-6 /bin/bash -x /root/Mysql-DailyBak.sh /dev/null 21 4手动执行上面两个脚本测试下备份效果[roottest-huanqiu backup]# pwd/home/mysql/backup[roottest-huanqiu backup]# mkdir daily[roottest-huanqiu backup]# lltotal 4drwxr-xr-x. 2 root root 4096 Nov 29 11:29 daily[roottest-huanqiu backup]# ll daily/total 0 先执行增量备份脚本[roottest-huanqiu backup]# sh /root/Mysql-DailyBak.sh [roottest-huanqiu backup]# lltotal 8-rw-r--r--. 1 root root 121 Nov 29 11:29 bak.logdrwxr-xr-x. 2 root root 4096 Nov 29 11:29 daily[roottest-huanqiu backup]# ll daily/total 8-rw-r-----. 1 root root 152 Nov 29 11:29 mysql-binlog.000030-rw-r-----. 1 root root 152 Nov 29 11:29 mysql-binlog.000031[roottest-huanqiu backup]# cat bak.log mysql-binlog.000030 copyingmysql-binlog.000031 copyingmysql-binlog.000032 skip!2016年11月29日 11:29:32 Bakup succ! 然后执行全量备份脚本[roottest-huanqiu backup]# sh /root/Mysql-FullyBak.sh 20161129.sql[roottest-huanqiu backup]# lltotal 152-rw-r--r--. 1 root root 145742 Nov 29 11:30 20161129.sql.tgz-rw-r--r--. 1 root root 211 Nov 29 11:30 bak.logdrwxr-xr-x. 2 root root 4096 Nov 29 11:30 daily[roottest-huanqiu backup]# ll daily/total 0[roottest-huanqiu backup]# cat bak.log mysql-binlog.000030 copyingmysql-binlog.000031 copyingmysql-binlog.000032 skip!2016年11月29日 11:29:32 Bakup succ!开始:2016年11月29日 11:30:38 结束:2016年11月29日 11:30:38 20161129.sql.tgz succ ***************当你发现自己的才华撑不起野心时就请安静下来学习吧*************** 实验楼的 MySQL 数据库需要设计一个自动备份方案能够每周执行一次全备份每天执行一次增量备份。 数据库名称为 shiyanlou管理的用户名为 shiyanlou密码为 shiyanlou。注意需要先手动启动 MySQL 服务。 目标 设计并实现备份方案任务完成后满足以下要求 MySQL 服务处于运行状态需要为服务器中的 shiyanlou 用户设定计划任务计划任务中设定每周的周一凌晨3点执行一次全备份计划任务中设定每天凌晨3点执行一次增量备份周一不执行请不要编写额外的脚本将备份命令直接写入 crontab -u shiyanlou 中提示语 mysqldump binary logs crontab -u shiyanlou -l 再次确认下计划任务策略是否准确 知识点 MySQL 数据库备份MySQL 全备份与增量备份crontab 计划任务的设定来源 实验楼测试团队日常数据备份场景 # GRANT ALL PRIVILEGES ON *.* TO montylocalhost 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 表示将权限赋予某个用户。 montylocalhost 表示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 customlocalhost- IDENTIFIED BY obscure;mysql GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP- ON expenses.*- TO customwhitehouse.gov - IDENTIFIED BY obscure; mysql GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP - ON customer.* - TO customserver.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工具来恢复从指定的时间点开始(例如从你最后一次备份)直到现在或另一个指定的时间点的数据。 恢复原mysqldump备份或二进制备份。 执行下面的命令重新更新二进制日志 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文件系统可以这样备份 从客户端程序执行FLUSH TABLES WITH READ LOCK语句。 从另一个shell执行mount vxfs snapshot命令。 从第一个客户端执行UNLOCK TABLES。 从快照复制文件。 卸载快照。 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-data2 --all-databases backup_sunday_1_PM.sql执行该命令后数据目录则包含新的二进制日志文件。产生的.sql文件包含下列行 -- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILEgbichot2-bin.000007,MASTER_LOG_POS4; 因为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命令这样星期一下午1点和星期二下午1点之间的所有更改为文件gbichot2-bin.000008(也应复制到某个安全的地方)。 MySQL二进制日志占据硬盘空间。要想释放空间应随时清空。操作方法是删掉不再使用的二进制日志例如进行完全备份时输入以下命令 shell mysqldump --single-transaction --flush-logs --master-data2--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-binlog_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 \G1 指定恢复时间 对于MySQL 5可以在mysqlbinlog语句中通过--start-date和--stop-date选项指定DATETIME格式的起止时间。举例说明假设在今天上午10:00(今天是2015年8月6日)执行SQL语句来删除一个大表。要想恢复表和数据你可以恢复前一晚上的备份并从命令行输入以下命令 shell mysqlbinlog --stop-date2015-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-date2014-10-29 9:55:00 --stop-date2014-10-29 10:05:00 \/var/log/mysql/bin.123456 /tmp/mysql_restore.sql 该命令将在/tmp目录创建小的文本文件将显示执行了错误的SQL语句时的SQL语句。你 可以用文本编辑器打开该文件寻找你不要想重复的语句。如果二进制日志中的位置号用于停止和继续恢复操作应进行注释。用log_pos加一个数字来标 记位置。使用位置号恢复了以前的备份文件后你应从命令行输入下面内容 shell mysqlbinlog --stop-position368312 /var/log/mysql/bin.123456 \| mysql -u root -pmypwd shell mysqlbinlog --start-position368315 /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-binfile_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-dbdb_name 告诉主服务器如果当前的数据库(即USE选定的数据库)是db_name应将更新记录到二进制日志中。其它所有没有明显指定的数据库 被忽略。如果使用该选项你应确保只对当前的数据库进行更新。 对于CREATE DATABASE、ALTER DATABASE和DROP DATABASE语句有一个例外即通过操作的数据库来决定是否应记录语句而不是用当前的数据库。 一个不能按照期望执行的例子如果用binlog-do-dbsales启动服务器并且执行USE prices; UPDATE sales.january SET amountamount1000该语句不写入二进制日志。 --binlog-ignore-dbdb_name 告诉主服务器如果当前的数据库(即USE选定的数据库)是db_name不应将更新保存到二进制日志中。如果你使用该选项你应确保只对当前的数据库进行更新。 一个不能按照你期望的执行的例子如果服务器用binlog-ignore-dbsales选项启动并且执行USE prices; UPDATE sales.january SET amountamount1000该语句不被写入二进制日志。 类似于--binlog-do-db对于CREATE DATABASE、ALTER DATABASE和DROP DATABASE语句有一个例外即通过操作的数据库来决定是否应记录语句而不是用当前的数据库。 要想记录或忽视多个数据库可以在启动服务器的时候使用多个选项为每个数据库指定相应的选项。 服务器根据下面的规则对选项进行评估以便将更新记录到二进制日志中或忽视。请注意对于CREATE/ALTER/DROP DATABASE语句有一个例外。在这些情况下根据以下列出的不同情况所创建、修改或删除的数据库将代替当前的数据库。 是否有binlog-do-db或binlog-ignore-db规则? 没有将语句写入二进制日志并退出。 有执行下一步。 有一些规则(binlog-do-db或binlog-ignore-db或二者都有)。当前有一个数据库(是否使用USE语句选择了数据库)? 没有不要写入语句并退出。 有执行下一步。 有一些binlog-ignore-db规则。当前的数据库是否匹配binlog-ignore-db规则? 有不要写入语句并退出。 没有写入查询并退出。 例如只用binlog-do-dbsales运行的服务器只将当前数据库为sales的语句写入二进制日志(换句话说binlog-do-db有时可以表示“忽视其它数据库”)。 如果你正进行复制应确保没有子服务器在使用旧的二进制日志文件时方可删除它们。一种方法是每天一次执行mysqladmin flush-logs并删除三天前的所有日志。可以手动删除或最好使用PURGE MASTER LOGS语句删除日志。 具有SUPER权限的客户端可以通过SET SQL_LOG_BIN0语句禁止将自己的语句记入二进制记录。 你可以用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”。 转载于:https://www.cnblogs.com/ggh03/p/6980149.html
