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
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.
It is hard for me to say w/out knowing: the access pattern of your site w/the DB, the DB traffic, or how large you expect the DB to get. If you are fine w/any sort of locking that will take place, I say go for it.
I'm in an environment where any locking above and beyond typical usage is not acceptable and our DBs are around 750GB. What we do is take an LVM snapshot and then run a mysqldump off the snapshot. Sure we get some degraded performance during the snapshot but it is better than completely locking tables.
Mysqldump locks a DB being backed up, so you should consider using a live backup tool like
xtrabackup. You will need to use Percona's MySQL build and to convert all your tables to InnoDB. Latest InnoDB plugin from Oracle supports live backup too, can't remember the name of their live backup tool.I think mysql replication is a nice option. Mysql generate new logs after committing the transaction. So, you will have consistent backups as far as you are using transactions properly.
One full backup at the beginning, and then you can get the generated binary logs by the replication system. Ever applied binary log file should update the database in a consistent way.
This can always be used. However, the option of using system snapshots may not be feasible as it is related to the file system you are using.