Mysql日志管理笔记

1、Mysql日志管理笔记(5.7版本)wangzz四种日志文件：1,二进制日志： 以二进制形式记录数据库的各种操作，但不记录查询语句.2,错误日志: 该日志文件记录mysql服务器启动，关闭和运行时的出错等信息。3,通用查询日志：记录mysql启动，关闭，及客户端的连接信息，更新数据记录sql语句和查询数据记录sql语句.4,慢查询日志： 记录执行时间超过指定时间的各种操作，通过工具分析慢查询日志可以定位性能瓶颈。1,二进制日志 1）启动二进制日志 /etc/f 文件。 mysqld log-bin=dir/filename server_id=100 log-bin=/export/app/l

2、og/binlog/binlog.log 重启服务就可以启动二进制日志文件，如果启动不了。看log-error日志，你会时到重启不了的原因 mysql> show variables like '%bin%'+-+-+| Variable_name | Value |+-+-+| bind_address | * | binlog_cache_size | 32768 | binlog_checksum | CRC32 | binlog_direct_non_transactional_updates | OFF | binlog_error_action | ABORT

3、_SERVER | binlog_format | ROW | binlog_group_commit_sync_delay | 0 | binlog_group_commit_sync_no_delay_count | 0 | binlog_gtid_simple_recovery | ON | binlog_max_flush_queue_time | 0 | binlog_order_commits | ON | binlog_row_image | FULL | binlog_rows_query_log_events | OFF | binlog_stmt_cache_size |

4、32768 | innodb_api_enable_binlog | OFF | innodb_locks_unsafe_for_binlog | OFF | log_bin | ON | log_bin_basename | /export/app/log/binlog/binlog | log_bin_index | /export/app/log/binlog/binlog.index | log_bin_trust_function_creators | OFF | log_bin_use_v1_row_events | OFF | log_statements_unsafe_for_

5、binlog | ON | max_binlog_cache_size | 18446744073709547520 | max_binlog_size | 1073741824 | max_binlog_stmt_cache_size | 18446744073709547520 | sql_log_bin | ON | sync_binlog | 1 |+-+-+27 rows in set (0.00 sec) 查看二进制日志文件rootrisoserver binlog# mysqlbinlog binlog.000001停止二进制日志文件(ROOT用户权限)方法1,/etc/f 文件

6、删除bin-log行。重启服务就可以了。方法2,SET SQL_LOG_BIN=0 暂停二进制日志文件 SET SQL_LOG_BIN=1 开启二进制日志文件删除二进制日志文件RESET MASTER; 删除所有PURGE MASTER LOGS TO filename.number, 例: purge master logs to 'binlog.000003' 删除编号少于000003的所有二进制BINlog文件PURGE MASTER LOGS BEFORE 'YYYY-MM-DD HH:MM:SS'例: purge master logs befor &

7、#39;2016-08-29 18:00:01' 删除上述时间之前的所有二进制文件2） 错误日志文件 启动错误日志文件：默认开启，无法禁止，就算my.ini文件不设定它。照样存在数据库中，保存默认数据库数据文件里。 当然也可以修改此路径。log-error=/export/app/log/errorlog/errorlog.log 注意创建目录，以及更改权限目录,重启服务即可。查看错误日志文件：普通文本文件停止错误日志文件：无法停止删除错误日志文件：mysqladmin -u root -p flush-logs 创建一个新的，然后将旧的更名为filename.err-old，再由管理

8、员来确认是否手工删除该文件.3）通用查询日志 （因为记录所有客户端连接及操作库信息，信息量大。影响数据库性能，一般建议关闭） 通用查询日志可以存放到一个文本或一个表中，所有连接的语句都被记录到该日志文件或者表中，缺省未开启。 通过 -log=file_name或-l file_name选项启动它。如果没有给定file_name的值， 默认名是host_name.log。 mysqld按照它接收的顺序记录语句到查询日志。这可能与执行的顺序不同。 不同于更新日志和二进制日志，它们在查询执行后，但是任何一个锁释放之前记录日志。 查询日志包含所有语句，而二进制日志不包含只查询数据的语句。 服务器重新启

9、动和日志刷新不会产生新的一般查询日志文件。启动通用查询日志: 默认关闭，启动设定/etc/f 文件: log_output=none|file|table|file,table #通用查询日志输出格式 general_log=on|off #是否启用通用查询日志 general_log_file=filename #通用查询日志位置及名字 例: general_log_file=/export/app/log/generallog/risoserver.log 默认是在数据库文件中,更改后我们可以查询一次. mysql> show variables like '%version

10、%'+-+-+| Variable_name | Value |+-+-+| innodb_version | 5.7.14 | protocol_version | 10 | slave_type_conversions | | tls_version | TLSv1,TLSv1.1 | version | 5.7.14-log | version_comment | MySQL Community Server (GPL) | version_compile_machine | x86_64 | version_compile_os | Linux |+-+-+8 rows in

11、set (0.01 sec)mysql> show variables like '%general%' -更改前+-+-+| Variable_name | Value |+-+-+| general_log | OFF | general_log_file | /export/app/mysql/risoserver.log |+-+-+2 rows in set (0.00 sec) mysql> show variables like '%general_%'+-+-+| Variable_name | Value |+-+-+| gener

12、al_log | OFF | -这个时候只是更改了路径，但默认还是未开启状态。| general_log_file | /export/app/log/generallog/risoserver.log | -这是更改后的。+-+-+2 rows in set (0.00 sec)rootrisoserver errorlog# ls /export/app/log/generallog/risoserver.logls: cannot access /export/app/log/generallog/risoserver.log: No such file or directory -未产

13、生文件启动通用查询日志：mysql> set global.general_log=1; -开启，0 是关闭 停止通用查询日志：Query OK, 0 rows affected (0.03 sec)mysql> show variables like '%general%'+-+-+| Variable_name | Value |+-+-+| general_log | ON | general_log_file | /export/app/log/generallog/risoserver.log |+-+-+2 rows in set (0.00 sec)r

14、ootrisoserver errorlog# ls /export/app/log/generallog/risoserver.log/export/app/log/generallog/risoserver.log -文件已经生成rootrisoserver errorlog# mysql -uroot -p1111mysql: Warning Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or g.Your M

15、ySQL connection id is 5Server version: 5.7.14-log MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'hel

16、p;' or 'h' for help. Type 'c' to clear the current input statement.mysql> use mysqlReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select user,host from user;+-+-+| user |

17、 host |+-+-+| root | % | mysql.sys | 192.168.1.54 | nono | 192.168.1.54 | wangzz | 192.168.1.54 |+-+-+4 rows in set (0.00 sec)mysql> drop user nono;ERROR 1396 (HY000): Operation DROP USER failed for 'nono''%'mysql> drop user 'nono''192.168.1.54'Query OK, 0 rows

18、affected (0.02 sec)mysql> exitByerootrisoserver errorlog# more /export/app/log/generallog/risoserver.log 查看通用查询日志：/usr/sbin/mysqld, Version: 5.7.14-log (MySQL Community Server (GPL). started with:Tcp port: 3306 Unix socket: /export/app/mysql/mysql.sockTime Id Command Argument2016-08-30T07:41:11.8

19、88155Z 4 Query show variables like '%general%'2016-08-30T07:41:37.164497Z 4 Quit2016-08-30T07:43:00.028798Z 5 Connect rootlocalhost on using Socket2016-08-30T07:43:00.029097Z 5 Query select version_comment limit 12016-08-30T07:43:07.815420Z 5 Query SELECT DATABASE()2016-08-30T07:43:07.824000

20、Z 5 Init DB mysql2016-08-30T07:43:07.825750Z 5 Query show databases2016-08-30T07:43:07.844654Z 5 Query show tables2016-08-30T07:43:07.845260Z 5 Field List columns_priv 2016-08-30T07:43:07.845635Z 5 Field List db 2016-08-30T07:43:07.846286Z 5 Field List engine_cost 2016-08-30T07:43:07.846707Z 5 Field

21、 List event 2016-08-30T07:43:07.847342Z 5 Field List func 2016-08-30T07:43:07.847632Z 5 Field List general_log 2016-08-30T07:43:07.848020Z 5 Field List gtid_executed 2016-08-30T07:43:07.848334Z 5 Field List help_category 2016-08-30T07:43:07.849232Z 5 Field List help_keyword 2016-08-30T07:43:07.85590

22、2Z 5 Field List help_relation 2016-08-30T07:43:07.856912Z 5 Field List help_topic 2016-08-30T07:43:07.896056Z 5 Field List innodb_index_stats 2016-08-30T07:43:07.896976Z 5 Field List innodb_table_stats 2016-08-30T07:43:07.900019Z 5 Field List ndb_binlog_index 2016-08-30T07:43:07.901291Z 5 Field List

23、 plugin 2016-08-30T07:43:07.901523Z 5 Field List proc 2016-08-30T07:43:07.908515Z 5 Field List procs_priv 2016-08-30T07:43:07.908906Z 5 Field List proxies_priv 2016-08-30T07:43:07.909355Z 5 Field List server_cost 2016-08-30T07:43:07.909675Z 5 Field List servers 2016-08-30T07:43:07.910154Z 5 Field Li

24、st slave_master_info 2016-08-30T07:43:07.911203Z 5 Field List slave_relay_log_info 2016-08-30T07:43:07.911706Z 5 Field List slave_worker_info 2016-08-30T07:43:07.912408Z 5 Field List slow_log 2016-08-30T07:43:07.927753Z 5 Field List tables_priv 2016-08-30T07:43:07.928191Z 5 Field List time_zone 2016

25、-08-30T07:43:07.928505Z 5 Field List time_zone_leap_second 2016-08-30T07:43:07.928825Z 5 Field List time_zone_name 2016-08-30T07:43:07.929068Z 5 Field List time_zone_transition 2016-08-30T07:43:07.929406Z 5 Field List time_zone_transition_type 2016-08-30T07:43:07.929887Z 5 Field List user 2016-08-30

26、T07:43:27.363704Z 5 Query select user,host from user2016-08-30T07:43:35.350455Z 5 Query drop user nono -没执行的语句也进去了。应该是在发起之前就先写入2016-08-30T07:43:52.554257Z 5 Query drop user 'nono''192.168.1.54'2016-08-30T07:44:03.454291Z 5 Quit临时通用查询日志文件位置：rootrisoserver errorlog# service mysqld stop

27、Stopping mysqld: OK rootrisoserver errorlog# mysqld -general_log_file=/tmp/risoserver.log -user=mysql &1 6334rootrisoserver errorlog# ps -ef|grep mysqld |grep -v grepmysql 6334 4319 0 03:53 pts/0 00:00:00 mysqld -general_log_file=/tmp/risoserver.log -user=mysqlrootrisoserver errorlog# mysql -uro

28、ot -p1111mysql: Warning Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 2Server version: 5.7.14-log MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserve

29、d.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.mysql> show variables like 'gener%'+-+-+| Variable_nam

30、e | Value |+-+-+| general_log | OFF | general_log_file | /tmp/risoserver.log |+-+-+2 rows in set (0.00 sec)mysql> set global general_log=on; Query OK, 0 rows affected (0.00 sec)mysql> show variables like '%gener%' +-+-+| Variable_name | Value |+-+-+| general_log | ON | general_log_file

31、 | /tmp/risoserver.log |+-+-+2 rows in set (0.00 sec)mysql> select user,host from mysql.user;+-+-+| user | host |+-+-+| root | % | mysql.sys | 192.168.1.54 | wangzz | 192.168.1.54 |+-+-+3 rows in set (0.00 sec)mysql> exitByerootrisoserver errorlog# more /tmp/risoserver.logmysqld, Version: 5.7.

32、14-log (MySQL Community Server (GPL). started with:Tcp port: 3306 Unix socket: /export/app/mysql/mysql.sockTime Id Command Argument2016-08-30T07:57:15.024173Z 2 Query show variables like '%gener%'2016-08-30T07:57:34.577940Z 2 Query select user,host from mysql.user2016-08-30T07:59:10.114608Z

33、2 Quitmysql> show variables like 'log_output'+-+-+| Variable_name | Value |+-+-+| log_output | FILE |+-+-+1 row in set (0.01 sec)切换到表输出格式。mysql> set global log_output='TABLE' set global log_output='file,table'-混合输出Query OK, 0 rows affected (0.00 sec)mysql> show varia

34、bles like 'log_output'+-+-+| Variable_name | Value |+-+-+| log_output | TABLE |+-+-+1 row in set (0.01 sec)mysql> select host,user from mysql.user;+-+-+| host | user |+-+-+| % | root | 192.168.1.54 | mysql.sys | 192.168.1.54 | wangzz |+-+-+3 rows in set (0.00 sec)mysql> select thread_i

35、d,command_type,argument from mysql.general_log;+-+-+-+| thread_id | command_type | argument |+-+-+-+| 4 | Query | show variables like 'generial%' | 4 | Query | show variables like 'gener%' | 4 | Query | show variables like 'log_output' | 4 | Query | select user,host from mysq

36、l.user | 5 | Query | select event_time,server_id,comman_type,argument from mysql.general_log | 5 | Query | select event_time,server_id,command_type,argument from mysql.general_log | 5 | Query | show variables like '%log%' | 5 | Query | show variables like 'log_output' | 5 | Query | s

37、elect thread_id,command_type,argument from mysql.general_log |+-+-+-+50 rows in set (0.00 sec)删除通用查询日志：rootlocalhost(none)> set global general_log=off; mysqladmin -uroot -p flush-logs 这个命令会执行先删除旧的。并创建一个新的通用查询日志文件。在log_output=none,general_log=on的清下下无任何通用日志输出。 4）慢查询日志mysql> show variables like '%slow%'+-+-+| Variable_name | Value |+-+-+| log_slow_admin_statements | OFF | log_s