Menu

How to set up master slave replication in MySQL

05 September 2014

Master slave replication will automatically copy data from a master MySQL server to a slave MySQL server, and is very useful for a lot of things. Could be for a cluster setup, for a development enviroment, a database move with almost no downtime, or just for backup.

Installation

Install MySQL like always. Master/slave replication has been a part of MySQL for a long time. I would recommend to pick one of the newer versions tho.

In Debian (apt-get), just do:

$ sudo apt-get install mysql-server mysql-client

In CentOS/Redhat (yum) do:

# sudo yum install mysql-server mysql-client

Install with the mysql_secure_installation util, which will get your MySQL kickstarted in a good way. Before calling this util, make sure MySQL is actually started:

$ /etc/init.d/mysql start

The master server

You have to make a few additions in my.cnf (the MySQL configuration file), before you are ready to setup replication. Insert the following lines:

Comment the bind-address line, if you have it:

#bind-address = 127.0.0.1

Add a server id to your MySQL database - i usually use 1 for the master, and 2 for the slave:

server-id = 1

Write the location of your log_bin, which the slave will use to replicate:

log_bin = /var/log/mysql/mysql-bin.log

To replicate a specific database use:

binlog_do_db = newdatabase

To replicate all databases except some (this case only informationschema):

binlog-ignore-db = informationschema

Save your my.cnf-file, and make sure the /var/log/mysql directory exists before restarting MySQL. If not, create it and change ownership to mysql:

mkdir -p /var/log/mysql
chown -R mysql:mysql /var/log/mysql

Restart MySQL:

/etc/init.d/mysql restart

The next step is inside of the mysql shell. So go login to your MySQL shell.

$ mysql -uroot -p

Write your password when prompted, and continue into the MySQL shell:

Create the user, used to replicate, and grant it permissions to do so. I like to call mu user slave_user so i am not in doubt, and then change "password" with a password of your choice:

Explained

MASTER_USER is the user on the master, which we just created. In my case slave_user.

MASTER_PASSWORD is the password you chose.

MASTER_LOG_FILE is from what binlog at the master you should start replicating from. You can find this by calling SHOW MASTER STATUS; from the master. It will be easy to find in the output.

MASTER_LOG_POS is the position in the log file from what point the slave should replicate. Just start at 0. If you want, you can start at the position the master is at. You can find this by calling SHOW MASTER STATUS; from the master. It will be easy to find in the output.

To actually start the slave:

mysql> START SLAVE;

To check if everything is fine and synced like you want it (seconds_behind_master is important - should be as small a value as possible):

mysql> SHOW SLAVE STATUS\G

If something makes the slave impossible to replicate further, you can ignore the error (important to check up on what error it is first), with the following line: