Does mysqldump Use In-Memory Pages During Backup?

From here, the complete mysqldump command for a hot backup of a MySQL database using only InnoDB tables is: (from here).

mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql

In addition to the Enterprise solution mentioned by @TrentLloyd, there is a F/LOSS solution available from Percona (who are big hitters in the MySQL world). I would recommend without hesitation any tool by Percona and I would put this first on my list for testing on any MySQL system which I work with.

What they offer is Percona XtraBackup which has an incremental backup functionality. The documentation is here.

You might also be interested in LVM snapshots - it's not a technology with which I am currently familiar but it's on my to-do list (like a lot of stuff :-) ). If you're familiar with all that stuff about mounting logical volumes &c., this may be of interest to you.

You could also look at zmanda - (mentioned in "High Performance MySQL").

I would concur with @TrentLloyd that regularly taking a full backup (at such small time intervals) doesn't make sense - your system will not be able to do any real work with a constant resource drain like that.

For completeness, you might want to look at the options here: (Credit where credit is due, I "stole" this image from here :-) )

On a final note, there is an endless number of shell (mostly bash) scripts out there. A Google of "mysql incremental backup bash script" (or similar) will get you a shedload of these. One of the best (IMHO) of which (and one of the most popular) is automysqlbackup, but you may like to try out any/all/some of the others.

With bash now being available natively on Windows (traditionally poor in the MySQL backup area), these might be worth investigating for users of that OS.

An interesting variant on automysqlbackup (disclaimer - I haven't used it) is a script which will perform incremental backups which is to be found here.

Another way to backup - which is "hot" is to use replication - this process is explained in detail here. The MySQL documentation is available here.

Dumping subsets of table data can only be done backing up individual tables.

I once answered a post entitled Is it possible to mysqldump a subset of a database required to reproduce a query?. In that post, I used mysqldump strictly against the tables in question.

The only thing you can do is to produce two dumps

• mysqldump of the database without table1 (means you have to name all other tables (See my old post How do you mysqldump specific table(s)?)
• mysqldump only table1 using -w or --where
• concatenate the second dump to the first dump

Give it a Try !!!