Some days back, I encountered a server where it happens that some data was altered in the database. A quick report can be generated with the mysqlbinlog command.
The MySQL bin logs contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes:
- For replication, the binary log on a master replication server provides a record of the data changes to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master. See Section 18.2, “Replication Implementation”.
- Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup. See Section 8.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.
It is to be noted that by enabling My SQL bin logs, servers will tend to react more slowly though the benefits are really useful. My SQL bin logs should not be deleted straight forward with a rm -f command but rather with the command PURGE BINARY LOGS TO ‘mysql-bin.111’;
In this article i will demonstrate some command to strip out interesting information from a Binary log file. Logs that can alter information in a database are classified with the following database requests such as UPDATE, DELETE, INSERT, DELETE, REPLACE and ALTER.
1. If you want to read the whole content of a binlog, use the following command. This will comprise of all request made including selects statement.
mysqlbinlog binlog.1111
2.Let’s say you have a list of binlogs and you want to find all the ALTER carried out only for a specific database called “question”
mysqlbinlog binlog.* | grep -i -e "^alter" | grep -i -e "question" >> /tmp/alter_question.txt
3. Let’s say you want to find the date for 03/05/2016 of all alter commands carried out from the file generated from part 1
grep -i -A 3 '#160503' /tmp/alter_question.txt | less
4. If you want to extract all ALTER from a bunch of binlogs for a specific database (question)
mysqlbinlog --database=question binlog.* | grep -B 5 -i -e "^alter" >> /tmp/alter_question.txt
5. You might also want to retrieve information from a specific date and time.
mysqlbinlog --start-datetime="2016-2-02 5:00:00"--stop-datetime="2016-03-03 8:10:00" mysql-bin.000007
However, analysis of the Mysqlbin logs are pretty vast. It depends what are the information that is being needed. I also find out that the mk-query-digest is also an interesting tool to extract information and perform analysis. SEE http://linux.die.net/man/1/mk-query-digest
