In this tutorial we will cover the steps needed to setup master slave replication in MySQL on your CentOS 6.4, Debian or Ubuntu platform.

The MySQL replication allows data from source MySQL server to be replicated to one (or more) slave servers. Replication is asynchronous which means that slaves need not be connected permanently to receive updates from the master server. The main uses of MySQL replication are load spreading among few servers, data backup and analyzing the information without affecting the performance of the master server.

In this tutorial we assume that the MySQL service is already installed on both servers.

Requirements

  • CentOS 6.4, Debian or Ubuntu installed on your computer/server
  • SSH access (Command line access to the server)
  • root privileges
  • Basic skills for working on a Linux environment
  • MySQL servers installed on both servers

Configure Master MySQL server

CentOS 6.4:

We need to edit the main MySQL configuration file and set some required values inside:

where:

bind-address – the server's IP address. An easy way to find your server's IP address is to execute the following command:

server-id – unique ID which should not be used for another server withing the replication configuration
log_bin – the slave server will copy all changes registered inside that log
binlog_do_db – the name of the database which will be replicated

When ready save the file and restart the MySQL service:

After that we will need to grant privileges to the slave server:

You will be prompted to fill in the MySQL root password. After that inside the MySQL command prompts execute:

where slave is the MySQL username which will be used and password a desired password.

Don't close this session and open new one by accessing your server inside new window/tab. In short term we will use the new session to export the database.

Now inside the current tab (where we have executed the above MySQL commands) type:

We should note the above log and position information because we will use on the Slave server.

After that we can remove the lock:

Configure Slave MySQL server

We will need to access the MySQL prompt on this server and specify the Master server information:

Here we will provide the bind log and position information from the previous section:

where:

We can now start the slave thread:

Test the functionality of the service

In order to make sure that the configuration is successful we can use the command:

In case Slave_IO_State is “Waiting for master to send event” and Seconds_Behind_Master is a number but not “NULL” then the replication was successful.