Set up binary log position based replication

Published On January 06, 2017

category mysql | tags mysql replication master-slave


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.%';
check for it:
show grants for 'repl'@'192.168.172.%';
then restart
/etc/init.d/mysql restart

obtain the master binary log coordinates

flush all tables and block write statements

FLUSH TABLES WITH READ LOCK;
determine the current binary log file name and position
SHOW MASTER STATUS;
this information will be used late.

slave

set a unique server ID

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

#server-id               = 1
to
server-id               = 2
then restart mysql server

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;
The user repl is created above and The MASTER_LOG_FILE and MASTER_LOG_POS are obtained from the master status.

start slave threads

START SLAVE;
replication begins now! check for it:
SHOW SLAVE STATUS \G;

Test

Create a db in master:

create database mydb character set utf8;
Check in slave:
show databases;
you can see our new created database mydb;

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
By default mysql only allow connection from localhost'. You should comment bind-address = 127.0.0.1 in master's config file.

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
When starting, Mysql server reads the UUID from datadir/auto.cnf. It will generate one and save in this file if not found. Because my slave vm is a clone of master vm by simply copy the vmdk files. So the UUID is the same.

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


qq email facebook github
© 2018 - Xurui Yan. All rights reserved
Built using pelican