Sv translation |
---|
|
Master ServerThe 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: Translations Ignore |
---|
Code Block |
---|
language | bash |
---|
title | 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 |
|
Note |
---|
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: Status |
---|
colour | Red |
---|
title | Caution Password |
---|
|
Translations Ignore |
---|
Code Block |
---|
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" |
|
Afterwards the MySQL server must be restarted so that all settings are applied: Translations Ignore |
---|
Code Block |
---|
| service mysqld restart |
|
The following steps are required to configure a DATA server as a slave. This is an unencrypted replication setup. The first step is to create an appropriate configuration file. This is done with the following command: Translations Ignore |
---|
Code Block |
---|
language | bash |
---|
title | 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 |
|
Note |
---|
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 . |
Afterwards the MySQL server must be restarted so that all settings are applied: Translations Ignore |
---|
Code Block |
---|
| service mysqld restart |
|
|
Sv translation |
---|
|
toc |
|
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:
Translations Ignore |
---|
Code Block |
---|
language | bash |
---|
title | 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 |
Note |
---|
|
Der Wert server_id taucht sowohl in den Konfigurationsmodulen für Master-Server als auch in den Konfigurationsmodulen für Slave-Server auf. Hierbei ist strikt darauf zu achten, dass dieser Wert eindeutig ist. In einem Verbund dürfen keine DATA-Server die gleiche server_id haben. |
Als nächstes wird ein Benutzer angelegt, mit dem sich die Slave-Server mit dem Master-Server verbinden können - <password> mit den entsprechenden Passwort ersetzen:
MySQL 8.x
Translations Ignore |
---|
Code Block |
---|
title | 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
Translations Ignore |
---|
Code Block |
---|
title | 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" |
colour | Red |
---|
title | This page is only available in English |
---|
|
Im Anschluss muss der MySQL-Server neu gestartet werden, damit alle Einstellungen übernommen werden:
Translations Ignore |
---|
Code Block |
---|
language | bash |
---|
title | 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:
Translations Ignore |
---|
Code Block |
---|
language | bash |
---|
title | 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
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:
Translations Ignore |
---|
Code Block |
---|
language | bash |
---|
title | 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 |
Note |
---|
|
Der Wert server_id taucht sowohl in den Konfigurationsmodulen für Master-Server als auch in den Konfigurationsmodulen für Slave-Server auf. Hierbei ist strikt darauf zu achten, dass dieser Wert eindeutig ist. In einem Verbund dürfen keine DATA-Server die gleiche server_id haben. |
Im Anschluss muss der MySQL-Server neu gestartet werden, damit alle Einstellungen übernommen werden:
Translations Ignore |
---|
Code Block |
---|
language | bash |
---|
title | Restart the MySQL server |
---|
|
service mysqld restart