MySQL replication ensures that changes on a main database (master) are automatically copied to one or more copies (slaves). This is used for failover protection, distributing read traffic, or as an extra backup layer. In this article, you’ll learn how replication works, how to set it up step by step, and which pitfalls to avoid.
What is MySQL replication
With MySQL replication, the master server sends a log of all write operations (the binary log) to one or more slave servers. The slave executes those same operations locally, keeping the data synchronized. This happens almost in real time, although there’s always a slight delay between the master and slave.
Replication is often used for: an up-to-date backup on another server, distributing read load across multiple servers, or setting up a failover environment. Note: replication is not a substitute for a full backup strategy, since faulty or harmful queries are also passed on to the slave.
Preparing the master
Before setting up replication, you need to prepare the master server. You do this by enabling binary logging and creating a separate user account that the slave uses to connect.
Open the MySQL configuration file (usually my.cnf or my.ini) and make sure the following settings are present under [mysqld]:
log_bin = mysql-bin
server_id = 1
Restart the MySQL service after modifying the configuration. Every server in a replication setup must have a unique server_id, so give the slave, for example, server_id = 2.
Setting up replication step by step
Follow the steps below to set up a working master-slave replication.
Create a replication user
Log in to the master and create a user that only has replication rights: CREATE USER 'replica'@'%' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON . TO 'replica'@'%';
Note the master status
Run SHOW MASTER STATUS; on the master and remember the file name and position. You’ll need this information to start the slave at the correct point.
Create a database export
Export the existing data using mysqldump so the slave starts with the same initial state. Import this file onto the slave server.
Connect the slave to the master
On the slave, run the following, using the information from step 2: CHANGE MASTER TO MASTER_HOST='masterip', MASTER_USER='replica', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=12345;
Start replication
Run START SLAVE; and then check with SHOW SLAVE STATUSG whether Slave_IO_Running and Slave_SQL_Running are both set to Yes.
Management, monitoring, and common mistakes
Regularly check the replication status with SHOW SLAVE STATUSG. Pay special attention to the Seconds_Behind_Master field, which indicates how much delay there is. An increasing value may indicate a slow connection or a heavy query slowing down the slave.
Common mistakes include: forgetting to set a unique server_id, firewall rules blocking traffic between master and slave, and not synchronizing the starting position, causing the slave to miss data. Also check that both servers are running the same MySQL version to avoid compatibility issues.
If you want to use replication as part of a broader migration, for instance when switching to Tandata, it’s wise to test first on a copy of the environment before going live. This helps prevent data loss during the transition.
Frequently Asked Questions
Can I connect multiple slaves to one master?
Yes, one master can replicate data to multiple slaves. Each slave does need its own unique server_id and its own connection to the master.
Is replication the same as a backup?
No. Replication copies changes directly, so faulty deletions or harmful queries are also sent to the slave. Use replication as a supplement to, not a replacement for, daily backups.
What should I do if replication gets stuck?
First check the error message with SHOW SLAVE STATUS. Often it’s a conflicting query. You can skip it or resynchronize replication from a fresh export of the master.
Does replication also work between servers at different providers?
Yes, as long as the servers can reach each other over the network and the correct ports are open. Pay attention to firewall settings and preferably use a secure connection.
Do I need replication for a small website?
For most small websites, regular web hosting with daily backups is sufficient. Replication is mainly useful for high load, critical availability, or distributing read traffic.
Conclusion
MySQL replication is a powerful way to synchronize databases between servers, provided you follow the steps carefully: enable binary logging, create a replication user, note the starting position, and test the connection. Always combine this with regular backups for true safety. Check out our web hosting or knowledge base for more technical background information.