MySQL :: MySQL 5.5 Reference Manual :: 17.1.1.7 Setting Up Replication with New Master and Slaves

5.6

MySQL 5.5 Reference Manual / ... / Setting Up Replication with New Master and Slaves

17.1.1.7 Setting Up Replication with New Master and Slaves

The easiest and most straightforward method for setting up replication is to use new master and slave servers.

You can also use this method 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 will be automatically replicated to the slaves.

To set up replication between a new master and slave:

Configure the MySQL master with the necessary configuration properties. See Section 17.1.1.1, “Setting the Replication Master Configuration”.
Start up the MySQL master.
Set up a user. See Section 17.1.1.3, “Creating a User for Replication”.
Obtain the master status information. See Section 17.1.1.4, “Obtaining the Replication Master Binary Log Coordinates”.
On the master, release the read lock:
```
mysql> UNLOCK TABLES;
```
On the slave, edit the MySQL configuration. See Section 17.1.1.2, “Setting the Replication Slave Configuration”.
Start up the MySQL slave.
Execute a CHANGE MASTER TO statement to set the master replication server configuration. See Section 17.1.1.10, “Setting the Master Configuration on the Slave”.

Perform the slave setup steps on each slave.

Because there is no data to load or exchange on a new server configuration you do not need to copy or import any information.

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

shell> mysql -h master < fulldb.dump

PREV HOME UP NEXT

User Comments

Posted by Erik Runeland on February 28, 2008

I would like to clarify my understanding of the above scheme a bit, in case it may help someone else to get a better understanding. Hoping that I don't bring further confusion to the table.

I was a bit confused over this section since it talks about new servers and existing data in a way that to me was a bit similar to the other sections that talk about loading a snapshot on the slave and then starting replication from a specific binlog position.

The scheme above is intended to work only on new servers, which are either empty, or loaded with data from a sqldump file. The replication starts when the server is at zero and then the statements loaded from the dump file are replicated as the binlog starts filling.

The above scheme should also work for setting up replication against servers that have all the binlogs in place, i.e. hasn't deleted any of them. Deleting binlogs may be done after backing up the databases to preserve space.

Also, I think that there could be some more information on what configuration should be done in CHANGE MASTER on the slave as suggested in step 8.

Posted by Thomas Zenz on October 6, 2008

CHANGE MASTER TO MASTER_HOST='ServerIP/FQDN', MASTER_USER='ReplClient', MASTER_PASSWORD='ClientPassword', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;

Posted by David Hirst on March 18, 2010

Don't forget that at the very end of the slave configuration process to use "START SLAVE;" or else you will sit there wondering why the two servers are not doing anything!