MySQL Replication

MySQL Replication

Replication copies data from a primary (master) to one or more replicas (slaves) for read scaling and high availability.

# On primary — my.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW

# Create replication user
CREATE USER 'replicator'@'%' IDENTIFIED BY 'pass';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;  # note File and Position

# On replica — my.cnf
[mysqld]
server-id = 2

# Configure replica
CHANGE MASTER TO
    MASTER_HOST = '192.168.1.10',
    MASTER_USER = 'replicator',
    MASTER_PASSWORD = 'pass',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 154;
START SLAVE;
SHOW SLAVE STATUSG