Well, the blog was down for almost 3 days. Reasons? My own mistake of deleting the database, without taking a backup. Stupidity of me. The reason was to move the blog from the current machine to another faster machine along with an upgrade of WordPress. After struggling for 3 days, getting the remote backup, looking at Google cache pages, I was finally able to get the blog working. Being a sysadmin, I should have taken care of backup, which I did not do.
Well the other good part was of the things I learnt, while trying to recover the database. Normally, when you configure MySql in Ubuntu/Debian, you are not provided with the option of creating binary logs. Binary logging will record in a binary file all SQL transactions executed and attempted on the server. By using the `mysqlbinlog` utility, the contents of the binary log file can be extracted so that the SQL statements may easily be rerun. To enable binary logging, add the following line to your server’s options file `my.cnf`.
log-bin = /var/log/mysql/mysql-bin.log
After making the entry above to your options file, you will need to restart the MySQL server for it to take effect.
Though I have personally not tested it out in a production server, I have tried using it on my laptop and what I notice from the log file is that it notes down each and every database transaction.
prasanta@ophiophagus:/tmp$ sudo mysqlbinlog /var/log/mysql/mysql-bin.000002 > /tmp/mysql.sql
This will create a mysqlsql text file which you can edit it manually.
I hope that this will help some person like myself in the future.
Leave a Reply