So despite my regular back-ups, one of the regular power cuts happened exactly during the auto-update of my RSS feeds and.. corrupted the database to the point where the mysql service wouldn’t start. And with the autocopy of the filesystem every 15 minutes, all of that got backed up, rendering any recovery impossible. I ended up wiping the /usr/local/mysql/data directory and creating everything anew, losing all my data in the process.
Once I got up and running again, what to do to prevent this from happening? Extra backup! I added 2 cron jobs calling scripts, one to back-up specifically the /usr/local/mysql/data directory as a .tar under /opt/backups/ on a weekly basis, one to run a mysqldump .sql export of all the databases on a daily basis, stopping the apache service beforehand (and restarting it upon completion) to reduce the risk of data corruption.. AND rotating the .tar and .sql backups up to 7 iterations. Next time, I should (?) be able to get a clean copy of my data, minimizing any loss. We’ll see at the next power cut!
Syntax of the mysqldump command (note the lack of space between -p and the actual password value):
sudo mysqldump -u username -ppassword–lock-all-tables –all-databases