Documentation Home
MySQL Enterprise Backup 4.0 User's Guide
Related Documentation Download this Manual
PDF (US Ltr) - 1.1Mb
PDF (A4) - 1.1Mb
EPUB - 250.9Kb
HTML Download (TGZ) - 189.9Kb
HTML Download (Zip) - 219.3Kb


6.1 Setting Up a New Replication Slave

MySQL Enterprise Backup allows you to set up a replication slave by backing up the master and restoring the backup on a new slave server, without having to stop the master.

For servers NOT using GTID:

  1. Take a backup of the master and then use, for example, the copy-back-and-apply-log command, to restore the backup and the log files to the right directories on the new slave and prepare the data.

  2. Edit the my.cnf file of the new slave and put skip-slave-start and event_scheduler=off 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 TO SQL statement 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 mysql.event SET status = 'SLAVESIDE_DISABLED';

  6. Remove the line skip-slave-start and event_scheduler=off entries you added to the my.cnf file of the slave in step 2.

  7. Restart the slave server. Replication starts.

For servers using GTIDs (supported by MySQL Server 5.6 and after):

  1. Take a backup of the master and then use, for example, the copy-back-and-apply-log command, to restore the backup and the log files to the right directories on a new GTID-enabled slave and prepare the data.

  2. Edit the my.cnf file of the new slave and put skip-slave-start, event_scheduler=off, and sql_log_bin=0 under the [mysqld] section.

  3. Start the new slave server.

  4. Connect to the slave server with the mysql client.

  5. When a server using the GTID feature is backed up, mysqlbackup produces a file named backup_gtid_executed.sql inside the meta folder under the backup directory. The file contains a SQL statement that sets the GTID_PURGED configuration option on the slave:

    # On a new slave, issue the following command if GTIDs are enabled:
    SET @@GLOBAL.GTID_PURGED='f65db8e2-0e1a-11e5-a980-080027755380:1-3';
    

    It also contains a commented-out CHANGE MASTER TO statement for initializing the slave. Uncomment the command and add any needed connection and authentication parameters to it; for example:

    # Use the following command if you want to use the GTID handshake protocol:
    CHANGE MASTER TO MASTER_HOST="127.0.0.1", MASTER_USER="muser", MASTER_PASSWORD="mpass", MASTER_PORT=18675, MASTER_AUTO_POSITION = 1;

    Execute the file with the mysql client

    mysql> source /path-to-backup_gtid_executed.sql/backup_gtid_executed.sql

  6. Set the statuses of any events that were copied from the master to SLAVESIDE_DISABLED. For example:

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

  7. Remove the skip-slave-start, event_scheduler=off, and sql_log_bin=0 entries you added to the my.cnf file of the slave in step 2.

  8. Restart the slave server. Replication starts.

For more information on the GTIDs, see GTID feature.


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.