MySQL Log parsing

MySQL has 4 different logs, namely, binary log, query log, slow query log and error log, these diaries record all aspects of database work, can help us understand the different aspects of the database traces, the following first describes the role of binary logs and the use of methods, And the use of binary logs to the database for various maintenance and optimization, other logs will be in the following will be detailed introduction.

Binary log (Bin-log log)
In the previous blog about MySQL master-slave configuration, has mentioned the role of Bin-log log and use, Bin-log log records all the DDL and DML statements, but does not include the query statement, the statement is saved as an event, describes the data change process, This log plays a critical role in recovering data in the event of a disaster.

Open
MySQL default is no development bin-log log, first we need to open Bin-log log, modify in MY.CNF



The path of the Bin-log log is specified, myssqladmin flush log is required to open the log, and after reboot we find that the log file has been added to the path we just set, which is the binary log we need



Since the log is stored in binary mode and cannot be read directly, it needs to be viewed using MySQL's own Mysqlbinlog tool.
The syntax is as follows:

#mysqlbinlog mysql-bin.000002

Now we're trying to insert data into the Test1 table



Then use the Mysqlbinlog tool for log viewing

#mysqlbinlog mysql-bin.000002-d Test

Clean up

If a large number of binary logs are generated on a daily basis, and these logs are not cleaned up for a long time, it can be a waste of disk space, so regular cleanup of logs is an important task for DBAs to maintain MySQL

1)RESET MASTER
In the folder above to view the log, the binary log named format is mysql-bin.*,* for the log sequence number, the sequence number is incremented, and Mysql-bin.index is the index file of the log, recording the maximum sequence number of the log
We perform a reset master name to delete all logs



View post-deletion logs



As you can see, the previous logs are all emptied, and the new log starts at 00001.

2)PURGE MASTER LOGS to&PURGE MASTER LOGS before
Executes the purge MASTER LOGS to ' mysql-bin.****** ' command, which deletes all logs before the ' ****** ' number
Execute purge MASTER LOGS before ' yyyy-mm-dd hh:mm:ss ' command to delete all logs before ' Yyyy-mm-dd Hh:mm:ss ' time

3)-expire_logs_days
This parameter is to set the number of days that the log expires, and the expired logs will be automatically deleted, which helps reduce the amount of work we need to manage the logs and modify the MY.CNF



Here we set the Save log to 3 days, 3 days after the expiration of the log will be automatically deleted

Recovery

Bin-log is a record of all MySQL events, when MySQL has a catastrophic error, you can do full recovery through bin-log, point-in-time recovery, and location-based recovery

Full Recovery, suppose we use mysqldump to back up the database 2 o'clock in the morning every day, but at 9 o ' Day the next morning, when the database fails, the data is inaccessible, the data needs to be recovered, and the files backed up yesterday morning are restored to the 2 o'clock in the morning state. Binlog since mysqldump backup was restored with Mysqlbinlog
mysql localhost mysql-bin.000001 | Mysql-uroot-p
This allows the database to be fully restored to its full state before the crash.

Point-in-time recovery, due to misoperation, such as the deletion of a table, the use of the above said full recovery is no use, because the log there is also a false operation of the statement, we need to restore to the state before the wrong operation, and then skip the wrong operation of the statement, and then restore the statement after operation, assuming we delete a table error operation occurred in 10 : 00 at this point in time, we can use the following statement to restore data to a failure with backup and Binlog

Mysqlbinlog--stop-date= ' 2010-09-04 9:59:59 '/var/log/mysql-bin.000001 | Mysql-uroot-p

Then skip the time of the misoperation and proceed to the following Binlog

Mysqlbinlog--start-date= ' 2010-09-04 10:01:00 '/var/log/mysql-bin.000001 | Mysql-uroot-p

One of the--stop-date= ' 2010-09-04 9:59:59 ' and--start-date= ' 2010-09-04 10:01:00 ' which is the time you mistakenly operate, of course, this time you need your own calculations, And this time point can also involve not only the misoperation, but also can have the correct operation has been skipped.

based on location recovery , as mentioned above, using a point-in-time recovery may occur, there may be misoperation and other correct actions in one point of time, so we need a more accurate way of recovery
Using Mysqlbinlog to view the binary, you can see



Where drop tables Test1 This misoperation end_log_pos is 8879917, a few below this ID, the ID of the operation before and after it is 8879916,8879918
We will perform a location recovery operation

Mysqlbinlog--stop-position= ' 8879916 '/var/log/mysql-bin.000001 | Mysql-uroot-p

Mysqlbinlog--start-position= ' 8879918 '/var/log/mysql-bin.000001 | Mysql-uroot-p

The first line is to revert to the stop position so the transaction, the second is to recover everything from the given starting position knowing that the binary logs end.

Master-slave replication
MySQL replication refers to the primary database of DDL and DML operations through the binary log to the server, and then re-execute the logs from the server to do the operation, so that the data from the server and the primary server to maintain the synchronization, through the binary log for master-slave replication can see the previous article "MySQL Master-slave Replication Configuration "

MySQL Log parsing