MySQL/MariaDB Replication¶

Traditional asynchronous binlog replication — see Overview: A Note on Replication Approach for how this differs from Galera, which is the better fit for new multi-master deployments.

Master/Slave Setup¶

On the master¶

Enable binary logging and set a unique server ID:

# my.cnf
[mysqld]
server-id           = 1
log-bin              = mysql-bin
log-slave-updates
relay-log            = relay-bin
relay-log-index      = relay-log.index
replicate-ignore-db  = mysql

Restart, then create a replication user:

CREATE USER 'replicator'@'%' IDENTIFIED BY 'a_strong_password';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

Take a consistent snapshot to seed the slave, noting the binlog position it was taken at:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
-- Note the File and Position values, then in a separate session:

mysqldump -A --master-data > seed.sql

UNLOCK TABLES;

On the slave¶

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

Load the seed dump, then point at the master:

mysql < seed.sql

CHANGE MASTER TO
  MASTER_HOST='master.host.tld',
  MASTER_USER='replicator',
  MASTER_PASSWORD='a_strong_password',
  MASTER_LOG_FILE='mysql-bin.000017',
  MASTER_LOG_POS=16195490;

START SLAVE;
SHOW SLAVE STATUS\G

Check Slave_IO_Running and Slave_SQL_Running are both Yes — that's the core health signal for the rest of this page.

Master/Master Replication¶

Each node replicates from the other. auto_increment_increment/auto_increment_offset keep auto-increment IDs from colliding between the two — see Overview for the residual risk this doesn't fully eliminate.

Node 1¶

[mysqld]
server-id                = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset    = 1
replicate-do-db           = your_database
log_bin                   = /var/lib/mysql/mysql-bin.log
binlog_do_db              = your_database
log-slave-updates
relay-log                 = /var/lib/mysql/relay.log
relay-log-index           = /var/lib/mysql/relay-log.index

Node 2¶

Same as above, but:

server-id                = 2
auto-increment-offset    = 2

Create a replication user on both nodes¶

CREATE USER 'replicator'@'%' IDENTIFIED BY 'a_strong_password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';

Point each node at the other¶

On node 2, get the current binlog position:

SHOW MASTER STATUS\G

On node 1, point at node 2:

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='node2.domain.tld', MASTER_USER='replicator', MASTER_PASSWORD='a_strong_password',
  MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=508;
START SLAVE;
SHOW SLAVE STATUS\G

Then, symmetrically, get node 1's binlog position and point node 2 at node 1 the same way.

Adding a Read Replica to a Master/Master Pair¶

If a third node should replicate from the pair without joining as a master, point it at a virtual IP shared by the two master/master nodes rather than a specific one directly — otherwise you'll have to manually re-point replication any time the preferred master changes.

-- On both existing master/master nodes:
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'node3_host' IDENTIFIED BY 'a_strong_password';

# On the new replica
[mysqld]
server-id       = 3
replicate-do-db = your_database
log_bin         = /var/lib/mysql/mysql-bin.log
binlog_do_db    = your_database
log-slave-updates
relay-log       = /var/lib/mysql/relay.log
relay-log-index = /var/lib/mysql/relay-log.index

CHANGE MASTER TO MASTER_HOST='virtual_ip_or_hostname', MASTER_USER='replicator', MASTER_PASSWORD='a_strong_password',
  MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=1611;
START SLAVE;
SHOW SLAVE STATUS\G

Replicating Over an SSH Tunnel¶

Useful when you don't want to expose the MySQL port directly between hosts:

ssh -f master.host.tld -L 3305:127.0.0.1:3306 -N

Point the slave's CHANGE MASTER TO at 127.0.0.1:3305 instead of the real master address. Keep the tunnel alive with a cron entry:

* * * * * nc -z localhost 3305 || ssh -f master.host.tld -L 3305:127.0.0.1:3306 -N

SSL-Secured Replication¶

Check whether SSL is already enabled:

SHOW VARIABLES LIKE '%ssl%';

Enable it in my.cnf on both master and slave:

ssl
ssl-ca=/etc/mysql/certs/ca-cert.pem
ssl-cert=/etc/mysql/certs/server-cert.pem   # server-cert.pem on the master, client-cert.pem on the slave
ssl-key=/etc/mysql/certs/server-key.pem     # server-key.pem on the master, client-key.pem on the slave

Generate a CA, server cert, and client cert¶

mkdir /etc/mysql/certs && cd /etc/mysql/certs

# CA
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

# Server cert (for the master)
openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem
openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

# Client cert (for the slave)
openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem
openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 02 > client-cert.pem

Copy ca-cert.pem, client-cert.pem, and client-key.pem to the slave (scp or similar), keeping the private key's permissions restricted to the MySQL service account.

Require SSL for the replication user¶

GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%' IDENTIFIED BY 'a_strong_password' REQUIRE SSL;
FLUSH PRIVILEGES;

Restart both instances, confirm have_ssl reports ENABLED, then point the slave at the master with the SSL options included:

CHANGE MASTER TO MASTER_HOST='master_host', MASTER_PORT=3306, MASTER_USER='replicator', MASTER_PASSWORD='a_strong_password',
  MASTER_SSL=1, MASTER_SSL_CA='/etc/mysql/certs/ca-cert.pem',
  MASTER_SSL_CERT='/etc/mysql/certs/client-cert.pem', MASTER_SSL_KEY='/etc/mysql/certs/client-key.pem',
  MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=106;
START SLAVE;

If the OS uses AppArmor, its mysqld profile may also need the certificate directory allowed explicitly:

/etc/mysql/certs/*.pem r,

Re-syncing With SSL Newly Enabled¶

If SSL is being turned on for an existing pair (rather than at initial setup), take a fresh consistent dump under lock rather than assuming existing replication position is still trustworthy:

-- On the master, in one session (keep it open):
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS\G
-- note File and Position

# In a second session/shell, while the lock is held:
mysqldump --databases your_database --add-drop-table | gzip -c > master_dump.sql.gz
scp master_dump.sql.gz user@slave_host:~

-- Back in the first session, on the master:
UNLOCK TABLES;

# On the slave:
mysqladmin stop
zcat master_dump.sql.gz | mysql your_database

Then run the CHANGE MASTER TO shown above using the noted File/Position, and START SLAVE.

Monitoring Replication¶

Ad-hoc check¶

SHOW SLAVE STATUS\G

Look specifically at Slave_IO_Running, Slave_SQL_Running, and Seconds_Behind_Master.

Automated check with monit¶

yum install monit

A small script that touches a marker file when replication is healthy:

#!/bin/bash
chk_io=$(mysql -e 'SHOW SLAVE STATUS\G' | grep Slave_IO_Running | awk '{print $2}')
chk_sql=$(mysql -e 'SHOW SLAVE STATUS\G' | grep Slave_SQL_Running | awk '{print $2}')
if [[ "$chk_io" == "Yes" ]] && [[ "$chk_sql" == "Yes" ]]; then
  touch /var/run/mysql_repl_check
fi

Run it on a schedule (e.g. every 30 minutes via cron), and have monit alert if the marker goes stale:

check file DbSlaveReplication with path /var/run/mysql_repl_check
  if timestamp > 35 minutes then alert
check process mysql with pidfile /var/run/mysqld/mysqld.pid
  start program = "/etc/init.d/mysql start"
  stop program = "/etc/init.d/mysql stop"
  if failed host 127.0.0.1 port 3306 then restart
  if 5 restarts within 5 cycles then timeout

Restoring / Rebuilding a Broken Replica¶

Full re-seed from the master (or from either master/master node):

# On the master
mysqldump -v --databases your_database | bzip2 -v -c > your_database.sql.bz2
scp your_database.sql.bz2 replica_host:~/

# On the replica
mysql -e 'DROP DATABASE your_database;'
mysql -e 'CREATE DATABASE your_database;'
bzcat ~/your_database.sql.bz2 | mysql your_database

Or piped directly over SSH without an intermediate file:

ssh replica_host "mysql -e 'DROP DATABASE your_database;'"
ssh replica_host "mysql -e 'CREATE DATABASE your_database;'"
mysqldump -v --databases your_database | ssh replica_host 'mysql your_database'

Then re-point with CHANGE MASTER TO as shown earlier, using the current SHOW MASTER STATUS position.

Skipping a stuck duplicate-key error¶

If replication halts on a duplicate row that's safe to skip:

SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

Repeat if there's more than one duplicate in a row.

Exporting Data for a New Replica¶

# Master-side dump with binlog position embedded
mysqldump --master-data --single-transaction your_database > your_database.sql

# Slave-side dump with the slave's own replication position embedded (for chaining a replica off a replica)
mysqldump --dump-slave your_database > your_database.sql

Replication-Specific Troubleshooting¶

`ERROR 1200 (HY000): The server is not configured as slave`¶

SHOW VARIABLES LIKE 'server_id';

If it's 0, server-id was never set in my.cnf — set it and restart.

`ERROR 1201 (HY000): Could not initialize master info structure`¶

RESET SLAVE;
STOP SLAVE;
CHANGE MASTER TO ...;  -- re-issue with correct values
START SLAVE;

Replication connects but immediately fails with a CREATE USER error¶

Last_Error: Error 'Operation CREATE USER failed for ...' on query.

Usually means a statement in the replicated stream conflicts with something that already exists on the replica (e.g. the account was created independently on both sides before replication started). Skip it and move on if it's a one-off:

SET GLOBAL sql_slave_skip_counter=1;
START SLAVE;

Distro-managed maintenance accounts broken after loading a master dump¶

Some distro packages (older Debian-family MySQL packages in particular) maintain an internal maintenance account whose credentials are stored in a package-managed config file. Restoring a full dump from another server can overwrite that account's password entry, breaking package-managed maintenance scripts (Access denied for user '...'@'localhost'). If this happens:

# Find the maintenance account's expected password
cat /etc/mysql/debian.cnf   # Debian-family systems specifically

GRANT ALL PRIVILEGES ON *.* TO 'maintenance_user'@'localhost' IDENTIFIED BY 'password_from_the_config_file' WITH GRANT OPTION;

`mysqldump Error: Binlogging on server not active`¶

Add log-bin to my.cnf (see Master/Slave Setup above) and restart — you can't dump --master-data from an instance that isn't actually running with binary logging enabled.

Connection refused between master and slave (error 2013 / general connection failures)¶

Check for a bind-address = 127.0.0.1 line in my.cnf — this restricts the server to local-only connections and will silently refuse remote replication connections until removed or changed to the interface you actually want to listen on.

Backup and Restore — the mysqldump/pg_dump-style flags referenced throughout this page
Troubleshooting — general (non-replication-specific) error codes
Galera Cluster — the modern alternative for new multi-master deployments