Mysql日志管理笔记(5.7版本)wangzz四种日志文件：1,二进制日志：以二进制形式记录数据库的各种操作，但不记录查询语句.2,错误日志: 该日志文件记录mysql服务器启动，关闭和运行时的出错等信息。
3,通用查询日志：记录mysql启动，关闭，及客户端的连接信息，更新数据记录sql语句和查询数据记录sql语句.4,慢查询日志：记录执行时间超过指定时间的各种操作，通过工具分析慢查询日志可以定位性能瓶颈。
1,二进制日志1）启动二进制日志/etc/f 文件。
[mysqld]log-bin[=dir/[filename]]server_id=100log-bin=/export/app/log/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_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 | 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_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)查看二进制日志文件[root@risoserverbinlog]# mysqlbinlog binlog.000001停止二进制日志文件(ROOT用户权限)方法1,/etc/f 文件删除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 '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，再由管理员来确认是否手工删除该文件.3）通用查询日志（因为记录所有客户端连接及操作库信息，信息量大。
影响数据库性能，一般建议关闭）通用查询日志可以存放到一个文本或一个表中，所有连接的语句都被记录到该日志文件或者表中，缺省未开启。
通过 --log[=file_name]或-l [file_name]选项启动它。
如果没有给定file_name的值，默认名是host_name.log。
mysqld按照它接收的顺序记录语句到查询日志。
这可能与执行的顺序不同。
不同于更新日志和二进制日志，它们在查询执行后，但是任何一个锁释放之前记录日志。
查询日志包含所有语句，而二进制日志不包含只查询数据的语句。
服务器重新启动和日志刷新不会产生新的一般查询日志文件。
启动通用查询日志: 默认关闭，启动设定/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%';+-------------------------+------------------------------+| 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 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 |+------------------+-------------------------------------------+| general_log | OFF | --这个时候只是更改了路径，但默认还是未开启状态。
| general_log_file | /export/app/log/generallog/risoserver.log | --这是更改后的。
+------------------+-------------------------------------------+2 rows in set (0.00 sec)[root@risoservererrorlog]# ls /export/app/log/generallog/risoserver.logls: cannot access /export/app/log/generallog/risoserver.log: No such file or directory --未产生文件启动通用查询日志：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)[root@risoservererrorlog]# ls /export/app/log/generallog/risoserver.log/export/app/log/generallog/risoserver.log --文件已经生成[root@risoservererrorlog]# 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 MySQL 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 'help;' 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 | 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 affected (0.02 sec)mysql> exitBye[root@risoservererrorlog]# 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.888155Z 4 Query show variables like '%general%'2016-08-30T07:41:37.164497Z 4 Quit2016-08-30T07:43:00.028798Z 5 Connect root@localhoston 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.824000Z 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_priv2016-08-30T07:43:07.845635Z 5 Field List db2016-08-30T07:43:07.846286Z 5 Field List engine_cost2016-08-30T07:43:07.846707Z 5 Field List event2016-08-30T07:43:07.847342Z 5 Field List func2016-08-30T07:43:07.847632Z 5 Field List general_log2016-08-30T07:43:07.848020Z 5 Field List gtid_executed2016-08-30T07:43:07.848334Z 5 Field List help_category2016-08-30T07:43:07.849232Z 5 Field List help_keyword2016-08-30T07:43:07.855902Z 5 Field List help_relation2016-08-30T07:43:07.856912Z 5 Field List help_topic2016-08-30T07:43:07.896056Z 5 Field List innodb_index_stats2016-08-30T07:43:07.896976Z 5 Field List innodb_table_stats2016-08-30T07:43:07.900019Z 5 Field List ndb_binlog_index2016-08-30T07:43:07.901291Z 5 Field List plugin2016-08-30T07:43:07.901523Z 5 Field List proc2016-08-30T07:43:07.908515Z 5 Field List procs_priv2016-08-30T07:43:07.908906Z 5 Field List proxies_priv2016-08-30T07:43:07.909355Z 5 Field List server_cost2016-08-30T07:43:07.909675Z 5 Field List servers2016-08-30T07:43:07.910154Z 5 Field List slave_master_info2016-08-30T07:43:07.911203Z 5 Field List slave_relay_log_info2016-08-30T07:43:07.911706Z 5 Field List slave_worker_info2016-08-30T07:43:07.912408Z 5 Field List slow_log2016-08-30T07:43:07.927753Z 5 Field List tables_priv2016-08-30T07:43:07.928191Z 5 Field List time_zone2016-08-30T07:43:07.928505Z 5 Field List time_zone_leap_second2016-08-30T07:43:07.928825Z 5 Field List time_zone_name2016-08-30T07:43:07.929068Z 5 Field List time_zone_transition2016-08-30T07:43:07.929406Z 5 Field List time_zone_transition_type2016-08-30T07:43:07.929887Z 5 Field List user2016-08-30T07:43:27.363704Z 5 Query select user,host from user2016-08-30T07:43:35.350455Z 5 Query drop user nono --没执行的语句也进去了。