Mysql日志管理筆記

1、Mysql日志管理筆記(5.7版本)wangzz四種日志文件：1,二進(jìn)制日志： 以二進(jìn)制形式記錄數(shù)據(jù)庫的各種操作，但不記錄查詢語句.2,錯(cuò)誤日志: 該日志文件記錄mysql服務(wù)器啟動(dòng)，關(guān)閉和運(yùn)行時(shí)的出錯(cuò)等信息。3,通用查詢?nèi)罩荆河涗沵ysql啟動(dòng)，關(guān)閉，及客戶端的連接信息，更新數(shù)據(jù)記錄sql語句和查詢數(shù)據(jù)記錄sql語句.4,慢查詢?nèi)罩荆?記錄執(zhí)行時(shí)間超過指定時(shí)間的各種操作，通過工具分析慢查詢?nèi)罩究梢远ㄎ恍阅芷款i。1,二進(jìn)制日志 1）啟動(dòng)二進(jìn)制日志 /etc/f 文件。 mysqld log-bin=dir/filename server_id=100 log-bin=/export/app/l

2、og/binlog/binlog.log 重啟服務(wù)就可以啟動(dòng)二進(jìn)制日志文件，如果啟動(dòng)不了?？磍og-error日志，你會(huì)時(shí)到重啟不了的原因 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) 查看二進(jìn)制日志文件rootrisoserver binlog# mysqlbinlog binlog.000001停止二進(jìn)制日志文件(ROOT用戶權(quán)限)方法1,/etc/f 文件

6、刪除bin-log行。重啟服務(wù)就可以了。方法2,SET SQL_LOG_BIN=0 暫停二進(jìn)制日志文件 SET SQL_LOG_BIN=1 開啟二進(jìn)制日志文件刪除二進(jìn)制日志文件RESET MASTER; 刪除所有PURGE MASTER LOGS TO filename.number, 例: purge master logs to 'binlog.000003' 刪除編號(hào)少于000003的所有二進(jìn)制BINlog文件PURGE MASTER LOGS BEFORE 'YYYY-MM-DD HH:MM:SS'例: purge master logs befor &

7、#39;2016-08-29 18:00:01' 刪除上述時(shí)間之前的所有二進(jìn)制文件2） 錯(cuò)誤日志文件 啟動(dòng)錯(cuò)誤日志文件：默認(rèn)開啟，無法禁止，就算my.ini文件不設(shè)定它。照樣存在數(shù)據(jù)庫中，保存默認(rèn)數(shù)據(jù)庫數(shù)據(jù)文件里。 當(dāng)然也可以修改此路徑。log-error=/export/app/log/errorlog/errorlog.log 注意創(chuàng)建目錄，以及更改權(quán)限目錄,重啟服務(wù)即可。查看錯(cuò)誤日志文件：普通文本文件停止錯(cuò)誤日志文件：無法停止刪除錯(cuò)誤日志文件：mysqladmin -u root -p flush-logs 創(chuàng)建一個(gè)新的，然后將舊的更名為filename.err-old，再由管理

8、員來確認(rèn)是否手工刪除該文件.3）通用查詢?nèi)罩?（因?yàn)橛涗浰锌蛻舳诉B接及操作庫信息，信息量大。影響數(shù)據(jù)庫性能，一般建議關(guān)閉） 通用查詢?nèi)罩究梢源娣诺揭粋€(gè)文本或一個(gè)表中，所有連接的語句都被記錄到該日志文件或者表中，缺省未開啟。 通過 -log=file_name或-l file_name選項(xiàng)啟動(dòng)它。如果沒有給定file_name的值， 默認(rèn)名是host_name.log。 mysqld按照它接收的順序記錄語句到查詢?nèi)罩?。這可能與執(zhí)行的順序不同。 不同于更新日志和二進(jìn)制日志，它們?cè)诓樵儓?zhí)行后，但是任何一個(gè)鎖釋放之前記錄日志。 查詢?nèi)罩景姓Z句，而二進(jìn)制日志不包含只查詢數(shù)據(jù)的語句。 服務(wù)器重新啟

9、動(dòng)和日志刷新不會(huì)產(chǎn)生新的一般查詢?nèi)罩疚募?。啟?dòng)通用查詢?nèi)罩? 默認(rèn)關(guān)閉，啟動(dòng)設(shè)定/etc/f 文件: log_output=none|file|table|file,table #通用查詢?nèi)罩据敵龈袷?general_log=on|off #是否啟用通用查詢?nèi)罩?general_log_file=filename #通用查詢?nèi)罩疚恢眉懊?例: general_log_file=/export/app/log/generallog/risoserver.log 默認(rèn)是在數(shù)據(jù)庫文件中,更改后我們可以查詢一次. 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 | -這個(gè)時(shí)候只是更改了路徑，但默認(rèn)還是未開啟狀態(tài)。| 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 -未產(chǎn)

13、生文件啟動(dòng)通用查詢?nèi)罩荆簃ysql> set global.general_log=1; -開啟，0 是關(guān)閉 停止通用查詢?nèi)罩荆篞uery 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 -文件已經(jīng)生成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 查看通用查詢?nèi)罩荆?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 -沒執(zhí)行的語句也進(jìn)去了。應(yīng)該是在發(fā)起之前就先寫入2016-08-30T07:43:52.554257Z 5 Query drop user 'nono''192.168.1.54'2016-08-30T07:44:03.454291Z 5 Quit臨時(shí)通用查詢?nèi)罩疚募恢茫簉ootrisoserver 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)刪除通用查詢?nèi)罩荆簉ootlocalhost(none)> set global general_log=off; mysqladmin -uroot -p flush-logs 這個(gè)命令會(huì)執(zhí)行先刪除舊的。并創(chuàng)建一個(gè)新的通用查詢?nèi)罩疚募?。在log_output=none,general_log=on的清下下無任何通用日志輸出。 4）慢查詢?nèi)罩緈ysql> show variables like '%slow%'+-+-+| Variable_name | Value |+-+-+| log_slow_admin_statements | OFF | log_s