Mysql Log Management

MySQL log-records operations and information during MySQL database operation

Log role
(1) When the database has been accidentally damaged, you can query the cause of the error through the log file.
(2) Using log files for data recovery

Log classification
(1) Binary log: Records operations in the database as a binary file, but does not log query statements
(2) Error log: Log the MySQL server startup, shutdown and run errors and other information
(3) Slow query log: Record the execution time more than a specified time operation
(4) Universal query log: Record all actions of the user, including starting and shutting down the MySQL service, updating statements, query statements, etc.

Log Settings
(1) In addition to the binary log, the others are text files
(2) Log files are usually stored in the MySQL data directory
(3) The error log function is started by default, others need to be started manually
(4) But the start log function will slow down the execution of MySQL, because it takes time for an operation to be written into the log.

binary log
(1) Records operations in the database as a binary file, but does not log query statements
(2) Also called the Change log (update log), which is used primarily to record database changes
(3) Start and set binary log: in MySQL configuration file, log-bin= Dir/filename
                          and nbsp      dir is the directory where the binary log is stored;
                                    Every time MySQL is started, a filename.00000x file will be generated in this directory;
                                    There is also a filename.index file for storing all two List of files in the file,
                                    If we do not set dir and filename, the default is to name the hostname-bin.00000x under Data directory

# cat/etc/my.cnf|egrep "Log-bin" Log-bin =/data/mysqldb/binlog/mysql-bin# ls/data/mysqldb/binlog/mysql-bin.000001 mysql-bin.000003 mysql-bin.000005 mysql-bin.000007 mysql-bin.indexmysql-bin.000002 mysql-bin.000004 mysql-bin.000006 mysql-bin.000008

(4) Temporary stop and start binary log

Set sql_log_bin=0; stop -set sql_log_bin=1; Start

(5) Note: Binary log files and database data files should not be placed on the same hard disk, if the hard disk holding the data file is broken, you can use the binary log of another hard disk to recover data

(6) To view the binary log:

# Mysqlbinlog Mysql-bin. 000001

(7) Delete the binary log:

// Delete all reset master; // Delete all prior to 00004 ' mysql-bin.000004 '; // deletes all before the specified date time ' 2016-08-10 15:00:00 ';

(8) Restoring a database using a binary log

# Mysqlbinlog Mysql-bin. 000001 | mysql-u root-p# mysqlbinlog mysql-bin.000002 | Mysql-u root-p This command can be interpreted as: Read the binary log file using Mysqlbinlog and then restore to the database using the MySQL command note that the restore must be a small number to restore first

(9) POS number using binary log file | Point-in-time recovery

//perform 3 operations first-CREATE DATABASE 2test-CREATE DATABASE Alvin-drop Database 2test//view binary logs, observe the start/end POS number for each command, and start/end time# Mysqlbinlog Mysql-bin.000001# at#160813 15:13:53ServerID 706 End _log_pos 217 CRC32 0x781f816eQuery thread_id= +Exec_time=0Error_code=0SET TIMESTAMP=1471072433/*!*/; SET @ @session. pseudo_thread_id=21/*!*/; SET @ @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=0, @ @session. Unique_checks=1, @ @session. autocommit= 1/*!*/; SET @ @session. sql_mode=1075838976/*!*/; SET @ @session. auto_increment_increment=1, @ @session. auto_increment_offset=1/*!*/;/*!\c UTF8 *//*!*/; SET @ @session. character_set_client=33,@@session.collation_connection=33,@ @session. collation_server=33/*!*/;SET @ @session. lc_time_names=0/*!*/;SET @ @session. collation_database=default/*!*/;CREATE DATABASE 2test/*!*/; #  at217#160813 15:14:11 ServerID 706 e Nd_log_pos 314 CRC32 0x0fde9820Query thread_id= +Exec_time=0Error_code=0SET TIMESTAMP=1471072451/*!*/;CREATE DATABASE Alvin/*!*/;//when you want to return to the state of which command, you can specify the start/end POS number recovery# mysqlbinlog--start-position= "--stop-position=" 217 "mysql-bin.000001 |mysql-u root-  P
//The log will also record this recovery statement, we can see the time of this command is  exactly the same as the original one# Mysqlbinlog Mysql-bin.000001# at 401#160813 15:13:53 Server ID 706 end_log_pos 498 CRC32 0x3a899ca5 Query thread_id=21 exec_t ime=619 Error_code=0set timestamp=1471072433/*!*/; CREATE DATABASE 2test /*!*/;

You can also use time to recover.
# mysqlbinlog–start-datetime= "2016-08-13 15:13:53" –stop-datetime= "2016-08-13 15:14:11" mysql-bin.000001 | Mysql–uroot–p

Error log
(1) mainly used to record the MySQL service open, close and error messages, if the service start is not successful the first thing should be to see this error log
(2) The error log is turned on by default and the error log cannot be turned off
(3) Setting error log: In the configuration file,log-error=dir/filename

# cat/etc/my.cnf |egrep "log-error" log-error =/data/mysqldb/log/mysql-error.log# ls/data/mysqldb/logmysql-error.log

(4) Delete error log

Mysqladmin-u root-p flush-logs# Lsmysql-error.log mysql-slow_query.log

General Query Log
(1) To record all actions of the user: start and close MySQL, UPDATE statements, query statements
(2) Enable and set general query log: In config file, log=dir/filename
(3) Delete the general query log:

# mysqladmin-u Root-p flush-logs
or # echo >access.log

Slow query log
(1) Execution statements that record execution time over a specified time
(2) Start and set the slow query log: In the configuration file

log-slow-queries=dir/filenamelong_query_time=n   // set time is n seconds, default is 10s

(3) Delete slow query log:

Mysql Log Management