MariaDB Replikation (Ubuntu): Unterschied zwischen den Versionen

Aus Tutorials
Zur Navigation springen Zur Suche springen
Zeile 141: Zeile 141:
== Probleme ==
== Probleme ==


=== Slave has read all relay log; waiting for the slave I/O thread to update it ===
=== Could not initialize master info structure for ''; more error messages can be found in the MariaDB error log ===


<pre>
<pre>
MariaDB [(none)]> show processlist
RESET SLAVE;
    -> ;
+----+-------------+------------------+----------+-------------+------+-----------------------------------------------------------------------------+------------------+----------+
| 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)
</pre>
</pre>


Version vom 8. Februar 2020, 13:36 Uhr

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

Probleme

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

RESET SLAVE;

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

Abgerufen von „https://tutorials.kirner.or.at/tutorials/index.php?title=MariaDB_Replikation_(Ubuntu)&oldid=6967“