Skip navigation links
MySQL Backup Reference Manual :: 5 MySQL Backup and Replication :: 5.2 RESTORE and Replication of Non-Replicated Databases
6 Restrictions on MySQL Backup »
Section Navigation      [Toggle]

5.2. RESTORE and Replication of Non-Replicated Databases

Server administrators should be careful which users are granted the RESTORE privilege because restore operations are destructive and replace existing data. Users with the RESTORE privilege should be aware of the impact that the RESTORE statement can have on replication. In particular, it can affect replication of non-restored databases.

If a backup image contains no replicated databases, a RESTORE of that image should include the SKIP_GAP_EVENT option to avoid interrupting replication: 

RESTORE FROM 'image_file_name' SKIP_GAP_EVENT;

If a backup image does contain any replicated databases, restoring it causes the master to become unsynchronized with its slaves because the RESTORE statement is not replicated. Therefore, a RESTORE of that image should omit the SKIP_GAP_EVENT option so that post-restore maintenance can be performed on the slaves to resynchronize them to the master: 

RESTORE FROM 'image_file_name';

When SKIP_GAP_EVENT is not specified, RESTORE generates an incident event in the binary log to cause slaves to stop executing further events when they receive the event. As each slave stops, the following procedure should be performed on that slave to resynchronize it to the master and restart replication:

  1. Copy the backup image to the slave and use RESTORE there to resynchronize the slave with the RESTORE that was done on the master. 

    RESTORE FROM 'image_file_name';

  2. Skip the incident event that was received from the master and restart replication: 

    SET GLOBAL sql_slave_skip_counter=1;
START SLAVE;

  3. If the slave has slaves of its own, you must resynchronize and restart replication on them as well using the previous steps just described.

Because restore operations can affect databases other than those being restored, users who have the RESTORE privilege should take care to either use SKIP_GAP_EVENT or to restart replication on slaves as necessary after using the RESTORE statement. Consider the following scenario:

  • User 1 has a database db1 on the master that is replicated to the slave.

  • User 2 has a database db2 on the master that is not replicated to the slave. User 2 backs up this database on the master: 

    BACKUP DATABASE db2 TO 'db2.bak';

  • It is desired that restoring db2 does not affect replication of db1.

Suppose that User 2 restores db2 on the master and uses SKIP_GAP_EVENT: 

RESTORE FROM 'db2.bak' SKIP_GAP_EVENT;

In this case, db2 is restored, no incident event is written to the binary log, and replication of db1 is uninterrupted (which is the desired result).

Now suppose instead that User 2 restores db2 on the master but neglects to use SKIP_GAP_EVENT: 

RESTORE FROM 'db2.bak';

In this case, db2 is restored, but the RESTORE statement generates an incident event in the binary log that causes the slave to stop executing further events when it receives the event. As a result, replication stops for db1, even though db1 was not involved in the backup or restore operations. At this point, it is necessary on the slave to skip the incident event and restart replication: 

SET GLOBAL sql_slave_skip_counter=1;
START SLAVE;