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”.
mysql> CREATE USER 'replicator'@'%.example.com' IDENTIFIED BY 'mypass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%.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.
shell> mysqldump --all-databases --master-data > dbdump.db
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:
3. Restart the server.
4. Setup the master configuration on the slave:
mysql> CHANGE MASTER TO
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:
shell> mysql -h master < fulldb.dump
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:
shell > service mysql start --skip-slave-start
shell > mysqld_safe –-skip-slave-start
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.
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;