MySQL Master Slave replication

MySQLSometimes it might be usefull or even needed to replicate data from one place to another. If you have data you want to replicate in MySQL database, you are lucky, because replication is integrated in MySQL by default and you just have to set it up.

What is the reason for replication?

Replication can be benefitial for several reasons, ie. you have database with millions of users and during time you find out that just MySQL backups are simply not enough or you want to handle high database load. So what exactly is MySQL replication helping to solve ?

  • load of primary MySQL
  • need of having the same data on more places (for read only)
  • data backup into separate machine which can be used in case of primary machine failure

Replication setup

Replication is quite easy – in general it’s all about modification of few lines in MySQL config files and execution few SQL queries for establishing communication between master and slave.

Master config

On Master server we will make few changes in file /etc/mysql/my.cnf

In section [mysqld] we have to comment out the following command:

#bind-address = 127.0.0.1

By commenting this line out we open potential security hole because this parameter ensured that connection to MySQL server was allowed to localhost users only. That’s why it’s important to keep and monitor the security policies of users inside MySQL now

and add the following lines:

log-bin
server-id=1
binlog-ignore-db = mysql

Parameter server-id has to be unique (in our case ‘1’ is reserved for Master, ‘2’ for Slave).

Parameter binlog-ignore-db specifies which databases are to be ignored for replication – we won’t replicate database mysql only.

Our next step will be calling SQL queries which setup the user who will execute(will be allowed to) the replication.

mysql> mysql GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication'@'%' IDENTIFIED BY 'password';
mysql> mysql FLUSH PRIVILEGES;

The above query is rather illustrative – for a specific deployment it’d be appropriate to assign exact IP allowed to connect and define exact set of tables allowed to be read.

The last thing on Master is restart of MySQL server.

# service mysql restart

If you want to check if Master server is ready for replication you can use the following command:

mysql> mysql SHOW MASTER STATUS;

…which should produce something similar to the following:

+--------------+----------+--------------+------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------+----------+--------------+------------------+
| node1.000002 | 131      |              |                  |
+--------------+----------+--------------+------------------+

In this table we’re interested in two values: File and Position. We’ll need them during configuration of Slave server.

Warning: The Position value changes with every write to master database. This is a reason why the master database should be locked for writing during replication setup (before the slave server is started).

Slave config

Now we setup the slave server = target where data will be replicated.

We have to add the following lines into /etc/mysql/my.cnf:

server-id=2
master-host=192.168.1.1
master-user=replication
master-password=password
replicate-do-db=databaze
log-warnings

The config is similar to Master server, we just added definitions for IP address, user and password used by Slave for connection to Master.

Probably the most interesting parameter is replicate-do-db which specifies the set of databases we would like to replicate on Slave server.

Warning: If there are already some data on Master server, it’s necessary to make SQL dump on master which you subsequently load into Slave – replication is not made backwards (for already existing data).

Noe we have to create SQL query which establishes communication between Master and Slave servers. Here we find as useful the File and Position from command SHOW MASTER STATUS; executed previously on Master.

The File goes into master_log_filePosition will be used for master_log_pos.

The final query will be similar to the following:

mysql> mysql CHANGE MASTER TO master_host = '192.168.1.1',
                        master_user='replication',
                        master_password='password',
                        master_log_file='node1.000002',
                        master_log_pos=131;

Now we start replication on the Slave server:

mysql> mysql START SLAVE;

Using the following command we can check everything is ok:

mysql> mysql SHOW SLAVE STATUS\G;

From now on any change done on Master will be replicated (with small delay) on the Slave server.

Leave a Reply