Master Servers
The following steps configure the master servers, and are performed on both master servers.
First of all, a configuration file is created on each server.
CAUTION - if you make modifications to the configuration:
- The server_id must be unique per server.
The Parameter auto_increment_offset must be different for each master.
Configure Master 1
cat <<EOFF > /etc/my.cnf.d/jtel-master.cnf # Custom MySQL settings for a specific SQL master server # # WARNING: This file is specific to the master server [mysqld] # # Replication Options # # Specific options for MASTER role # server_id = 1 binlog_format = ROW expire_logs_days = 1 max_binlog_size = 100M log_bin = binlog auto_increment_increment = 2 auto_increment_offset = 1 log_slave_updates relay_log = mysqld-relay-bin relay_log_index = mysqld-relay-bin.index relay_log_info_file = relay-log.info EOFF
Configure Master 2
cat <<EOFF > /etc/my.cnf.d/jtel-master.cnf # Custom MySQL settings for a specific SQL master server # # WARNING: This file is specific to the master server [mysqld] # # Replication Options # # Specific options for MASTER role # server_id = 2 binlog_format = ROW expire_logs_days = 1 max_binlog_size = 100M log_bin = binlog auto_increment_increment = 2 auto_increment_offset = 2 log_slave_updates relay_log = mysqld-relay-bin relay_log_index = mysqld-relay-bin.index relay_log_info_file = relay-log.info EOFF
Replication User
Next, a replication user is created, which is used to connect to the master servers.
CAUTION PASSWORD
mysql -u root -p<password> -v -e"CREATE USER 'repl'@'%' IDENTIFIED BY '<password>'" mysql -u root -p<password> -v -e"GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'" mysql -u root -p<password> -v -e"FLUSH PRIVILEGES"
Slave Servers
The following steps configure the slave servers, and are performed on both slave servers.
First of all, a configuration file is created on each server.
CAUTION - if you make modifications to the configuration:
- The server_id must be unique per server.
Configure Slave 1
cat <<EOFF > /etc/my.cnf.d/jtel-slave.cnf # Custom MySQL settings for a specific SQL slave server # # WARNING: This file is specific to the slave server [mysqld] # Specific options for SLAVE role # server_id = 101 relay_log = mysqld-relay-bin relay_log_index = mysqld-relay-bin.index relay_log_info_file = relay-log.info skip-log-bin EOFF
Configure Slave 2
cat <<EOFF > /etc/my.cnf.d/jtel-slave.cnf # Custom MySQL settings for a specific SQL slave server # # WARNING: This file is specific to the slave server [mysqld] # Specific options for SLAVE role # server_id = 102 relay_log = mysqld-relay-bin relay_log_index = mysqld-relay-bin.index relay_log_info_file = relay-log.info skip-log-bin EOFF
Restart MySQL Servers
Next, all 4 mysql servers are restarted, to reload the configuration.
systemctl restart mysqld.service