MySQL Master Master replication

Veröffentlicht am | Von :)

MySQL Master Master Replikation einrichten

Als Basis dient eine Standardinstallation unter Debian/Ubuntu mit MySQL 5.5

Host A: 192.168.0.10 | Host B: 192.168.1.10

In der Konfigurationsdatei unter /etc/mysql/my.cnf sind folgende Einträge hinzuzufügen bzw. anzupassen im Bereich Replication:

Host A

# Replication
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 1000M
sync_binlog = 1
log-slave-updates
server-id = 1
slave-skip-errors = 1062,1050 # Duplicate entry '%s' for key %d |Table '%s' already exists
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1

Host B

# Replication
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 1000M
sync_binlog = 1
log-slave-updates
server-id = 2
slave-skip-errors = 1062,1050 # Duplicate entry '%s' for key %d |Table '%s' already exists
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2

Die restlichen Schritte finden innerhalb von MySQL statt:

Host A

CREATE USER 'repl'@'192.168.1.10' IDENTIFIED BY 'replPW';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.10';

Host B

CREATE USER 'repl'@'192.168.0.10' IDENTIFIED BY 'replPW';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.10';

Slave aktivieren auf Host A

slave stop;
reset slave;
reset master;
change master to MASTER_HOST='192.168.1.10',MASTER_USER='repl',MASTER_PASSWORD='replPW',MASTER_PORT=3306;
slave start;

danach noch auf Host B den Slave aktivieren

slave stop;
reset slave;
reset master;
change master to MASTER_HOST='192.168.0.10',MASTER_USER='repl',MASTER_PASSWORD='replPW',MASTER_PORT=3306;
slave start;

Um zu sehen, ob der Slave auf beiden Seiten korrekt läuft geben wir folgendes ein:

Host A:

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 12
Relay_Log_File: mysqld-relay-bin.000001
Relay_Log_Pos: 2
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 1
Relay_Log_Space: 1
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
1 row in set (0.00 sec)
mysql>

Host B:

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 12
Relay_Log_File: mysqld-relay-bin.000001
Relay_Log_Pos: 2
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 1
Relay_Log_Space: 1
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: 1
1 row in set (0.00 sec)
mysql>

Wenn die Replikation mal auf einen der beiden Knoten mit einem Error hängen bleibt:

slave stop;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
slave start;

ausführen um den Fehler zu skippen

Möchte man die Replikation an einer bestimmten Stelle im Logfile starten oder wiederholen

slave stop;
CHANGE MASTER TO MASTER_LOG_FILE = 'mysqld-bin.000002', MASTER_LOG_POS = 0;
slave start;
show slave status\G

Binary logs aus Platzgründen manuell löschen

mysql> show binary logs;
+------------------+------------+
| Log_name | File_size|
+------------------+------------+
| mysql-bin.000002 | 1048576510 |
| mysql-bin.000003 | 1048587858 |
| mysql-bin.000004 | 1048608016 |
| mysql-bin.000005 | 1048577116 |
| mysql-bin.000006 | 1048584247 |
| mysql-bin.000007 |443699407 |
+------------------+------------+
6 rows in set (0.00 sec)

mysql> PURGE BINARY LOGS TO 'mysql-bin.000007';

dadurch werden alle binary logs bis auf das Letzte gelöscht

Was ist los auf dem Server

mysql> show processlist;

oder

mysql> show full processlist;
Kategorie: howtos