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.