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 重啟服務就可以啟動二進制日志文件，如果啟動不了?？磍og-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