Backing up MySQL with MySQLDump

ump 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 thoughts on “Backing up MySQL with MySQLDump

  1. 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 🙂

Leave a Reply

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