How to configure Master Slave replication in MySQL


Replication enables data from one MySQL server, called the master to be replicated to one or more MySQL servers, called slaves. MySQL database replication is used to spread the load among multiple slaves to improve performance. MySQL allows slave nodes to have read access on the replicated databases. This means, that you can load balance the requests so that the master will handle the write requests while the slave(s) will manage the reading.

I suppose MySQL is already installed in the Master as well as slave server. Now, follow the steps to configure Master Slave replication:

Master server Setup:

1. Login to the MySQL shell and create a new user for replication using following queries:

[root@master-node~]# mysql

mysql> GRANT REPLICATION SLAVE ON *.* to 'replication_user'@'%' IDENTIFIED BY 'repl_password';
mysql> FLUSH PRIVILEGES;
mysql> quit;

2. Dump the data from the Master server and copy it to the Slave server using mysqldump:

[root@master-node~]# mysqldump [database] > [database].sql

***Note that the [database] field is to be replaced with the actual name of each database, one at a time. You can get the list of the databases present in the MASTER SERVER by typing following commands in the mysql prompt:

mysql> SHOW DATABASES;

3. Now we jump to the configuration file located at "etc/my.cnf" and add the following lines under the [mysqld] heading. 

log-bin=mysql-bin
binlog-do-db="[database]" 
server-id=1

The first one(log-bin) will enable the binary logging feature and use the provided name for it. The second one is used to name the database that the log will monitor. And the last one to identify this server with an ID during the MySQL communication between the two(or more) servers. After doing this, restart the service.

4. [root@master-node~]# /etc/init.d/mysqld restart

5. Get the binary position of the log file using following query:

[root@master-node~]# mysql

mysql> SHOW MASTER STATUS;

The output should look something like this:

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 98 | | mysql |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec) 

That shows the binary log file name as well as what databases are affected by it and what is the masterç—´ current position in that file. 

6. Copy the databases to your Slave server.

[root@master-node~]# scp [database].sql [Slave IP address]:/ 

Now we are pretty much done with the master setup. We can move to the slave node now.

Slave server Setup:

1. Edit the Slave server's mysql configuation file "/etc/my.cnf" to identify its server number, master host and user. Under the [mysqld] heading add the following lines:

server-id=2
master-host = [IP of Master Server]
master-user = replication_user
master-password = repl_password
master-port = 3306

2. Insert the data from the backup we took earlier from the master server. 

[root@slave-node~]# mysql [database] < [database].sql

3. Restart the mysql server service using following command:

[root@slave-node~]# /etc/init.d/mysqld restart

4. Now log in to Mysql and configure the Slave replication.

[root@slave-node~]# mysql

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS='98';
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

Now if we jump back to the master server and login to its MySQL shell we can see the connected slave using following query:

[root@master-node~]# mysql 

mysql> SHOW SLAVE HOSTS; 

All done. You can now use the master server as a common MySQL database server and see the replicated records on the slave server. :) 

Comments

Popular posts from this blog

SVN: File remains in conflict

HowTo: Enable extended logging for exim

Error: could not open mime types config file