不论哪个数据库产品，一定会有日志文件。在MariaDB和MySQL中，主要有5种日志文件：
|
1 2 3 4 5 |
1.错误日志(error log)：记录mysql服务的启停时正确和错误的信息，还记录启动、停止、运行过程中的错误信息。 2.查询日志(general log)：记录建立的客户端连接和执行的语句。 3.二进制日志(bin log)：记录所有更改数据的语句，可用于数据复制。 4.慢查询日志(slow log)：记录所有执行时间超过long_query_time的所有查询或不使用索引的查询。 5.中继日志(relay log)：主从复制时使用的日志。 |
|
1 |
除了这5种日志，在需要的时候还会创建DDL日志。本文暂先讨论错误日志、一般查询日志、慢查询日志和二进制日志，中继日志和主从复制有关，将在复制的章节中介绍。 |
1.日志刷新操作
以下操作会刷新日志文件，刷新日志文件时会关闭旧的日志文件并重新打开日志文件。对于有些日志类型，如二进制日志，刷新日志会滚动日志文件，而不仅仅是关闭并重新打开。
|
1 2 3 |
mysql> FLUSH LOGS; shell> mysqladmin flush-logs #注意这个要带账号密码 shell> mysqladmin refresh #注意这个要带账号密码 |
2.错误日志
错误日志是最重要的日志之一，它记录了MariaDB/MySQL服务启动和停止正确和错误的信息，还记录了mysqld实例运行过程中发生的错误事件信息。
可以在MariaDB/MySQL配置文件(my.cnf)中的mysqld配置部分，使用log-error指定错误日志的路径
如果不知道错误日志的位置，可以查看变量log_error来查看。
注意：在MySQL 5.5.7之前，刷新日志操作(如flush logs)会备份旧的错误日志(以_old结尾)，并创建一个新的错误日志文件并打开，在MySQL 5.5.7之后，执行刷新日志的操作时，错误日志会关闭并重新打开，如果错误日志不存在，则会先创建。
在MariaDB/MySQL正在运行状态下删除错误日志后，不会自动创建错误日志，只有在刷新日志的时候才会创建一个新的错误日志文件。
3.一般查询日志
查询日志分为一般查询日志和慢查询日志，它们是通过查询是否超出变量 long_query_time 指定时间的值来判定的。在超时时间内完成的查询是一般查询，可以将其记录到一般查询日志中(但是建议关闭一般查询这种日志（默认是关闭的))，超出时间的查询是慢查询，可以将其记录到慢查询日志中。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
使用 " --general_log={0|1} " 来决定是否启用一般查询日志， 使用 "--general_log_file=file_name" 来指定查询日志的路径。不给定路径时默认的文件名以 `hostname`.log 命名。 和查询日志有关的变量有： long_query_time = 10 # 指定慢查询超时时长，超出此时长的属于慢查询，会记录到慢查询日志中 log_output={TABLE|FILE|NONE} # 定义一般查询日志和慢查询日志的输出格式，不指定时默认为file #注 TABLE表示记录日志到表中，FILE表示记录日志到文件中，NONE表示不记录日志。只要这里指定为NONE，即使开启了一般查询日志和慢查询日志，也都不会有任何记录。 |
和一般查询日志相关的变量有：
|
1 2 3 |
set global general_log=off # 是否启用一般查询日志，为全局变量，必须在global上修改。 set global sql_log_off=off # 在session级别控制是否启用一般查询日志，默认为off，即启用 set global general_log_file=/mydata/data/hostname.log # 默认是库文件路径下主机名加上.log |
|
1 |
默认没有开启一般查询日志，也不建议开启一般查询日志。 |
4.慢查询日志
查询超出变量 long_query_time 指定时间值的为慢查询。 但是查询获取锁(包括锁等待)的时间不计入查询时间内。
mysql记录慢查询日志是在查询执行完毕且已经完全释放锁之后才记录的，因此慢查询日志记录的顺序和执行的SQL查询语句顺序可能会不一致(例如语句1先执行，查询速度慢，语句2后执行，但查询速度快，则语句2先记录)。
|
1 2 |
注意:MySQL 5.1之后就支持微秒级的慢查询超时时长，对于DBA来说，一个查询运行0.5秒和运行0.05秒是非常不同的，前者可能索引使用错误或者走了表扫描，后者可能索引使用正确。 另外，指定的慢查询超时时长表示的是超出这个时间的才算是慢查询，等于这个时间的不会记录。 |
和慢查询有关的变量：
|
1 2 3 4 5 6 |
long_query_time=10 # 指定慢查询超时时长(默认10秒)，超出此时长的属于慢查询 log_output={TABLE|FILE|NONE} # 定义一般查询日志和慢查询日志的输出格式，默认为file log_slow_queries={yes|no} # 是否启用慢查询日志，默认不启用 slow_query_log={1|ON|0|OFF} # 也是是否启用慢查询日志，此变量和log_slow_queries修改一个另一个同时变化 slow_query_log_file=/mydata/data/hostname-slow.log #默认路径为库文件目录下主机名加上-slow.log log_queries_not_using_indexes=OFF # 查询没有使用索引的时候是否也记入慢查询日志 |
现在启用慢查询日志
|
1 |
set @@global.slow_query_log=on; |
随着时间的推移，慢查询日志文件中的记录可能会变得非常多，这对于分析查询来说是非常困难的。好在提供了一个专门归类慢查询日志的工具mysqldumpslow。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
解析并总结MySQL慢查询日志。选项是 --verbose verbose --debug调试 --help将此文本写入标准输出 -v详细 -d调试 -s ORDER排序依据（al，at，ar，c，l，r，t），默认为“ at” al：平均锁定时间 ar：发送的平均行数 于：平均查询时间 c：计数 l：锁定时间 r：已发送的行 t：查询时间 -r反转排序顺序（从大到小而不是从大到小） -t NUM只显示前n个查询 -a请勿将所有数字抽象为N并将字符串抽象为'S' -n名称中至少包含n位数字的NUM个抽象数字 -g PATTERN grep：仅考虑包含此字符串的stmts -h HOSTNAME数据库服务器的主机名，用于* -slow.log文件名（可以是通配符）， 默认为'*'，即全部匹配 -i服务器实例的名称（如果使用mysql.server启动脚本） -l不要从总时间中减去锁定时间 |
该工具归类的时候，默认会将同文本但变量值不同的查询语句视为同一类，并使用N代替其中的数值变量，使用S代替其中的字符串变量。可以使用-a来禁用这种替换。如：
|
1 |
mysqldumpslow -a mysql-slow.log |
显然，这里归类后的结果只是精确到0.01秒的，如果想要显示及其精确的秒数，则使用-d选项启用调试功能。
|
1 |
mysqldumpslow -d mysql-slow.log |
|
1 |
慢查询在SQL语句调优的时候非常有用，应该将它启用起来，且应该让慢查询阈值尽量小，例如1秒甚至低于1秒。就像一天执行上千次的1秒语句，和一天执行几次的20秒语句，显然更值得去优化这个1秒的语句。 |
二进制日志文件
|
1 2 3 4 |
二进制日志包含了引起或可能引起数据库改变(如delete语句但没有匹配行)的事件信息，但绝不会包括select和show这样的查询语句。语句以"事件"的形式保存，所以包含了时间、事件开始和结束位置等信息。 二进制日志是以事件形式记录的，不是事务日志(但可能是基于事务来记录二进制日志)，不代表它只记录innodb日志，myisam表也一样有二进制日志。 对于事务表的操作，二进制日志只在事务提交的时候一次性写入(基于事务的innodb二进制日志)，提交前的每个二进制日志记录都先cache，提交时写入。对于非事务表的操作，每次执行完语句就直接写入。 MariaDB/MySQL默认没有启动二进制日志，要启用二进制日志使用 --log-bin=[on|off|file_name] 选项指定，如果没有给定file_name，则默认为datadir下的主机名加"-bin"，并在后面跟上一串数字表示日志序列号，如果给定的日志文件中包含了后缀(logname.suffix)将忽略后缀部分。 |
|
1 |
或者在配置文件(my.cnf)中的[mysqld]部分设置log-bin也可以。注意：对于mysql 5.7，直接启动binlog可能会导致mysql服务启动失败，这时需要在配置文件中的mysqld为mysql实例分配server_id。 |
注意：
|
1 |
mysqld还创建一个二进制日志索引文件，当二进制日志文件滚动的时候会向该文件中写入对应的信息。所以该文件包含所有使用的二进制日志文件的文件名。默认情况下该文件与二进制日志文件的文件名相同，扩展名为'.index'。要指定该文件的文件名使用 --log-bin-index[=file_name] 选项。当mysqld在运行时不应手动编辑该文件，免得mysqld变得混乱。 |
|
1 2 3 |
当重启mysql服务或刷新日志或者达到日志最大值时，将滚动二进制日志文件，滚动日志时只修改日志文件名的数字序列部分。 二进制日志文件的最大值通过变量 max_binlog_size 设置(默认值为1G)。但由于二进制日志可能是基于事务来记录的(如innodb表类型)，而事务是绝对不可能也不应该跨文件记录的，如果正好二进制日志文件达到了最大值但事务还没有提交则不会滚动日志，而是继续增大日志，所以 max_binlog_size 指定的值和实际的二进制日志大小不一定相等。 因为二进制日志文件增长迅速，但官方说明因此而损耗的性能小于1%，且二进制目的是为了恢复定点数据库和主从复制。 |
出于安全和功能考虑，极不建议将二进制日志和datadir放在同一磁盘上。
查看二进制日志
MySQL中查看二进制日志的方法主要有几种
1.使用show显示对应的信息。
|
1 2 |
SHOW BINARY/MASTER LOGS # 查看使用了哪些日志文件 |
|
1 |
SHOW BINLOG EVENTS IN 'mysql-bin.000005' # 查看日志中进行了哪些操作 |
可以指定起始位置。同样，起始位置必须指定正确，不能指定不存在的位置。
|
1 |
SHOW BINLOG EVENTS IN 'binlog.000005' from 4; |
SHOW MASTER STATUS # 显式主服务器中的二进制日志信息
2.使用mysqlbinlog工具查看日志。
二进制日志也可以使用mysqlbinlog命令查看。
|
1 2 3 4 5 6 7 8 9 10 |
mysqlbinlog [选项] log-file1 log-file2... -d,--database=name：只查看指定数据库的日志操作 -o,--offset=#：忽略掉日志中的前n个操作命令 -r,--result-file=name：将输出的日志信息输出到指定的文件中，使用重定向也一样可以。 -s,--short-form：显示简单格式的日志，只记录一些普通的语句，会省略掉一些额外的信息如位置信息和时间信息以及基于行的日志。可以用来调试，生产环境千万不可使用 --set-charset=char_name：在输出日志信息到文件中时，在文件第一行加上set names char_name --start-datetime,--stop-datetime：指定输出开始时间和结束时间内的所有日志信息 --start-position=#,--stop-position=#：指定输出开始位置和结束位置内的所有日志信息 -v,-vv：显示更详细信息，基于row的日志默认不会显示出来，此时使用-v或-vv可以查看 |
在进行测试之前，先对日志进行一次刷新，以方便解释二进制日志的信息。
|
1 2 3 4 5 6 |
/usr/local/mysql/bin/mysqladmin -uroot -p123456 refresh 然后插入2条语句 insert into book (c1,c2) value ('90','1111'); insert into book (c1,c2) value ('92','2222'); |
查看binlog
|
1 2 3 4 5 6 7 8 9 10 11 |
/usr/local/mysql/bin/mysqlbinlog binlog.000007 因为我的binlog记录方式为row,所以你这样执行查看是什么都看不到的，怎样能看到呢 mysqlbinlog mysql-bin.000005 -vv #加上-vv <1>基于开始/结束时间 mysqlbinlog --start-datetime='2013-09-10 00:00:00' --stop-datetime='2013-09-10 01:01:01' -d 库名 二进制文件 <2>基于pos值 mysqlbinlog --start-postion=107 --stop-position=1000 -d 库名 二进制文件 <3>转换为可读文本 mysqlbinlog –base64-output=DECODE-ROWS -v -d 库名 二进制文件 |
|
1 |
/usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS binlog.000007 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 |
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #200114 15:56:24 server id 1121 end_log_pos 123 Start: binlog v 4, server v 5.7.28-log created 200114 15:56:24 # Warning: this binlog is either in use or was not closed properly. # at 123 #200114 15:56:24 server id 1121 end_log_pos 206 Previous-GTIDs # 6cab5d54-2aed-11ea-9bda-0050568b6568:12-102:1000009-1000028 # at 206 #200114 16:00:50 server id 1121 end_log_pos 267 GTID last_committed=0 sequence_number=1 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= '6cab5d54-2aed-11ea-9bda-0050568b6568:103'/*!*/; # at 267 #200114 16:00:50 server id 1121 end_log_pos 342 Query thread_id=88 exec_time=0 error_code=0 SET TIMESTAMP=1578988850/*!*/; SET @@session.pseudo_thread_id=88/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549152/*!*/; SET @@session.auto_increment_increment=7, @@session.auto_increment_offset=1121/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 342 #200114 16:00:50 server id 1121 end_log_pos 389 Table_map: `wechat`.`book` mapped to number 122 # at 389 #200114 16:00:50 server id 1121 end_log_pos 431 Write_rows: table id 122 flags: STMT_END_F ### INSERT INTO `wechat`.`book` ### SET ### @1=91 /* INT meta=0 nullable=0 is_null=0 */ ### @2='wan2' /* BLOB/TEXT meta=2 nullable=0 is_null=0 */ # at 431 #200114 16:00:50 server id 1121 end_log_pos 458 Xid = 621564 COMMIT/*!*/; # at 458 #200114 16:00:52 server id 1121 end_log_pos 519 GTID last_committed=1 sequence_number=2 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= '6cab5d54-2aed-11ea-9bda-0050568b6568:104'/*!*/; # at 519 #200114 16:00:52 server id 1121 end_log_pos 594 Query thread_id=88 exec_time=0 error_code=0 SET TIMESTAMP=1578988852/*!*/; BEGIN /*!*/; # at 594 #200114 16:00:52 server id 1121 end_log_pos 641 Table_map: `wechat`.`book` mapped to number 122 # at 641 #200114 16:00:52 server id 1121 end_log_pos 683 Write_rows: table id 122 flags: STMT_END_F ### INSERT INTO `wechat`.`book` ### SET ### @1=92 /* INT meta=0 nullable=0 is_null=0 */ ### @2='wan2' /* BLOB/TEXT meta=2 nullable=0 is_null=0 */ # at 683 #200114 16:00:52 server id 1121 end_log_pos 710 Xid = 621565 COMMIT/*!*/; # at 710 #200114 16:00:57 server id 1121 end_log_pos 771 GTID last_committed=2 sequence_number=3 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= '6cab5d54-2aed-11ea-9bda-0050568b6568:105'/*!*/; # at 771 #200114 16:00:57 server id 1121 end_log_pos 846 Query thread_id=88 exec_time=0 error_code=0 SET TIMESTAMP=1578988857/*!*/; BEGIN /*!*/; # at 846 #200114 16:00:57 server id 1121 end_log_pos 893 Table_map: `wechat`.`book` mapped to number 122 # at 893 #200114 16:00:57 server id 1121 end_log_pos 935 Write_rows: table id 122 flags: STMT_END_F ### INSERT INTO `wechat`.`book` ### SET ### @1=93 /* INT meta=0 nullable=0 is_null=0 */ ### @2='wan2' /* BLOB/TEXT meta=2 nullable=0 is_null=0 */ # at 935 #200114 16:00:57 server id 1121 end_log_pos 962 Xid = 621572 COMMIT/*!*/; # at 962 #200114 16:07:05 server id 1121 end_log_pos 1023 GTID last_committed=3 sequence_number=4 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= '6cab5d54-2aed-11ea-9bda-0050568b6568:106'/*!*/; # at 1023 #200114 16:07:05 server id 1121 end_log_pos 1098 Query thread_id=89 exec_time=0 error_code=0 SET TIMESTAMP=1578989225/*!*/; BEGIN /*!*/; # at 1098 #200114 16:07:05 server id 1121 end_log_pos 1145 Table_map: `wechat`.`book` mapped to number 122 # at 1145 #200114 16:07:05 server id 1121 end_log_pos 1204 Update_rows: table id 122 flags: STMT_END_F ### UPDATE `wechat`.`book` ### WHERE ### @1=89 /* INT meta=0 nullable=0 is_null=0 */ ### @2='wan1' /* BLOB/TEXT meta=2 nullable=0 is_null=0 */ ### SET ### @1=89 /* INT meta=0 nullable=0 is_null=0 */ ### @2='123123123' /* BLOB/TEXT meta=2 nullable=0 is_null=0 */ # at 1204 #200114 16:07:05 server id 1121 end_log_pos 1231 Xid = 621795 COMMIT/*!*/; # at 1231 #200114 16:07:19 server id 1121 end_log_pos 1292 GTID last_committed=4 sequence_number=5 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= '6cab5d54-2aed-11ea-9bda-0050568b6568:107'/*!*/; # at 1292 #200114 16:07:19 server id 1121 end_log_pos 1367 Query thread_id=89 exec_time=0 error_code=0 SET TIMESTAMP=1578989239/*!*/; BEGIN /*!*/; # at 1367 #200114 16:07:19 server id 1121 end_log_pos 1414 Table_map: `wechat`.`book` mapped to number 122 # at 1414 #200114 16:07:19 server id 1121 end_log_pos 1470 Update_rows: table id 122 flags: STMT_END_F ### UPDATE `wechat`.`book` ### WHERE ### @1=90 /* INT meta=0 nullable=0 is_null=0 */ ### @2='wan2' /* BLOB/TEXT meta=2 nullable=0 is_null=0 */ ### SET ### @1=90 /* INT meta=0 nullable=0 is_null=0 */ ### @2='222222' /* BLOB/TEXT meta=2 nullable=0 is_null=0 */ # at 1470 #200114 16:07:19 server id 1121 end_log_pos 1497 Xid = 621808 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; |
日志文件大致分析 #at 行 bengin commit暂没有计入
删除二进制日志
|
1 2 3 4 5 6 7 |
reset master; #master和binary是同义词 purge master logs to "mysql-bin.000006"; purge binary logs to "mysql-bin.000006"; #删除指定日期之前的所有日志。但是若指定的时间处在正在使用中的日志文件中，将无法进行purge。 purge master logs before 'yyyy-mm-dd hh:mi:ss' 例:purge master logs before '2017-03-29 07:36:40'; |
使用–expire_logs_days=N选项指定过了多少天日志自动过期清空。
二进制日志的记录格式
|
1 2 |
从MySQL 5.1开始，MySQL支持statement、row、mixed三种形式的记录方式。row形式是基于行来记录，也就是将相关行的每一列的值都在日志中保存下来，这样的结果会导致日志文件变得非常大，但是保证了动态值的确定性。还有一种mixed形式，表示如何记录日志由MySQL自己来决定。 日志的记录格式由变量 binlog_format 来指定。其值有：row,statement,mixed。innodb引擎的创始人之一在博客上推荐使用row格式。 |
|
1 2 3 |
#下面将记录格式改为row mysql> flush logs; mysql> set binlog_format='row'; |
查看产生的日志。
还是使用上面的方法， 使用-vv可将这些显示出来。
|
1 2 3 4 5 |
还有一种mixed模式。这种模式下默认会采用statement的方式记录，只有以下几种情况会采用row的形式来记录日志。 1.表的存储引擎为NDB，这时对表的DML操作都会以row的格式记录。 2.使用了uuid()、user()、current_user()、found_rows()、row_count()等不确定函数。但测试发现对now()函数仍会以statement格式记录，而sysdate()函数会以row格式记录。 3.使用了insert delay语句。 4.使用了临时表。 |
二进制日志相关的变量
|
1 |
注意：在配置binlog相关变量的时候，相关变量名总是搞混，因为有的是binlog，有的是log_bin，当他们分开的时候，log在前，当它们一起的时候，bin在前。在配置文件中也同样如此 |
- log_bin = {on | off | base_name} #指定是否启用记录二进制日志或者指定一个日志路径(路径不能加.否则.后的被忽略)
- sql_log_bin ={ on | off } #指定是否启用记录二进制日志，只有在log_bin开启的时候才有效
- expire_logs_days = #指定自动删除二进制日志的时间，即日志过期时间
- binlog_do_db = #明确指定要记录日志的数据库
- binlog_ignore_db = #指定不记录二进制日志的数据库
- log_bin_index = #指定mysql-bin.index文件的路径
- binlog_format = { mixed | row | statement } #指定二进制日志基于什么模式记录
- binlog_rows_query_log_events = { 1|0 } # MySQL5.6.2添加了该变量，当binlog format为row时，默认不会记录row对应的SQL语句，设置为1或其他true布尔值时会记录，但需要使用mysqlbinlog -v查看，这些语句是被注释的，恢复时不会被执行。
- max_binlog_size = #指定二进制日志文件最大值，超出指定值将自动滚动。但由于事务不会跨文件，所以并不一定总是精确。
- binlog_cache_size = 32768 #基于事务类型的日志会先记录在缓冲区，当达到该缓冲大小时这些日志会写入磁盘
- max_binlog_cache_size = #指定二进制日志缓存最大大小，硬限制。默认4G，够大了，建议不要改
- binlog_cache_use：使用缓存写二进制日志的次数(这是一个实时变化的统计值)
- binlog_cache_disk_use:使用临时文件写二进制日志的次数，当日志超过了binlog_cache_size的时候会使用临时文件写日志，如果该变量值不为0，则考虑增大binlog_cache_size的值
- binlog_stmt_cache_size = 32768 #一般等同于且决定binlog_cache_size大小，所以修改缓存大小时只需修改这个而不用修改binlog_cache_size
- binlog_stmt_cache_use：使用缓存写二进制日志的次数
- binlog_stmt_cache_disk_use: 使用临时文件写二进制日志的次数，当日志超过了binlog_cache_size的时候会使用临时文件写日志，如果该变量值不为0，则考虑增大binlog_cache_size的值
- sync_binlog = { 0 | n } #这个参数直接影响mysql的性能和完整性
- sync_binlog=0:不同步，日志何时刷到磁盘由FileSystem决定，这个性能最好。
- sync_binlog=n:每写n次二进制日志事件(不是事务)，MySQL将执行一次磁盘同步指令fdatasync()将缓存日志刷新到磁盘日志文件中。Mysql中默认的设置是sync_binlog=0，即不同步，这时性能最好，但风险最大。一旦系统奔溃，缓存中的日志都会丢失。
在innodb的主从复制结构中，如果启用了二进制日志(几乎都会启用)，要保证事务的一致性和持久性的时候，必须将sync_binlog的值设置为1，因为每次事务提交都会写入二进制日志，设置为1就保证了每次事务提交时二进制日志都会写入到磁盘中，从而立即被从服务器复制过去。
二进制日志定点还原数据库
只需指定二进制日志的起始位置（可指定终止位置）并将其保存到sql文件中，由mysql命令来载入恢复即可。当然直接通过管道送给mysql命令也可。
至于是基于位置来恢复还是基于时间点来恢复，这两种行为都可以。选择时间点来恢复比较直观些，并且跨日志文件恢复时更方便
|
1 2 3 4 5 6 7 8 |
mysqlbinlog --stop-datetime="2014-7-2 15:27:48" /tmp/mysql-bin.000008 | mysql -u user -p password 或者 例: #通过Pos mysqlbinlog --stop-position="102" --start-position="367" mysql-bin.000001 | mysql -uroot -pxxx database_name #通过起始时间 mysqlbinlog --start-datetime="2018-04-20 10:01:00" --stop-datetime="2005-04-21 10:01:00" mysql-bin.000001 | mysql -u root -pxxx database_name |
恢复多个二进制日志文件时：
|
1 |
mysqlbinlog mysql-bin.[*] | mysql -uroot -p password |
或者将它们导入到一个文件中后恢复
|
1 2 3 |
mysqlbinlog mysql-bin.000001 > /tmp/a.sql mysqlbinlog mysql-bin.000002 >>/tmp/a.sql mysql -u root -p password -e "source /tmp/a.sql" |
题外记录
|
1 2 3 4 5 6 |
<1>全量备份mysql mysqldump -u root -pxxx database_name | gzip > /localmnt/database_name.sql.gz <2>如果数据被删除了，首先通过全量备份导入数据 mysql -uroot -pxxx database_name < /localmnt/database_name.sql <3>通过查询 binlog 日志，找出需要恢复的时间点 mysqlbinlog binlog_files | more |
- 本文固定链接: https://www.yoyoask.com/?p=1780
- 转载请注明: shooter 于 SHOOTER 发表
这个作者貌似有点懒，什么都没有留下。