Shulou(Shulou.com)06/01 Report--

One or four journals.

Error log: records the startup, operation and shutdown process of mysql server, ending with.err by default.

mysql> show variables like 'log_error';

2. Binary log ***

Recording all ddl and dml operations performed by users on the database, without recording query statements, is critical to disaster recovery of data.

ddl：create、alter、drop、truncate

dml：insert、update、delete

Binary logging functions: recovery, AB replication

Binary logging is enabled by default in 5.5 and disabled by default in 5.6.

/data/mysql/mysql-bin.XXXXXX

3. General query log: records all request information for database (including login, query, etc.), which is not enabled by default.

Log volume is large, occupy disk space, generally do not open

Default Name: Hostname.log

4. Slow query log: It records query statements whose query time exceeds the specified time, which is generally used to optimize queries.

The default is also not open

Second, open various logs (5.5), modify the configuration file

1. Open general query log and specify log file name

Add the following line to the mysqld paragraph:

log=general_select_log //log file name on the right side of the equal sign custom

# /etc/init.d/mysqld restart

# ls /data/mysql/general_select_log

Validation: Run some queries in mysql> to see if the file has content.

2. Open slow query log, specify log file name, set timeout time

Add the following line to the mysqld paragraph:

log-slow-queries=slow_log //Open slow query logs, specify log file names

long_query_time=5 //Set slow query timeout

# /etc/init.d/mysqld restart

# ls /data/mysql/slow_log

mysql> select sleep(6); //perform an operation that queries for more than 5 seconds

See if slow_log has any relevant records.

3. Error log, modify prefix name

Add the following line to the mysqld paragraph:

log-error=errorlog //log file name changed to errorlog.err

[root@s200 mysql]# /etc/init.d/mysqld restart

[root@s200 mysql]# ls /data/mysql/errorlog.err

4. Open binary log

log-bin=mysql-bin //Open binary log, there is this line

log-bin-index=mysql-bin.index //added after, generally do not change

III. Management of logs

Delete logs (do not delete easily)

1) Delete all logs before a log file

mysql> purge master logs to 'mysql-bin.000008';

2) How many days old log files are deleted

mysql> purge master logs before date_sub(current_date,interval 1 day);

3) Reset binary log

mysql> reset master; //Recount from 00001

4) Manually generate new binary log files

mysql> flush logs;

view the log information

mysql> show master logs; //Displays binary log file information, including name and size

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.