Advice for backing up slave servers.

[dchicks]

I’m implementing XtraBackup now, and I have two slaves that I’d like to back up. Our first night encountered problems. So, I wanted to ask for some advice about how to manage this.

The problem is that apparently our slaves aren’t powerful enough to process replication data efficiently enough for the slave_open_temp_tables to become 0. I could try setting the timeout period longer, but I have no idea what that period should be other than to do a little trial and error.

I had another thought, though. It looks to me from the output of the failed backup that XtraBackup waits until it is ready to backup the MyISAM portion of the server before it cares about shutting down slave I/O. If that is the case and if all of my own tables are in InnoDB, then does it really matter if I use the --safe-slave-backup option? The only MyISAM in my server is the MySQL data dictionary.

Thanks in advance!
Dave

[xaprb]

slave_open_temp_tables becoming 0 doesn’t depend on the replica catching up to the master. Rather, it depends on temporary tables not being kept open forever on the master. If someone opens a connection, creates a temporary table, and then returns the connection to the pool, then the variable will never decrease to zero until the connection is recycled (closed).

This isn’t related to MyISAM. It is to prevent the following scenario: I open a connection, create a temporary table, and you take a backup. Now I insert into the temporary table. You restore the backup and use it to set up a replica, start replication, and my INSERT gets executed – but the temporary table doesn’t exist, and replication breaks. This happens with any storage engine.

[dchicks]

Thanks for the explanation, Baron. I guess I need to figure out where we are holding open temporary tables, and the scenario you painted seems right on target.