How to Back Up Selected MySQL Databases

I recently had to do a backup of a 30 very large databases within a MySQL instance that had a total of 60+ databases.  I did NOT want to back up the other databases. In fact, the other databases had very large tables in them and I had very little disk space, so I could not afford to back them up. In this post I will share what I learned.

I’ve simplified the problem to illustrate the idea.  My goal in this post is to backup one database when an instance has many, with mixed InnoDB and MyIsam tables.

  • I have 3 databases called dbtest1, dbtest2, and dbtest3, in one instance of MySQL .  I only want to back up  the tables in database dbtest2

3dbs

  • Each database has one InnoDB and one MyISAM table
  • The InnoDB tables are quite large, and I have limited disk space for the backup
  • I am using file-per-table option for all InnoDB tables

The –databases Option

First I tried the–databases=dbname  option in the mysqlbackup command line.  This option filters  all non-innoDB files, including FRM, MYD, MYI etc. .

mysqlbackup –u username –p –databases=”dbtest2–backup_dir=yourdirectory backup

This translates to:

  • Backup all the non-InnoDB files (including frm files) in the database I’ve specified (dbtest2)
  • Do NOT backup the non-InnoDB files in any other databases
  • Back up all IBD files in the instance

results1A full backup of dbtest2  was saved (as outlined above) but  the IBD files  in all other databases in the instance were also backed up.  So this is not the exact option I needed.

The –include  Option

Next I added  the –include option to filter IBD files in other databases.  It’s important to think about the—include option as filtering IBD files (not InnoDB tables, which in my mind include IBD and FRM files)

mysqlbackup –u username –p –databases=”dbtest2” –include=”dbtest2  backup_dir=yourdirectory  backup

This translates to:

  • Backup all the non-InnoDB files (including frm files) in the database I’ve specified (dbtest2), as before
  • Do NOT backup the non-InnoDB files in any other databases, as before
  • Back up all IBD files only in the dbtest2 database

.results2

This gave me exactly what I wanted – one directory with all the IBD, FRM, MYD, and MYI files for dbtest2.  All other databases are completely ignored, so I did not run out of disk!.

Note that the –include syntax does not permit you to list more than one database at a time.  However, it does allow you to use regular expression syntax such as :

–include=db.                        (include all databases starting with db followed by any character)

  • or

–include=dbtest[12]             (include dbtest1 and dbtest2)

Compression

Consider trying  compression – in my original scenario, I also used compression which was a great help and compressed my 1300 gigabyte files 80% in the backup.  Of course the downside to compression is you must decompress to restore, but it is definitely worth considering if you have space limitations.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s