Replication is a significant feature of MySQL and MariaDB, enabling the distribution of data across different servers. Through replication, data from one MySQL or MariaDB database server (the primary or “master” server) can be duplicated onto one or multiple secondary (or “slave”) servers.
This process ensures data availability, boosts performance for high-availability setups, and provides an essential backup strategy. It's especially beneficial in scenarios where you want to distribute the read load across multiple servers or maintain an off-site backup in real-time.
Configuring replication for MySQL or MariaDB entails setting up the primary server to maintain a binary log of changes and preparing the secondary server to read from that log. The secondary then applies these changes to keep its data in sync with the primary.
Steps to configure MySQL or MariaDB replication:
- Primary Server Configuration.
- Edit the MySQL or MariaDB configuration file.
$ sudo nano /etc/mysql/my.cnf # On Ubuntu/Debian
Configuration file paths might differ based on the Linux distribution. It could also be /etc/my.cnf or /etc/mysql/mariadb.conf.d/50-server.cnf.
- Restart the MySQL or MariaDB service.
$ sudo systemctl restart mysql
- Setting Up a Replication User.
- Log into MySQL/MariaDB on the primary server.
$ mysql -u root -p
- Exit the MySQL/MariaDB shell.
exit;
- Secondary Server Configuration.
- Edit the MySQL or MariaDB configuration on the secondary server.
$ sudo nano /etc/mysql/my.cnf # On Ubuntu/Debian
- In the [mysqld] section, set a unique server_id (different from the primary).
server_id = 2
- Restart the MySQL or MariaDB service.
$ sudo systemctl restart mysql
- Start Replication.
- On the secondary server, log into MySQL/MariaDB.
$ mysql -u root -p
- Connect to the primary server as the replication user.
CHANGE MASTER TO MASTER_HOST='primary_server_ip', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=0;
Update 'primary_server_ip' and 'password' with appropriate values. MASTER_LOG_FILE and MASTER_LOG_POS values can be fetched from the primary server's bin-log.
- Start the slave process.
START SLAVE;
- Exit the MySQL/MariaDB shell.
exit;
Mohd Shakir Zakaria is an experienced cloud architect with a strong development and open-source advocacy background. He boasts multiple certifications in AWS, Red Hat, VMware, ITIL, and Linux, underscoring his expertise in cloud architecture and system administration.
Comment anonymously. Login not required.