MySQL binlog
查看日志是否开启
1).可以通过Mysql配置文件my.cnf来确认(Mysql默认开启二进制日志记录):
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
刷新日志
flush logs;
查看当前日志位置
show master status;
查看当前所有日志
show master logs;
清空所有的bin-log日志
reset master;
查看日志内容
mysqlbinlog –no-defaults mysql-bin.00001;
删除bin-log
mysql> purge binary logs to 'ablelee.000003';
Query OK, 0 rows affected (0.16 sec)
关闭 bin-log 日志
找到配置文件my.cnf,对于linux,一般默认在/etc目录下,打开此文件,使用井号(#)注释掉如下两个配置项目即可。
log-bin=mysql-bin
binlog_format=mixed
显示所有日志
mysql> show binary logs;
1.前言
日志是把数据库的每一个变化都记载到一个专用的文件里,这种文件就叫做日志文件。Mysql默认只打开出错日志,因为过多的日志将会影响系统的处理性能。
在5.0前支持文本格式和二进制格式,5.0后只支持二进制格式,因为二进制日志在性能、信息处理方面有更多的优点。
2.基础知识
2.1、二进制日志的启用
二进制日志由配置文件的log-bin选项负责启用,Mysql服务器将在数据根目录创建两个新文件XXX-bin.001和XXX-bin.index,若配置选项没有给出文件名,Mysql将使用主机名称命名这两个文件,其中.index文件包含一份全体日志文件的清单。
Mysql会把用户对所有数据库的内容和结构的修改情况记入XXX-bin.n文件,而不会记录SELECT和没有实际
2.2、更新的UPDATE语句。
日志文件的扩展
当停止或重启时,服务器会把日志文件记入下一个日志文件,Mysql会在重启时生成一个新的日志文件,文件序号递增,此外,如果日志文件超过max_binlog_size系统变量配置的上限时,也会生成新的日志文件。
2.3、日志文件的查看
Mysql提供了mysqlbinlog命令来查看日志文件,如mysqlbinlog xxx-bin.001 | more。在记录每条变更日志的时候,日志文件都会把当前时间给记录下来,以便进行数据库恢复。
2.4、日志文件的停用
可以使用SET SQL_LOG_BIN=0命令停止使用日志文件,然后可以通过SET SQL_LOG_BIN=1命令来启用。
2.5、使用日志进行数据库恢复
如果遇到灾难事件,应该用最近一次制作的完整备份恢复数据库,然后使用备份之后的日志
文件把数据库恢复到最接近现在的可用状态。
使用日志进行恢复时需要依次进行,即最早生成的日志文件要最先恢复:
mysqlbinlog xxx-bin.00001 | mysql -u root -p
mysqlbinlog xxx-bin.00002 | mysql -u root -p
3.日志跟换策略
使用索引来循环文件,在以下条件将循环至下一个索引
a.服务器重启
b.服务器被更新
c.日志达到了最大日志长度max_binlog_size
d.日志被刷新mysql> flush logs;
4.日志格式
从官网文档中看到,之前的MySQL一直都只有基于statement的复制模式,直到5.1.5版本的MySQL才开始支持row level的复制。从5.0开始,MySQL的复制已经解决了大量老版本中出现的无法正确复制的问题。但是由于存储过程的出现,给MySQL Replication复制又带来了更大的新挑战。另外,看到官方文档说,从5.1.8版本开始,MySQL提供了除Statement Level和Row Level之外的第三种复制模式:Mixed,实际上就前两种模式的结合。在Mixed模式下,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也就是在Statement和Row之间选择一种。新版本中的Statement Level还是和以前一样,仅仅记录执行的语句。而新版本的MySQL中对row level模式也被做了优化,并不是所有的修改都会以row level来记录,像遇到表结构变更的时候就会以statement模式来记录,如果sql语句确实就是update或者delete等修改数据的语句,那么还是会记录所有行的变更。
--基于SQL语句的复制(statement-based replication,SBR),
--基于行的复制(row-based replication,RBR),
--混合模式复制(mixed-based replication,MBR)。
静态设置binlog格式:
vi my.cnf log-bin = mysql-bin #binlog_format = "STATEMENT" #binlog_format = "ROW" binlog_format = "MIXED"
动态修改binlog格式:
mysql> SET SESSION binlog_format = 'STATEMENT'; mysql> SET SESSION binlog_format = 'ROW'; mysql> SET SESSION binlog_format = 'MIXED'; mysql> SET GLOBAL binlog_format = 'STATEMENT'; mysql> SET GLOBAL binlog_format = 'ROW'; mysql> SET GLOBAL binlog_format = 'MIXED';
5.binary log相关变量和参数
5.1、命令行参数
--log-bin [=file_name]
设置此参数表示启用binlog功能,并制定路径名称。
--log-bin-index[=file]
设置此参数是指定二进制索引文件的路径与名称。
--max_binlog_size
Binlog最大值,最大和默认值是1GB,该设置并不能严格控制Binlog的大小,尤其是Binlog比较靠近最大值而又遇到一个比较大事务时,
为了保证事务的完整性,不可能做切换日志的动作,只能将该事务的所有SQL都记录进当前日志,直到事务结束。
--binlog-do-db=db_name
此参数表示只记录指定数据库的二进制日志
--binlog-ignore-db=db_name
此参数表示不记录指定的数据库的二进制日志
5.2、系统变量
log_bin
binlog_cache_size
此参数表示binlog使用的内存大小,可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。
max_binlog_cache_size
此参数表示binlog使用的内存最大的尺寸
binlog_cache_use
使用二进制日志缓存的事务数量
binlog_cache_disk_use
使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量。
binlog_do_db
binlog_ignore_db
sync_binlog
这个参数直接影响mysql的性能和完整性。
sync_binlog=0:
当事务提交后,Mysql仅仅是将binlog_cache中的数据写入binlog文件,但不执行fsync之类的磁盘,同步指令通知文件系统将缓存刷新到磁盘,而让Filesystem自行决定什么时候来做同步,这个是性能最好的。
sync_binlog=0,在进行n次事务提交以后,Mysql将执行一次fsync之类的磁盘同步指令,通知文件系统将Binlog文件缓存刷新到磁盘。
Mysql中默认的设置是sync_binlog=0,即不做任何强制性的磁盘刷新指令,这时性能是最好的,但风险也是最大的。一旦系统Crash,在文件系统缓存中的所有Binlog信息都会丢失。
6.常见问题
6.1、如何清除binlog
--使用下面的两个命令
PURGE {MASTER|BINARY} LOGS TO 'log_name' //log_name不会被清除
PURGE {MASTER|BINARY} LOGS BEFORE 'date' //date不会被清除
实例如下:
mysql> show master logs; +----------------------+-----------+ | Log_name | File_size | +----------------------+-----------+ | mysql3306-bin.000001 | 107 | +----------------------+-----------+ 1 row in set (0.00 sec) mysql> flush logs; Query OK, 0 rows affected (0.11 sec) mysql> flush logs; Query OK, 0 rows affected (0.02 sec) mysql> flush logs; Query OK, 0 rows affected (0.01 sec) mysql> flush logs; Query OK, 0 rows affected (0.01 sec) mysql> show master logs; +----------------------+-----------+ | Log_name | File_size | +----------------------+-----------+ | mysql3306-bin.000001 | 154 | | mysql3306-bin.000002 | 154 | | mysql3306-bin.000003 | 154 | | mysql3306-bin.000004 | 154 | | mysql3306-bin.000005 | 107 | +----------------------+-----------+ 5 rows in set (0.00 sec) mysql> purge master logs to 'mysql3306-bin.000002'; Query OK, 0 rows affected (0.01 sec) mysql> show master logs; +----------------------+-----------+ | Log_name | File_size | +----------------------+-----------+ | mysql3306-bin.000002 | 154 | | mysql3306-bin.000003 | 154 | | mysql3306-bin.000004 | 154 | | mysql3306-bin.000005 | 107 | +----------------------+-----------+ 4 rows in set (0.00 sec)
[root@node4 data]# date Tue Jul 30 01:27:04 CST 2013 mysql> flush logs; Query OK, 0 rows affected (0.01 sec) mysql> show master logs; +----------------------+-----------+ | Log_name | File_size | +----------------------+-----------+ | mysql3306-bin.000002 | 154 | | mysql3306-bin.000003 | 154 | | mysql3306-bin.000004 | 154 | | mysql3306-bin.000005 | 154 | | mysql3306-bin.000006 | 107 | +----------------------+-----------+ 5 rows in set (0.00 sec) mysql> purge master logs before '2013-07-30 01:27:04'; Query OK, 0 rows affected (0.02 sec) mysql> show master logs; +----------------------+-----------+ | Log_name | File_size | +----------------------+-----------+ | mysql3306-bin.000005 | 154 | | mysql3306-bin.000006 | 107 | +----------------------+-----------+ 2 rows in set (0.00 sec)
--或使用命令:
RESET MASTER
删除之前所有的binlog,并重新生成新的binlog,后缀从000001开始。
注:如果您有一个活性的从属服务器,该服务器当前正在读取您正在试图删除的日志之一,则本语句不会起作用,而是失败,并伴随一个错误。
不过,如果从属服务器是休止的,并且您碰巧清理了其想要读取的日志之一,则从属服务器启动后不能复制。
当从属服务器正在复制时,本语句可以安全运行。您不需要停止它们。
6.2、记录到二进制日志知的内容配置
binlog-do-db=sales 只记录sales库 binlog-ignore-db=sales 除sales库不记录,其他都记录。
但是如果在操作数据库之前,不使用use $dbname 那么所有的SQL都不会记录 如果使用了use $dbname,那么判断规则取决于这里的$dbname,而不是SQL中操作的库
6.3、二进制日志不准确的处理
默认情况下,并不是每次写入时都将二进制日志与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃,有可能二进制日志中最后的语句丢失。 要想防止这种情况,你可以使用sync_binlog全局变量(1是最安全的值,但也是最慢的),使二进制日志在每N次二进制日志写入后与硬盘同步。 即使sync_binlog设置为1,出现崩溃时,也有可能表内容和二进制日志内容之间存在不一致性。
如果崩溃恢复时MySQL服务器发现二进制日志变短了(即至少缺少一个成功提交的InnoDB事务), 如果sync_binlog =1并且硬盘/文件系统的确能根据需要进行同步(有些不需要)则不会发生,则输出错误消息 (“二进制日志<名>比期望的要小”)。 在这种情况下,二进制日志不准确,复制应从主服务器的数据快照开始。 为了您的安全,请只打开来源可靠的网址