Menu

Subscribe

How to set up master slave replication in MySQL

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:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';

Flush privileges to make the new user available. Then you do not have to restart MySQL:

mysql> FLUSH PRIVILEGES;

Check if the master is running:

mysql> SHOW MASTER STATUS;

The slave 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:

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

server-id = 2

Write the location of your relay log, which the slave use to log the events it creates from the master bin log:

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

Write the location of your log_bin, which will also write the events of what is going on, on the slave:

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

It is just important that the binlog_do_db or binlog-ignore-db is configured in the same way as the master.

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:

Now it is time to setup your slave to replicate from the master. So use the CHANGE MASTER statement like the following:

mysql> CHANGE MASTER TO MASTER_HOST='12.34.56.789',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 0;
Explained
  • MASTER_HOST is the ip-address of the remote master MySQL server.
  • 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:

mysql> STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;

Existing databases

Before doing the steps above, do the following for preparation for existing databases.

Master

Go to your MySQL shell, and use the following line to lock all tables, so data is consistent:

mysql> FLUSH TABLES WITH READ LOCK;

Go to your normal shell, and use the mysqldump tool to dump the database you want to replicate:

$ mysqldump -u root -p --opt databasename > databasename.sql

When the dump is done, and you got your databasename.sql file, go to your MySQL shell again, and unlock the tables:

UNLOCK TABLES;

Transfer the databasename.sql file (the dump) to the slave server. You could eg. use scp.

Slave

Go to your MySQL shell at your slave, and create the database you want to import from dump:

mysql> CREATE DATABASE databasename;

Exit to your normal shell again, and import the dump you just transfered from the master to the slave:

$ mysql -uroot -p databasename < /dir/transfered/to/databasename.sql

Then you can start from the top at The master server.