MySQL Log management

One or four types of logs

1. Error log: Log the start, run, and shutdown process of MySQL server, ending with. Err by default
Mysql> Show variables like ' log_error ';

2, binary log * * *
Documenting all DDL and DML operations that a user makes to a database, without logging query statements, plays a critical role in disaster recovery of the data.
Ddl:create, ALTER, DROP, truncate
Dml:insert, UPDATE, delete
Binary log functions: recovery, AB replication
Binary logs are turned on by default in 5.5 and are not turned on by default in 5.6.
/data/mysql/mysql-bin. Xxxxxx
3, General query log: Record all requests for the database (including login, query, etc.), default does not open
Large log volume, disk space consumption, generally do not open
Default name: Host name. log
4, slow query log: The record is the query time more than the specified time of the query statement, generally used to optimize the query
Default is not turned on

Second, open various logs (5.5), modify the configuration file
1, open the General query log, specify the log file name
Add the following line to the MYSQLD segment:
Log=general_select_log//The log file name to the right of the equals sign custom
#/etc/init.d/mysqld Restart
# Ls/data/mysql/general_select_log
Validation: Execute some query statements inside the mysql> to see if the file has a corresponding content.
2. Turn on the slow query log, specify the log file name, set the time-out
Add the following line to the MYSQLD segment:
Log-slow-queries=slow_log//Turn on slow query log, specify the name of the log file
Long_query_time=5//Set slow query timeout time
#/etc/init.d/mysqld Restart
# Ls/data/mysql/slow_log
Mysql> Select Sleep (6); Perform a query that exceeds 5 seconds
See if the slow_log has a related record.
3. Error log, modify prefix name
Add the following line to the MYSQLD segment:
Log-error=errorlog//log file name becomes Errorlog.err
[Email protected] mysql]#/etc/init.d/mysqld restart
[Email protected] mysql]# Ls/data/mysql/errorlog.err
4. Open Binary Log
Log-bin=mysql-bin//Open binary log with the original line
Log-bin-index=mysql-bin.index//After adding, generally do not change


Third, the management of the log
Delete log (do not delete it easily)
1) Delete all logs before a log file
Mysql> purge master logs to ' mysql-bin.000008 ';
2) How many days before the log file was deleted
Mysql> purge master logs before date_sub (Current_date,interval 1 day);
3) Reset the binary log
mysql> Reset Master; Re-counting starting from 000001
4) manually generate a new binary log file
mysql> flush logs;
Viewing log information
Mysql> show master logs; Displays information about binary log files, including name and size

MySQL Log management