MySQL Replication

The native replication built into MySQL is a powerful tool to keep an identical copy or copies of a database across multiple servers. MySQL replication is useful if you want to have an exact copy of a live database for backup or application purposes. In the event of a total failure of the primary master database server, the application utilizing the database can be manually pointed to the backup slave database. Replication is also useful to high traffic sites where splitting the database reads across many servers is beneficial for performance.

Here are the steps of how to set up your databases replication. Be aware that variables listed below may be slightly changed across different MySQL versions.

Step 1. Configure the master server

log-bin = /var/lib/mysql/master-bin.log
log-bin-index = /var/lib/mysql/master-bin.index.log
server-id = 1
expire_logs_days = 7

log-bin sets – path and name for your binary logs which are used for replication.
log-bin-index – sets path and name for the index file which contains list of all binary log names
server-id is a uniq server identificator that must be different from all other id’s
expire_logs_days helps to rotate your binary logs

Now, restart your MySQL server by issuing the following command:

service mysqld restart

Let’s create a user with replication privileges on the master server. Start by logging into MySQL command line:

master> CREATE USER repl_user;
master> GRANT REPLICATION SLAVE ON *.* TO repl_user IDENTIFIED BY 'password';

Step 2. Configure the slave server
Add or modify the following lines in your my.cnf:

server-id = 2
relay-log = /var/lib/mysql/slave-relay-bin.log
relay-log-index = /var/lib/mysql/slave-relay-bin.index.log

Restart your MySQL server:

service mysqld restart

Step 3. Connect both servers and start replication

Connect two servers:

slave> CHANGE MASTER TO
 -> MASTER_HOST = 'master_IP_address',
 -> MASTER_PORT = 3306,
 -> MASTER_USER = 'repl_user',
 -> MASTER_PASSWORD = 'password';

Finally, start replication:

slave> START SLAVE;

That’s all. MySQL replication has been setup and ready for use.