Versions Compared

Old Version 3

changes.mady.by.user jtel Support

Saved on

compared with

New Version 4

changes.mady.by.user jtel Support

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Sv translation
languageen

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.

Warning

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

Translations Ignore


Code Block
languagebash
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

Translations Ignore


Code Block
languagebash
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.

Status
colourRed
titleCaution Password

Translations Ignore


Code Block
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.

Warning

CAUTION - if you make modifications to the configuration:

  • The server_id must be unique per server. 

Configure Slave 1

Translations Ignore


Code Block
languagebash
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

Translations Ignore


Code Block
languagebash
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.

Translations Ignore


Code Block
languagebash
systemctl restart mysqld.service



Master Server (Primary)

Folgende Schritte sind erforderlich, um einen DATA Server als Master zu konfigurieren.

Als erstes muss ein entsprechendes Konfigurationsmodul erstellt werden. Dies erfolgt mit folgendem Befehl.

ACHTUNG:

  • die server_id sind pro Master Server unterschiedlich 

    • Der Parameter auto_increment_offset ist pro Master Server unterschiedlich

    Sv translation
    languagede
    toc

    Status

    maxLevel3
    printablefalse
    Translations Ignore
    Code Block
    languagebash
    titleConfigure MySQL 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
    Translations Ignore
    Code Block
    languagebash
    titleConfigure MySQL 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

    Als nächstes wird ein Benutzer angelegt, mit dem sich die Slave-Server mit dem Master-Server verbinden können - <password> mit dem entsprechenden Passwort ersetzen.

    Translations Ignore
    Code Block
    titleCreate 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"

    colourRed
    titleThis page is only available in English

    Im Anschluss muss der MySQL-Server neu gestartet werden, damit alle Einstellungen übernommen werden:

    Translations Ignore
    Code Block
    languagebash
    titleRestart the MySQL server
    service mysqld restart
    Slave Server

    Folgende Schritte sind erforderlich, um einen DATA-Server als Slave zu konfigurieren. Es handelt sich hierbei um eine unverschlüsselte Replikation. Eine verschlüsselte Replikation kann gemäß https://www.thomas-krenn.com/de/wiki/MySQL_Verbindungen_mit_SSL_verschl%C3%BCsseln umgesetzt werden.

    Als erstes muss ein entsprechendes Konfigurationsmodul erstellt werden. Dies erfolgt mit folgendem Befehl.

    ACHTUNG:

    • die server_id sind pro Slave Server unterschiedlich, und sind bewusst abseits von 100 gehalten, damit die nicht mit Master-IDs kollidieren.
    Translations Ignore
    Code Block
    languagebash
    titleConfigure MySQL 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
    Translations Ignore
    Code Block
    languagebash
    titleConfigure MySQL 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

    Im Anschluss muss der MySQL-Server neu gestartet werden, damit alle Einstellungen übernommen werden:

    Translations Ignore Code Block
    languagebash
    titleRestart the MySQL server
    service mysqld restart