Reference - making backups using mysqldump

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!

Avatar for peter Peter Upfold - http://peter.upfold.org.uk/

Peter Upfold is a technology enthusiast from the UK. Peter’s interest in Linux stems back to 2003, when curiosity got the better of him and he began using SUSE 9.0. Now he runs Linux Mint 9 on the desktop, runs a CentOS-based web server from home for his personal website and dabbles in all sorts of technology things across the Windows, Mac and open source worlds.

Home » Articles »

Discussion: Reference - making backups using mysqldump

  1. ogosense (guest)

    # Posted on 02 August 2007 at 06:42 AM

    hey thanks for this post. I find it very useful because we were just about to automate our database backups. Thank for your help!



  2. James (guest)

    # Posted on 03 August 2007 at 02:03 PM

    I've been using mysqldump for over a year to backup databases from our DB server system. Problem was that I didn't always know what the DB's where. (apps come and go) So I use the following (sanitized) bash script

    !/usr/local/bin/bash

    This is a bash script written to do a quick dirty dump

    of the mysql databases currently in production

    This doesn't provide a 100% guarantee of completeness it does however

    provide for a reasonable recover point in case of total disaster.

    variables used

    dbname => main db listing from one server

    stagedb => main db listing from another server

    a,e,i => These hold the name of each "named db" in the list one at a time so that

    a place holder for the current db being dumped is known. (Used in the for loops)

    WD => Week Day, used to figure out when to do the weekly portion of the script

    dt => Date time, used when nameing dirs and files.

    James Sparenberg 2006

    Variable declaration

    WD=date +%u dt=date +%Y%m%d dbname=/usr/bin/mysql -u [Username] -p[password] -h [DB hostname] -Bse 'show databases;'

    the following is to dump from mysql-bak on a daily basis.

        # create the days dir
                mkdir -p /space/mysqldump/mysql-bak/$dt
        #do the dump for each db
                for i in $dbname; do
                        /usr/bin/mysqldump -Ff -u [Username] -p[passwd] -h [DB hostname] $i | gzip -9 > /path/to/[DB hostname]/$dt/$i.$dt.gz
                done
    

    Then if the weekday is 5 (sat) do a dump from other less frequent DB's

        if [ $WD -eq 5 ] ; then
                mkdir -p /space/mysqldump/Media/$dt
                /usr/local/bin/mysqldump -Ff -u [some user] -p[password -h [Another DB server]  | gzip -9 > /path/to/(Directory)/(DBname).$dt.gz
    


  3. Artur Gouveia (guest)

    # Posted on 04 August 2007 at 10:03 AM

    Gostaria de saber se posso traduzir este tutorial de comando linux para o portugues e enviar para voces fazer as alterações e publicar.



  4. # Posted on 22 August 2007 at 08:47 AM

    [...] queramos volcar, de forma que tengamos toda nuestra información a salvo. Basado en el artículo Making backups using mysqldump. Mucha más información en el manual de referencia. backup, mysql, [...]



  5. # Posted on 23 October 2007 at 12:46 PM

    [...] and it gets run nightly in the early hours of the morning using cron. What the script does is it takes a mysqldump of the database, uses tar to backup the relevant [...]



  6. kiran (guest)

    # Posted on 21 January 2008 at 03:20 AM

    mysqldump --user=xyz --password=pqr DATABASE_Name TABLE_Name --where COLUMN_Name < 'Some_Value'

    Not working.why?????



  7. NameName (guest)

    # Posted on 12 June 2009 at 01:39 PM

    Can you confirm it is SECURE to do a mysqldump accross the web? thanks!



Home » Articles » Reference - making backups using mysqldump

Sign In