In the previous article of this series – Advantages of MySQL Server Replication, we have discussed the concept, basics and advantages of MySQL Server Replication. In this article, we will be seeing how to setup and implement a well configured MySQL master-slave replication for scalability and performance.

As a quick recap of replication basics – it is the setting up a master server and one or more slave servers. The master stores the original data and keeps track of and accepts all the WRITEs – updates, deletes, alters, etc. The slaves synchronize with the master independently, and provide READ access to clients. Thus the majority of the load – READs are shared across slaves, while master handles only WRITEs. A Load Balancer routes client requests to appropriate slave.

The built-in method for master-slave configuration is the Binary Log File Position based replication that we are going to address here.

Binary Log File Position Based Replication

In this implementation, all the events (database operations) performed on the master are recorded in the binary log file. Slaves periodically request, connect and pull these recorded events from master’s binary log file and execute or replay them on the slave to replicate the changes. To keep track of the replication synchronization, slaves store the positions of the binary file where last read was performed and resume from that point onwards.

Configure the Master

The first step is to enable binary logging by setting the option log-bin to a file name (the binary log) and create a unique master server ID by setting server-id to a positive integer id: 1 – (2³²)-1. Each and every server (master and slaves) need to have their own ids to work in replication. For this, edit the my.cnf or my.ini file to set the configuration parameters log-bin and server-id.

Ensure that the option skip-networking is not turned on for master, since this will prevent slaves from connecting to master.
Restart the master server to activate the changes.

Create a User in Master for Handling Replication Requests

Slaves will use this user to connect to the master. This user should have the REPLICATION SLAVE privilege. It is needed by slaves to request binary log updates from master. Either a single user for all slaves or one user per slave can be created.

Warning: Since the replication user credentials are stored as plain text in the master info repository files (with default names master.info and relay-log.info) or table with slave servers, this particular user should be granted only replication privileges to prevent compromising other users.

Example: creating a user in master named “replicator” with password “mypass” that slaves can used for connecting to master from a domain “example.com”.

Obtain the Coordinates of Replication Master Binary Log

The name of master’s binary log file and position of next event to be read by slave, also called coordinates of the master binary log are needed to setup the slave. Below are the steps to obtain the binary log coordinates of master:

1. Start a new session on master using the mysql CLI client. Flush all the tables and prevent further WRITEs using table locks.
mysql> FLUSH TABLES WITH READ LOCK;

2. Leave this session as it is to maintain the lock and start a new session to get the binary log file name and position (coordinates)
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73 | test | manual,mysql |
+------------------+----------+--------------+------------------+

Replicate existing data for running master server

If master is already running and has data associated with it, perform the below steps to copy the data for synchronizing the slave, before setting it up, while maintaining the above LOCKs to prevent data modifications during the synch.

Use mysqldump to take the current data snapshot along with –master-data option that adds the CHANGE MASTER TO statement needed by slave for replication.

This command dumps all databases. Use –databases option to select particular database(s) or –ignore-table to exclude some tables.

Setup Replication Slave Servers

1. Release the read lock on master, from the session that created it as mentioned above:
mysql> UNLOCK TABLES;

2. Set the unique slave server id on my.cnf or my.ini:
[mysqld]
server-id=2

3. Restart the server.

4. Setup the master configuration on the slave:

mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host_name',
-> MASTER_USER='replication_user_name',
-> MASTER_PASSWORD='replication_password',
-> MASTER_LOG_FILE='recorded_log_file_name',
-> MASTER_LOG_POS=recorded_log_position;

For setting up replication with a new master that does not have existing data and if data resides on another server, run the dump on new master:

Then the data will be automatically replicated to slaves.

For setting up replication with an existing master with data, perform the below steps:

1. Start the slave with –skip-slave-start so that replication is not started when starting the slave:

2. Import the previously exported master dump file:
shell> mysql < fulldb.dump

3. Start the slave threads
mysql> START SLAVE;

The slave is now running and will connect to the master for further replications.

Replication Administration

To verify slaves are properly setup and are running:
mysql> SHOW SLAVE STATUS\G

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master1
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 931
Relay_Log_File: slave1-relay-bin.000056
Relay_Log_Pos: 950
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Etc…

To check the status of connected slaves, at master:
SHOW PROCESSLIST \G;

*************************** 4. row ***************************
Id: 10
User: root
Host: slave1:58371
db: NULL
Command: Binlog Dump
Time: 777
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL

If the slave is started with –report-host option and connected to the master, below statement provides information about slaves for this master:
mysql> SHOW SLAVE HOSTS;

+-----------+--------+------+-------------------+-----------+
| Server_id | Host | Port | Rpl_recovery_rank | Master_id |
+-----------+--------+------+-------------------+-----------+
| 10 | slave1 | 3306 | 0 | 1 |
+-----------+--------+------+-------------------+-----------+

1 row in set (0.00 sec)

Stop a slave or pause replication:
mysql> STOP SLAVE;

To start a slave:
mysql> START SLAVE;