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.
mysqldump -u [username] -p --host=[hostname] [database_name] > [output_name].sql
mysql -u [username] -p --host=[hostname] [database_name] < [output_name].sql
mysqldump -u [username] -p --host=[hostname] [database_name] [table_name] > [table_name].sql
mysql -u [username] -p --host=[hostname] [database_name] < [table_name].sql
mysqldump --single-transaction -h [hostname] -u [username] -p [database_name] > [output_name].sqlIf 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.