mysqldump – Backing up your database
Connecting to a different server
This is all great if you are running your commands on the server that holds your databases. But that’s not likely. The most useful scenario is going to have you connecting to the database server to copy the data from the server to the computer you’re running the script on. mysqldump makes this easy with a series of options.
The command above will connect to the MySQL server at Hostname using the username Username and password Password. Notice that there is not a space between the -h and Hostname, the -u and Username, or the -p and Password.
You can also use -h=Hostname, -u=Username, and -p=Password instead.
One caveat is that most database servers don’t by default grant permissions for your username to be able to login from other machines. If you’re not sure how to get the permissions set right check with your host.
Backing up everything
But what if you don’t want to write out a script for each and every database? You have two options.
The command above will cause all the databases that you have permission to view to be dumped.
Be careful with this as you may wind up with a huge backup file. The server that this site is hosted on has about 3 dozen databases totaling several hundred megabytes of data which would make for a single unwieldy file. And if you have to restore a single database from a single file containing many separate databases you will have to edit the large file copying and pasting what you need to a new file.
Dumping as XML
The above command will dump the database in XML format rather than the default SQL. I’ve never used this so I can’t really comment, but it does seem that it could be useful in the right situation.
Anything else?
What I’ve written is just the tip of what you can do with mysqldump. The MySQL manual has a page for mysqldump that lists all of the options.
If you want to send a few specific databases to the dump file you can use the –databases option followed by the list of databases you want to dump. The same warnings from –all-databases apply here too.
Pages: 1 2
Question, Comments...
Do you have more questions. Please either leave a comment below or join us in our new forum.