MySQL Log Management

MySQL Log Management Because the Rizhi file is an important reference for mastering database operation. Therefore, the maintenance of log files is also very important. mysql Log type: error log, normal query log, binary log, slow query log 1. Error log (1) the error log primarily records information about when MySQL starts and stops, and when any errors occur during the run. The error log is fascinating to save in the MySQL installation path data folder, with the suffix named. Err. Specify in the configuration file

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

2: Query common log (1) The General query log is used to record all connected statements of MySQL, the default state is closed. Use Show to query the log information at

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)

Declare open in config file

[[email protected] data] vim /etc/my.cnf[mysqld]general_log=ONgeneral_log_file=/usr/local/mysql/data/mysql_general.log[[email protected] 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: Binary log binary logs are used to record all updates or statements that have potentially updated data, and the primary purpose of recording data changes is to maximize data recovery

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)

Add a word to the configuration file

 [[email protected] data] vim/etc/my.cnf[mysqld]log_bin=mysql-bin[[email protected] data] Systemctl Restart Mysqld.service [[email protected] data] lsmysql-bin.000001mysql-bin.index[[email protected] 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 I N Set (0.01 sec) 

Querying binary commands

[[email protected] data] mysqlbinlog --no-defaults mysql-bin.000001  #关闭默认的utf8字符集/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003 SET @[email protected]@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 [email protected]_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

4: Slow query log Slow query log is to record all command execution time check the setup time of Long_query_time to query those times that exceed the system settings and optimize them

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)

The first way to turn it on

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)

The second method is set directly inside the configuration file.

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

View Slow query times

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

MySQL inside the garbled is set its character set two ways 1: Temporary modification

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

2: Permanent modification (Specify character set utf-8 format in config file when MySQL is installed)

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

MySQL Log Management