Replication in MYSQL-Part 1

Setting up replication in MySQL is very simple. MySQL provides one-way asynchronous replication. In this setup one server acts as master and one or more servers act as slaves.

In my series of documents I will show single master replication. In single master replication, the master records the changes in the binary log files and maintain an index of those files to keep track of log rotation.

The binary log files stores the updates that are to be sent to the slave servers. The slave connects to the master and tells the master of the position from which it wants the updates. The slave receives any updates that has been taken place since the log position it specified, and apply those changes in it, and then blocks and waits for the master to notify it for new updates.

We can replicate all the databases in the master server or we can select particular database to replicate. We can even select only few tables in a database for replication.

NOTE:

1)      When we use replication make sure that all the updates to the tables that are replicated should be performed on the master server only.

2)      Make sure that master and slave MySQL server’s versions are compatible. It is advisable to have the version of the slave same or higher than the master.

In the first part of this series of documents, I will describe the simplest of all the replication. That is starting replication between two new installations.

 We have installed a new MySQL server and we want to replicate it to another server. As there is no data on a new server this process is very straightforward. I am using two windows server machines for this example. The process is same for other OS.

Following are the steps:

·         Install MySQL on the master server and slave server.
In this example, Master will be installed on a Windows 2003 server with IP 172.16.0.103 and Slave will be installed on a Windows 2003 server with IP 172.16.0.105. In this example I am using MySQL 5.0 server on both master and server.

Following are the sample screenshots to install the master MySQL server:

The same way we can install the slave MySQL server. But in slave we may reduce the number of concurrent connections and memory, CPU requirement of MySQL if we are not going to use this slave server for query purpose. If later we have to make this slave to master, we can increase the number of connections, same the case with memory, CPU requirement. Following are the screens where we can change the number of connections and memory/CPU requirement at the time of installation.

Create a replication account on the master server. With this account the slave will connect to the master. If we want to use the slave as the master in case the master server fails then we may create the replication account on the slave also

    Configure the master to start binary logging and give it an ID number.
Open MySQL’s configuration file my.ini (in case of Linux it is my.cnf) file of the master MySQL server, and add the following entries in [mysqld] section:
1) log-bin
This option tells MySQL to start binary logging. By default the binary log files are created in MySQL’s data directory. If we want to place the binary log files into some other place we can supply path and filename as follows
log-bin = "E:/Program Files/MySQL/MySQL Server 5.1/logbin/mysql-bin"
now the binary log files will be created in the E:\program files\mysql\mysql server 5.1\logbin folder and the files will be named as mysql-bin.xxxxxx (x is 0 to 9)
2) server-id = 1
server-id must be a positive integer value from 1 to 2³² – 1. In general I keep master’s server-id as 1

Restart the master server. Check the master server has created the index file and binary log file. If it doesn’t check the MySQL’s error file.

    Configure the slave

Open MySQL’s configuration file and add the following information:
1) server-id=2server-id must be a positive integer value from 1 to 2³² – 1. It is necessary that the slave’s server-id  must be different from the master’s server-id.
2) master-host=172.16.0.103Enter the master’s hostname or IP address.
3) master-user=replEnter the username of the replication account on the master server.
4) master-password=repl123
Enter the password of the replication account on the master server.
5) master-port=3306
Enter the port on which the master server is listening.

Restart the slave MySQL server.


We can skip the master-host, master-user, master-password and master-port options in MySQL’s configuration file. In that case we have to run the following command after adding the server-id and restarting the slave server.

    

     This should be sufficient to start the replication process.

     Run some insert/update queries on the master and verify the data in master is getting replicated in the slave.

জয় আই অসম,

প্রণব শর্মা