Does anyone have a step by step to setup incremental mysql backups? A sort of replication using binary logs maybe?

Current we use rsyc to backup the entire db which may not be the wisest route.

See my comment in this thread. Seems this is sort of already being discussed.

http://www.webhostingtalk.com/showthread.php?t=955373

or this

http://dev.mysql.com/doc/refman/5.1/en/backup-methods.html

Still only gives suggestions, no solutions.

Still only gives suggestions, no solutions.

You have to get used to the fact that MySQL users do nothing the way you'd expect and that it might be broken in any location.
(i.e. binlogs have no crc and there's no "backup mode", and there's no standard method for freezing the DB on disk transaction safe, as InnoDB might still be changing things internally (that means even read only full flush does *not* really give you a good backup for snapshotting)

The "usual" way is "replicate to another box where you can shut down things for backup". Comparing this to real databases I could just cry.

Percona has a working backup solution for MySQL, but it costs money.
If you can, go for it anyway.


AS of now I can get along with a nightly backup which is currently done with mysqldump (lol) and will be switched to a offline snapshot which can then be backupped or rsynced.
While it just takes a few seconds to shutdown, snapshot, and start this solution won't be enough once the users need something like hourly backups. But TBH if they need a transaction-safe replayable snapshot of multiple points in time then I won't support them using MySQL.

The little roadster is fun but if you need to move 200 people you still need an airplane.

1. backup mysql using mysql dump
2. use rsync to backup the .sql file (it does incrementals by default)

The linked threads above go deeper if you want to understand why/how.

I run MySQL on a dedicated LVM Partition and use its snapshot feature, works great.. I have wrote a script that takes regular snapshots, then mounts the snapshot and copies the data to my file storage cluster.. Giving me a full daily backup and hourly incremental backups.

Cheers,
-R

I run MySQL on a dedicated LVM Partition and use its snapshot feature, works great.. I have wrote a script that takes regular snapshots, then mounts the snapshot and copies the data to my file storage cluster.. Giving me a full daily backup and hourly incremental backups.

Cheers,
-R

How do you freeze MySQL for the second where the snapshot is allocated? I read that innodb doesn't very much care about flush with read lock.

Really very interested. So far I'm shutting down the whole VM and take the snapshot offline. Downtime is about one minute which is OK-ish and goes well with my paranoia of unsynced fs writes or something being stuck in dom0 buffer cache...

since I cant yet post links, google mylvmbackup that should give u a good starting point... there's also alot if you search for "MySQL LVM Snapshots"

since I cant yet post links, google mylvmbackup that should give u a good starting point... there's also alot if you search for "MySQL LVM Snapshots"

Thanks for the reply.
Not wanting to go too far off topic, but maybe it's relevant anyway.

If you look in the source you'll see a TODO file.
Stupid me didn't look into that file until I had read through almost all of Mylvmbackup. Nice script, but even while reading it I wondered where the hook "presnapshot" is. Now back to he readme...:
http://bazaar.launchpad.net/~lenzgr/mylvmbackup/trunk/view/head:/TODO

That explicitely mentions that so far mylvmbackup isn't able to do really consistent safe InnoDB backups and that flush/read lock isn't enough.


So it's still un-solve-able, except by *buying* the one hot backup solution it seems?
I hope the good work on InnoDB goes on like it has over the last year or so, then hopefully we'll see a working "backup mode". It shouldn't be really that hard...

Percona has a working backup solution for MySQL, but it costs money.
If you can, go for it anyway.



Percona Xtrabackup is free software, like all software produced by Percona. Binary releases are also available for free.

You can check it out here: https://launchpad.net/percona-xtrabackup.

You may have been thinking of Oracle's backup solution, which is proprietary and does cost money.

Regards,

rSnap for incremental SQL backups. See howtoforge.

You can also have a look at http://www.mydumper.org and see how it can fit in.

this is video tutorial:

http://www.youtube.com/watch?v=rYeBXxWVxkw