![\"material-icon-1307676_640\"](\"http:\/\/www.mooreds.com\/wordpress\/wp-content\/uploads\/2016\/09\/material-icon-1307676_640-300x300.png\"){kind=icon}
When you use SQL, how do you write delete statements at the database prompt?<\/p>\nWhen you use SQL, how do you write delete statements at the database prompt?<\/p>\n
A delete statement typically looks like this: Even though this is a pain because you have to move back and forth (I really need to look into vi keybindings for mysql<\/a>), it prevents you from making sending this command by accident: But, recently, I did exactly that, because I forgot.\u00a0 I deleted all the data from one table in our production database.\u00a0 It was billing data, so rather important.\u00a0 Luckily, I am using Amazon RDS and had set up backup retention.<\/p>\n I wanted to outline what I did to recover from this.<\/p>\n Unfortunately, it wasn’t clear how to restore a single table.\u00a0 I’m used to being able to download a .sql file and hand edit it, but that’s not an option.\u00a0 Stackoverflow wasn’t super helpful<\/a>. \u00a0 But if there’s anytime you want clarity, it’s when you are restoring production data.\u00a0 You don’t want to compound the problem by screwing up something else.<\/p>\n So, here’s how to restore a single table from an Amazon RDS backup:<\/p>\ndelete from table_name where column_name = 'foo';<\/code>. I usually write it in this order:<\/p>\n\n
delete<\/code><\/li>\ndelete where column_name = 'foo';<\/code><\/li>\ndelete from table_name where column_name = 'foo';<\/code><\/li>\n<\/ol>\ndelete from table_name;<\/code> which deletes all the data in your table.\u00a0 (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.)<\/p>\n\n
\n
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;<\/code> (-t<\/code> omits the create database\/table statements.)<\/li>\nrestore-table_name.sql;<\/code>\u00a0I was able to avoid this step.<\/li>\nmysql --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;<\/code><\/li>\n