Setting Up MySQL Master-Slave Replication with Binary Logs

2017-09-14 · mysql, replication, high-availability

The first time a lone MySQL server went down on me with nothing to fail over to, I promised myself every database I care about would get a slave. Replication is the cheapest insurance there is: the master writes every change to its binary log, and the slave pulls that log and replays it. You also get somewhere to run reports and backups without bothering the master.

Here's how I set up a single slave for a MySQL 5.7 master with the classic binary log file and position coordinates. It works on 5.6 and MariaDB 10.1 with small differences, which I note as I go. The master already has data in it, so we take a consistent snapshot and seed the slave from that.

You need two servers on the same major version, port 3306 open from the slave to the master (firewall-cmd --add-port=3306/tcp --permanent on CentOS 7, ufw allow from <slave-ip> to any port 3306 on Ubuntu), and enough disk on the master for several days of binary logs.

1. Configure the master

Edit /etc/my.cnf (CentOS) or /etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu) on the master:

[mysqld]
server_id          = 1
log_bin            = /var/lib/mysql/mysql-bin
binlog_format      = ROW
expire_logs_days   = 7
sync_binlog        = 1
innodb_flush_log_at_trx_commit = 1

server_id must be unique across every server in the topology. ROW format is the safest choice for correctness; STATEMENT breaks on non-deterministic functions. The last two lines make the binlog crash-safe at the cost of some write throughput. I leave them on unless someone shows me the workload really can't afford it.

sudo systemctl restart mysqld
mysql -e "SHOW MASTER STATUS"

If you see a file name like mysql-bin.000001, binary logging is on.

2. Replication user

CREATE USER 'repl'@'10.0.0.%' IDENTIFIED BY 'repl-password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%';
FLUSH PRIVILEGES;

Restrict the host pattern to the subnet the slave lives on. REPLICATION SLAVE is the only privilege this account needs.

3. Snapshot with coordinates

For an all-InnoDB database, mysqldump can do this online:

mysqldump --all-databases --single-transaction --master-data=2 \
  --routines --triggers --events --hex-blob > /tmp/master.sql

--master-data=2 writes the binlog file and position as a comment near the top of the dump, captured at the same instant as the snapshot. Pull them out now:

grep -m1 'CHANGE MASTER TO' /tmp/master.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154;

If you have MyISAM tables, replace --single-transaction with --lock-all-tables, and accept that writes will block during the dump.

Copy the file to the slave:

scp /tmp/master.sql slave.internal:/tmp/

4. Configure the slave

The slave needs a different server_id. I always set it read-only too, because an application accidentally writing to the slave is the most common way I've seen replication break.

[mysqld]
server_id        = 2
relay_log        = /var/lib/mysql/relay-bin
read_only        = 1
log_bin          = /var/lib/mysql/mysql-bin
log_slave_updates = 1

The last two lines are optional but let you promote this slave to master later, or chain another slave behind it. Restart, then load the dump:

sudo systemctl restart mysqld
mysql < /tmp/master.sql
sudo systemctl restart mysqld

The second restart is needed because --all-databases overwrites the mysql schema, including accounts. Note that on 5.7, if mysql.user on the master and slave differ in plugin columns, load the dump with mysql -f and then run mysql_upgrade.

5. Point the slave at the master

Use the coordinates from the dump:

CHANGE MASTER TO
  MASTER_HOST='10.0.0.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl-password',
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=154;
START SLAVE;

On MariaDB 10.1 the syntax is identical. On MySQL 5.7 you may also add MASTER_SSL=1 if the master has TLS configured.

Is it replicating?

SHOW SLAVE STATUS\G

The lines I look at:

Slave_IO_Running: Yes and Slave_SQL_Running: Yes
Seconds_Behind_Master: 0 (or a small number that shrinks)
Last_Error: empty

Then prove it end to end, because a green status page has fooled me before. On the master:

CREATE DATABASE repl_test;
CREATE TABLE repl_test.t (id INT PRIMARY KEY, note VARCHAR(50));
INSERT INTO repl_test.t VALUES (1, 'hello from master');

On the slave:

SELECT * FROM repl_test.t;

Drop the test database on the master afterwards and confirm it disappears from the slave too.

When it breaks

Slave_IO_Running: Connecting with Last_IO_Error: error connecting to master 'repl@10.0.0.10:3306' - retry-time: 60. The slave cannot reach the master. Test with mysql -h 10.0.0.10 -u repl -p from the slave; if that fails, check bind-address on the master and the firewall.

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids. You cloned the VM and forgot to change server_id. Also delete auto.cnf in the data directory, because 5.6+ uses a server_uuid that must also be unique.

Last_SQL_Errno: 1062 Duplicate entry. Someone wrote to the slave, or the coordinates were wrong. With ROW format you cannot safely skip events; rebuild the slave from a fresh dump. SET GLOBAL sql_slave_skip_counter = 1 is only acceptable if you understand exactly which transaction you are discarding.

Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'. The master purged the log the slave needs, usually because expire_logs_days was too short for a long outage. Again, re-seed from a new dump.

Put Seconds_Behind_Master and the two Running flags into your monitoring, and rehearse a failover at least once. An untested standby is just a hope.