MySQL replication has a wide range of uses, including debugging applications writing to the DB being replicated. Once set up, all statements “that update data or potentially could have updated it” are stored in one or more binary logs (MySQL manual on the binary log). So if something happens to your data and your application level logs aren’t cutting it, you can use the binary logs of the underlying DB as a source of information.
Assuming you have binary logging turned on, you first need to know where the binary logs are. The query “show binary logs;” will give you the filenames and sizes, but not the path. If you only set “log-bin” in my.cnf then the binary logs are placed in your data directory by default (find this by running “show variables like ‘datadir’;”). Otherwise they will be wherever you specified with “log-bin = /some/path”. If you passed the value via a command line option, run “ps aux | grep mysql” to view the options passed. If all else fails, run “sudo updatedb && locate mysqld-bin”.
Now how to view these logs? There is in fact a handy utility for just this purpose: mysqlbinlog. Just pass it the name of the log file and it returns the text version. So if you only had a single search you could use:
1 | sudo mysqlbinlog /var/lib/mysql/mysqld-bin.000001 | grep foobar |
If you had a number of searches you could redirect to a file for processing at your own pace:
1 | sudo mysqlbinlog /var/lib/mysql/mysqld-bin.000001 > mysqld-bin.000001_plain.txt |
This gives you the ability to search across all of the statements affecting your DB, as well as across all of the data coming in.
No related posts.
Related posts brought to you by Yet Another Related Posts Plugin.
