MySQL cluster
doel
Om te zorgen dat onze database server ook volledig redundant is hebben we hier een cluster gemaakt, onze loadbalancer zorgt er voor dat verkeer of op db01 of op db02 terecht komt.
opzet
normale situatie
Dit geeft weer hoe in normale situaties het verkeer loopt.
Loadbalancer faalt
Dit geeft weer hoe in een situatie het verkeer loopt als er een loadbalancer faalt.
Database server faalt
Dit geeft weer hoe in een situatie het verkeer loopt als er een Database server faalt.
database cluster 01 (hobbynet) dbclus01-int
configuratie
De twee server staan in de master master replicatie, dit wil zeggen als er mutatie wordt gedaan op server 1 dat deze wordt door gevoerd op server 2. De loadbalancer zorgt er voor dat server 1 altijd wordt aangesproken, is server 1 down dat wordt er omgeschakeld naar server2 zonder dat gebruiker hier iets van merkt.
config files
db01
de config aanpassingen die gedaan zijn op standaard config zijn:
server-id = 1 replicate-same-server-id = 0 auto-increment-increment = 2 auto-increment-offset = 1 master-host = 172.31.1.214 master-user = repl master-password = fxq5jLqWDYXYKhPD master-connect-retry = 60 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M binlog_do_db = bas # lbtest mag nooit weg ivm loadbalancing test of mysql nog werkt! binlog_do_db = lbtest binlog_do_db = xibo binlog_do_db = groepentest binlog_do_db = groepen binlog_do_db = groepen-test binlog_do_db = watersport binlog_do_db = watersport-test binlog_do_db = z-drenthe binlog_do_db = z-drenthe-test binlog_do_db = z-drenthe-hulp binlog_do_db = z-drenthe-hulp-test binlog_do_db = vandenbussche binlog_do_db = vandenbussche-test binlog_do_db = mz-limburg binlog_do_db = mz-limburg-test binlog_do_db = hccm-gg binlog_do_db = hccm-gg-test binlog_do_db = afd-gouda binlog_do_db = afd-gouda-test binlog_do_db = flightsimulator binlog_do_db = flightsimulator-test binlog_do_db = deventer binlog_do_db = deventer-test binlog_do_db = basic binlog_do_db = basic-test binlog_do_db = afd-arnhem binlog_do_db = afd-arnhem-test binlog_do_db = afd-almere binlog_do_db = afd-almere-test binlog_do_db = hcc-dagen binlog_do_db = basd binlog_do_db = basd-test binlog_do_db = int-nlb binlog_do_db = int-nlb-test binlog_ignore_db = mysql
db02
de config aanpassingen die gedaan zijn op standaard config zijn:
server-id = 2 replicate-same-server-id = 0 auto-increment-increment = 2 auto-increment-offset = 1 master-host = 172.31.1.213 master-user = repl master-password = fxq5jLqWDYXYKhPD master-connect-retry = 60 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M binlog_do_db = bas # lbtest mag nooit weg ivm loadbalancing test of mysql nog werkt! binlog_do_db = lbtest binlog_do_db = xibo binlog_do_db = groepentest binlog_do_db = groepen binlog_do_db = groepen-test binlog_do_db = watersport binlog_do_db = watersport-test binlog_do_db = z-drenthe binlog_do_db = z-drenthe-test binlog_do_db = z-drenthe-hulp binlog_do_db = z-drenthe-hulp-test binlog_do_db = vandenbussche binlog_do_db = vandenbussche-test binlog_do_db = mz-limburg binlog_do_db = mz-limburg-test binlog_do_db = hccm-gg binlog_do_db = hccm-gg-test binlog_do_db = afd-gouda binlog_do_db = afd-gouda-test binlog_do_db = flightsimulator binlog_do_db = flightsimulator-test binlog_do_db = deventer binlog_do_db = deventer-test binlog_do_db = basic binlog_do_db = basic-test binlog_do_db = afd-arnhem binlog_do_db = afd-arnhem-test binlog_do_db = afd-almere binlog_do_db = afd-almere-test binlog_do_db = hcc-dagen binlog_do_db = hcc-dagen-test binlog_do_db = basd binlog_do_db = basd-test binlog_do_db = int-nlb binlog_do_db = int-nlb-test binlog_ignore_db = mysql
database cluster 02 (hcc.nl) hcc-dbclus02-int
configuratie
De twee server staan in de master master replicatie, dit wil zeggen als er mutatie wordt gedaan op server 1 dat deze wordt door gevoerd op server 2. De loadbalancer zorgt er voor dat server 1 altijd wordt aangesproken, is server 1 down dat wordt er omgeschakeld naar server2 zonder dat gebruiker hier iets van merkt.
config files
hcc-db03
de config aanpassingen die gedaan zijn op standaard config zijn:
server-id = 3 log_bin = /var/log/mysql/mysql-bin.log replicate-same-server-id = 0 auto-increment-increment = 2 auto-increment-offset = 1 master-host = 172.31.1.216 master-user = repl master-password = fxq5jLqWDYXYKhPD master-connect-retry = 60 expire_logs_days = 10 max_binlog_size = 100M binlog_do_db = bas # lbtest mag nooit weg ivm loadbalancing test of mysql nog werkt! binlog_do_db = lbtest binlog_do_db = www-prod binlog_do_db = www-test binlog_do_db = hcc-gebruikers binlog_do_db = hcc-gebruikers-test binlog_ignore_db = mysql
hcc-db04
de config aanpassingen die gedaan zijn op standaard config zijn:
server-id = 4 replicate-same-server-id = 0 auto-increment-increment = 2 auto-increment-offset = 2 master-host = 172.31.1.215 master-user = repl master-password = fxq5jLqWDYXYKhPD master-connect-retry = 60 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M binlog_do_db = bas # lbtest mag nooit weg ivm loadbalancing test of mysql nog werkt! binlog_do_db = lbtest binlog_do_db = www-prod binlog_do_db = www-test binlog_do_db = hcc-gebruikers binlog_do_db = hcc-gebruikers-test binlog_ignore_db = mysql
database aan replicatie toevoegen
Op beide database servers moet in /etc/mysql/my.cnf het volgende regeltje worden toegevoegd
binlog_do_db = databasenaam
Hierna moet je op db01 en db02 mysql herstarten dit kan zonder probleem door de loadbalancing.
Hierna moet je op db01 en db02 de database aanmaken en user met bijbehorende rechten aanmaken.
rechten worden niet gerepliceerd
database uit replicatie verwijderen
Op beide database servers moet in /etc/mysql/my.cnf het volgende regeltje worden verwijderd:
binlog_do_db = databasenaam
Hierna moet je op db01 en db02 mysql herstarten dit kan zonder probleem door de loadbalancing.
Hierna moet je op db01 en db02 de database eventueel verwijderen en eventueel de user met bijbehorende rechten verwijderen/aanpassen.
herstel van replicatie
automatische
Als als goed gaat wordt nadat een server weer online komt hij weer helemaal upd2date gebracht.
handmatig
Op het moment dat server niet automatische up2date wordt gebracht, moet dit handmatig gebeuren
replicatie stop zetten
Dan moet op beide server het commando "stop slave;" gegeven worden.
De host die niet up2date is moet in de loadbalancer worden uitgeschakeld, indien mogelijk beide voorkomt fouten.
replicatie herstellen
voor de gefaalde host
Maak eerst backup van de gefaalde en goede host je weet maar nooit
Hierna drop je alle databases die gerepliceerd worden dus niet MYSQL!!!!
Daar doe je eerst op de master een "show master status \g" op de goede host. De gegevens hier uit hebben we nodig om de replicatie naar de gefaalde host weer op gang te brengen
mysql> show master status \g +------------------+----------+-----------------------------------------------------------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+-----------------------------------------------------------------+------------------+ | mysql-bin.000012 | 4154828 | bas,lbtest,www-prod,www-test,hcc-gebruikers,hcc-gebruikers-test | mysql | +------------------+----------+-----------------------------------------------------------------+------------------+ 1 row in set (0.00 sec)
Hier moet de huidige stand van zaken overbrengen naar de gefaalde host dat doe je als volgt:
mysqldump --all-databases | mysql -h hostgefaalt-int -u bas -pjegeheimewachtwoord
Gebruik hier natuurlijk je mysql credentials met volledige rechten.
daarna login met mysql in op de gefaalde host en geef je volgende query in.
CHANGE MASTER TO MASTER_HOST='IP goede DB server', MASTER_USER='repl', MASTER_PASSWORD='fxq5jLqWDYXYKhPD', MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=4154828 ;
De master_log_file en _master_log_pos haal je op van de eerder gedaane show master status
Hier na geef je het commando start slave;
Hier na ga je met commando show slave status\G zien of alles weer loopt als goed is ziet er dan ongeveer zo uit.
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.31.1.214
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000023
Read_Master_Log_Pos: 23081
Relay_Log_File: db01-int-relay-bin.000135
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000023
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: 23081
Relay_Log_Space: 554
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:
1 row in set (0.00 sec)
Het belangrijkste is dat dat state op Waiting for master to send event en de volgende beide op yes staan. (let op de state kan even duren voor dat hij deze status krijgt)
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Nu kan de goede host weer worden bij geschakeld in de loadbalancer want hij stuurt zijn wijzigingen door naar de ander database server.
Nu moet nog alleen replicatie van gefaalde host naar de goede host ook hersteld worden.
voor de goede host
Nu moeten we ook op de voormalig gefaalde host een "show master status \g" doen. De gegevens van hier hebben we nodig om de replicatie naar de goede host weer op gang te brengen. de waarde verschillen bijna elke keer als je het commando geeft
mysql> show master status \g +------------------+----------+-----------------------------------------------------------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+-----------------------------------------------------------------+------------------+ | mysql-bin.000012 | 4154828 | bas,lbtest,www-prod,www-test,hcc-gebruikers,hcc-gebruikers-test | mysql | +------------------+----------+-----------------------------------------------------------------+------------------+ 1 row in set (0.00 sec)
Hier naar starten we op de goede host de replicatie weer op halen bij de voormalig gefaalde DB server.
CHANGE MASTER TO MASTER_HOST='IP voormalig gefaalde DB server', MASTER_USER='repl', MASTER_PASSWORD='fxq5jLqWDYXYKhPD', MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=4154828 ;
De master_log_file en _master_log_pos haal je op van de eerder gedaane show master status
Hier na geef je het commando start slave;
Hier na ga je met commando show slave status\G zien of alles weer loopt als goed is ziet er dan ongeveer zo uit.
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.31.1.214
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000023
Read_Master_Log_Pos: 23081
Relay_Log_File: db01-int-relay-bin.000135
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000023
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: 23081
Relay_Log_Space: 554
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:
1 row in set (0.00 sec)
Het belangrijkste is dat dat state op Waiting for master to send event en de volgende beide op yes staan. (let op de state kan even duren voor dat hij deze status krijgt)
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Als ook dit in orde kan je ook deze host weer bijschakelen in de loadbalancer.
links
http://www.hbyconsultancy.com/blog/create-a-master-master-mysql-replication-ubuntu-server-10-04-x64.html de handleiding die ik gebruikt hebt in eerste instantie
http://www.neocodesoftware.com/replication/ deze handleiding heb ik later gebruikt om het een en ander te verbeteren