MySQL Server Replication using Binary Logs

MySQL / 3 min read

Suppose you want to create a replica of your MySQL database. The replica should:

  • start with a complete snapshot of the current (initial) state of the master database and
  • be updated with any changes to the master database.

This post will outline how MySQL server replication can be done using binary logs.

What are Binary Logs?

MySQL binary logs are a set of log files which contain detailed information about all changes made to a MySQL database. These logs are extremely granular and contain sufficient information to recreate all changes.

Setting up the Master

First we’ll need to configure the master server to enable binary logs.

Make a backup copy of /etc/mysql/mysql.conf.d/mysqld.cnf. Now edit the original file as follows:

[mysqld]
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
sync_binlog             = 1

# Which database are we replicating?
#
binlog_do_db            = master_database

Create a user which will be used for replication.

GRANT REPLICATION SLAVE
ON *.*
TO 'replication_slave'@'%'
IDENTIFIED BY 'hazIct0Ospurn';    -- Choose a secure password!

FLUSH PRIVILEGES;

You need to create a fixed copy of the database which will serve as the reference point from which replication will occur. Dump the database to an SQL file.

mysqldump -u root -p \
  --master-data \
  --single-transaction \
  --flush-logs master_database \
  --result-file master_database.sql

The --flush-logs option will flush all transactions to the binary logs. It will then close the current binary log file and open a new one.

Take a look at the top of the SQL dump in master_database.sql. You should find a line which begins with CHANGE MASTER. This gives the information on where the slave will start reading from the binary logs to get updates relative to the dump.

Copy the SQL dump across to the slave.

Open up port 3306 on the master for access from the slave.

Setting up the Slave

First stop and remove any existing slave configurations.

STOP SLAVE;
RESET SLAVE ALL;

Check.

SHOW SLAVE STATUS\G

Create a new database.

DROP DATABASE IF EXISTS master_database;
CREATE DATABASE master_database;

Make a backup copy of /etc/mysql/mysql.conf.d/mysqld.cnf. Now edit the original file as follows:

[mysqld]
server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log

binlog_do_db            = master_database

Restart the server.

sudo service mysql restart

Restore the database from the dump file.

mysql -u root -p master_database <master_database.sql

Set up the connection to the master. This will

  • configure the current server as a slave
  • provide the IP for the master
  • provide the access credentials for the master and
  • specify the master log file and where to start replicating from (as mentioned above, this information can be found towards the top of the SQL dump file).
CHANGE MASTER TO
  MASTER_HOST='178.128.151.83',
  MASTER_USER='replication_slave',
  MASTER_PASSWORD='hazIct0Ospurn',
  MASTER_LOG_FILE='mysql-bin.000001',  -- First log file on master
  MASTER_LOG_POS=307;                  -- Position in log file

Start the slave.

START SLAVE;

Check on status.

SHOW SLAVE STATUS\G

These are the critical components that you are looking for in the output:

         Slave_IO_State: Waiting for master to send event
       Slave_IO_Running: Yes
      Slave_SQL_Running: Yes
  Seconds_Behind_Master: 0
          Last_IO_Error: 
         Last_SQL_Error: 
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

You can also check on the most recently modified tables.

SELECT table_schema, table_name, update_time
FROM information_schema.tables
WHERE engine = 'MyISAM'
ORDER BY update_time;

Troubleshooting

If replication breaks (you’ll see an error message and Seconds_Behind_Master will be NULL) then one quick fix is to simply skip the offending transaction.

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
SELECT SLEEP(5);

Now check the status again. Hopefully there will not be any further errors.

It might take a while for the slave to catch up with the master, so keep your eye on Seconds_Behind_Master until it does.