Implementing Master-Slave Replication:

Master-Slave replication is mainly for scale-out solutions- spreading the load among the multiple slaves to improve the performance.

And for Analytics – while the analysis of the information can take place on the slave without affecting the performance of the master.

The target uses for Replication in MySQL include:

1. Scale-out solutions: spreading the load among multiple slaves to improve performance. In this environment, all writes and updates must take place on the master server. Reads, however, may take place on one or more slaves
2. Data Security: Because data is replicated to the slave, and the slave can pause the replication process, it is possible to run backup services on the slave without corrupting the corresponding master data.
3. Analytics: while the analysis of the information can take place on the slave without affecting the performance of the master.

Replication Configuration:

Replication between servers in MySQL works through binary logging mechanism.

Each slave must connect to the master using a standard mysql username and password, so there must be a user account on master that the slave can use to connect, and the user needs only REPLICATION SLAVE privilege.

Assuming that username is slave, password is slave123 and Master host name is 192.168.36.1, Slave host name is 192.168.36.2.

On Master Server: (192.168.36.1)

1. Create a user for replication and grant REPLICATION SLAVE privilege.

GRANT REPLICATION SLAVE ON *.* TO ‘slave’@’192.168.36.1’ IDENTIFIED BY ‘slave123′;

Note: We can create a different user for each slave or we can use the same user for each slave that needs to connect and the user we want to use for replication has REPLICATION SLAVE privilege.

2. Setting the Replication Master Configuration.

Note: For replication to work you must enable binary logging on the master. If binary logging is not enabled, replication will not be possible as it is the binary log that is used to exchange data between the master and slaves.

Edit the configuration file on Master Server:  /etc/my.cnf

log-bin    = mysql-bin

Each server within a replication group must be configured with a unique server-id value.  Server-id is used to identify individual servers within the group.

And server-id must be an integer between 1 and (2³²)–1.

server-id = 1 # selection of these integers are entirely up to you

3. Setting the Replication Slave Configuration:

On Slave Server: (192.168.36.2)

The only option we must configure on slave is to set the unique server-id.

server-id=2

Note: If you do not specify a server-id value, it defaults to 0.  If you omit server-id (or set it explicitly to 0), a master refuses connections from all slaves, and a slave refuses to connect to a master. Thus, omitting server-id is good only for backup with a binary log.

We do not have to enable binary logging on the slave for replication to be enabled.

If we enable the binary logging on slave , we can user binary log for data backup and crash recovery on the slave. And also we can use the slave as  part of a more complex replication topology,  where the slave acts as a Master to other slaves.

log-bin= mysql-bin

4. Getting the replication master information:

1. Start the command-line and flush all tables and block write statements by executing  the statement :

flush tables with read lock;

1. Use the following MySQL statement to determine the current binary log file name and position.

show master status;

Ex: mysql-bin.0001 and position is 98

Note: This represents the replication coordinates at which the slave should begin processing new updates from the master.

On Master Server (192.168.36.1)

5. Take the database snapshot from the master with the tool mysqldump or whatever.

–         If you haven’t already locked the tables on the server to prevent statements that update data from executing start by executing

flush tables with read lock ;

–         In another session , use mysqldump to create a dump of all –databases or selected databases to replicate.

mysqldump –u root –p –all-databases –lock-all-tables  > db_dump.sql

Another alternative is use with the –master-data option, which automatically appends the change master to statement required on the slave to start the eplication process.

mysqldump –u root –p –all-databases  –master-data –lock-all-tables  > db_dump.sql

6. Restore the backup on slave server. ( 192.168.36.2)

mysql –u root –p < db_dump.sql    (OR)

On Master Server (192.168.36.1)

You will need either to copy the dump file to the slave, or to use the file from the master when connecting remotely to the slave to import the data.

mysql -h 192.168.36.2 < db_dump.sql

7.  After restoring the db dump start the slave :

CHANGE MASTER TO MASTER_HOST=’192.168.36.1’, MASTER_USER=‘slave’, MASTER_PASSWORD=‘slave123d’, MASTER_LOG_FILE= ‘mysql-bin.0001‘, MASTER_LOG_POS=98;

8.  After that, start the slave server.

Start slave;

9. Check the replication status with the following command

Show slave status\G

Note: To check the slave status on replication server, the user requires either the SUPER or REPLICATION CLIENT privilege.

Note: Check both the Slave_IO_Running & Slave_SQL_Running should be YES.

.