MySQL日志：
查询日志
慢查询日志：查询执行时长超过指定时长的查询，即为慢查询
错误日志
二进制日志：复制依赖于此日志
中继日志：
事务日志：
随机I/O转换为顺序I/O
ACID：持久性
日志文件组：至少应该有两个日志文件：
注意：尽可能
查询日志：
log={ON|OFF}：是否记录所有语句的日志信息于一般查询日志文件(general_log)；
log_output：{TABLE|FILE|NONE}
TABLE和FILE可以同时出现，用逗号分隔即可：
general_log：是否启用查询日志：
general_log_file：定义一般查询日志保存的文件
慢查询日志：
long_query_time： 10.00000 #慢查询的时长，超过此时间的都被视为慢查询
slow_query_log： OFF
设定是否启用慢查询日志：它的输出位置也取决log_output={TABLE|FILE|NONE}:
slow_query_log_file： /var/run/mysqld/mysqld-slow.log
定义日志文件路径及名称
错误日志：
服务器启动和关闭过程中的信息
服务器运行过程中的错误信息
时间调度器运行一个事件时产生的信息
在复制架构中的从服务器上启动从服务器线程产生的信息
log_error = /path/to/error_log_file
log_warnings = {1|0}
二进制日志：
时间点恢复
复制
1、查看所有与日志相关的系统变量
mysql> SHOW GLOBAL VARIABLES LIKE '%log%';
+-----------------------------------------+---------------------------------+
| Variable_name | Value |
+-----------------------------------------+---------------------------------+
| back_log | 50 |
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| expire_logs_days | 0 |
| general_log | OFF |
| general_log_file | /var/run/mysqld/mysqld.log |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_mirrored_log_groups | 1 |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_bin_trust_routine_creators | OFF |
| log_error | /var/log/mysqld.log |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_relay_log_size | 0 |
| relay_log | |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| slow_query_log | OFF |
| slow_query_log_file | /var/run/mysqld/mysqld-slow.log |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sql_log_update | ON |
| sync_binlog | 0 |
+-----------------------------------------+---------------------------------+
38 rows in set (0.02 sec)
2、启用查询日志功能
mysql> SET GLOBAL log='ON';
Query OK, 0 rows affected, 1 warning (0.02 sec)
3、启用通用日志
mysql> SET GLOBAL general_log='on';
Query OK, 0 rows affected (0.00 sec)
4、看到查询日志已启用
mysql> SHOW GLOBAL VARIABLES LIKE '%log%';
+-----------------------------------------+---------------------------------+
| Variable_name | Value |
+-----------------------------------------+---------------------------------+
| back_log | 50 |
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| expire_logs_days | 0 |
| general_log | ON |
| general_log_file | /var/run/mysqld/mysqld.log |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_mirrored_log_groups | 1 |
| log | ON |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_bin_trust_routine_creators | OFF |
| log_error | /var/log/mysqld.log |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_relay_log_size | 0 |
| relay_log | |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| slow_query_log | OFF |
| slow_query_log_file | /var/run/mysqld/mysqld-slow.log |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sql_log_update | ON |
| sync_binlog | 0 |
+-----------------------------------------+---------------------------------+
38 rows in set (0.01 sec)
5、使用查询功能，这里是查询classes表
mysql> SELECT * FROM classes;
+---------+----------------+----------+
| CLASSID | Class | NumOfStu |
+---------+----------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 3 | QingCheng Pai | 11 |
| 4 | Wudang Pai | 12 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
| 9 | Liangshan | 22 |
+---------+----------------+----------+
9 rows in set (0.01 sec)
6、看到查询日志已经生成
[root@localhost mysqld]# cat /var/run/mysqld/mysqld.log
/usr/libexec/mysqld, Version: 5.1.73 (Source distribution). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
170325 22:30:28 11 Query SET GLOBAL general_log='on'
170325 22:31:08 11 Query SHOW GLOBAL VARIABLES LIKE '%log%'
170325 22:33:01 11 Query show database
170325 22:33:04 11 Query show databases
170325 22:33:08 11 Query SELECT DATABASE()
11 Init DB success
11 Query show databases
11 Query show tables
11 Field List classes
170325 22:33:42 11 Query SELECT * FROM classes
6、生产环境一般建议把查询日志记录到数据库中
mysql> SET GLOBAL log_output='TABLE';
Query OK, 0 rows affected (0.00 sec)
7、看到查询日志已记录到表中
mysql> SHOW GLOBAL VARIABLES LIKE '%log%';
+-----------------------------------------+---------------------------------+
| Variable_name | Value |
+-----------------------------------------+---------------------------------+
| back_log | 50 |
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| expire_logs_days | 0 |
| general_log | ON |
| general_log_file | /var/run/mysqld/mysqld.log |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_mirrored_log_groups | 1 |
| log | ON |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_bin_trust_routine_creators | OFF |
| log_error | /var/log/mysqld.log |
| log_output | TABLE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_relay_log_size | 0 |
| relay_log | |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| slow_query_log | OFF |
| slow_query_log_file | /var/run/mysqld/mysqld-slow.log |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sql_log_update | ON |
| sync_binlog | 0 |
+-----------------------------------------+---------------------------------+
38 rows in set (0.11 sec)
8、执行重复的select操作
mysql> SELECT * FROM classes;
+---------+----------------+----------+
| CLASSID | Class | NumOfStu |
+---------+----------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 3 | QingCheng Pai | 11 |
| 4 | Wudang Pai | 12 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
| 9 | Liangshan | 22 |
+---------+----------------+----------+
9 rows in set (0.01 sec)
mysql> SELECT * FROM classes;
+---------+----------------+----------+
| CLASSID | Class | NumOfStu |
+---------+----------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 3 | QingCheng Pai | 11 |
| 4 | Wudang Pai | 12 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
| 9 | Liangshan | 22 |
+---------+----------------+----------+
9 rows in set (0.01 sec)
9、再查看查询日志信息，日志每有记录
[root@localhost mysqld]# cat /var/run/mysqld/mysqld.log
/usr/libexec/mysqld, Version: 5.1.73 (Source distribution). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
170325 22:30:28 11 Query SET GLOBAL general_log='on'
170325 22:31:08 11 Query SHOW GLOBAL VARIABLES LIKE '%log%'
170325 22:33:01 11 Query show database
170325 22:33:04 11 Query show databases
170325 22:33:08 11 Query SELECT DATABASE()
11 Init DB success
11 Query show databases
11 Query show tables
11 Field List classes
170325 22:33:42 11 Query SELECT * FROM classes
170325 22:40:39 11 Query SET GLOBAL log_output='TABLE'
10、查看mysql数据库中的general_log表
mysql> SHOW TABLES FROM mysql;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
23 rows in set (0.02 sec)
11、查看general_log表的内容
mysql> SELECT * FROM general_log;
+---------------------+---------------------------+-----------+-----------+--------------+-----------------------------------------------------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+---------------------+---------------------------+-----------+-----------+--------------+-----------------------------------------------------------------+
| 2017-03-25 22:41:35 | root[root] @ localhost [] | 11 | 0 | Query | SELECT * FROM classes |
| 2017-03-25 22:41:36 | root[root] @ localhost [] | 11 | 0 | Query | SELECT * FROM classes |
| 2017-03-25 22:42:50 | root[root] @ localhost [] | 11 | 0 | Query | SHOW VARIBALES LIKE '%log%' |
| 2017-03-25 22:43:05 | root[root] @ localhost [] | 11 | 0 | Query | SHOW GLOBAL VARIABLES LIKE '%log%' |
| 2017-03-25 22:46:02 | root[root] @ localhost [] | 11 | 0 | Query | SHOW TABLES |
| 2017-03-25 22:46:10 | root[root] @ localhost [] | 11 | 0 | Quit | |
| 2017-03-25 22:46:13 | [root] @ localhost [] | 12 | 0 | Connect | Access denied for user 'root'@'localhost' (using password: YES) |
| 2017-03-25 22:46:16 | root[root] @ localhost [] | 13 | 0 | Connect | root@localhost on |
| 2017-03-25 22:46:16 | root[root] @ localhost [] | 13 | 0 | Query | select @@version_comment limit 1 |
| 2017-03-25 22:46:20 | root[root] @ localhost [] | 13 | 0 | Query | show databases |
| 2017-03-25 22:46:31 | root[root] @ localhost [] | 13 | 0 | Query | SELECT TABLES FROM mysql |
| 2017-03-25 22:46:36 | root[root] @ localhost [] | 13 | 0 | Query | SELECT DATABASE() |
| 2017-03-25 22:46:36 | root[root] @ localhost [] | 13 | 0 | Init DB | mysql |
| 2017-03-25 22:46:36 | root[root] @ localhost [] | 13 | 0 | Query | show databases |
| 2017-03-25 22:46:36 | root[root] @ localhost [] | 13 | 0 | Query | show tables |
| 2017-03-25 22:46:36 | root[root] @ localhost [] | 13 | 0 | Field List | columns_priv |
| 2017-03-25 22:46:36 | root[root] @ localhost [] | 13 | 0 | Field List | db |
| 2017-03-25 22:46:36 | root[root] @ localhost [] | 13 | 0 | Field List | event |
| 2017-03-25 22:46:36 | root[root] @ localhost [] | 13 | 0 | Field List | func |
| 2017-03-25 22:46:36 | root[root] @ localhost [] | 13 | 0 | Field List | general_log |
| 2017-03-25 22:46:36 | root[root] @ localhost [] | 13 | 0 | Field List | help_category |
| 2017-03-25 22:46:36 | root[root] @ localhost [] | 13 | 0 | Field List | help_keyword |
| 2017-03-25 22:46:36 | root[root] @ localhost [] | 13 | 0 | Field List | help_relation |
| 2017-03-25 22:46:36 | root[root] @ localhost [] | 13 | 0 | Field List | help_topic |
| 2017-03-25 22:46:36 | root[root] @ localhost [] | 13 | 0 | Field List | host |
| 2017-03-25 22:46:36 | root[root] @ localhost [] | 13 | 0 | Field List | ndb_binlog_index |
| 2017-03-25 22:46:36 | root[root] @ localhost [] | 13 | 0 | Field List | plugin |
| 2017-03-25 22:46:36 | root[root] @ localhost [] | 13 | 0 | Field List | proc |
| 2017-03-25 22:46:36 | root[root] @ localhost [] | 13 | 0 | Field List | procs_priv |
| 2017-03-25 22:46:36 | root[root] @ localhost [] | 13 | 0 | Field List | servers |
| 2017-03-25 22:46:36 | root[root] @ localhost [] | 13 | 0 | Field List | slow_log |
| 2017-03-25 22:46:36 | root[root] @ localhost [] | 13 | 0 | Field List | tables_priv |
| 2017-03-25 22:46:36 | root[root] @ localhost [] | 13 | 0 | Field List | time_zone |
| 2017-03-25 22:46:36 | root[root] @ localhost [] | 13 | 0 | Field List | time_zone_leap_second |
| 2017-03-25 22:46:36 | root[root] @ localhost [] | 13 | 0 | Field List | time_zone_name |
| 2017-03-25 22:46:36 | root[root] @ localhost [] | 13 | 0 | Field List | time_zone_transition |
| 2017-03-25 22:46:36 | root[root] @ localhost [] | 13 | 0 | Field List | time_zone_transition_type |
| 2017-03-25 22:46:36 | root[root] @ localhost [] | 13 | 0 | Field List | user |
| 2017-03-25 22:46:45 | root[root] @ localhost [] | 13 | 0 | Query | SELECT TABLES FROM mysql |
| 2017-03-25 22:47:21 | root[root] @ localhost [] | 13 | 0 | Query | SHOW TABLES FROM mysql |
| 2017-03-25 22:47:50 | root[root] @ localhost [] | 13 | 0 | Query | SELECT * FROM general_log |
+---------------------+---------------------------+-----------+-----------+--------------+-----------------------------------------------------------------+
41 rows in set (0.00 sec)
12、一般建议关闭查询日志
mysql> SET GLOBAL log='OFF';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SET GLOBAL general_log='OFF';
Query OK, 0 rows affected (0.01 sec)
13、查看跟慢查询日志相关的参数
mysql> SHOW GLOBAL VARIABLES LIKE 'slow%';
+---------------------+---------------------------------+
| Variable_name | Value |
+---------------------+---------------------------------+
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /var/run/mysqld/mysqld-slow.log |
+---------------------+---------------------------------+
3 rows in set (0.04 sec)
14、执行多长时间的查询算是慢查询，大于这个时间的都被视为慢查询
mysql> SHOW GLOBAL VARIABLES LIKE 'long%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.03 sec)
阅读(1333) | 评论(0) | 转发(0) |
