MySQL Log management

Log type:

First, error log

1. Record the information in the server startup and shutdown process

2. Error messages during server operation

3. Information generated when the event Scheduler runs an event

4. Information generated when booting from the server on a server

To view the storage path for the error log:

Show variables like '%err% ';

Viewing warning messages

Show variables like ' log% ';

If log_warnings = 1, the warning message is also written to the error log, 0, and the description is not written to the error log

My.ini configuration information:

#log_error = Xx/mysql_log_err.txt

Second, the general inquiry log

General_log = on: Enabled

General_log_file = Specifies the path of the query log

Three, slow query log

If the value of Slow_query_log is on, the slow query log is turned on, otherwise it is not turned on

Four or two binary logs: any operations that cause or may cause data changes are stored

Function: 1. Do instant point recovery (based on point-in-time recovery)

2. Replication (copy any changes that occur on the master server to ensure consistent data from the server)

To see if binary logging is turned on:

Show variables like '%bin% ';

If the value of Log_bin is on, the binary log is turned on, otherwise it is not turned on

To see which binary logs are available:

Show binary logs;

To view the binary log files that are in use:

Show master status;

To view information for binary log file records:

Show Binlog events in ' mysql-bin.00001 ';

Binary logs in three formats:

1. Statement-based statement, records are SQL statements

2. Row-based row, which records the results of the SQL statement execution

3. Mixed mixed

To view the binary log Ask price content command: Mysqlbinlog

#mysqlbinlog

--start-position Start position

--stop-position End Position

--start-datetim Start time

--stop-datetim End Time

Example: # Mysqlbinlog--start-position=107 mysql-bin.00002
Example: # mysqlbinlog--start-position=107--stop-position=300 mysql-bin.00002
Example: # mysqlbinlog--start-datetime= ' 2014-12-10 21:00:00 '--stop-datetime= ' 2014-12-10 21:10:00 ' mysql-bin.00002

Every time you restart the server, the log will scroll

#flush logs implement log scrolling, only binary logs, other logs do not scroll

#purge binary logs to ' mysql-bin.00002 ' delete binary log files

V. Relay log

Events that are copied from the primary server and saved as log files

Relay-log = Specifies the location of the trunk log good name

Relay-log-index = Specifies the location and name of the index file for the trunk log name

VI. transaction Logs

Transactional storage engine to ensure atomicity, consistency, isolation, and durability

Innodb_flush_log_at_trx_commit:

0 Synchronize and perform disk flush operations per second

1 synchronize per transaction and perform a disk flush operation

2 synchronize per transaction, but do not perform disk flush operations

This article is from the "ngames" blog, make sure to keep this source http://ngames.blog.51cto.com/3187187/1588515

MySQL Log management