Sometimes 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 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.
On Master server we will make few changes in file
[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
server-id has to be unique (in our case ‘1’ is reserved for Master, ‘2’ for Slave).
binlog-ignore-db specifies which databases are to be ignored for replication – we won’t replicate database
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:
Position. We’ll need them during configuration of Slave server.
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).
Now we setup the slave server = target where data will be replicated.
We have to add the following lines into
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
Position from command
SHOW MASTER STATUS; executed previously on Master.
File goes into
Position will be used for
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.