Set up binary log position based replication

What is Binary Log File Position Based Replication?

If you don't care the details, you can skip this.

The MySQL instance operating as the master (the source of the database changes) writes updates and changes as “events” to the binary log. The information in the binary log is stored in different logging formats according to the database changes being recorded. Slaves are configured to read the binary log from the master and to execute the events in the binary log on the slave's local database.

If required, you can configure the slave to process only events that apply to particular databases or tables.But you cannot configure the master to log only certain events. Each slave keeps a record of the binary log coordinates: the file name and position within the file that it has read and processed from the master. The details are stored within the slave's master info repository.

How to set up?

environment

• ubuntu-16.10-desktop-amd64 running on vmware workstation
• install the newest mysql:

  sudo apt-get install mysql-server
  

• master(192.168.172.128)-slave(192.168.172.129)

master

edit /etc/mysql/mysql.conf.d/mysqld.cnf

• comment this line to allow connection from other hosts:

  bind-address = 127.0.0.1
  

• enable binary logging and establish a unique server ID uncomment these two lines

  #server-id               = 1
  #log_bin                 = var/log/mysql/mysql-bin.log
  

create a user and grant privilege

CREATE USER 'repl'@'192.168.172.%' IDENTIFIED BY 'slavepass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.172.%';

show grants for 'repl'@'192.168.172.%';

/etc/init.d/mysql restart

obtain the master binary log coordinates

flush all tables and block write statements

FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

slave

set a unique server ID

Edit /etc/mysql/mysql.conf.d/mysqld.cnf to change

#server-id               = 1

server-id               = 2

set the replication

CHANGE MASTER TO
MASTER_HOST='192.168.172.128',
MASTER_USER='repl',
MASTER_PASSWORD='slavepass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1521;

start slave threads

START SLAVE;

SHOW SLAVE STATUS \G;

Test

Create a db in master:

create database mydb character set utf8;

show databases;

You can try many staffs by yourself:

• stop the slave and do some updates in the master then start the slave
• restart the master(binary log file changed when restarted) then do some updates

problems

Replication status is shown in slave's error log file '/var/log/mysql/error.log'. Here are some problems I encountered.

Error_code: 2003

2017-01-06T09:01:45.221848Z 2 [ERROR] Slave I/O for channel '': error connecting to master 'repl@192.168.172.128:3306' - retry-time: 60  retries: 10, Error_code: 2003

master and slave have equal MySQL server UUIDs

2017-01-06T09:11:45.273548Z 2 [ERROR] Slave I/O for channel '': Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. Error_code: 1593
2017-01-06T09:11:45.273587Z 2 [Note] Slave I/O thread exiting for channel '', read up to log 'mysql-bin.000001', position 1521

To solve this problem, just remove the file and restart:

sudo su
rm /var/lib/mysql/auto.cnf
/etc/init.d/mysql restart
exit

Question

New binary log files will be generated every time mysql sever restarts or the size the exceeds the max_binlog_size variable. Increasing number is appended to the binary log base name to create an ordered series of files.

How does mysql make synchronization work well as the slave sets a specific binary file to start replicate while new binary log files in master are generated afterwards?

Reference

• Mysql: 18.1.2 Setting Up Binary Log File Position Based Replication
• Mysql: 6.4.4 The Binary Log