Replication in MySQL or MariaDB is a mechanism that allows one or more servers to synchronize data in real-time, ensuring high availability, data redundancy, and load balancing. It uses a primary-replica architecture where the primary server sends updates to one or more replicas, which then apply the changes to maintain the same data as the primary. The primary server processes write queries, while replicas handle read queries, thus distributing the workload across multiple servers.
Setting up replication involves configuring the primary and replica servers, enabling the binary log on the primary server, and establishing a connection between the servers. This guide will walk you through the process of creating MySQL or MariaDB replication step-by-step. Note that the steps provided assume that you already have a running MySQL or MariaDB installation and necessary privileges to perform the tasks.
server-id=1 log-bin=mysql-bin binlog-format=ROW
CREATE USER 'replica_user'@'%' IDENTIFIED BY 'your_password'; GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';
SHOW MASTER STATUS;
server-id=2 relay-log=relay-log read-only=1
CHANGE MASTER TO MASTER_HOST='primary_server_ip', MASTER_USER='replica_user', MASTER_PASSWORD='your_password', MASTER_LOG_FILE='File', MASTER_LOG_POS=Position;
SHOW SLAVE STATUS\G
Comment anonymously. Login not required.