Backing up MySQL with MySQLDump

Posted on March 22, 2011 by Anthony James Founder/CEOAnthony James Founder/CEO

Dump can be used for many different reasons. For one, you can use mysqldump to backup the data in your MySQL DataBase. Here is what mysqldump does.
MYSQLDUMP – Takes all the information in the selected database or table and dumps it into a
text file. After the information is in a text file you can dump it into any database you want.
Perform a dump in the following ways at your command line:
1.) Dump only one database into a text file
mysqldump -uroot -ppassword -hhost (if needed) mydb > txtfile.sql
2.) Dump all MySQL databases into a text file
mysqldump -uuser -ppassword -hhost —all-databases > txtfile.sql
3.) Dump multiple databases into a text file
mysqldump -uuser- ppassword -hhost —databases database1 database2 database3 > txtfile.sql
That command will take all the information in the database dump it into txtfile.dump
Now that you have your backup, let’s say you need to migrate your MySQL database to a new server. Copy the text file to the new server, install MySQL and perform the following command to import your database file.
mysql -uroot -ppassword -hhost < txtfile.sql
< signifies that it is going from txtfile.sql “piping” into the mysql server. The text file holds all of the create tables/databases that it needs to perform.
Now check your db and all the same tables and information in the tables will be back in the
MySQL server.
Thanks,
twitter: @anthonydjames

2 Comments

Image of Glenn Kelley
Glenn Kelley
8 years ago

http://www.hostmedic.com/admin/operating_systems/linux-operating_systems/mysql-dump-to-another-server/
Backups are always best kept on another machine. mysqldump directly to the remote host with this command:
mysqldump [mysqldump options] | gzip -c | ssh user@remotehost “cat > /path/to/some-file.sql.gz”
In short – this method pipes the mysqldump command through gzip, then to through and SSH connection.
SSH on the remote side runs the ‘cat’ command to read the stdin, then redirects that to the actual file where you want it saved.
Pretty nifty 🙂

Image of Anthony James Founder/CEO
Anthony James Founder/CEO
8 years ago

Thanks for the post Glenn !!

Leave a Reply

Your email address will not be published. Required fields are marked *