Master Server (Primary)

The following steps are required to configure a DATA server as master.

The first step is to create an appropriate configuration module. This is done with the following command:

Configure MySQL Master

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                = 3
max_binlog_size                 = 100M
log_bin                         = binlog
relay_log                       = mysqld-relay-bin
relay_log_index                 = mysqld-relay-bin.index
relay_log_info_file             = relay-log.info
EOFF

ACHTUNG

The value server_id appears both in the configuration modules for master servers and in the configuration modules for slave servers. It is important to ensure that this value is unique. No DATA servers in a group may have the same server_id.

Next, a user is created with which the slave servers can connect to the master server - replace <password> with the corresponding password:

MySQL 8.x

Create replication user

mysql -u root -p<password> -v -e"CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY '<password>'"
mysql -u root -p<password> -v -e"GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'"
mysql -u root -p<password> -v -e"FLUSH PRIVILEGES"

MySQL 5.6

Create replication user

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"

Afterwards the MySQL server must be restarted so that all settings are applied:

Restart the MySQL server

service mysqld restart

Keep only bin logs for 4 hours

This is NOT RECOMMENDED.

This step is necessary, on master servers with a high load and low disk capacity - replace <password> with the DB password:

Values to the system environment

echo "FLUSH LOGS;" > /home/jtel/purge.sql
echo "PURGE BINARY LOGS BEFORE NOW() - INTERVAL 4 HOUR;" >> /home/jtel/purge.sql

echo '#!/bin/bash' > /home/jtel/purge.sh
echo "mysql -uroot -p<password> < /home/jtel/purge.sql" >> /home/jtel/purge.sh
chmod 700 /home/jtel/purge.sh
mv /home/jtel/purge.sh /etc/cron.hourly/

Slave Server

The following steps are required to configure a DATA server as a slave. This is an unencrypted replication. Encrypted replication can be performed according to https://www.thomas-krenn.com/de/wiki/MySQL_Verbindungen_mit_SSL_verschl%C3%BCsseln.

The first step is to create an appropriate configuration module. This is done with the following command:

Configure MySQL slave

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
log_slave_updates
relay_log                       = mysqld-relay-bin
relay_log_index                 = mysqld-relay-bin.index
relay_log_info_file             = relay-log.info
skip-log-bin
EOFF