Store
Community Documentation

v3 Knowledgebase

How to get MySQL Replication up and running.

This tutorial describes how to set up database replication in MySQL.

MySQL replication allows you to have an exact copy of a database from a master server on another server (slave), and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync. This is not a backup policy because an accidentally issued DELETE command will also be carried out on the slave; but replication can help protect against hardware failures though.

Server “black” (192.168.0.31) is to be our master and “blue” (192.168.0.34) the slave.

I want to say first that this is not the only way of setting up such a system. There are many ways of achieving this goal but this is the way I recommend.


Step 1: Edit the configuration files & start the MySQL Servers

The first step in setting up replication involves editing the “my.cnf” file on the servers that will serve as the master and slave. A default is provided with the MySQL installation but in case there is already a production MySQL database running on these servers, we provide local configuration files “master.cnf” and “slave.cnf” that will be used when starting up the MySQL servers.

At a minimum we’ll want to add two options to the [mysqld] section of the master.cnf file:

log-bin: in this example we choose black-bin.log
server-id: in this example we choose 1. The server cannot act as a replication master unless binary logging is enabled. The server_id variable must be a positive integer value between 1 to 2^32

master.cnf:

[mysqld]
server-id=1
log-bin=black-bin.log
datadir=/home/billy/mysql/master/data
innodb_flush_log_at_trx_commit=1
sync_binlog=1

Note: For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should also specify the innodb_flush_log_at_trx_commit=1, sync_binlog=1 options.

Next, you’ll need to add the server-id option to the [mysqld] section of the slave’s slave.cnf file. The server-id value, like the master_id value, must be a positive integer between 1 to 2^32, It is also necessary that the ID of the slave be different from the ID of the master. 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 each of the other slaves.

slave.cnf:

[mysqld]
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
datadir=/home/billy/mysql/slave/data

Now, start the MySQL servers using the service manager or directly from the command line if not being run as a service:

[billy@black ~]$ mysqld --defaults-file=/home/billy/mysql/master/master.cnf &

[billy@blue ~]$ mysqld --defaults-file=/home/billy/mysql/slave/slave.cnf&



Step 2: Create Replication User

Create an account on the master server that the slave server can use to connect.

This account must be given the REPLICATION SLAVE privilege:

[billy@black ~]$ mysql -u root --prompt='master> '
master> CREATE USER repl_user@192.168.0.34;
master> GRANT REPLICATION SLAVE ON *.* TO repl_user@192.168.0.34 IDENTIFIED BY 'billy';



Step 3: Initialize Replication

We are now ready to initialize replication on the slave; issue a CHANGE MASTER command:

slave> CHANGE MASTER TO MASTER_HOST='192.168.0.31',
-> MASTER_USER='repl_user',
-> MASTER_PASSWORD='billy',
-> MASTER_LOG_FILE='',
-> MASTER_LOG_POS=4;


Where:

MASTER_HOST: the IP or hostname of the master server, in this example blue or 192.168.0.31
MASTER_USER: this is the user we granted the REPLICATION SLAVE privilege to in Step 2, in this example, “repl_user”
MASTER_PASSWORD: this is the password we assigned to ”rep_user” in Step 2
MASTER_LOG_FILE: is an empty string (wouldn’t be empty if there were existing writes to be picked up from the master)
MASTER_LOG_POS: is 4 (would likely be different if there were existing writes to be picked up from the master)



Step 4: Start replication on the slave:

slave> start slave;


Step 5: Basic Checks

Now we are ready to perform a basic check to ensure that replication is indeed working. In this example we insert a row of data into the “simples” table on the master server and then verify that these new rows materialize on the slave server:

master> create database clusterdb;
master> create table clusterdb.simples (id int not null primary key) engine=ndb;
master> insert into clusterdb.simples values (999),(1),(2),(3);
slave> select * from clusterdb.simples;
+-----+
| id |
+-----+
| 1 |
| 2 |
| 3 |
| 999 |
+-----+