Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 39.1Mb
PDF (A4) - 39.2Mb
PDF (RPM) - 38.3Mb
HTML Download (TGZ) - 10.7Mb
HTML Download (Zip) - 10.8Mb
HTML Download (RPM) - 9.5Mb
Man Pages (TGZ) - 223.4Kb
Man Pages (Zip) - 331.3Kb
Info (Gzip) - 3.6Mb
Info (Zip) - 3.6Mb
Excerpts from this Manual Setting Up Replication Slaves

The following sections describe how to set up slaves. Before you proceed, ensure that you have: Setting the Replication Slave Configuration

Each replication slave must have a unique server ID, as specified by the server_id system variable. If you are setting up multiple slaves, each one must have a unique server_id value that differs from that of the master and from any of the other slaves. If the slave's server ID is not already set, or the current value conflicts with the value that you have chosen for the master server or another slave, you must change it. With the default server_id value of 0, a slave refuses to connect to a master.

You can change the server_id value dynamically by issuing a statement like this:

SET GLOBAL server_id = 21;

If the default server_id value of 0 was set previously, you must restart the server to initialize the replication slave with your new nonzero server ID. Otherwise, a server restart is not needed when you change the server ID, unless you make other configuration changes that require it. For example, if binary logging was disabled on the server and you want it enabled for your slave, a server restart is required to enable this.

If you are shutting down the slave server, you can edit the [mysqld] section of the configuration file to specify a unique server ID. For example:


A slave is not required to have binary logging enabled for replication to take place. However, binary logging on a slave means that the slave's binary log can be used for data backups and crash recovery. Slaves that have binary logging enabled can also be used as part of a more complex replication topology. If you want to enable binary logging on a replication slave, use the log-bin option in the [mysqld] section of the configuration file. A server restart is required to start binary logging on a server that did not previously use it. Setting the Master Configuration on the Slave

To set up the slave to communicate with the master for replication, configure the slave with the necessary connection information. To do this, execute the following statement on the slave, replacing the option values with the actual values relevant to your system:

    ->     MASTER_HOST='master_host_name',
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;

Replication cannot use Unix socket files. You must be able to connect to the master MySQL server using TCP/IP.

The CHANGE MASTER TO statement has other options as well. For example, it is possible to set up secure replication using SSL. For a full list of options, and information about the maximum permissible length for the string-valued options, see Section, “CHANGE MASTER TO Statement”.

The next steps depend on whether you have existing data to import to the slave or not. See Section, “Choosing a Method for Data Snapshots” for more information. Choose one of the following: Setting Up Replication between a New Master and Slaves

When there is no snapshot of a previous database to import, configure the slave to start the replication from the new master.

To set up replication between a master and a new slave:

  1. Start up the MySQL slave and connect to it.

  2. Execute a CHANGE MASTER TO statement to set the master replication server configuration. See Section, “Setting the Master Configuration on the Slave”.

Perform these slave setup steps on each slave.

This method can also be used if you are setting up new servers but have an existing dump of the databases from a different server that you want to load into your replication configuration. By loading the data into a new master, the data is automatically replicated to the slaves.

If you are setting up a new replication environment using the data from a different existing database server to create a new master, run the dump file generated from that server on the new master. The database updates are automatically propagated to the slaves:

shell> mysql -h master < fulldb.dump Setting Up Replication with Existing Data

When setting up replication with existing data, transfer the snapshot from the master to the slave before starting replication. The process for importing data to the slave depends on how you created the snapshot of data on the master.

Choose one of the following:

If you used mysqldump:

  1. Start the slave, using the --skip-slave-start option so that replication does not start.

  2. Import the dump file:

    shell> mysql < fulldb.dump

If you created a snapshot using the raw data files:

  1. Extract the data files into your slave data directory. For example:

    shell> tar xvf dbdump.tar

    You may need to set permissions and ownership on the files so that the slave server can access and modify them.

  2. Start the slave, using the --skip-slave-start option so that replication does not start.

  3. Configure the slave with the replication coordinates from the master. This tells the slave the binary log file and position within the file where replication needs to start. Also, configure the slave with the login credentials and host name of the master. For more information on the CHANGE MASTER TO statement required, see Section, “Setting the Master Configuration on the Slave”.

  4. Start the slave threads:

    mysql> START SLAVE;

After you have performed this procedure, the slave connects to the master and replicates any updates that have occurred on the master since the snapshot was taken.

If the server_id system variable for the master is not correctly set, slaves cannot connect to it. Similarly, if you have not set server_id correctly for the slave, you get the following error in the slave's error log:

Warning: You should set server-id to a non-0 value if master_host
is set; we will force server id to 2, but this MySQL server will
not act as a slave.

You also find error messages in the slave's error log if it is not able to replicate for any other reason.

The slave stores information about the master you have configured in its master info repository. The master info repository can be in the form of files or a table, as determined by the value set for the master_info_repository system variable. When a slave runs with master_info_repository=FILE, two files are stored in the data directory, named and If master_info_repository=TABLE instead, this information is saved in the master_slave_info table in the mysql database. In either case, do not remove or edit the files or table. Always use the CHANGE MASTER TO statement to change replication parameters. The slave can use the values specified in the statement to update the status files automatically. See Section 16.2.4, “Replication Relay and Status Logs”, for more information.


The contents of the master info repository override some of the server options specified on the command line or in my.cnf. See Section 16.1.6, “Replication and Binary Logging Options and Variables”, for more details.

A single snapshot of the master suffices for multiple slaves. To set up additional slaves, use the same master snapshot and follow the slave portion of the procedure just described.