mysql日常维护表_在日常维护管理中对MySQL 日志的需求

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]# ls

mysql_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=ON

general_log_file=/usr/local/mysql/data/mysql_general.log

[root@localhost data] systemctl restart mysqld.service

mysql_general.log

mysql> 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] ls

mysql-bin.000001

mysql-bin.index

[root@localhost data]# mysql -u root -p

mysql> 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 '

taI0Ww8BAAAAdwAAAHsAAAABAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAC1ojRbEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

AUTbzU4=

'/*!*/;

# 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.cnf

slow_query_log=ON #开启慢日志

slow_query_log_file=mysql_slow_query.log

long_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