Loading ...
Backup your MySQL databases manually with mysqldump
I often need to make a quick backup of one mysql database. This happens right before there will be some major changes to the database, or before performing an upgrade on the mysql server software. Now I don’t recommend this to be done regularly, but still, I use this extensively in this kind of situations… If you are looking for an automated solution to backup your databases you might want to check my other post: “Backup your MySQL databases automatically”
MySQL provides us the tool required to do this: mysqldump. As the name implies, this can make a dump of one database or even to all the databases on the server. You can see all the options on its help manual or on the mysql site.
Basically this is used like:
mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] –databases [OPTIONS] DB1 [DB2 DB3...]
mysqldump [OPTIONS] –all-databases [OPTIONS]based on the fact that you need to backup one database, more than one, or all the databases. All the available options are well documented, so let me show you just a simple example how to backup and restore a single database. Let’s say that the database is called ‘wordpress‘ ;).
To backup this database and save it in a file we run:
mysqldump wordpress > wordpress.sqlI am not using any other options; just create a dump of the database.
Now let’s assume that we need to restore this backup. How do we do this? Well as simple as this:
mysql wordpress < wordpress.sqlThis will run all the backup file against the existing database as a set of mysql commands (that is what the backup file really is).
Note: this will delete all the existing data in the tables of this database and replace them with the data from the saved file.
If you want to save all the databases existing on the server:
mysqldump -A > all_dbs.sqlNow, in the example from above I have assumed that you are able to connect directly to the mysql server, without the need to enter any other parameters (if you have configured properly your .my.cnf, or if the details are the default ones: localhost, root, no password - not recommended… you should have a password!). If this is not the case, add to the command line the usual mysql parameters (the ones that are needed of course):
-h hostname -u username -p passwordand the command will look like this:
mysqldump -h hostname -u username -p wordpress > wordpress.sqlThis covers the basic usage of saving and restoring one mysql database with mysqldump. Let me know if you are using mysqldump in a special way and if you want to share this with other peoples.
Share This
29th May 2006, 12:32
One neat thing you can do using this, is setup a remote replication or off server backup, by using SSH.
If you used a stored key ( ./.ssh/authorized_keys2 ) and ssh, you can pipe your db dump to a remote server and either 1) pump the output to a file for later restore, or 2) pipe the output into mysql to instantly restore the database on a remote server.
It’s essentially a poor mans’s Disaster Recovery model - and in a hurry I’ve used it on some large installs for short to medium term ( ok, so some have been in place for years, but only where budgets don’t allow time to do it better ) remote replication / fail over / DR / BCP solutoin.
Cheers,
Dez
—
—
Dez Blanchfield
http://WebSearch.COM.AU
http://TheStorageForum.COM
http://www.Blanchfield.COM.AU
http://CradleTechnologies.COM
25th August 2006, 19:01
[...] I’ve changed web hosts, because GoDaddy don’t allow SSH connections, (Bluehost to the resuce!), but then found I needed to provide photo-ID to Bluehost before they would enable SSH on my hosting account. I’ve been cursing Firefox on Ubuntu for making it so difficult to install a simple plug-in. I’ve been cursing Ubuntu generally, because after installing phpmyadmin on my localhost, it point-blank refused to open. As you can see, it worked! It got to the point where I had visions of not being able to import any posts and categories and being forced to start over. Then I found this article, and figured that I use could the SQL script created by the mysqldump command and then cut and paste that script into the mySQL query window at Bluehost. [...]