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           = 
binlog_do_db           = 
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

Probleme

Slave has read all relay log; waiting for the slave I/O thread to update it

MariaDB [(none)]> show processlist
    -> ;
+----+-------------+------------------+----------+-------------+------+-----------------------------------------------------------------------------+------------------+----------+
| Id | User        | Host             | db       | Command     | Time | State                                                                       | Info             | Progress |
+----+-------------+------------------+----------+-------------+------+-----------------------------------------------------------------------------+------------------+----------+
|  1 | system user |                  | NULL     | Daemon      | NULL | InnoDB purge coordinator                                                    | NULL             |    0.000 |
|  2 | system user |                  | NULL     | Daemon      | NULL | InnoDB purge worker                                                         | NULL             |    0.000 |
|  3 | system user |                  | NULL     | Daemon      | NULL | InnoDB purge worker                                                         | NULL             |    0.000 |
|  4 | system user |                  | NULL     | Daemon      | NULL | InnoDB purge worker                                                         | NULL             |    0.000 |
|  5 | system user |                  | NULL     | Daemon      | NULL | InnoDB shutdown handler                                                     | NULL             |    0.000 |
| 10 | system user |                  | NULL     | Slave_IO    |  833 | Waiting for master to send event                                            | NULL             |    0.000 |
| 11 | system user |                  | NULL     | Slave_SQL   |  528 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |    0.000 |
| 15 | powerdns    | 10.0.0.167:51160 | powerdns | Sleep       |  833 |                                                                             | NULL             |    0.000 |
| 16 | powerdns    | 10.0.0.167:51162 | powerdns | Sleep       |  462 |                                                                             | NULL             |    0.000 |
| 17 | powerdns    | 10.0.0.167:51164 | powerdns | Sleep       |   17 |                                                                             | NULL             |    0.000 |
| 18 | powerdns    | 10.0.0.167:51166 | powerdns | Sleep       |  832 |                                                                             | NULL             |    0.000 |
| 19 | master      | 10.0.0.157:47574 | NULL     | Binlog Dump |  823 | Master has sent all binlog to slave; waiting for binlog to be updated       | NULL             |    0.000 |
| 33 | root        | localhost        | NULL     | Query       |    0 | Init                                                                        | show processlist |    0.000 |
+----+-------------+------------------+----------+-------------+------+-----------------------------------------------------------------------------+------------------+----------+
13 rows in set (0.001 sec)

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