MySQL incremental backups using dumps from a slave (InnoDB and MyISAM)

Assume I already have a master and a slave DB server that are up and running.

I'd like to introduce incremental backups/point in time recovery into the mix. Since I've already got binary logs going for the replication, it should be as simple as making a full backup and storing the log position.

However, I have both MyISAM and InnoDB tables on my server, and there seems to be conflicting suggestions on how to do full backups in each case. If it were strictly InnoDB, I could do mysqldump with --single-transaction, but that option warns me that MyISAM may still be changed.

My questions are as follows:

(1) Is it a valid concern that the MySQL manual suggested backup of:

mysqldump --single-transaction --flush-logs --master-data=2 --all-databases > whatever.sql

...would be unsuitable because MyISAM tables may change (tables are not locked with --single-transaction), so the new log file is started but it may开发者_Go百科 contain queries for MyISAM tables which were actually already run on the server before those tables made it into the dump (and an incremental restore would try to re-do those queries when they aren't needed)?

(2) Is the only way to get a "proper" full backup with both MyISAM and InnoDB tables to use --lock-all tables? (Or at that point just shut down the server/copy files since everything is locked anyway)

I'm assuming that the answer to those questions is yes, but please correct me if I'm wrong, because I've based the next idea off of it.

I'd like to keep interruptions to a minimum on the master server, so I am considering doing the full dumps off of the slave based on the directions here:

http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_master-data

Those directions are actually for setting up a slave off of a slave, but I am wondering, is the following plausible?

Once a day:

1. Stop the slave
2. Show slave status and get the master log file and position
3. Do a full dump of the slave while no changes are being made to it (MyISAM or InnoDB)
4. Start up the slave again
5. Move my full dump over to the master server in some backup dir

In case of recovery:

1. Restore to the full dump from (5) above
2. Run point in time recovery using positions from here http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery-positions.html to go from the position in (2) above to whatever position I want to restore to

Is that legit? I don't see why the full dump from the slave would be any different from the master one, so it seems like it would be fine.

Thanks for any help!

Your once a day plan is very plausible for one simple reason: You stopped the slave first. No new transactions would be coming in. I would like to suggest something additional.

On the slave please set the following in /etc/my.cnf

[mysqld]
innodb_max_dirty_pages_pct=0

Here is why:

While the slave is processing the mysqldump, if any tables have dirty pages registered in the innodb buffer pool, the pages must be flushed to disk. I noticed in your mysqldump you issue this option already. By default, innodb_max_dirty_pages_pct is 90. Whatever dirty pages exist must be written to disk. If innodb_max_dirty_pages_pct is zero all the time, flushing the innodb bufferpool is quicker.

You can set this dynamically without restarting mysql. Just run this:

SET GLOBAL innodb_max_dirty_pages_pct = 0;

This will keep the innodb buffer pool lean and mean.

I also see that --master-data=2 is in your mysqldump command. This will only work if the binary logs are enabled on the slave. If not, you need to retrieve the master log and position from the master, because mysqldump cannot do that. Here is how you can get the master's log file and position from the slave:

Step 1) Run "SHOW SLAVE STATUS\G" and rediredct to ShowSlaveStatus.txt

Step 2) Get the following info from ShowSlaveStatus.txt
Relay_Master_Log_File
Exec_Master_Log_Pos

Step 3) Write these two values at the back of the dump file.

One more thing:

Please add --routines --triggers to the mysqldump command. You never know when you may decide to write stored procedures and triggers. Also, no need to --lock-tables is the slave is stopped.