Lets say your web host does not provide backups for your MySQL database(s). Or perhaps you are changing hosts, and you need retrieve the database. Follow these steps to grab a backup, easily.
Here’s how to export mysql DB to SQL file using at the command line, after telnetting in via SSH. Of course, I had to do it command line style!
mysqldump -user=username -password=1234 -databases your_database -opt -quote-names -complete-insert > example.sql
or you can use this command:
$ mysql -u username -p dbname > database.sql
If the MySQL programs are not in your path, you will need to manually specify the location of the mysqldump program:
$ /usr/local/mysql/bin/mysqldump -u username -p --opt dbname > database.sql
Download the database.sql and keep it in a safe place (CDR, Zip disk, etc). If you need to restore your database, you can do so like this:
$ mysql -u username -p databasename < database.sql