- 错误日志:数据库故障时可以查看这个日志。 可以通过--log-error[=file_name]来指定mysqld保存错误日志文件的位置 默认保存地址:参数DATADIR指定的目录下 默认文件名:host_name.err,其中host_name为主机名。
more localhost.localdomain.err
- 二进制日志:灾难时的数据恢复的重要依据。
- STATEMENT格式:mysqlbinlog localhost-bin.00001(要查看的二进制日志文件名)
- ROW格式:mysqlbinlog -v|-vv localhost-bin.00001(要查看的二进制日志文件名)
- 删除所有日志,新日志编号从000001开始
- 删除编号‘001234’之前的所有日志
- 删除'2018-6-20 11:59:00'之前的所有日志
- 设置日志的过期天数,重启MySQL服务后生效。
- 查询日志
保存位置:通过--log-output[=TABLE|FILE|NONE|TABLE,FILE]来指定保存到表(mysql库中的general_log)或文件中,默认输出到文件。注意:NONE优先级最高。例如:--log-output=TABLE,NONE表示不输出到文件和表中。
启用:
global级别:
是否启用:--general_log[={0|1}],不启用:0或者不指定这个参数,启用:1或者不带值;日志文件路径:--general_log_file[=file_name]。
session级别:
sql_log_off=[on|off]
默认文件名及路径:host_name.log、参数datadir指定的路径下。
格式:纯文本
读取:more XXX.log
- 慢查询日志:默认不记录管理语句和不使用索引进行查询的语句
保存位置:
启用:
5.1.29之前:mysqld启动时加入--log-slow-queries[=file_name]。
5.1.29之后:--slow_query_log --slow_query_log
指定慢查询状态:--slow_query_log[=0|1]
指定慢查询日志的路径slow_query_log_file[=file_name]
指定输出方式:与查询相同。注意:表中只能精确到秒,日志文件中可以精确到微妙。
默认文件名及路径:host_name-slow.log、参数datadir指定的路径下。
格式:纯文本
读取:more XXX.log
在测试过程中遇到的问题:
1、使用mysqlbinlog解析日志时报错并且显示出一堆无法读懂的字符。
第一步:由于出现server id 1 end_log_pos 123 Start: binlog v 4, server v 5.7.22-log created 180612 2:04:08 at startup,怀疑是版本不一致导致的,find / -name mysqlbinlog找一下有没有一个以上的mysqlbinlog,果然有两个。
第一个是我安装的mysql,第二个是系统自带的。
第二步:指定使用自己安装的mysql的bin目录下的mysqlbinlog,版本不一致的问题解决!
第三步,由于MySQL二进制日志格式默认使用MIXED,所以会导致有些使用ROW格式存储的日志出现解码出来时一些天书一样的字符。
解决办法:bin/mysqlbinlog --base64-output=DECODE-ROWS -vv log/mysql_bin.000003
[root@bogon mysql]# bin/mysqlbinlog -vv log/mysql_bin.000003
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180612 2:23:02 server id 1 end_log_pos 123 CRC32 0xa92858b2 Start: binlog v 4, server v 5.7.22-log created 180612 2:23:02 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
9pAfWw8BAAAAdwAAAHsAAAABAAQANS43LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAD2kB9bEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AbJYKKk=
'/*!*/;
# at 123
#180612 2:23:02 server id 1 end_log_pos 154 CRC32 0x83536be4 Previous-GTIDs
# [empty]
# at 154
#180612 3:14:38 server id 1 end_log_pos 219 CRC32 0xf6ba88c7 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#180612 3:14:38 server id 1 end_log_pos 398 CRC32 0xbba4ddb7 Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1528798478/*!*/;
SET @@session.pseudo_thread_id=7/*!*/;
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=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=224/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'
/*!*/;
# at 398
#180612 6:33:49 server id 1 end_log_pos 463 CRC32 0x860f10af Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 463
#180612 6:33:49 server id 1 end_log_pos 557 CRC32 0xcc98d4bc Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1528810429/*!*/;
create database test
/*!*/;
# at 557
#180612 6:34:52 server id 1 end_log_pos 622 CRC32 0x33b7bd8a Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 622
#180612 6:34:52 server id 1 end_log_pos 722 CRC32 0xd7d030d7 Query thread_id=8 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1528810492/*!*/;
create table t1(id int(4))
/*!*/;
# at 722
#180612 6:35:41 server id 1 end_log_pos 787 CRC32 0x8c2094da Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 787
#180612 6:35:41 server id 1 end_log_pos 859 CRC32 0x3f6a4e7c Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1528810541/*!*/;
BEGIN
/*!*/;
# at 859
#180612 6:35:41 server id 1 end_log_pos 904 CRC32 0x6be2b561 Table_map: `test`.`t1` mapped to number 108
# at 904
#180612 6:35:41 server id 1 end_log_pos 959 CRC32 0x0c86ca14 Write_rows: table id 108 flags: STMT_END_F
BINLOG '
LcwfWxMBAAAALQAAAIgDAAAAAGwAAAAAAAEABHRlc3QAAnQxAAEDAAFhteJr
LcwfWx4BAAAANwAAAL8DAAAAAGwAAAAAAAEAAgAB//4BAAAA/gIAAAD+AwAAAP4EAAAAFMqGDA==
'/*!*/;
### INSERT INTO `test`.`t1`
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `test`.`t1`
### SET
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `test`.`t1`
### SET
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `test`.`t1`
### SET
### @1=4 /* INT meta=0 nullable=1 is_null=0 */
# at 959
#180612 6:35:41 server id 1 end_log_pos 990 CRC32 0xc9a7df25 Xid = 18
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*/;
[root@bogon mysql]# bin/mysqlbinlog --base64-output=DECODE-ROWS -vv log/mysql_bin.000003
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180612 2:23:02 server id 1 end_log_pos 123 CRC32 0xa92858b2 Start: binlog v 4, server v 5.7.22-log created 180612 2:23:02 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#180612 2:23:02 server id 1 end_log_pos 154 CRC32 0x83536be4 Previous-GTIDs
# [empty]
# at 154
#180612 3:14:38 server id 1 end_log_pos 219 CRC32 0xf6ba88c7 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#180612 3:14:38 server id 1 end_log_pos 398 CRC32 0xbba4ddb7 Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1528798478/*!*/;
SET @@session.pseudo_thread_id=7/*!*/;
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=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=224/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'
/*!*/;
# at 398
#180612 6:33:49 server id 1 end_log_pos 463 CRC32 0x860f10af Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 463
#180612 6:33:49 server id 1 end_log_pos 557 CRC32 0xcc98d4bc Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1528810429/*!*/;
create database test
/*!*/;
# at 557
#180612 6:34:52 server id 1 end_log_pos 622 CRC32 0x33b7bd8a Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 622
#180612 6:34:52 server id 1 end_log_pos 722 CRC32 0xd7d030d7 Query thread_id=8 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1528810492/*!*/;
create table t1(id int(4))
/*!*/;
# at 722
#180612 6:35:41 server id 1 end_log_pos 787 CRC32 0x8c2094da Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 787
#180612 6:35:41 server id 1 end_log_pos 859 CRC32 0x3f6a4e7c Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1528810541/*!*/;
BEGIN
/*!*/;
# at 859
#180612 6:35:41 server id 1 end_log_pos 904 CRC32 0x6be2b561 Table_map: `test`.`t1` mapped to number 108
# at 904
#180612 6:35:41 server id 1 end_log_pos 959 CRC32 0x0c86ca14 Write_rows: table id 108 flags: STMT_END_F
### INSERT INTO `test`.`t1`
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `test`.`t1`
### SET
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `test`.`t1`
### SET
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `test`.`t1`
### SET
### @1=4 /* INT meta=0 nullable=1 is_null=0 */
# at 959
#180612 6:35:41 server id 1 end_log_pos 990 CRC32 0xc9a7df25 Xid = 18
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*/;
加上'--base64-output=DECODE-ROWS'之后
[root@bogon mysql]# bin/mysqlbinlog --base64-output=DECODE-ROWS -vv log/mysql_bin.000003
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180612 2:23:02 server id 1 end_log_pos 123 CRC32 0xa92858b2 Start: binlog v 4, server v 5.7.22-log created 180612 2:23:02 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#180612 2:23:02 server id 1 end_log_pos 154 CRC32 0x83536be4 Previous-GTIDs
# [empty]
# at 154
#180612 3:14:38 server id 1 end_log_pos 219 CRC32 0xf6ba88c7 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#180612 3:14:38 server id 1 end_log_pos 398 CRC32 0xbba4ddb7 Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1528798478/*!*/;
SET @@session.pseudo_thread_id=7/*!*/;
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=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=224/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'
/*!*/;
# at 398
#180612 6:33:49 server id 1 end_log_pos 463 CRC32 0x860f10af Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 463
#180612 6:33:49 server id 1 end_log_pos 557 CRC32 0xcc98d4bc Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1528810429/*!*/;
create database test
/*!*/;
# at 557
#180612 6:34:52 server id 1 end_log_pos 622 CRC32 0x33b7bd8a Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 622
#180612 6:34:52 server id 1 end_log_pos 722 CRC32 0xd7d030d7 Query thread_id=8 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1528810492/*!*/;
create table t1(id int(4))
/*!*/;
# at 722
#180612 6:35:41 server id 1 end_log_pos 787 CRC32 0x8c2094da Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 787
#180612 6:35:41 server id 1 end_log_pos 859 CRC32 0x3f6a4e7c Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1528810541/*!*/;
BEGIN
/*!*/;
# at 859
#180612 6:35:41 server id 1 end_log_pos 904 CRC32 0x6be2b561 Table_map: `test`.`t1` mapped to number 108
# at 904
#180612 6:35:41 server id 1 end_log_pos 959 CRC32 0x0c86ca14 Write_rows: table id 108 flags: STMT_END_F
### INSERT INTO `test`.`t1`
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `test`.`t1`
### SET
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `test`.`t1`
### SET
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `test`.`t1`
### SET
### @1=4 /* INT meta=0 nullable=1 is_null=0 */
# at 959
#180612 6:35:41 server id 1 end_log_pos 990 CRC32 0xc9a7df25 Xid = 18
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*/;