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
Please tell me you are not (and not even thinking of) using file system copying on a database server that's up and running? That is virtually guaranteed to lead to corruption of your copies, leaving your backups useless. You won't receive any errors - except, of course, when you try to restore :-)
It's not very clear from your post (correct me if I'm wrong), but you're now running your database on Linux? If so, then use Percona's XtraBackup for your InnoDB tables. This tool performs non-blocking hot backups of InnoDB tables.Take a look at my answer here for some MySQL backup options. For MyISAM, you could look at MyDumper if mysqldump isn't performant.
If your server is still on Windows, then you're out of luck. XtraBackup doesn't work on Windows, nor does MyDumper (AFAIK). You could check this out (disclaimer - never used).
Again, I'm not totally clear on what, exactly, you mean here.
If you mean that you are copying the mysqldump from Linux to Windows, then yes, that will work as any file copy. If, on the other hand, you mean that you are copying (using the file system) data files on a running server and tranferring those copies from Linux to Windows, then your "backups" will be useless on both systems.
If you are backing up the database using mysqldump, then that is sufficient. If you are simply copying files from one location to another on a running database server, then it really doesn't matter what you do or don't copy, because your backup will be useless.
[EDIT] in response to the original poster's comments.
From here (correct answer)
Just substitute your schema name for --all-databases. You say that your "table" is InnoDB - is the rest of the database/schema InnoDB? If not, you should make it so. You seem to think that the datadir has to be copied in some shape or form - it DOES NOT. The dump contains the necessary data to reconstruct your database.
In the event of a problem, you restore your database from the last BACKUP (i.e. the dump). There are other solutions which allow PITR (Point In Time Recovery), but they are more complex and replication can also address this issue.
See also these posts (1, 2).
My take on the content of these posts (check the reputations of the posters) is that with InnoDB, the above command will allow for a backup while at the same time permitting reads and writes to the database - note that performance may be affected.