Backup and Restore MySQL DBs via SSH

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

About this post

Posted: 2011-04-02
By: FortyPoundHead
Viewed: 1,488 times

Comments

No comments have been added for this post.

You must be logged in to make a comment.