Replication is a powerful feature in MySQL and MariaDB that allows data to be copied from one server (the master) to another (the slave). This feature ensures data availability and redundancy, providing a safety net in case of failure and improving system performance by distributing read operations across multiple servers. Replication is a key component in high-availability and load-balanced setups.
By logging all changes on the master server and having the slave server apply these changes, you can keep databases synchronized. This setup not only improves the read capacity of your infrastructure but also acts as a real-time backup solution. As a result, replication reduces downtime and protects against data loss.
Setting up replication involves configuring both the master and slave servers. The master server needs to maintain a binary log of all changes, while the slave must be able to read and apply these logs to stay in sync. Additionally, a replication user with specific privileges must be created to handle the communication between the servers.
Steps to configure MySQL or MariaDB replication:
- Edit the configuration file on the master server.
$ sudo nano /etc/mysql/my.cnf # On Ubuntu/Debian
Common paths include /etc/my.cnf or /etc/mysql/mariadb.conf.d/50-server.cnf depending on your distribution.
- Restart the MySQL or MariaDB service.
$ sudo systemctl restart mysql
- Log in to MySQL or MariaDB on the master server.
$ mysql -u root -p
- Exit the MySQL or MariaDB shell.
exit;
- Edit the configuration file on the slave server.
$ sudo nano /etc/mysql/my.cnf # On Ubuntu/Debian
- Set a unique server ID for the slave in the [mysqld] section.
server_id = 2
Ensure the slave's server ID is different from the master server.
- Restart the MySQL or MariaDB service.
$ sudo systemctl restart mysql
- Log in to MySQL or MariaDB on the slave server.
$ mysql -u root -p
- Configure the slave to connect to the master.
CHANGE MASTER TO MASTER_HOST='primary_server_ip', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=0;
Replace 'primary_server_ip' with the master server's IP. Retrieve MASTER_LOG_FILE and MASTER_LOG_POS from the master’s binary log.
- Start the replication process on the slave server.
START SLAVE;
- Exit the MySQL or MariaDB shell.
exit;

Mohd Shakir Zakaria is a cloud architect with deep roots in software development and open-source advocacy. Certified in AWS, Red Hat, VMware, ITIL, and Linux, he specializes in designing and managing robust cloud and on-premises infrastructures.
Comment anonymously. Login not required.