Nov 18, 2012
Sometimes you get more done by doing nothing at all. It's pretty rare, so you always want to do something rather than nothing. But some nights just don't go the way you wanted. Tonight I woke up at 1am wanting to do a little bit of hacking on my website. Everything was going alright. I only made 5 updates to the website.
update comments set moderation=5 where id = 1507606; update comments set moderation = 0 where moderation <= -1 and (content not like '%http:%' and posted_by not like '%http:%' and subject not like '%http:%' and content not like '%https:%' and posted_by not like '%https:%' and subject not like '%https:%'); update comments set moderation=5 where subject = 'qdoiyQEAaWd'; update comments set moderation=5 where subject = 'ZNzbBCfWReAQVApO'; update comments set moderation=5 where subject = 'CxBCrAoBcAnBXxzvJ';
It was then that I discovered that I had lost useful data before a certain date. That's alright, it was intentional but I want the data back if I have it. It turns out that I have several backups of the data I lost. So I decided to create a database and import the data and do some easy database import if the data is just there.
mysql -u root -p mysql> create database a20120127; zcat /home/jvoss/recent/altscip/altsci_mysql_20120127.sql.gz |mysql -u root -p a20120127
So what is wrong with that choice? When you pipe to mysql, you assume the format of the file. If you use mysqldump on a single database, the file format does this:
DROP TABLE IF EXISTS `account`; CREATE TABLE ...
But if you are backing up your entire database it does this:
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `altsci`; USE `altsci`; DROP TABLE IF EXISTS `account`; CREATE TABLE ...
Yup, my website was reverted back to Jan 27, 2012. The front page contained Explanation Needed. Far worse, I lost data from projects I had been working on in this database. Luckily my really important data was doubly backed up on Friday and other difficult to reproduce data was in a different server and database. I don't do daily backups. Even though it would be easy. Even though I tell people it is easy enough to do it even if your data don't matter very much. So now I do. Daily backups from now on. How did I get my data back? Well, it was a dirty hack. First I only had a backup of data that changes often from Friday, so that was first:
zcat /home/dmitry/dmitry_mysql_altsci_2012-11-17a.sql.gz |mysql -u root -p altsci
Then I checked a few of my very dear websites: AltSci Brasil, Philisophical Transactions, and J4va. Brasil required a backup from Oct 9 and a server restart for some reason. PhilTrans required a backup from Oct 9 and then a column that was missing needed to be added and populated with valid data. I have no explanation why that occurred. J4va needed a backup from Oct 9. When I visited J4va, I noticed some spam which I cleaned up.
This blog post started talking about getting more done by doing nothing at all. But that isn't entirely true here. I lost valuable time tonight by making one mistake. But this is something I should definitely understand by now. mysqldump has two formats: one that allows you to import into a new db and another that overwrites your data and costs you 2-∞ hours to replace. Also, backup daily. Make a simple cron task that does something like:
mysqldump -u root -p --all-databases --defaults-file=/home/jvoss/.mysql |gzip >/home/jvoss/recent/altscip/dmitry_mysqldump_$(date +%Y%m%d).gz
There are so many ways to screw up. Run out of disk space, hard drive failure, overwrite, dictionary password and open port 3306, replication conflict, SQLi, XSS, or run out of time developing a usable website. Let's write good websites that last forever folks. They don't have to be perfect on first release, but over time you have the resources to make a pretty stable system.