MySQL cluster: Difference between revisions

From Hobbynet Admin Wiki
Jump to navigation Jump to search
VisualWikitext
(layout aanpassingen)
Line 21: Line 21:


= loadbalancing =
= loadbalancing =
Omdat we maar willen dat er 1 server wordt aangesproken ipv allebij tegelijk zodat er geen locking problemen ontstaan is loadblancer in failover mode geconfigureerd. voor meer info zie [[Loadbalancers]]
Omdat we maar willen dat er 1 server wordt aangesproken (i.p.v. allebei tegelijk), zodat er geen locking problemen ontstaan, is de loadbalancer in failover mode geconfigureerd. Voor meer info zie [[Loadbalancers]]


= database cluster 01 (hobbynet) dbclus01-int =
= database cluster 01 (hobbynet) dbclus01-int =
== configuratie ==
== 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 twee servers staan in 'master master replicatie', dit wil zeggen als er een mutatie wordt gedaan op server 1 dat deze ook wordt doorgevoerd 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.
 
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 ==
== config files ==
=== db01 ===
=== db01 ===
de config aanpassingen die gedaan zijn op standaard config zijn:
De config aanpassingen die gedaan zijn op standaard config zijn:
<pre>
server-id              = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1


master-host = 172.31.1.214
server-id                = '''1'''
master-user = repl
replicate-same-server-id = 0
master-password = fxq5jLqWDYXYKhPD
auto-increment-increment = 2
master-connect-retry = 60
auto-increment-offset    = 1
log_bin                = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
master-host             = '''172.31.1.214'''
max_binlog_size        = 100M
master-user             = repl
binlog_do_db            = bas
master-password         = fxq5jLqWDYXYKhPD
# lbtest mag nooit weg ivm loadbalancing test of mysql nog werkt!
master-connect-retry   = 60
binlog_do_db            = lbtest
binlog_do_db            = xibo
log_bin                = /var/log/mysql/mysql-bin.log
binlog_do_db            = groepentest
expire_logs_days        = 10
binlog_do_db            = groepen
max_binlog_size        = 100M
binlog_do_db            = groepen-test
binlog_do_db            = bas
binlog_do_db            = watersport
# lbtest mag nooit weg ivm loadbalancing test of mysql nog werkt!
binlog_do_db            = watersport-test
binlog_do_db            = lbtest
binlog_do_db            = z-drenthe
binlog_do_db            = xibo
binlog_do_db            = z-drenthe-test
binlog_do_db            = groepentest
binlog_do_db            = z-drenthe-hulp
binlog_do_db            = groepen
binlog_do_db            = z-drenthe-hulp-test
binlog_do_db            = groepen-test
binlog_do_db            = vandenbussche
binlog_do_db            = watersport
binlog_do_db            = vandenbussche-test
binlog_do_db            = watersport-test
binlog_do_db            = mz-limburg
binlog_do_db            = z-drenthe
binlog_do_db            = mz-limburg-test
binlog_do_db            = z-drenthe-test
binlog_do_db            = hccm-gg
binlog_do_db            = z-drenthe-hulp
binlog_do_db            = hccm-gg-test
binlog_do_db            = z-drenthe-hulp-test
binlog_do_db            = afd-gouda
binlog_do_db            = vandenbussche
binlog_do_db            = afd-gouda-test
binlog_do_db            = vandenbussche-test
binlog_do_db            = flightsimulator
binlog_do_db            = mz-limburg
binlog_do_db            = flightsimulator-test
binlog_do_db            = mz-limburg-test
binlog_do_db            = deventer
binlog_do_db            = hccm-gg
binlog_do_db            = deventer-test
binlog_do_db            = hccm-gg-test
binlog_do_db            = basic
binlog_do_db            = afd-gouda
binlog_do_db            = basic-test
binlog_do_db            = afd-gouda-test
binlog_do_db            = afd-arnhem
binlog_do_db            = flightsimulator
binlog_do_db            = afd-arnhem-test
binlog_do_db            = flightsimulator-test
binlog_do_db            = afd-almere
binlog_do_db            = deventer
binlog_do_db            = afd-almere-test
binlog_do_db            = deventer-test
binlog_do_db            = hcc-dagen
binlog_do_db            = basic
binlog_do_db            = basd
binlog_do_db            = basic-test
binlog_do_db            = basd-test
binlog_do_db            = afd-arnhem
binlog_do_db            = int-nlb
binlog_do_db            = afd-arnhem-test
binlog_do_db            = int-nlb-test
binlog_do_db            = afd-almere
binlog_ignore_db        = mysql
binlog_do_db            = afd-almere-test
 
binlog_do_db            = hcc-dagen
</pre>
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 ===
=== db02 ===
de config aanpassingen die gedaan zijn op standaard config zijn:
de config aanpassingen die gedaan zijn op standaard config zijn:
<pre>
server-id              = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1


master-host = 172.31.1.213
server-id                = '''2'''
master-user = repl
replicate-same-server-id = 0
master-password = fxq5jLqWDYXYKhPD
auto-increment-increment = 2
master-connect-retry = 60
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


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
</pre>


= database cluster 02 (hcc.nl) hcc-dbclus02-int=
= database cluster 02 (hcc.nl) hcc-dbclus02-int=
== configuratie ==
== 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 twee servers staan in de 'master master replicatie' mode, dit wil zeggen als er een mutatie wordt gedaan op server 1, dat deze ook wordt doorgevoerd 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.
 
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 ==
== config files ==
=== hcc-db03 ===
=== hcc-db03 ===
de config aanpassingen die gedaan zijn op standaard config zijn:
De config aanpassingen die gedaan zijn op standaard config zijn:
<pre>
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


</pre>
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 ===
=== hcc-db04 ===
de config aanpassingen die gedaan zijn op standaard config zijn:
De config aanpassingen die gedaan zijn op standaard config zijn:
<pre>
server-id               = '''4'''
server-id               = 4
replicate-same-server-id = 0
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-increment = 2
auto-increment-offset   = 2
auto-increment-offset = 2
 
master-host             = '''172.31.1.215'''
master-host = 172.31.1.215
master-user             = repl
master-user = repl
master-password         = fxq5jLqWDYXYKhPD
master-password = fxq5jLqWDYXYKhPD
master-connect-retry     = 60
master-connect-retry = 60
log_bin                 = /var/log/mysql/mysql-bin.log
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days       = 10
expire_logs_days         = 10
max_binlog_size         = 100M
max_binlog_size         = 100M
binlog_do_db           = bas
binlog_do_db             = bas
# lbtest mag nooit weg ivm loadbalancing test of mysql nog werkt!
# lbtest mag nooit weg ivm loadbalancing test of mysql nog werkt!
binlog_do_db           = lbtest
binlog_do_db             = lbtest
binlog_do_db           = www-prod
binlog_do_db             = www-prod
binlog_do_db           = www-test
binlog_do_db             = www-test
binlog_do_db           = hcc-gebruikers
binlog_do_db             = hcc-gebruikers
binlog_do_db           = hcc-gebruikers-test
binlog_do_db             = hcc-gebruikers-test
binlog_ignore_db       = mysql
binlog_ignore_db         = mysql
 
</pre>


= database aan replicatie toevoegen =
= database aan replicatie toevoegen =
Line 202: Line 201:
binlog_do_db            = databasenaam
binlog_do_db            = databasenaam
</pre>
</pre>
Hierna moet je op db01 en db02 mysql herstarten dit kan zonder probleem door de loadbalancing.<br>
Hierna moet je op db01 en db02 mysql herstarten (dit kan zonder probleem door de loadbalancing).<br>
Hierna moet je op db01 en db02 de database aanmaken en user met bijbehorende rechten aanmaken.<br>
Hierna moet je op db01 en db02 de database aanmaken en user met bijbehorende rechten aanmaken.<br>
'''rechten worden niet gerepliceerd'''
'''rechten worden niet gerepliceerd'''
Line 211: Line 210:
binlog_do_db            = databasenaam
binlog_do_db            = databasenaam
</pre>
</pre>
Hierna moet je op db01 en db02 mysql herstarten dit kan zonder probleem door de loadbalancing.<br>
Hierna moet je op db01 en db02 mysql herstarten (dit kan zonder probleem door de loadbalancing).<br>
Hierna moet je op db01 en db02 de database eventueel verwijderen en eventueel de user met bijbehorende rechten verwijderen/aanpassen.<br>
Hierna moet je op db01 en db02 de database eventueel verwijderen en eventueel de user met bijbehorende rechten verwijderen/aanpassen.<br>
= status replicatie bekijken =
= status replicatie bekijken =
== goede status ==
== goede status ==
een goede status ziet er als volgt uit:
Een goede status ziet er als volgt uit:
<pre>
<pre>
mysql> show slave status\G
mysql> show slave status\G
Line 259: Line 258:
1 row in set (0.00 sec)
1 row in set (0.00 sec)
</pre>
</pre>
Zo wel Slave_IO_Running als Slave_SQL_Running staan op yes, en Slave_IO_State staat op Waiting for master to send event
Zowel Slave_IO_Running als Slave_SQL_Running staan op 'yes', en Slave_IO_State staat op 'Waiting for master to send event'


== foute status ==
== foute status ==
Line 306: Line 305:
1 row in set (0.00 sec)
1 row in set (0.00 sec)
</pre>
</pre>
zoals je kan zien staat Slave_IO_Running op No en staat er melding bij Last_IO_Error. In dit geval staat de ander db server gewoon uit voor bij voorbeeld een OS update. Dit probleem zal zich daarom zelf weer herstellen
zoals je kan zien staat Slave_IO_Running op 'No' en staat er een melding bij Last_IO_Error. In dit geval staat de ander db server gewoon uit voor bijvoorbeeld een OS update. Dit probleem zal zich daarom vanzelf weer herstellen.


= herstel van replicatie =
= herstel van replicatie =
== automatische ==
== automatisch ==
Als als goed gaat wordt nadat een server weer online komt hij weer helemaal upd2date gebracht.
Als als goed gaat wordt nadat een server weer online komt hij weer helemaal upd2date gebracht.
== handmatig ==
== handmatig ==
Op het moment dat server niet automatische up2date wordt gebracht, moet dit handmatig gebeuren
Op het moment dat een server niet automatisch up2date wordt gebracht, moet dit handmatig gebeuren.
=== replicatie stop zetten ===
=== replicatie stop zetten ===
Dan moet op beide server het commando "stop slave;" gegeven worden.<br>
Dan moet op beide server het commando "stop slave;" gegeven worden.<br>
Line 318: Line 317:
=== replicatie herstellen ===
=== replicatie herstellen ===
==== voor de gefaalde host ====
==== voor de gefaalde host ====
Maak eerst backup van de gefaalde en goede host je weet maar nooit<br>
Maak eerst backup van de gefaalde en goede host je weet maar nooit.<br>
Hierna drop je alle databases die gerepliceerd worden '''dus niet MYSQL!!!!'''<br>  
Hierna drop je alle databases die gerepliceerd worden '''dus niet MYSQL!!!!'''<br>  
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   
Daar doe je eerst op de master een "show master status \g" op de goede host. De gegevens hieruit hebben we nodig om de replicatie naar de gefaalde host weer op gang te brengen.  
<pre>
<pre>
mysql> show master status \g
mysql> show master status \g
Line 340: Line 339:
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 ;
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 ;
</pre>
</pre>
De master_log_file en _master_log_pos haal je op van de eerder gedaane show master status<br>
De master_log_file en _master_log_pos haal je op van de eerder gedane 'show master status'<br>
Hier na geef je het commando start slave;<br>
Hierna geef je het commando 'start slave;'<br>
Hier na ga je met commando show slave status\G zien of alles weer loopt als goed is ziet er dan ongeveer zo uit.
Hierna ga je het commando 'show slave status\G' om te zien of alles weer loopt, als het goed is ziet het er dan ongeveer zo uit.
<pre>
<pre>
mysql> show slave status\G
mysql> show slave status\G
Line 386: Line 385:
1 row in set (0.00 sec)
1 row in set (0.00 sec)
</pre>
</pre>
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)
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 voordat hij deze status krijgt)
<pre>
<pre>
           Slave_IO_State: Waiting for master to send event
           Slave_IO_State   : Waiting for master to send event
           Slave_IO_Running: Yes
           Slave_IO_Running : Yes
           Slave_SQL_Running: Yes
           Slave_SQL_Running: Yes
</pre>
</pre>
Nu kan de goede host weer worden bij geschakeld in de loadbalancer want hij stuurt zijn wijzigingen door naar de ander database server.<br>
Nu kan de goede host weer worden bijgeschakeld in de loadbalancer, want hij stuurt zijn wijzigingen door naar de ander database server.<br>
Nu moet nog alleen replicatie van gefaalde host naar de goede host ook hersteld worden.
Nu moet nog alleen de replicatie van de gefaalde host naar de goede host ook hersteld worden.


==== voor de goede host ====
==== voor de goede host ====
Line 406: Line 405:
1 row in set (0.00 sec)
1 row in set (0.00 sec)
</pre>
</pre>
Hier naar starten we op de goede host de replicatie weer op halen bij de voormalig gefaalde DB server.  
Hierna starten we op de goede host de replicatie weer op halen bij de voormalig gefaalde DB server.
''(??wat wordt hier bedoeld??)''
 
<pre>
<pre>
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 ;
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 ;
</pre>
</pre>
De master_log_file en _master_log_pos haal je op van de eerder gedaane show master status<br>
De master_log_file en _master_log_pos haal je op van de eerder gedane 'show master status'<br>
Hier na geef je het commando start slave;<br>
Hierna geef je het commando 'start slave;'<br>
Hier na ga je met commando show slave status\G zien of alles weer loopt als goed is ziet er dan ongeveer zo uit.
Hierna kan je met het commando 'show slave status\G' zien of alles weer loopt, als het goed is ziet er dan ongeveer zo uit.
<pre>
<pre>
mysql> show slave status\G
mysql> show slave status\G
Line 456: Line 457:
1 row in set (0.00 sec)
1 row in set (0.00 sec)
</pre>
</pre>
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)
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).
<pre>
<pre>
           Slave_IO_State: Waiting for master to send event
           Slave_IO_State   : Waiting for master to send event
           Slave_IO_Running: Yes
           Slave_IO_Running : Yes
           Slave_SQL_Running: Yes
           Slave_SQL_Running: Yes
</pre>
</pre>
Als ook dit in orde kan je ook deze host weer bijschakelen in de loadbalancer.
Als ook dit in orde is kan je ook deze host weer bijschakelen in de loadbalancer.


= links =
= 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<br>
http://www.hbyconsultancy.com/blog/create-a-master-master-mysql-replication-ubuntu-server-10-04-x64.html de handleiding die ik gebruikt heb in eerste instantie<br>
http://www.neocodesoftware.com/replication/ deze handleiding heb ik later gebruikt om het een en ander te verbeteren
http://www.neocodesoftware.com/replication/ deze handleiding heb ik later gebruikt om het een en ander te verbeteren

Revision as of 13:39, 26 September 2011

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.

File:DBnormaal.dia

Loadbalancer faalt

Dit geeft weer hoe in een situatie het verkeer loopt als er een loadbalancer faalt.

File:DBlbfaalt.dia

Database server faalt

Dit geeft weer hoe in een situatie het verkeer loopt als er een Database server faalt.

File:DBdbfaalt.dia

loadbalancing

Omdat we maar willen dat er 1 server wordt aangesproken (i.p.v. allebei tegelijk), zodat er geen locking problemen ontstaan, is de loadbalancer in failover mode geconfigureerd. Voor meer info zie Loadbalancers

database cluster 01 (hobbynet) dbclus01-int

configuratie

De twee servers staan in 'master master replicatie', dit wil zeggen als er een mutatie wordt gedaan op server 1 dat deze ook wordt doorgevoerd 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 servers staan in de 'master master replicatie' mode, dit wil zeggen als er een mutatie wordt gedaan op server 1, dat deze ook wordt doorgevoerd 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.

status replicatie bekijken

goede status

Een goede status ziet er als volgt 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)

Zowel Slave_IO_Running als Slave_SQL_Running staan op 'yes', en Slave_IO_State staat op 'Waiting for master to send event'

foute status

Zo ziet een foute status er uit. 

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Reconnecting after a failed master event read
                  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.000136
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000023
             Slave_IO_Running: No
            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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2013
                Last_IO_Error: error reconnecting to master 'repl@172.31.1.214:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

zoals je kan zien staat Slave_IO_Running op 'No' en staat er een melding bij Last_IO_Error. In dit geval staat de ander db server gewoon uit voor bijvoorbeeld een OS update. Dit probleem zal zich daarom vanzelf weer herstellen.

herstel van replicatie

automatisch

Als als goed gaat wordt nadat een server weer online komt hij weer helemaal upd2date gebracht.

handmatig

Op het moment dat een server niet automatisch 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 hieruit 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 --databases db1 db2 db3 | mysql -h hostgefaalt-int -u bas -pjegeheimewachtwoord

letop je kan niet --all-databases gebruiken want dan breng je ook de mysql tabel over 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 gedane 'show master status'
Hierna geef je het commando 'start slave;'
Hierna ga je het commando 'show slave status\G' om te zien of alles weer loopt, als het goed is ziet het 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 voordat 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 bijgeschakeld in de loadbalancer, want hij stuurt zijn wijzigingen door naar de ander database server.
Nu moet nog alleen de replicatie van de 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)

Hierna starten we op de goede host de replicatie weer op halen bij de voormalig gefaalde DB server. (??wat wordt hier bedoeld??) 

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 gedane 'show master status'
Hierna geef je het commando 'start slave;'
Hierna kan je met het commando 'show slave status\G' zien of alles weer loopt, als het 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 is 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 heb in eerste instantie
http://www.neocodesoftware.com/replication/ deze handleiding heb ik later gebruikt om het een en ander te verbeteren

Retrieved from "https://wiki.hobby.nl/index.php?title=MySQL_cluster&oldid=4388"