With mysqldump you can only safely use --single-transaction
if all your tables are InnoDB, otherwise your backup is inconsistent.
If you have the requirement for a hybrid backup, then you need the lock-tables
on all tables in the backup (default), which will be safe for all engines. It's also worth mentioning that the default options will make sure your backup is safe, you don't need to turn any special flag on.
Note: If you do have a hybrid mix, perhaps look at xtrabackup. It will only be locking during the MyISAM phase of the backup.
From the sound of this, you could use a different DB infrastructure.
Based on your aforementioned needs, I have a suggestion but would require a little compromise.
SUGGESTION #1 : Use MySQL Replication on the Same Server but Different Disk
Your second need (No need of real-time replication
) would have to take a slight backseat in this suggestion. Since you have an external harddrive big enough for XtraBackup, why not use as a datadir for a second instance of MySQL on the same box?
I scripted my own service to provision dedicated mysql instance for ports 3307 - 3399. Here are my past posts on this:
SUGGESTION #2 : Use MySQL Replication on the Different Server
In consideration of your first need (Low Cost
), if you have access to a commodity server with adequate diskspace, setup MySQL Replication to that external server. That way you can run backups on the Slave as follows
STOP SLAVE;
- XtraBackup
START SLAVE;
You can do this with zero impact (no server load, no disk I/O) on the Master
SUGGESTION #3 : Parallel mysqldumps
You can setup parallel mysqldumps of databases or tables. This may result in a shorter window of time for backups. XtraBackup essentially does a checkpointed transactional backup with point-in-time sensitive operations so that the point-in-time of the backup is when the backup finished. With parallel mysqldumps on a slave which is not replicating gives you backups whose point-in-time is the beginning of the backup. Doing mysqldump on a stopped slave would be storage engine agnostic. In other words, it would not matter whether tables are InnoDB or MyISAM.
If you apply this to SUGGESTION #1, this will incur server load and disk I/O since both Master and Slave reside on the same machine.
If you apply this to SUGGESTION #2, there is no penalty on the Master. You can run backups at will on the Slave. You also will not have to concern yourself with locking MyISAM tables.
Please see my past posts on doing parallel mysqldumps
Best Answer
Xtrabackup website says something different:
This information used to be in the documentation, probably better detailed, but nowadays only this enigmatic claim remains. Probably you are worrying too much, but I will answer the rest of the question anyway.
There doesn't seem to be any way to easily exclude non-InnoDB tables from the backup. What you can do is to manually exclude those tables with --table-exclude If you have entire databases dedicated to non-InnoDB tables, --databases-exclude should be easier to use.
You can dynamically compose the list of those tables with this query: