FOSSwire

MySQL 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!

Quick headnote - welcome to a new breed of FOSSwire post, the Reference post. In a Reference post, we'll try to put as much useful information as possible that you might want to quickly come back and find later!

If you've ever worked with a Unix system, you've probably used tar. Put simply, tar is a way to archive a folder, or multiple files into one file (and optionally add compression). It comes in really handy for pretty much anything, from backups, to moving files between places to using it to store lots of files for a download.

We're using GNU tar here, which is pretty much standard everywhere. If you're using a different flavour of tar, not everything here might work.

So let's get straight to it!

Creating an archive

Run tar with the c switch to create an archive. You'll probably want to also use the f switch to send tar's output to a file:

$ tar cf outputfile.tar inputs

To add compression, use the z switch for gzip, and the j switch for bzip2:

$ tar cjf outputfile.tar.bz2 inputs

Finally, you can also use the v switch (verbose) to watch progress.

Extracting an archive

Extracting is basically the same, but you use the x option to extract:

$ tar xf inputfile.tar

That will extract said archive to the current directory. For a compressed archive, you'll again need to add the z for a .tar.gz, or j for .tar.bz2.

$ tar xjf inputfile.tar.bz2

Again, to see progress you may want to add the v option for verbose logging.

Listing contents of an archive

The t switch is helpful if you want to peek into an archive without actually extracting it. Unless you're piping output in and out, you'll need to use the f switch again to determine the archive file.

$ tar tvf myarchive.tar

You will get an ls -l style listing of the contents. You may wish to pipe the output to less for a large archive for readability.

Again, for compressed tar archives, add z or j.

Wrapping up

There's plenty more that tar can do, so I've just touched on the most common uses of tar. Feel free to fire your power tar user tips in the comments!