Documentation Home
MySQL Enterprise Backup 3.9 User's Guide
Related Documentation Download this Manual
PDF (US Ltr) - 0.9Mb
PDF (A4) - 0.8Mb


6.1 Setting Up a New Replication Slave

If you use MySQL replication, MySQL Enterprise Backup allows you to set up a slave database without stopping the master, by backing up the master and restoring that backup on a new slave server.

  1. Take the backup, transfer it to the slave server, use mysqlbackup with the apply-log option to prepare it, and put the restored backup and the log files in the right directories for the new slave.

  2. Edit the my.cnf file of the new slave and put skip-slave-start under the [mysqld] section.

  3. Start the new slave mysqld (version >= 5.1). It prints the latest MySQL binary log position the backup knows of.

    …
    InnoDB: Last MySQL binlog file position 0 128760128, file name ./hundin-bin.006
    …
    

    Note that InnoDB only stores the binary log position information to its tablespace at a transaction commit. To make InnoDB aware of the current binary log position, you must run at least one transaction while binary logging is enabled.

  4. Use the CHANGE MASTER SQL command on the slave to initialize it properly. For example:

    CHANGE MASTER TO
    MASTER_LOG_FILE='hundin-bin.006',
    MASTER_LOG_POS=128760128;
    
  5. Set the statuses of any events that were copied from the master to SLAVESIDE_DISABLED. For example:

    mysql> UPDATE TABLE mysql.event SET status = 'SLAVESIDE_DISABLED';

  6. Start replication in the new slave with the SLAVE START SQL command.

  7. Remove the line skip-slave-start from the my.cnf file of the slave.


User Comments
  Posted by I88.CA IT on September 24, 2014
Some errors in this document, shown below by http://it.i88.ca:

mysql> UPDATE TABLE mysql.event SET status = 'SLAVESIDE_DISABLED';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLE mysql.event SET status = 'SLAVESIDE_DISABLED'' at line 1
mysql> UPDATE mysql.event SET status = 'SLAVESIDE_DISABLED';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> slave start;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'slave start' at line 1
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
Sign Up Login You must be logged in to post a comment.