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.


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