/ mozey.co / blog

mysql replication

March 1, 2015

How Replication Works 1

Setting up replication 2

Master

Edit the MySQL config file

sudo vi /etc/mysql/my.cnf

Unique server ID

Binary logging must be enabled

log_bin = /var/log/mysql/mysql-bin.log
binlog-ignore-db = mysql # Skip system database

For innodb also add

innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

The master must be listening on a public IP address. For extra security check that only the replication user is allowed to connect from outside localhost.

bind-address = publicIpAddress

Or for any interface that is connected

bind-address = 0.0.0.0

Create replication user

create user 'replicationUser'@'%.domain.com' identified by 'replicationUserPassword';
grant replication slave on *.* to 'replicationUser'@'%.domain.com';

Restart the MySQL service on the master

sudo service mysql restart

Obtain the replication master binary log coordinates

flush tables with read lock; show master status; 

Creating a database snapshot

mysqldump -u root -p --master-data mydb > mydb.sql

Unlock tables

unlock tables;

Slave

The slave also requires a server ID

[mysqld]
server-id = 2

Slave does not require binary logging unless it will also replicated

Restart the MySQL service on the slave

Setup the master database on the slave

stop slave; create database mydb; use mydb; source mydb.sql

And setup the slave config by running the following SQL query

change master to
master_host='master_host_name',
master_user='replication_user_name',
master_password='replication_password',
master_log_file='recorded_log_file_name',
master_log_pos=recorded_log_position;

grant to replication_user_name super, replication client, replication slave;

Start the slave

start slave;

And show slave status

show slave status\G;

Debugging

Skip a query on the slave

stop slave; set global sql_slave_skip_counter=1; start slave;

Reference

  1. MySQL Docs: Chapter 16 Replication

  2. How to Set Up Replication

  3. MySQL Docs: FAQ Replication

  4. How MySQL replication works

  5. MySQL Point in Time Recovery