Just a reminder from the NYTimes cartoon department. Work toward the “Wow!”.
When you use SQL, how do you write delete statements at the database prompt?
A delete statement typically looks like this:
delete from table_name where column_name = 'foo';. I usually write it in this order:
delete where column_name = 'foo';
delete from table_name where column_name = 'foo';
Even though this is a pain because you have to move back and forth (I really need to look into vi keybindings for mysql), it prevents you from making sending this command by accident:
delete from table_name; which deletes all the data in your table. (Another alternative is to never use the interactive client and always write out your delete statements in a file and run that file to delete data.)
But, recently, I did exactly that, because I forgot. I deleted all the data from one table in our production database. It was billing data, so rather important. Luckily, I am using Amazon RDS and had set up backup retention.
I wanted to outline what I did to recover from this.
Unfortunately, it wasn’t clear how to restore a single table. I’m used to being able to download a .sql file and hand edit it, but that’s not an option. Stackoverflow wasn’t super helpful. But if there’s anytime you want clarity, it’s when you are restoring production data. You don’t want to compound the problem by screwing up something else.
So, here’s how to restore a single table from an Amazon RDS backup:
mysqldump -t --ssl-ca=./amazon-rds-ca-cert.pem -u user -ppassword -h has-data-from-tablename.c1m7x25w24qor.us-east-1.rds.amazonaws.com -P3306 database_name tablename > restore-table_name.sql;(
-tomits the create database/table statements.)
restore-table_name.sql;I was able to avoid this step.
mysql --ssl-ca=./amazon-rds-ca-cert.pem -u user -ppassword -h production.c1m7x25w24qor.us-east-1.rds.amazonaws.com -P3306 database_name < restore-table_name.sql;
Note this only works if you caught your mistake within the backup retention window. (Make sure you set that up.) We aren’t multi AZ or clustered, so I’m not sure how that would affect things.
Happy deep breathing!