MySQL Master-Slave Replication

Master-slave replication can be used to replicate database to another server – as a hot standby backup, but it won’t offer any performance increase (all writes will still happen on one server).

Configuring Master Database

First, we set up MySQL config file

vi /etc/my.cnf

Find the following line to make MySQL listen on its network interface rather than on localhost:

bind-address = 127.0.0.1

change it to:

bind-address = [master-ip-address]

Next we need to set up server-id, which must be unique for every server participating in replication:

server-id = 1

Next step is setting up binary log. This log will contain all changes replicated from Master database to Slave database.

log-bin = /var/lib/mysql/mysql-bin

By default everything will be wirtten to the log. If you want to only replicate a specific database you will use binlog-do-db, and to replicate all databases except some, you must use binlog-ignore-db. In this example we only want to replicate database named “radius”.

binlog-do-db = radius

Close the file, restart the database

service mysql restart

On Red Hat and CentOS you will have to open up port 3306 on your local iptables

vi /etc/sysconfig/iptables

Add the following line to allow slave database to connect to master:

-A INPUT -s [slave-ip-address] -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT

Restart iptables

service iptables restart

Now we have to set up replication user. to do this, open up mysql console:

mysql -u root -p

Run the following command to create “replicator” user. Make sure to use a safe password!

GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%' IDENTIFIED BY 'replicator-password';

Now, force MySQL to reload privileges:

FLUSH PRIVILEGES;

Now we will lock and export the database. Locking is necessary to prevent accidential writes which could cause inconsistency between master and slave. Go to the database you want to replicate – in our example “radius”:

USE radius;

Lock all tables:

FLUSH TABLES WITH READ LOCK;

Now run the following command and note the output – this is the replication position from which slave will later start replication:

SHOW MASTER STATUS;

You will see a table similar to the one below:

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      856 | radius       |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Now let’s export the database, so we can load it up on slave. Open up new console to your master server and type:

mysqldump -u root -p --opt radius > radius.sql

Configuring Slave Database

Again, we start with setting up MySQL config file

vi /etc/my.cnf

Find the following line to make MySQL listen on its network interface rather than on localhost:
server-id has to be different than master:

server-id = 2

Next step is setting up binary and relay logs.

log-bin = /var/lib/mysql/mysql-bin
relay-log               = /var/lib/mysql/mysql-relay-bin.log
binlog_do_db            = radius

Close the file, restart the database

service mysql restart

Let’s create the same database on slave:

mysql -u root -p
CREATE DATABASE radius;

EXIT

Now we can import the database from the master server. You can use scp to copy the db from master to slave:

scp master-ip-address:/root/radius.sql /root/

Once file is copied, import it on slave:

mysql -u root -p radius < /root/radius.sql

We are now ready to start replication! Run mysql console first:

mysql -u root -p

We have to use the same log file name and log position that we’ve seen in SHOW MASTER STATUS output on master database:

CHANGE MASTER TO MASTER_HOST='[master-ip-address]',MASTER_USER='replicator', MASTER_PASSWORD='replicator-password', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=856;
SLAVE START;
SHOW SLAVE STATUS\G

You should see:

Slave_IO_State	Waiting for master to send event
...
Slave_IO_Running	Yes
Slave_SQL_Running	Yes

This means that replication is working. That’s it!

Do not forget to unlock tables on master!

Go back to your master server console and type:

UNLOCK TABLES;
EXIT

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.