1.错误日志

MySQL的错误日志error log记录mysqld服务进程启动/关闭或运行过遇到的错误日志。
1.在配置文件增加如下行

[root@sql ~]# grep log-error  /etc/my.cnf
[mysqld]
log-error=/log/mysql/err.log

2.创建对应目录与日志文件, 并授权

[root@sql ~]# mkdir -p /log/mysql/
[root@sql ~]# touch /log/mysql/err.log
[root@sql ~]# chown -R mysql.mysql /log/mysql/err.log
//重启数据库生效
[root@sql ~]# systemctl restart mysqld

3.查看错误日志

[root@vm-70-161 ~]# grep -i "error" /var/log/mysqlerr.log
2018-05-07T02:46:08.487851Z 0 [ERROR] unknown option '--dasdasdsadas'

4.通过终端查看日志

mysql> show variables like "log_error%";
+---------------------+--------------------+
| Variable_name       | Value              |
+---------------------+--------------------+
| log_error           | /log/mysql/err.log |
| log_error_verbosity | 3                  |
+---------------------+--------------------+
2 rows in set (0.00 sec)

2.查询日志

普通查询日志general query log记录客户端连接信息和执行的所有SQL语句信息, 默认关闭
1.开启general日志

[root@sql ~]# cat /etc/my.cnf
[mysqld]
general_log=ON
general_log_file=/log/mysql/select.log

2.创建对应目录与日志文件, 并授权

[root@sql ~]# mkdir -p /log/mysql
[root@sql ~]# touch /log/mysql/select.log 
[root@sql ~]# chown mysql.mysql /log/mysql/select.log
//重启数据库生效
[root@sql ~]# systemctl restart mysqld

3.查看日志

[root@sql ~]# cat /log/mysql/select.log 
/usr/sbin/mysqld, Version: 5.7.22-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
2018-05-07T03:05:28.890758Z     4 Query insert into t5 values (1,"test")
2018-05-07T03:06:08.983337Z     4 Query update t5 set name='tt' where name='test'
2018-05-07T03:06:19.528444Z     4 Query show databases
2018-05-07T03:06:25.949473Z     4 Query drop database test
2018-05-07T03:06:25.972178Z     4 Query SELECT DATABASE()

4.终端查询日志情况

mysql> show variables like 'general_log%';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| general_log      | ON                    |
| general_log_file | /log/mysql/select.log |
+------------------+-----------------------+
2 rows in set (0.01 sec)
//临时开启查询日志
mysql> set global general_log = On;
//临时关闭查询日志
mysql> set global general_log = Off;

3.慢查询日志

慢查询日志slow query log记录执行过长的SQL语句, 可利用慢查询优化进行调整优化
1.开启慢查询日志

[root@sql ~]# cat /etc/my.cnf
[mysqld]
slow_query_log = on
slow_query_log_file=/log/mysql/slow.log
long_query_time=2
log_queries_not_using_indexes  #没有启用索引的查询语句

2.创建对应目录与日志文件, 并授权

[root@sql ~]# mkdir -p /log/mysql
[root@sql ~]# touch /log/mysql/slow.log 
[root@sql ~]# chown mysql.mysql /log/mysql/slow.log
//重启数据库生效
[root@sql ~]# systemctl restart mysqld

3.执行过慢语句进行测试

mysql> SELECT BENCHMARK(50000000,2*3);

4.检查慢日志是否记录

[root@sql ~]# cat /var/lib/mysql/mysql-slow.log
Time                 Id Command    Argument
# Time: 2018-05-07T03:27:07.301336Z
# User@Host: root[root] @ localhost []  Id:     2
# Query_time: 23.956456  Lock_time: 0.000365 Rows_sent: 0  Rows_examined: 200000
use bgx;
SET timestamp=1525663627;
# Query_time: 8.766179  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1525651122;
select BENCHMARK(500000000,2*3);

4.二进制日志

二进制日志binary log记录数据被修改的相关信息
1.开启binlog二进制日志

[root@sql ~]# cat /etc/my.cnf
[mysqld]
server-id = 161
log-bin = /log/mysql/bin/sql_70_161
expire_logs_days = 30 #多少天天数后的日志将会被自动删除

2.创建对应目录与日志文件, 并授权

[root@vm-70-161 ~]# mkdir /log/mysql/bin
[root@vm-70-161 ~]# chown -R mysql.mysql /log/mysql/
[root@vm-70-161 ~]# systemctl restart mysqld
//重启数据库生效
[root@sql ~]# systemctl restart mysqld

3.查看binlog日志信息

[root@sql ~]# mysqlbinlog -v /log/mysql/bin/sql_70_161.000001

4.日志截断

    • 1.切割binlog日志
        ○ 重启mysqld服务, 在mysql控制台执行mysql> flush logs
        ○ 在mysql控制台执行reset master会清空所有binlog(危险)
    • 2.删除部分binlog日志
        ○ mysql> purge master logs to 'sql_70_161.000004'; #删除指定日志以前的日志文件
        ○ mysql> purge binary logs before '2018-05-07 11:57:33';#删除指定时间以前的日志文件

5.恢复日志

//使用时间节点进行恢复
# mysqlbinlog mysql.000002 --start-datetime="2018-05-07 12:05:00" 
# mysqlbinlog mysql.000002 --stop-datetime="2018-05-07 11:02:54"
# mysqlbinlog mysql.000002 --start-datetime="2018-12-05 10:02:56" -stop-datetime="2018-12-05 11:02:54"
//使用位置点进行恢复
# mysqlbinlog mysql.000002 --start-position=124 
# mysqlbinlog mysql.000002 --stop-position=124 
# mysqlbinlog mysql.000002 --start-position=124 --stop-position=336