PeterMySQL is one of the most popular relational database management systems out there and competes along with the other big database packages. Along with the server part of MySQL, there are also other tools included on the client side.
One of these tools is mysqldump, which, rather obviously, is used for making backups of one or more databases and for exporting them out to a file. In this reference post, I'm going to run through the main different ways you can use mysqldump. You might want to bookmark this post if you're planning any work with MySQL soon, as it is designed to be an easy resource to come back to and give you a refresher.
Without any further ado, let's get straight to it.
Logging in to use mysqldump
I'm putting this in first as it probably will affect most things you're doing with mysqldump (if you have a secure server setup anyway). If you need certain permissions to do a MySQL dump, you'll need to provide the username and password.
Working interactively
If you're working interactively - i.e. you are sitting at a terminal typing stuff in, you'll probably want to use this option:
$ mysqldump -u username -p [OTHER COMMANDS]
What this will do is ask you for the password immediately after you press enter to execute the command. You'll type the password in just like if you were using su or sudo.
Working non-interactively (from a script)
If you want to do dumps in a script (very useful for automated backups), this approach for supplying your authentication won't work. Instead, use this format:
$ mysqldump -u username -ppassword [OTHER COMMANDS]
That means you put the password straight after the -p, with no space. Obviously, if you choose this option, make sure your script permissions are set appropriately to keep your password secure.
Making a dump of a single database
To make a dump of a single database, invoke mysqldump with the name of the database. Remember, if you need to login to do this, use the approaches I've looked at above in your command.
$ mysqldump databasename
By default, the dump will be printed out to stdout.
Redirecting the output to a file
If you want to save the dump to a file (probably more useful in most circumstances), just use a standard redirect on the command line:
$ mysqldump databasename > somefile
Dumping all the databases
Again, depending on your security settings, you may need to run as the MySQL root user to do this, but this command will dump all the databases on your server, not just one:
$ mysqldump -A
Dumping only specific tables on a database
The default behaviour if you specify a database name is for mysqldump to dump all tables from that database. To override that, add the desired tables after the name of the database in the command like so:
$ mysqldump databasename table1 table2
Add DROP commands to the dump
If you're doing something like a backup, or manually synchronising the databases on two different servers, you may want to ask mysqldump to add DROP DATABASE and DROP TABLE commands before the CREATE statements so that old data will be flushed out when you execute your dump.
It's really easy, thankfully. For adding DROP DATABASE, use this notation:
$ mysqldump --add-drop-database
And for DROP TABLE:
$ mysqldump --add-drop-table
Even more
For even more detail on the possible command line switches, you can head over to the relevant page on the MySQL Online Manual. There is plenty I haven't covered here, so if you're looking to something a little more complex or advanced you may have to head over there for more information.
I've probably missed off something that other people use quite a bit, so if you think there is a neat mysqldump trick that's worthy of a mention in the main post, please do feel free to drop a comment and let us know!
