To execute mysqlbinlog in your terminal:
mysqbinlog [options] logfilelike:
mysqlbinlog /var/lib/mysql/localhost-bin.000005This command will then display the content of the binary file localhost-bin.000005. The content of this file is:
This 1st line which is "# at 430" means that the starting position of the event in the binary log file is 430. On the second line, the number's with colon is the timestamp of the event (this is when the event happen). The "server id 1" state that the event happen on server id number 1. "end_log_pos 537" indicates that the next event starts at 537. "thread_id=2" says that the thread that execute the event is id 2. "exec_time=0" means the time spent to execute the event. Lastly, the last line is the event executed by the server. As you can is it is an update.# at 430#111110 21:55:34 server id 1 end_log_pos 537 Query thread_id=2 exec_time=0 error_code=0SET TIMESTAMP=1320933334/*!*/;update Company set name='hello' where id = 1
For your server to log event as a binary file, you must first enable it. To enable, you must edit the my.cnf and append log-bin=path.
The normal formal of binary file logs is host_name-bin.###### and store in /var/bin/mysql directory.
Mysqbinlog I commonly use:
mysqbinlog /var/lib/mysql/localhost-bin.000005 > ~/mysql5.sqlThis will put all the content of the logbin to the file mysql5.sql.
mysqbinlog --start-datetime="2011-11-11 11:11:11" /var/lib/mysql/localhost-bin.000005This will only display event that is log on and after 2011-11-11 11:11:11.
mysqbinlog --stop-datetime="2011-11-12 11:11:11" /var/lib/mysql/localhost-bin.000005This will only display event that is log before 2011-11-12 11:11:11.
mysqbinlog --database=liims2 /var/lib/mysql/localhost-bin.000005
This will only display event that is invoke on database liims2.
Hope this will help you on using mysqlbinlog utility.