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;