I am currently investigating a mysql backup solution to implement on some production servers.
After reading a while, I concluded that the best solution is to implement a mysql replication using the master/slave methodology.
Honestly and considering my real needs, it may be a bit overkill, once a simple database dump would be enough.
So, I will try to implement a backup solution through rsync and mysqldump.
Once the databases size is not too big (the ibdata1 of the larger one has 42MB, being the mysqldump command execution quite fast, producing a dump file with 6.7M) and the database accesses are quite reduced, it would be safe enough to use mysqldump during the working period, with the option -single-transaction?
With this approach, it would be possile to configure a cron task to perform 2-3 dumps during the day? If I'm correct, the -single-transaction option usage grants some data integrity.
Thank in advance for all the help,
Regards
Best Answer
For InnoDB tables, --single-transaction will ensure that the dump of each table is consistent, but it will not necessarily be consistent with the other tables, since it locks and dumps them one at a time rather than locking them all for the duration of the dump. And you will, of course, have delayed writes waiting for the lock to release if you try to write to a table while it's being dumped.
A better bet is to use an LVM or filesystem that allows you to take snapshots, then lock the entire database for just long enough to take a snapshot, which you can then back up at your leisure.