Basic MySQL Replication Setup using Binary Log File Position

Introduction to MySQL

MySQL Replication using Binary Log File Position, as opposed to Global Transaction Identifiers(GTID), uses binary logs, relay logs, and index files to track the progress of events between the master and slave databases. GTID can be used in conjunction with binary/relay logs, however, starting with an understanding of binary log file position is beneficial. Shown here are the steps to set up new master and slave servers, including how to record the master log position to use with the slave configuration; resulting in consistent data between the master and slave servers.
This is an overview of the MySQL Replication setup process using Binary Log File Position. As a simplified guide with reference to configuration steps provided at the following: https://dev.mysql.com/doc/refman/5.7/en/replication-configuration.html

Operating system and MySQL versions

CentOS 7
MySQL 5.7

MySQL Definitions

Keywords/filenames used with MySQL Replication

• Master – primary database server data is copied from
• Slave – one or more database servers data is copied to
• Binary log file – containing database updates and changes written as events
• Relay log file – contains database events read from the master’s binary log and written by the slave I/O thread
• Index file – contains the names of all used binary log or relay log files
• Master log info file – contains master configuration information including user, host, password, log file, master log position. Found on slave
• Relay log info file – contains replication status information. Found on slave
• Global Transaction Identifiers(GTID) – Alternative method for tracking replication position, does not require binary logs enabled on slave(not used with binary log file position)

1. Setup MySQL

The latest repository(MySQL 8.1) includes previous versions of MySQL. Once the repository is added, use yum-config-manager to disable mysql80-community and enable mysql57-community; or by editing /etc/yum.repos.d/mysql-community.repo directly.

• Add MySQL Yum Repository

shell> sudo rpm -Uvh mysql80-community-release-el7-1.noarch.rpm

• Install MySQL 5.7

shell> sudo yum-config-manager --disable mysql80-community
shell> sudo yum-config-manager --enable mysql57-community
shell> sudo yum install mysql-community-server
shell> sudo systemctl start mysqld.service

• Reset MySQL root password

shell> sudo grep 'temporary password' /var/log/mysqld.log
shell> mysql -uroot -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

2. Setup Master Server

• Add the following to the [mysqld] section of /etc/my.cnf

[mysqld]
log-bin=mysql-bin
server-id=1
log-bin – The binary log file name, default stored in the MySQL data directory /var/lib/mysql.
server-id=1 – Unique identifier for the server. Defaults to 0 if not declared. If set to 0, connections to the slave servers will be refused.
Restart MySQL
shell> sudo systemctl restart mysqld.service

• Create MySQL Replication User

mysql> CREATE USER 'replication'@'%.example.com' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%.example.com';

• Record the Master binary log position for slave configuration

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
mysql> UNLOCK TABLES;
 

3. Setup Slave Server

• Add the following to the [mysqld] section of /etc/my.cnf

[mysqld]
log-bin=mysql-bin
server-id=2
log-bin – The binary log file name, default stored in the MySQL data directory /var/lib/mysql.
server-id=2 – Unique identifier for the server. Defaults to 0 if not declared. If set to 0, connections to the slave servers will be refused.

• Configuration using the master server replication position information recorded in step 2c.

mysql> 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;

• Start the Slave replication process and view status.

mysql> STOP SLAVE;
mysql> SHOW SLAVE STATUS\G;

Conclusion

Following these steps the slave server should be synced with the master log position. This can be read in the SHOW SLAVE STATUS\G; output, which is will be discussed in the next blog posts. In addition, more information on MySQL Replication GTID setup, variable configurations, and maintenance will be a topic of following blog posts.
 
Not quite ready to handle it yourself? Let us handle your server maintenance with GigeNET’s fully manged services.