MySQL Log management (i)

The MySQL logs have the following six types:

1. Error logs: Server startup, shutdown, error messages generated during operation, and information generated when an event (event dispatch) is run, and information generated from server processes starting and shutting down from the server.

2. General Query log: General_log, General_log_file.

3. Slow query log: The query time exceeds the specified query time record

4. Binary logs: Any operations that cause or may cause data changes are logged into the binary log (DDL, DML, DCL statements), and the binary log is called a dozens of-point restore, which restores the data to the moment the server crashes.

5. Relay log: The relay log is the binary log on the primary server that is copied from the server, in the same format that it will be re-executed on the slave server according to the records of the intermediate logs for data backup

6. Transaction log: Only transaction logs are available for transaction-enabled storage engines (such as InnoDB), the transaction log guarantees the acid of the data, and the random I/O is converted to sequential I/O.

Mysql> show global variables like '%log% '; Querying for log-related variables

Error log file parameters:

Log_error:/mydata/data/localhost.localdomain.err (current hostname under Data directory +.err)

Log_warnings

General Query Log Parameters:

General_log

General_log_file:/mydata/data/localhost.log

Log_output={table|file|none} table log is fully logged into the tables, file logs all the files, none does not log

Man query Log parameters:

long_query_time=# more than Long_query_time queries are logged, default is 10s

Slow_query_log={on|off} Enable or disable logging slow query log, enable does not represent record

Slow_query_log_file=/path Slow query Log save directory

LOG_SLOW_QUERIES={YES|NO} Slow query log is enabled, enable does not represent record

>set global slow_query_log=1; enable slow query logging

Binary log format:

1. Statement-based: statement

2. Row-based: row

3. Mixing mode: Mixed

A binary log event typically records two items:

1. Record the time the event was generated (StartTime)

2. The relative position of this event throughout the event

Let the binary log file not and data on the same disk, when the data is lost or crashed, you can use the backup + binary log to restore the data to the moment the data crashed

[Email protected] ~]# cd/mydata/data/

[[email protected] data]# ls

master-bin.000002 master-bin.000006 master-bin.000010

master-bin.000003 master-bin.000007 master-bin.000011

master-bin.000004 master-bin.000008 master-bin.000012

master-bin.000005 master-bin.000009 master-bin.000013

Mysql> Show master status; View which log file is currently in use

+-------------------+----------+--------------+------------------+-------------------+

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

+-------------------+----------+--------------+------------------+-------------------+

|    master-bin.000021 |         673 | |

+-------------------+----------+--------------+------------------+-------------------+

1 row in Set (0.14 sec)

MASTER-BIN.00002 is currently in use

The position of the event relative to the entire event is 673

Mysql> show Binlog events in ' master-bin.000021 '; viewing binary log files

+-------------------+-----+-------------+-----------+-------------+-------------------------------------------- ---------------------------------------------------------------------+

| Log_name | Pos | Event_type | server_id | End_log_pos | Info |

+-------------------+-----+-------------+-----------+-------------+-------------------------------------------- ---------------------------------------------------------------------+

|   master-bin.000021 | 4 |         Format_desc |          1 | 120 | Server Ver:5.6.34-log, Binlog ver:4

| master-bin.000021 | 120 |         Query |         1 | 207 | BEGIN |

| master-bin.000021 | 207 |         Query |         1 | 344 | Use ' students '; Insert into test1 (CID,NAME,SID) VALUES (4, ' faded ', ' D ')

| master-bin.000021 | 344 |         Xid |        1 | 375 | COMMIT/* xid=40 */|

| master-bin.000021 | 375 |         Query |         1 | 549 | Use ' MySQL '; CREATE USER ' faker ' @ ' 127.0.0.1 ' identified by PASSWORD ' *23ae809ddacaf96af0fd78ed04b6a265e05aa257 ' |

| master-bin.000021 | 549 |         Query |         1 | 673 | Use ' MySQL '; Rename user [email protected] ' 127.0.0.1 ' to [email protected] ' 127.0.0.1 '

+-------------------+-----+-------------+-----------+-------------+-------------------------------------------- ---------------------------------------------------------------------+

6 rows in Set (0.02 sec)

The starting position of the Pos event

Event_type Event Type

SERVER_ID Server ID Number

End_log_pos Event End Location

Info file header and what to do

Mysql> show Binlog events in ' master-bin.000021 ' from 344; From 304 Start location view

+-------------------+-----+------------+-----------+-------------+--------------------------------------------- --------------------------------------------------------------------+

| Log_name | Pos | Event_type | server_id | End_log_pos | Info |

+-------------------+-----+------------+-----------+-------------+--------------------------------------------- --------------------------------------------------------------------+

| master-bin.000021 | 344 |         Xid |         1 | 375 | COMMIT/* xid=40 */|

| master-bin.000021 | 375 |         Query |         1 | 549 | Use ' MySQL '; CREATE USER ' faker ' @ ' 127.0.0.1 ' identified by PASSWORD ' *23ae809ddacaf96af0fd78ed04b6a265e05aa257 ' |

| master-bin.000021 | 549 |         Query |         1 | 673 | Use ' MySQL ';                                                   Rename user [email protected] ' 127.0.0.1 ' to [email protected] ' 127.0.0.1 ' |

+-------------------+-----+------------+-----------+-------------+--------------------------------------------- --------------------------------------------------------------------+

3 Rows in Set (0.00 sec)

In addition, you can use the Mysqlbinlog command to view the log files (binary format binary files cannot be viewed with cat)

Mysqlbinlog

--start-datetime

--stop-datetime

--start-position

--stop-position

[Email protected] data]# Mysqlbinlog master-bin.000021

[Email protected] data]# mysqlbinlog--start-position=207--stop-position=375 master-bin.000021

[Email protected] data]# mysqlbinlog--start-datetime= ' 17-02-13 9:06:21 ' master-bin.000021

You can also import it into a file, and then import the file into another database, and you can re-execute the binary log by

[Email protected] data]# mysqlbinlog--start-datetime= ' 17-02-13 9:06:21 ' master-bin.000021 >/root/my.sql

mysql> flush logs; Log scrolling by hand

Mysql> Show master status;

+-------------------+----------+--------------+------------------+-------------------+

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

+-------------------+----------+--------------+------------------+-------------------+

|      master-bin.000022 |              120 |                  |                   | |

+-------------------+----------+--------------+------------------+-------------------+

1 row in Set (0.00 sec)

If I have already backed up data from a moment ago, I should delete the binary log before that moment, thus saving space

Mysql> purge binary logs to ' master-bin.0000010 '; Delete all logs before ' master-bin.0000010 '

Mysql> Show master status;

+-------------------+----------+--------------+------------------+-------------------+

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

+-------------------+----------+--------------+------------------+-------------------+

|      master-bin.000022 |              120 |                  |                   | |

+-------------------+----------+--------------+------------------+-------------------+

1 row in Set (0.00 sec)

Mysql> show binary logs; View the currently owned binary logs

+-------------------+-----------+

| Log_name | File_size |

+-------------------+-----------+

|     master-bin.000001 | 69435 |

|   master-bin.000002 | 1371047 |

|       master-bin.000003 | 143 |

|       master-bin.000004 | 143 |

|       master-bin.000005 | 120 |

|       master-bin.000006 | 143 |

|   master-bin.000007 | 4836454 |

|     master-bin.000008 | 21561 |

|       master-bin.000009 | 143 |

|       master-bin.000010 | 143 |

|       master-bin.000011 | 120 |

|       master-bin.000012 | 120 |

|       master-bin.000013 | 120 |

|       master-bin.000014 | 143 |

|       master-bin.000015 | 120 |

|       master-bin.000016 | 143 |

|       master-bin.000017 | 120 |

|       master-bin.000018 | 818 |

|      master-bin.000019 | 2220 |

|      master-bin.000020 | 4692 |

|       master-bin.000021 | 721 |

|       master-bin.000022 | 120 |

+-------------------+-----------+

This article is from the "11097124" blog, please be sure to keep this source http://11107124.blog.51cto.com/11097124/1897457

MySQL Log management (i)