Mysql学习总结_20190318_Mysql四类log简介

只是简单的描述了下四类日志的作用、相关参数，后续还需继续拓展补充

-----------Mysql二进制日志

my.cnf中和二进制日志有关的参数：
        expire_logs_days=10
        max_binlog_size=500M
        log-bin=/u01/mysql/log/mysql-bin

查看二进制日志文件：

[root@rhel6 log]# mysqlbinlog mysql-bin.000004 

 删除二进制日志文件：
1.reset master(删除所有的二进制日志文件，重新创建日志，扩展名从000001开始)
2.purge master logs(根据日志创建时间删除，日志创建时间可通过mysqlbinlog命令查看)

mysql> purge master logs to 'mysql-bin.000002';
Query OK, 0 rows affected (0.11 sec)

mysql> purge master logs before '2019-02-16';
Query OK, 0 rows affected (0.00 sec)

mysql> purge master logs before '20190219';
Query OK, 0 rows affected (0.10 sec)

 使用二进制文件恢复数据库(可行性待确认)：

[root@rhel6 log]# mysqlbinlog --stop-datetime="2019-02-20 09:30:00" mysql-bin.000008|mysql -uroot -pCqmyg14dss

 

暂停和恢复二进制日志功能：

暂停记录(sql_log_bin=OFF or sql_log_bin=0)：

mysql> set sql_log_bin=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

恢复记录(sql_log_bin=ON or sql_log_bin=1)：

mysql> set sql_log_bin=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

 

-----------Mysql错误日志

my.cnf中和错误日志有关的参数：
    log-error=/u01/mysql/log/mysqld.log
    
删除错误日志(两种方法)：

[root@rhel6 log]# mysqladmin -uroot -pCqmyg14dss flush-logs
 
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

-----------Mysql通用查询日志

my.cnf中和通用查询日志日志有关的参数：
        general_log=ON
        general_log_file=/u01/mysql/data/rhel6.log

设置开启/关闭通用日志查询(mysql重启后失效)：
        set global general_log = on;
        set global general_log = 1;
        set global general_log = off;
        set global general_log = 0;

mysql> show variables like 'gener%';
+------------------+---------------------------+
| Variable_name    | Value                     |
+------------------+---------------------------+
| general_log      | ON                        |
| general_log_file | /u01/mysql/data/rhel6.log |
+------------------+---------------------------+
2 rows in set (0.01 sec)

mysql> set global general_log = off;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'gener%';
+------------------+---------------------------+
| Variable_name    | Value                     |
+------------------+---------------------------+
| general_log      | OFF                       |
| general_log_file | /u01/mysql/data/rhel6.log |
+------------------+---------------------------+
2 rows in set (0.00 sec)

mysql> set global general_log = on;
Query OK, 0 rows affected (0.10 sec)

mysql> show variables like 'gener%';
+------------------+---------------------------+
| Variable_name    | Value                     |
+------------------+---------------------------+
| general_log      | ON                        |
| general_log_file | /u01/mysql/data/rhel6.log |
+------------------+---------------------------+
2 rows in set (0.01 sec)

删除通用日志：物理删除后执行下面语句生成新的log(err log、general log、slow log一样的处理方式)

[root@rhel6 log]# mysqladmin -uroot -pCqmyg14dss flush-logs
or
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

注：flush-logs：
    1.会生成新的binlog、从库执行会生成新的binlog和relaylog 
    2.当物理删除general log和slow log后执行，会重新生成新的log文件

-----------Mysql慢日志

my.cnf中和慢日志有关的参数：
        slow_query_log =1(ON)
        slow_query_log_file=/u01/mysql/data/rhel6-slow.log
        long_query_time = 10 ---单位：s
        log_queries_not_using_indexes = 1
        log_output='FILE,TABLE' -----对general log同样有效

设置开启/关闭慢日志(mysql重启后失效)：
        set global slow_query_log=ON;
        set global slow_query_log=1;
        set global slow_query_log=OFF;
        set global slow_query_log=0;  
        
----slow_query_log、slow_query_log_file指定慢日志开启or关闭和日志file存放位置,file默认存在data目录下
----log_slow_admin_statements表示是否将慢管理语句例如ANALYZE TABLE和ALTER TABLE等记入慢查询日志
----log_slow_slave_statements表示是否将slave产生的慢查询记入慢查询日志

mysql> show variables like '%slow%';
+---------------------------+--------------------------------+
| Variable_name             | Value                          |
+---------------------------+--------------------------------+
| log_slow_admin_statements | OFF                            |
| log_slow_slave_statements | OFF                            |
| slow_launch_time          | 2                              |
| slow_query_log            | OFF                            |
| slow_query_log_file       | /u01/mysql/data/rhel6-slow.log |
+---------------------------+--------------------------------+
5 rows in set (0.01 sec)

 ----log_output指定输出类型(file、table)

mysql> show variables like '%log_out%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

----使用set global long_query_time修改参数时，需要断开重连或者使用 show global variables才能看到修改变化 

 

mysql> show variables like '%long_query%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 6.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql> set global long_query_time = 9;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%long_query%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 6.000000 |
+-----------------+----------+
1 row in set (0.01 sec)

mysql> show global variables like '%long_query%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 9.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

----log_queries_not_using_indexes指定是否将未使用索引的查询记录到慢日志中(full index scan的sql也会被记录) 

mysql> show variables like '%log_queries%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)

删除慢日志：物理删除后执行下面语句生成新的log(err log、general log、slow log一样的处理方式)

[root@rhel6 log]# mysqladmin -uroot -pCqmyg14dss flush-logs
or
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)