MySQL 日志管理

由于rizhi文件是掌握数据库运行的重要参考。因此日志文件的维护也是十分重要的意义

MySQL的日志类型：错误日志，普通查询日志，二进制日志，慢查询日志

1.错误日志

（1）错误日志主要记录当MySQL启动和停止时，以及在运行过程中发生任何错误时的相关消息。错误日志迷人保存在MySQL的安装路径data文件夹下，后缀名为.err。在配置文件中指定

[root@localhost data] vim /etc/my.cnf #在mysqld里面添加[mysqld]log-error=/usr/local/mysql/data/mysql_error.log[root@localhost ~]# systemctl restart mysqld.service #重启之后会生成这个文件[root@localhost ~]# cd /usr/local/mysql/data/[root@localhost data]# lsmysql_error.log

2：查询通用日志

（1）通用查询日志用来记录MySQL的所有连接的语句，默认状态关闭，。使用SHOW可以查询处日志信息

mysql> show variables like ‘general%‘;+------------------+-------------------------------------+| Variable_name | Value |+------------------+-------------------------------------+| general_log | OFF || general_log_file | /usr/local/mysql/data/localhost.log |+------------------+-------------------------------------+2 rows in set (0.01 sec)

在配置文件中声明开启

[root@localhost data] vim /etc/my.cnf[mysqld]general_log=ONgeneral_log_file=/usr/local/mysql/data/mysql_general.log[root@localhost data] systemctl restart mysqld.service mysql_general.logmysql> show variables like ‘general%‘; #状态开启+------------------+-----------------------------------------+| Variable_name | Value |+------------------+-----------------------------------------+| general_log | ON || general_log_file | /usr/local/mysql/data/mysql_general.log |+------------------+-----------------------------------------+2 rows in set (0.01 sec)

3：二进制日志

二进制日志用来记录所有更新或者已经潜在更新了数据的语句，记录了数据的更改主要目的是能够最大程度的恢复数据

mysql> show variables like ‘log_bin%‘; #默认是关闭状态+---------------------------------+-------+| Variable_name | Value |+---------------------------------+-------+| log_bin | OFF || log_bin_basename | || log_bin_index | || log_bin_trust_function_creators | OFF || log_bin_use_v1_row_events | OFF |+---------------------------------+-------+5 rows in set (0.00 sec)

在配置文件中加入一句话

[root@localhost data] vim /etc/my.cnf[mysqld]log_bin=mysql-bin[root@localhost data] systemctl restart mysqld.service [root@localhost data] lsmysql-bin.000001mysql-bin.index[root@localhost data]# mysql -u root -pmysql> show variables like ‘log_bin%‘;+---------------------------------+---------------------------------------+| Variable_name | Value |+---------------------------------+---------------------------------------+| log_bin | ON || log_bin_basename | /usr/local/mysql/data/mysql-bin || log_bin_index | /usr/local/mysql/data/mysql-bin.index || log_bin_trust_function_creators | OFF || log_bin_use_v1_row_events | OFF |+---------------------------------+---------------------------------------+5 rows in set (0.01 sec)

查询二进制命令

[root@localhost data] mysqlbinlog --no-defaults mysql-bin.000001 #关闭默认的utf8字符集/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4#180628 16:56:21 server id 1 end_log_pos 123 CRC32 0x4ecddb44 Start: binlog v 4, server v 5.7.17-log created 180628 16:56:21 at startup# Warning: this binlog is either in use or was not closed properly.ROLLBACK/*!*/;BINLOG ‘taI0Ww8BAAAAdwAAAHsAAAABAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAC1ojRbEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQAAUTbzU4=‘/*!*/;# at 123#180628 16:56:21 server id 1 end_log_pos 154 CRC32 0x0b6126ac Previous-GTIDs# [empty]SET @@SESSION.GTID_NEXT= ‘AUTOMATIC‘ /* added by mysqlbinlog */ /*!*/;DELIMITER ;# End of log file/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

4:慢查询日志

慢查询日志是记录所有命令执行时间查过long_query_time的设置时间用于查询那些超过系统设置的时间与其对其进行优化

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 | /usr/local/mysql/data/localhost-slow.log |+---------------------------+------------------------------------------+5 rows in set (0.00 sec)

对其进行开启

第一种方法

mysql> set global slow_query_log=ON;Query OK, 0 rows affected (0.01 sec)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 | ON #开启 || slow_query_log_file | /usr/local/mysql/data/localhost-slow.log |+---------------------------+------------------------------------------+5 rows in set (0.00 sec)

第二种方法直接在配置文件里面设定

[root@localhost data] vim /etc/my.cnfslow_query_log=ON #开启慢日志slow_query_log_file=mysql_slow_query.loglong_query_time=5 #时间为5秒

查看慢查询时间

mysql> show variables like ‘long_query_time‘;+-----------------+----------+| Variable_name | Value |+-----------------+----------+| long_query_time | 5.000000 |+-----------------+----------+1 row in set (0.00 sec)

MySQL里面的乱码是设定它的字符集两种方法

1：临时修改

mysql> set names utf8; #开启重启后失效

2：永久性修改(安装MySQL的时候在配置文件里面就指定字符集utf-8格式)

[root@localhost data] vim /etc/my.cnf[client]default-character-set=utf8[mysql]default-char[mysqld][mysqld]character_set_server=utf8