Backup and Restore MySQL Databases and Tables

Anytime I need to move data or alter our database I get a little bit nervous that something is going to go wrong and crash the database losing all of our data, dropping tables, causing chaos, and so many phone calls. Our database automatically backs up every so often but right before any major changes I make a quick local copy of the data. This came in handy just the other day when a table was inadvertently dropped and I had a backup on hand. Here's how to do it using console commands.

Making a Backup of a Database

mysqldump -u [username] -p --host=[hostname] [database_name] > [output_name].sql

Restoring a Database from a Backup

Make sure the database is created before restoring.

mysql -u [username] -p --host=[hostname] [database_name] < [output_name].sql

Making a Backup of a Table in a Database

mysqldump -u [username] -p --host=[hostname] [database_name] [table_name] > [table_name].sql

Restoring a Table from a Backup

Make sure the table has been created already and MySQL will know where to put the data.

mysql -u [username] -p --host=[hostname] [database_name] < [table_name].sql

MySQL error 1449: The user specified as a definer does not exist

To fix this problem use the following command when restoring your database.

mysqldump --single-transaction -h [hostname] -u [username] -p [database_name] > [output_name].sql

If you rename columns you will get some weird results so make sure that the tables are the same before and after the backup. Let me know about experiences or tips you have with backing up and restoring MySQL databases.