Mysql log file usage and data recovery

Mysql log file usage and data recovery _ MySQL

Last Update:2018-04-13 Source: Internet

Author: User

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

Mysql log file usage and data recovery bitsCN.com

Use and data recovery of mysql log files

I. mysql log type

MYSQL has different types of log files (each storing different types of logs), from which you can query what MYSQL has done and manage MYSQL, these log files are indispensable.

1. The error log records The error information during database startup, operation, and stop;

2. ISAM operation log (The isam log): records all changes to The ISAM table. This log is only used to debug The ISAM mode;

3. SQL execution log (The query log): records The client connection and The executed SQL statements;

4. update log: The statement that records data changes. it is not recommended to use this log instead of binary logs;

5. binary log: records all statements for modifying database data;

6. The time-out log (The slow log): records all statements whose execution time exceeds The maximum SQL execution time (long_query_time) or where no index is used;

7. relay log: If you are using mysql's copy and backup functions, the slave server also provides a log file called relay log.

By default, all log files are recorded in the MYSQL data directory. you can force mysql to close and re-open a file to record the logs. of course, the system will automatically add a suffix (such. 00001 ,. 00002). you can run the mysql> flush logs statement in the mysql environment or run the mysqladmin management program # mysqladmin flush-logs or # mysqladmin refresh.

II. mysql log configuration

The startup method of these logs can be followed by the option parameters when the mysqld_safe method is used to start the database, or configured in the configuration file. The second method is recommended. the configuration method is very simple, I only configured three types of logs:

[Mysqld]

Log =/var/log/mysqld_common.log

Log-error =/var/log/mysqld_err.log

Log-bin =/var/log/mysqld_bin.bin

III. View mysql logs

Log viewing is very simple. most of the logs are text. you can directly view the logs using tools such as vim, less, and more. it is worth noting that you can view the binary files:

1. first determine whether the binary file record function is enabled

SQL code

Mysql> show variables like 'log _ bin ';

2. if you want to know the details of the file that records binary data, you can use the following statement to see which file is being recorded and the current location of the record:

SQL code

Mysql> show master status;

3. you need to use the program mysqlbinlog to view the binary data and use it as needed.

SQL code

Mysqlbinlog/var/log/mysql/mysql-bin.000040;

4. export the binary log file to the txt text file for viewing.

SQL code

Mysql> mysqlbinlog/var/log/mysql/mysql-bin.000040>/var/log/mysql/41540.txt;

5. if you query a time range, you can execute the following statements. if there are many records, you can direct the results to a file and read them :-):

SQL code

Mysql> mysqlbinlog -- start-datetime = '2017-01-01 00:00:00 '-- stop-datetime = '2017-08-08 00:00:00'/var/log/mysql/mysql-bin.000040>. /tmp. log

4. use binary logs to restore data

For the use of the mysqlbinlog tool, you can refer to the MySQL help manual, which provides detailed use. In this example, the focus is on the use of the -- start-position parameter and -- stop-position parameter.

-- Start-position = N: read from the event where the position in the binary log is equal to N.

-- Stop-position = N: stop reading from an event where the position in the binary log is equal to or greater than N.

1. create an experiment environment

In a test database, create a table, add records, and generate a log file.

SQL code

Mysql> create table test (id int auto_increment not null primary key, val int, data varchar (20 ));

Mysql> insert into test (val, data) values (10, 'Shanghai ');