![\"MySQL](\"https:\/\/storage.ghost.io\/c\/5f\/2f\/5f2f4d20-2abf-4534-8d40-7aa233aedd43\/content\/images\/2025\/03\/unnamed-1.png\")
<\/figure>\nHere is how MySQL replication works.<\/p>\n
- \n
- All database operations are copied to the master’s binary log.<\/li>\n
- Salves connect to the master and asks for the data.<\/li>\n
- The slave servers get the masters binary log.<\/li>\n
- Slaves then apply the binary log to its realy log.<\/li>\n
- The relay log is read by the SQL thread process and it applies all the operations\/data to the slave’s database and its binary log.<\/li>\n<\/ol>\n
TL;DR<\/h3>\n
- \n
- Replication can be Asynchronous or Semi-Synchronous<\/li>\n
- In asynchronous replication write latency is lower as the writes are acknowledged by master locally.<\/li>\n
- You can add more read replicas to improve the read throughput as your business grows.<\/li>\n
- Replication is not an HA solution. If the master goes down, there needs to be some work done to get another slave as a master. So this strategy can be used for business systems which can afford a small interruption.<\/li>\n<\/ol>\n
Use Cases<\/h3>\n
<\/p>\n
- \n
- This architecture is suitable for scaling MySQL<\/strong> with many read slaves.<\/li>\n
- For backup strategy<\/strong>:\n
- \n
- Backup solutions like mysqldump can cause locking problems when the backup is being done.<\/li>\n
- Replication allows you to reduce the load from the master as the replication slave will be involved in serving the application requests.<\/li>\n<\/ul>\n<\/li>\n
- For disaster recovery,<\/strong> replication is a great fit as you can set up a replication slave in a different region which asynchronously gets the master data. In an event of regional failures<\/strong>, we can bring up the database in a different region with the help of replication slave.<\/li>\n<\/ol>\n
<\/p>\n
Here is a reference architecture of a replication cluster with ETL, reporting and backup use cases.<\/p>\n
![\"MySQL](\"https:\/\/storage.ghost.io\/c\/5f\/2f\/5f2f4d20-2abf-4534-8d40-7aa233aedd43\/content\/images\/2025\/03\/nn09rfy9-1.png\")
<\/figure>\nMySQL Master Slave Replication Setup<\/h2>\n
In this tutorial, we will explain the step by step guide for setting up a MySQL master-slave replication. For the tutorial purpose, we will use three Nodes for the MySQL replication cluster. One master node and two slave nodes.<\/p>\n
Server Details:<\/p>\n
Master Node: 10.128.0.11\nSlave 01: 10.128.0.12\nSlave 02: 10.128.0.13<\/code><\/pre>\nPrerequisites:<\/h3>\n
- \n
- Minimum 2 \u00a0Nodes – one master & one slave. [Centos\/Redhat 7 or greater<\/strong>]<\/li>\n
- Connectivity to install packages using yum or through corp proxy if any.<\/li>\n
- Root access to the servers.<\/li>\n
- Each servers firewall should allow traffic on ports 22 and 3306.<\/li>\n<\/ol>\n
Install MySQL Server on all three nodes<\/h3>\n
Let’s install MySQL server on all the three nodes with specific configurations for master-slave replication.<\/p>\n
Execute the following steps on all the three nodes.<\/p>\n
Step 1:<\/strong> Install wget<\/p>\n
sudo yum install wget -y<\/code><\/pre>\nStep 2: <\/strong>To install MySQL, we need to add the MySQL community repo<\/a> which contains the actual MySQL community edition<\/p>\n
Download the repo<\/p>\n
wget https:\/\/dev.mysql.com\/get\/mysql57-community-release-el7-9.noarch.rpm<\/code><\/pre>\nInstall the repo<\/p>\n
sudo rpm -ivh mysql57-community-release-el7-9.noarch.rpm<\/code><\/pre>\nStep 3:<\/strong> Install MySQL server.<\/p>\n
sudo yum install mysql-server -y<\/code><\/pre>\nStep 4:<\/strong> Start and enable MySQL server.<\/p>\n
sudo systemctl start mysqld\nsudo systemctl enable mysqld<\/code><\/pre>\nStep 5:<\/strong> Get the default generated admin password from the mysqld.log file. This password has to be used in step 5 to change the default root password.<\/p>\n
sudo grep 'temporary password' \/var\/log\/mysqld.log<\/code><\/pre>\nStep 6: <\/strong>Setup MySQL password and other key configurations using the following command. All the steps are self-explanatory.<\/p>\n
mysql_secure_installation<\/code><\/pre>\nConfigure MySQL Master Node<\/h3>\n
First, we will configure the master node with the required configuration.<\/p>\n
The key configurations are<\/p>\n
- \n
- Binding static IP address to MySQL server.<\/li>\n
- Unique ID config for master. Each server in the cluster should be identified uniquely.<\/li>\n
- Enable Binary log – It contains information about the data changes made to MySQL.<\/li>\n
- Create a replication user that will be used by the slaves to replicate the data from the master.<\/li>\n<\/ol>\n
Note:<\/strong> In this installation, MySQL data dir will default to \/var\/lib\/mysql in the root file system. For production use cases, this path should be an external disk attached to the VM.<\/em><\/p><\/blockquote>\n
Step 1:<\/strong> Open \/etc\/my.cnf file.<\/p>\n
sudo vi \/etc\/my.cnf<\/code><\/pre>\nStep 2:<\/strong> Add the following three parameters under the [mysqld] section and save it. Replace 10.128.0.11 your server IP.<\/p>\n
bind-address = 10.128.0.11\nserver-id = 1\nlog_bin = mysql-bin<\/code><\/pre>\nStep 3:<\/strong> Restart the MySQL server for the configurations changes to take place.<\/p>\n
sudo systemctl restart mysqld<\/code><\/pre>\nStep 4:<\/strong> Check the MySQL status to make sure all the configurations are applied as expected without any errors.<\/p>\n
sudo systemctl status mysqld<\/code><\/pre>\nStep 5:<\/strong> Login to MySQL server as the root user.<\/p>\n
mysql -uroot -p<\/code><\/pre>\nStep 6:<\/strong> Create a user named
replicauser<\/code> with a strong password. This user will be used by the slaves to replicate the data from the master. Replace 10.128.0.11 with your master IP<\/p>\nCREATE USER 'replicauser'@'10.128.0.11' IDENTIFIED BY 'my-secret-password';<\/code><\/pre>\nStep 7:<\/strong> Grant privileges to the slave user for slave replication.<\/p>\n
GRANT REPLICATION SLAVE ON *.* TO 'replicauser'@'%' IDENTIFIED BY 'my-secret-password';<\/code><\/pre>\nStep 8:<\/strong> From the MySQL prompt, Check the master status. Note down the file [mysql-bin.000001<\/strong>] and Position[706<\/strong>] parameters from the output. It is required for the slave replication configuration.<\/p>\n
SHOW MASTER STATUS\\G<\/code><\/pre>\nThe output would look like the following.<\/p>\n
mysql> SHOW MASTER STATUS\\G\n*************************** 1. row ***************************\n File: mysql-bin.000001\n Position: 706\n Binlog_Do_DB:\n Binlog_Ignore_DB:\nExecuted_Gtid_Set:\n1 row in set (0.00 sec)<\/code><\/pre>\nConfigure MySQL Replication Slave Node<\/h3>\n
Execute the following steps in all the slaves.<\/p>\n
Step 1:<\/strong> Add the same configurations as the master to the \u00a0\/etc\/my.cnf file with the Slave Ip address and unique server ID.<\/p>\n
bind-address = 10.128.0.12\nserver-id = 2\nlog_bin = mysql-bin<\/code><\/pre>\nNote:<\/strong> If you more than one slave, make sure you replace the respective slave IP and add a unique server-id per slave.<\/p><\/blockquote>\n
- Minimum 2 \u00a0Nodes – one master & one slave. [Centos\/Redhat 7 or greater<\/strong>]<\/li>\n
- For backup strategy<\/strong>:\n
- This architecture is suitable for scaling MySQL<\/strong> with many read slaves.<\/li>\n