MariaDB Replikation (Ubuntu)

Noch in Bearbeitung

Server A (10.0.0.157) - Teil 1

Die Datei /etc/mysql/mariadb.conf.d/50-server.cnf editieren

sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf

und folgende Zeilen hinzufügen/anpassen:

bind-address=10.0.0.157

server-id              = 1
log_bin                = /var/log/mysql/mysql-bin.log
expire_logs_days       = 10
binlog_do_db           = powerdns
binlog_do_db           = ddns
replicate-do-db        = powerdns
replicate-do-db        = ddns
log-basename           = master1

Danach den Server neu starten:

sudo systemctl restart mariadb

CREATE USER 'master'@'%' IDENTIFIED BY '<password>';
GRANT REPLICATION SLAVE ON *.* TO 'master'@'%';
FLUSH PRIVILEGES;

SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      774 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

Server B (10.0.0.167)

Die Datei /etc/mysql/mariadb.conf.d/50-server.cnf editieren

sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf

und folgende Zeilen hinzufügen/anpassen:

bind-address=10.0.0.167

server-id              = 2
log_bin                = /var/log/mysql/mysql-bin.log
expire_logs_days       = 10
binlog_do_db           = powerdns
binlog_do_db           = ddns
replicate-do-db        = powerdns
replicate-do-db        = ddns
log-basename           = master2

Danach den Server neu starten:

sudo systemctl restart mariadb

CREATE USER 'master'@'%' IDENTIFIED BY '<password>';
GRANT REPLICATION SLAVE ON *.* TO 'master'@'%';
FLUSH PRIVILEGES;

STOP SLAVE;

CHANGE MASTER TO MASTER_HOST = '10.0.0.157', MASTER_USER = 'master', MASTER_PASSWORD = '<password>', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 774;

START SLAVE;

SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      774 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

Server A (10.0.0.157) - Teil 2

mysql -u root -p

STOP SLAVE;

CHANGE MASTER TO MASTER_HOST = '10.0.0.167', MASTER_USER = 'master', MASTER_PASSWORD = '<password>', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 774;

START SLAVE;

Client Status

SHOW SLAVE STATUS\G

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 79
Server version: 10.3.17-MariaDB-0+deb10u1-log Raspbian 10

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 10.0.0.167
                   Master_User: master
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000006
           Read_Master_Log_Pos: 987
                Relay_Log_File: master1-relay-bin.000007
                 Relay_Log_Pos: 1286
         Relay_Master_Log_File: mysql-bin.000006
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               Replicate_Do_DB: powerdns,ddns
           Replicate_Ignore_DB: 
            Replicate_Do_Table: 
        Replicate_Ignore_Table: 
       Replicate_Wild_Do_Table: 
   Replicate_Wild_Ignore_Table: 
                    Last_Errno: 0
                    Last_Error: 
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 987
               Relay_Log_Space: 1896
               Until_Condition: None
                Until_Log_File: 
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File: 
            Master_SSL_CA_Path: 
               Master_SSL_Cert: 
             Master_SSL_Cipher: 
                Master_SSL_Key: 
         Seconds_Behind_Master: 0
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error: 
                Last_SQL_Errno: 0
                Last_SQL_Error: 
   Replicate_Ignore_Server_Ids: 
              Master_Server_Id: 2
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 3
1 row in set (0.001 sec)

Log-Files

Wenn die Konfiguration log-basename gesetzt ist, dann werden die Log-Dateien direkt im Datenverzeichnis abgelegt. Wo sich das Datenverzeichnis befindet, findet man über folgenden SQL-Befehl heraus:

SHOW VARIABLES LIKE '%datadir%';

Das sollte dann in etwa folgendes Ergebnis zurückgeben:

+---------------------------------------------+------------------------+
| Variable_name                               | Value                  |
+---------------------------------------------+------------------------+
| datadir                                     | /var/lib/mysql/        |
+---------------------------------------------+------------------------+

Probleme

Could not initialize master info structure for ; more error messages can be found in the MariaDB error log

Nach Änderung der Konfiguration habe ich obenstehende Fehlermeldung beim Ausführen von

CHANGE MASTER TO MASTER_HOST = '10.0.0.157', MASTER_USER = 'master', MASTER_PASSWORD = '<password>', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 774;

bekommen. Nach Ausführen von

RESET SLAVE;

ist der Fehler nicht mehr aufgetreten.

Links

https://mariadb.com/kb/en/setting-up-replication/

https://forums.mysql.com/read.php?26,171776,205870

https://mariadb.com/kb/en/standard-replication/

https://www.vpsserver.com/community/tutorials/9/setup-a-master-to-master-replication-between-two-mariadb-servers/

Zurück zu MariaDB