- What is Binary Log File Position Based Replication?
- How to set up?
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?
- ubuntu-16.10-desktop-amd64 running on vmware workstation
- install the newest mysql:
sudo apt-get install mysql-server
- 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.%';
obtain the master binary log coordinates
flush all tables and block write statements
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
set a unique server ID
/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;
replis created above and The
MASTER_LOG_POSare obtained from the master status.
start slave threads
SHOW SLAVE STATUS \G;
Create a db in master:
create database mydb character set utf8;
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
Replication status is shown in slave's error log file '/var/log/mysql/error.log'. Here are some problems I encountered.
2017-01-06T09:01:45.221848Z 2 [ERROR] Slave I/O for channel '': error connecting to master 'email@example.com:3306' - retry-time: 60 retries: 10, Error_code: 2003
bind-address = 127.0.0.1in 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
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
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?