MySQL Replication : Setting up a Simple Master / Slave

It is often necessary, when designing high availability environments to implement a database replication scenario with MySQL.

This simple how-to is intended to setup a simple master / slave relationship.

PREPARATION OF MASTER SERVER

1. Select a master server. It can be either one.

2. Make sure all databases that you want to replicate to the slave already exist! The easist way is to just copy the database dirs inside your MySQL data directory intact over to your slave, and then recursively chown them to “mysql:mysql”. Remember, the binary structures are file-system dependant, so you can’t do this between MySQL servers on different OS’s. In this instance you will want to use mysqldump most likely.

3. Create /etc/my.cnf if you do not already have one:

4. Permit your slave server to replicate by issuing the following SQL command (substituting your slave’s IP and preferred password):

5. Shut down and restart MySQL daemon and verify that all is functional.

PREPARATION OF SLAVE

1. Create /etc/my.cnf if you do not already have one:

2. Shut down and restart MySQL on slave.

3. Issue the following SQL command to check status:

Ensure that the following two fields are showing this :

If not, try to issue the following command :

This will manually start the slave process. Note that only updated tables and entries after the slave process has started will be sent from the master to the slave — it is not a differential replication.

TESTING

Just update some data on the master, and query that record on the slave. The update should be instantaneous.

Test creating a table on the master MySQL server database :

And check the database on the slave to ensure that the recently created table on the master was replicated properly.