Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.9Mb
PDF (A4) - 39.0Mb
PDF (RPM) - 38.1Mb
HTML Download (TGZ) - 10.8Mb
HTML Download (Zip) - 10.8Mb
HTML Download (RPM) - 9.5Mb
Man Pages (TGZ) - 211.0Kb
Man Pages (Zip) - 320.0Kb
Info (Gzip) - 3.5Mb
Info (Zip) - 3.5Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Adding Slaves to a Replication Environment

16.1.2.6 Adding Slaves to a Replication Environment

You can add another slave to an existing replication configuration without stopping the master. To do this, you can set up the new slave by copying the data directory of an existing slave, and giving the new slave a different server ID (which is user-specified) and server UUID (which is generated at startup).

To duplicate an existing slave:

  1. Stop the existing slave and record the slave status information, particularly the master binary log file and relay log file positions. You can view the slave status either in the Performance Schema replication tables (see Section 25.11.11, “Performance Schema Replication Tables”), or by issuing SHOW SLAVE STATUS as follows:

    mysql> STOP SLAVE;
    mysql> SHOW SLAVE STATUS\G
  2. Shut down the existing slave:

    shell> mysqladmin shutdown
  3. Copy the data directory from the existing slave to the new slave, including the log files and relay log files. You can do this by creating an archive using tar or WinZip, or by performing a direct copy using a tool such as cp or rsync.

    Important
    • Before copying, verify that all the files relating to the existing slave actually are stored in the data directory. For example, the InnoDB system tablespace, undo tablespace, and redo log might be stored in an alternative location. InnoDB tablespace files and file-per-table tablespaces might have been created in other directories. The binary logs and relay logs for the slave might be in their own directories outside the data directory. Check through the system variables that are set for the existing slave and look for any alternative paths that have been specified. If you find any, copy these directories over as well.

    • During copying, if files have been used for the master info and relay log info repositories (see Section 16.2.4, “Replication Relay and Status Logs”), which is the default in MySQL 5.7, ensure that you also copy these files from the existing slave to the new slave. If tables have been used for the repositories, the tables are in the data directory.

    • After copying, delete the auto.cnf file from the copy of the data directory on the new slave, so that the new slave is started with a different generated server UUID. The server UUID must be unique.

    A common problem that is encountered when adding new replication slaves is that the new slave fails with a series of warning and error messages like these:

    071118 16:44:10 [Warning] Neither --relay-log nor --relay-log-index were used; so
    replication may break when this MySQL server acts as a slave and has his hostname
    changed!! Please use '--relay-log=new_slave_hostname-relay-bin' to avoid this problem.
    071118 16:44:10 [ERROR] Failed to open the relay log './old_slave_hostname-relay-bin.003525'
    (relay_log_pos 22940879)
    071118 16:44:10 [ERROR] Could not find target log during relay log initialization
    071118 16:44:10 [ERROR] Failed to initialize the master info structure

    This situation can occur if the --relay-log option is not specified, as the relay log files contain the host name as part of their file names. This is also true of the relay log index file if the --relay-log-index option is not used. See Section 16.1.6, “Replication and Binary Logging Options and Variables”, for more information about these options.

    To avoid this problem, use the same value for --relay-log on the new slave that was used on the existing slave. If this option was not set explicitly on the existing slave, use existing_slave_hostname-relay-bin. If this is not possible, copy the existing slave's relay log index file to the new slave and set the --relay-log-index option on the new slave to match what was used on the existing slave. If this option was not set explicitly on the existing slave, use existing_slave_hostname-relay-bin.index. Alternatively, if you have already tried to start the new slave after following the remaining steps in this section and have encountered errors like those described previously, then perform the following steps:

    1. If you have not already done so, issue STOP SLAVE on the new slave.

      If you have already started the existing slave again, issue STOP SLAVE on the existing slave as well.

    2. Copy the contents of the existing slave's relay log index file into the new slave's relay log index file, making sure to overwrite any content already in the file.

    3. Proceed with the remaining steps in this section.

  4. When copying is complete, restart the existing slave.

  5. On the new slave, edit the configuration and give the new slave a unique server ID (using the server-id option) that is not used by the master or any of the existing slaves.

  6. Start the new slave server, specifying the --skip-slave-start option so that replication does not start yet. Use the Performance Schema replication tables or issue SHOW SLAVE STATUS to confirm that the new slave has the correct settings when compared with the existing slave. Also display the server ID and server UUID and verify that these are correct and unique for the new slave.

  7. Start the slave threads by issuing a START SLAVE statement:

    mysql> START SLAVE;

    The new slave now uses the information in its master info repository to start the replication process.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Gary Fischman on November 11, 2013
This process can produce unpredictable results if the new slave is running a different version of MySQL from the existing slave. Instead, use mysqldump to create the new slave. It takes longer to import the dump file, but the result is a reliable, stable slave.

Procedure is documented in the mysqldump docs.
http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
  Posted by laraki fissel on April 25, 2017
Hello, especially do not forget to delete the auto.cnf file before running mysql on the new slave, and let mysql create another one with a new UUID
Sign Up Login You must be logged in to post a comment.