MySQL: Automated incremental backups of InnoDB tables

I know there have been similar/related questions on SO and elsewhere, and I at least think I understand the various options.

We have a largish MySQL (community edition) db with InnoDB tables. I would like to take daily shapshots of the DB to be backed up off-site. Currently I am using a mysqldump script that gets run by cron.daily. However it takes 3 hours to write out the copy. So - incremental it must be.

As we are community edition and cannot afford Enterprise we don't have InnoDB hotcopy which would be a viable solution as it is non-blocking. The other option is turning on binary logging and taking incremental update开发者_开发问答s. But this seems like a colossal pain and prone to error: flushing and rotating logs, recording timestamps etc. The advice seems to be avoid this if possible.

So the final option is setting up replication with a Master-Slave configuration. I've scanned the docs but am not clear what constitutes a "Master" and what constitutes a "Slave". So a few questions:

• Is a slave a separate mysqld process?
• Can the slave run on the same machine as the Master?
• Once set up and running is any intervention required or will the Master faithfully push all updates?
• To make my backups without impacting the Master, presumably I can just run mysqldump on the slave?
• And finally, is this a reasonable solution to maintaining a backup for copying off-site?

Thanks Richard

---

Is a slave a separate mysqld process?

there is no need to using separate mysql daemon,
in master you enable the binary log
in slave you configure relay-bin, and the relevant information on master
(see http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html)

Can the slave run on the same machine as the Master?

Yes, but don't do this ,
because once the machine crashed,
both your master and slave is gone
(DTP)

Once set up and running is any intervention required or will the Master faithfully push all updates?

master will faithfully push all updates,
master log the write sqls into binary log,
and slave should obtains the binary log (as @Johan explained, it pull from master)

however,

1. table locking could lead to replication delay on slave
2. fail write query that cannot executed on slave

you can treat replication is just a method that allow ALL the sqls execute on master,
copy over to slave,
and execute again in the receiving order
(this is what binary log does)

To make my backups without impacting the Master, presumably I can just run mysqldump on the slave?

Of course, this is the purpose of having slave
However, there will be some delay ...
when you are dumping the data,
you might blocking table for write (assuming innodb has lesser impact),
while master could be carry on further writing

And finally, is this a reasonable solution to maintaining a backup for copying off-site?

yes, this is consider as a mysql disaster recovery

---

Is a slave a separate mysqld process?

It can be, but it is more commonly a separate MySQL server installed on a different machine.

Can the slave run on the same machine as the Master?

Yes, if you must, just assign it a different port number and different server_id.
Note that it's not much of a backup if it's on the same machine.

Once set up and running is any intervention required or will the Master faithfully push all updates?

The master does not push anything, the slave pulls the updates.

To make my backups without impacting the Master, presumably I can just run mysqldump on the slave?

Yes, but if master and slave are running on the same machine, running mysqldunmp will slow down that server.

And finally, is this a reasonable solution to maintaining a backup for copying off-site?

Yes, off site is the keyword here.
I would place the slave off-site, that make way more sense than having the slave on the same machine.

Here is a tutorial
http://www.howtoforge.com/mysql_master_master_replication