With continuos I mean short-intervals, i.e. every 15 minutes for example. I'm not interested in where to store the backups, just what the most elegant solution is... I'd like to avoid running mysqldump via cron every 15 minutes.... So here is what I thought about, just need some feedback if that is doable and makes sense... Use a copy-script to copy /var/lib/mysql into a specific backup folder. What happens there (i.e. dedup, compression, upload to S3) isn't important, I just need to know if this is feasible or if file-based mysql backup like this isn't safe? Anyone else doing short-interval mysql backups care to share your methods?
You can copy the mysql-databases if you don´t need a safe backup. When copying databases mysql may change the tables. You can prevent this by shutting down mysql . Is there any reasons why you don´t want to use mysqldump? You can also try mysqlhotcopy.
If you want to do frequent mysql backus without affecting the performance of your mysql server, then setup a second mysql server and configure it as slave server of your master server and run the mysql backups on that slave server.
@florian030: just spinning some ideas. didn't want to use mysqldump because apparently mysqldump has to get a read lock on the tables and hold it for the duration of the backup in order to ensure a consistent backup and mysqlhotcopy cannot handle InnoDB as far as I have found out. Was looking at file based backups as not to interrupt access to the DB and because I could use something like rsync that only copies changes... @till: that sounds like the best idea so far, could setup a tiny virtual machine on the same server actually for this...
You can use --skip-lock-tables. BTW i´m running a sql-save for creating the backups as Till suggested.
I agree with Till. If you have several servers up and running, you could even go with a snapshot from the server you want to backup. Send the snapshot to a second server. Check the tables for corruption. If all fine - Run the backup.
hm, yeah, all good advice. Basically, I was just looking if I can duplicate a feature I saw at another webhost offering: I know that is quite a service they are offering so I was looking into something like that They say: So I guess the closest I can do to that is, have a plugin that hooks into different Wordpress actions and runs a mysqldump or export on certain tables that have changed... Thanks for all the feedback though
