Replication in MySQL

Replication allows databases on one MySQL server to be duplicated on another. Starting in Version 3.23.15, MySQL supports for one-way replication. One server acts as the master, while one or more other servers act as slaves. The master server keeps a binary log of updates. It also maintains the index file of binary logs to keep track of log rotation. Each slave upon connecting informs the master where it left off since the last successfully propagated update, catches up any updates that have occurred since then, and then blocks and waits for the master to notify it of new updates. When using replication, all updates to the tables that are replicated should be performed on master server.

Benefits of Replication:

q  Robustness: is increased with a master/slave setup. In the event of problems you can switch to the Slave as a backup.

q  Speed: The extra speed is achieved by splitting the load for processing client queries between master and slave servers. SELECT queries may be sent to the slave and the queries that modify data should still be sent to the master, so that master and slave to not get out of synchronization.

q  System Administration: You can get non disturbing backups of system by doing backup of slave instead of doing it on master.

Replication Implementation Overview:

MySQL replication is based on the master server keeping track of all changes to your database (updates, deletes, etc.) in the binary logs. Therefore, to use replication, you must enable binary logging on the master server. Each slave server receives from the master the saved updates that the master has recorded in its binary log, so that the slave can execute the same updates on its copy of the data. Binary log is simply a record starting from the fixed point in time at which you enable binary logging. Any slaves that you set up will need copies of the databases on your master as they existed at the moment you enabled binary logging on the master. If you start your slaves with data that is not the same as what was on the master when the binary log was started, your slaves may fail.

After the slave has been set up with a copy of the master's data, it will simply connect to the master and wait for updates to process. If the master goes away or the slave loses connectivity with your master, it will keep trying to connect periodically until it is able to reconnect and resume listening for updates. Each slave keeps track of where it left off. The master server has no knowledge of how many slaves there are or which ones are up to date at any given time.

Replication Setup:

1.    Make sure you have a recent version of MySQL installed on the master and slaves, and that these versions are compatible according to the table shown below.

Master/ Slave replication compatibility between different versions of MySQL

		Master	Master	Master
		3.23.33 and up	4.0.3 and up or any 4.1.x	5.0.0
Slave	3.23.33 and up	yes	no	no
Slave	4.0.3 and up	yes	yes	no
Slave	5.0.0	yes	yes	yes

But it is recommended using same version for both the master and the slave.

2.    Make sure the [mysqld] section of the `my.cnf' file on the master host includes a log-bin option. The section should also have a server-id=master_id option, where master_id must be a positive integer value from 1 to 2^32 - 1.

[mysqld]

log-bin

server-id=1

If those options are not present, add them and restart the server.

3.    Take the full database backup from Master server using option –master-data=2. Below is the command for same.

mysqldump –u –p –all-database –-master-data=2>fulldump.sql

4.    Set up an account on the master server that the slave server can use to connect. This account must be given the REPLICATION SLAVE privilege.

Suppose that your domain is mydomain.com and you want to create an account with a username of repl such that slave servers can use the account to access the master server from any host in your domain using a password of slavepass. To create the account, use GRANT statement:

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.*

à TO repl@'%.mydomain.com' IDENTIFIED BY 'slavepass;

5.    Stop the server that is to be used as a slave server and add the following to its `my.cnf' file:

[mysqld]

server-id=2

The slave_id value, like the master_id value, must be a positive integer value from 1 to 2^32 - 1. In addition, it is very important that the ID of the slave be different than the ID of the master. If you are setting up multiple slaves, each one must have a unique server-id value that differs from that of the master and from each of the other slaves.

6.    Start the slave server.

#   mysql –u root –p

7.    Restore the database to slave using commands

mysql –u –p <

8.    Execute the following statement on the slave, replacing the values within <> with the actual values relevant to your system:

mysql> CHANGE MASTER TO

    ->    MASTER_HOST='',

    ->     MASTER_USER='',

    ->     MASTER_PASSWORD='',

    ->     MASTER_LOG_FILE='',

    ->     MASTER_LOG_POS=;

This statement creates master account on slave.

9.    Start the slave threads.

mysql> START SLAVE;

If you have forgotten to set server-id for the master, slaves will not be able to connect to it.

If you have forgotten to set server-id for the slave, you will get the following error in its error log:

Warning: one should set server_id to a non-0 value if

master_host is set.  The server will not act as a slave.

You will also find error messages in the slave's error log if it is not able to replicate for any other reason. Once a slave is replicating, you will find in its data directory one file called `master.info' and another called `relay-log.info'. The slave uses these two files to keep track of how much of the master's binary log it has processed.