MySQL cluster: Difference between revisions

From Hobbynet Admin Wiki
Jump to navigation Jump to search
VisualWikitext
 
(46 intermediate revisions by 3 users not shown)
Line 2: Line 2:
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.
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=
=opzet=
== normale situatie ==
Dit geeft weer hoe in normale situaties het verkeer loopt.<br>
[[File:DBnormaal.png]]
[[File:DBnormaal.dia]]
== Loadbalancer faalt ==
Dit geeft weer hoe in een situatie het verkeer loopt als er een loadbalancer faalt.<br>
[[File:DBlbfaalt.png]]
[[File:DBlbfaalt.dia]]
== Database server faalt ==
Dit geeft weer hoe in een situatie het verkeer loopt als er een Database server faalt.<br>
[[File:DBdbfaalt.png]]
[[File:DBdbfaalt.dia]]


= loadbalancing =
= 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]]
Onze servers zijn opgezet in een master-master setup, dus er kan op beide gemuteerd worden, de loadbalancer verspreid de mutatie naar de andere server.<br>
 
'''belangrijk'''<br>
= database cluster 01 (hobbynet) dbclus01-int =
Op alle clusters moet database lbtest aanwezig zijn met tabel lbtest. Deze tabel moet minimaal 1 record hebben, hier test de loadbalancer namelijk op. De usernaam voor loadbalancer en nagios is lbtest met ww Testing123! deze gebruiker heeft alleen select rechten op deze database
== 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 ==
= database clusters =
=== db01 ===
== dbclus01-int ==
De config aanpassingen die gedaan zijn op standaard config zijn:
Dit cluster bestaat uit mysql01 en mysql02


server-id                = '''1'''
== hcc-dbclus02-int ==
replicate-same-server-id = 0
Dit cluster bestaat uit mysql03 en mysql04
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 ===
== dbclus04-int ==
de config aanpassingen die gedaan zijn op standaard config zijn:
Dit cluster bestaat uit mysql05 en mysql06
== dbclus05-int ==
Dit cluster bestaat uit mysql07 en mysql08


server-id                = '''2'''
=configuratie =  
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset    = 2
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 files ==  
== configuratie ==
al deze bestanden bevinden zich in /etc/mysql/mysql.conf.d
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.
=== mysqld.cnf ===
In deze file hebben we 1 aanpassing gedaan we hebben
bind-address          = 127.0.0.1
voorzien van commentaar


== config files ==
=== ssl.cnf ===
=== hcc-db03 ===
Mysql beschikt over ssl daarom is dit ook geconfigureerd, onder andere de replicatie gebruikt het. De ssl.cnf file ziet er zo uit:
De config aanpassingen die gedaan zijn op standaard config zijn:
[mysqld]
ssl-ca=/disk/site/etc/ssl/hobby.nl/CA-bundel.crt
ssl-cert=/disk/site/etc/ssl/hobby.nl/hobby.nl.crt
ssl-key=/disk/site/etc/ssl/hobby.nl/hobby.nl.key


server-id                = '''3'''
=== replicatie.cnf ===
log_bin                  = /var/log/mysql/mysql-bin.log
Bij de replicatie is het belangrijk dat de oneven servers het server id 1 hebben en de even server id 2. Er mogen nooit 2 de zelfde id's zijn. Ook is de waarde van auto-increment-offset belangrijk; bij de oneven server is dat 1 en bij even 2. <br>
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 ===
Voor oneven server ziet hij er zo uit:
De config aanpassingen die gedaan zijn op standaard config zijn:
#sql mode uit voor toestaan lege velden
  server-id               = '''4'''
sql-mode                        =       ""
  replicate-same-server-id = 0
  server-id                       =       1
  auto-increment-increment = 2
  replicate-same-server-id       =       0
  auto-increment-offset   = 2
  auto-increment-increment       =       2
   
  auto-increment-offset           =       1
  master-host              = '''172.31.1.215'''
  relay-log                      =      mysql01-relay-bin
  master-user              = repl
  binlog_format                  =       "STATEMENT"
  master-password          = fxq5jLqWDYXYKhPD
  replicate-ignore-db            =       information_schema
  master-connect-retry    = 60
  replicate-ignore-db            =       mysql
   
  binlog-ignore-db                =       information_schema
  log_bin                 = /var/log/mysql/mysql-bin.log
  binlog-ignore-db                =      mysql
  expire_logs_days        = 10
  log_bin                         =       /var/log/mysql/mysql-bin.log
max_binlog_size          = 100M
  slave-skip-errors = 1062
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 =
=== credentials.cnf ===
Op beide database servers moet in /etc/mysql/my.cnf het volgende regeltje worden toegevoegd
Op de server is root voor localhost voorzien van een wachtwoord om toch mysqldump en het mysql commando te kunnen gebruiken. De eigenaar van deze file is root:mysql met permissies 640.
<pre>
binlog_do_db            = databasenaam
</pre>
Hierna moet je op db01 en db02 mysql herstarten (dit kan zonder probleem door de loadbalancing).<br>
Hierna moet je op db01 de database aanmaken en user met bijbehorende rechten aanmaken op db01 en db02.<br>
'''rechten worden niet gerepliceerd'''


= database uit replicatie verwijderen =
=== tunning.cnf ===
Op beide database servers moet in /etc/mysql/my.cnf het volgende regeltje worden verwijderd:
Deze file bevat performance tuning. Dit is server specifiek, maar er moet in ieder geval deze regel in staan :
<pre>
[mysqld]
binlog_do_db            = databasenaam
skip-name-resolve    =       1
</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 de database eventueel verwijderen en eventueel de user met bijbehorende rechten verwijderen/aanpassen.<br>
= status replicatie bekijken =
== goede status ==
Een goede status ziet er als volgt uit:
<pre>
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)
</pre>
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 ==
= starten/herstellen replicatie =
Zo ziet een foute status er uit.
== herstel ==
<pre>
Bij herstellen moet je op beide servers de volgende sql commando's uitvoeren:
mysql> show slave status\G
stop slave;
*************************** 1. row ***************************
reset slave;
              Slave_IO_State: Reconnecting after a failed master event read
Op de '''defecte''' server verwijder je vervolgens alle databases '''nadat je eerst een backup hebt gemaakt'''
                  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)
</pre>
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 =
=== start replicatie ===  
== automatisch ==
Zorg er voor dat de database niet meer benaderd/gemuteerd wordt. Zet hem uit in de loadbalancer, zowel voor IPv4 als voor IPv6<br>
Als als goed gaat wordt nadat een server weer online komt hij weer helemaal upd2date gebracht.
Haal bij de '''goede''' server de log positie op:
== handmatig ==
  mysql> show master status\G
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.<br>
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.<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 hieruit hebben we nodig om de replicatie naar de gefaalde host weer op gang te brengen. 
<pre>
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)
</pre>
Hier moet de huidige stand van zaken overbrengen naar de gefaalde host dat doe je als volgt:
<pre>
mysqldump --databases db1 db2 db3 | mysql -h hostgefaalt-int -u bas -pjegeheimewachtwoord
</pre>
'''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.<br>
daarna login met mysql in op de gefaalde host en geef je volgende query in.<br>
<pre>
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>
De master_log_file en _master_log_pos haal je op van de eerder gedane 'show master status'<br>
Hierna geef je het commando 'start slave;'<br>
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>
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)
</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 voordat hij deze status krijgt)
<pre>
          Slave_IO_State  : Waiting for master to send event
          Slave_IO_Running : Yes
          Slave_SQL_Running: Yes
</pre>
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 de replicatie van de gefaalde host naar de goede host ook hersteld worden.


==== voor de goede host ====
  *************************** 1. row ***************************
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'''
              File: mysql-bin.000057
<pre>
          Position: 71061044
mysql> show master status \g
      Binlog_Do_DB:
+------------------+----------+-----------------------------------------------------------------+------------------+
  Binlog_Ignore_DB: information_schema,mysql
| File            | Position | Binlog_Do_DB                                                   | Binlog_Ignore_DB |
Executed_Gtid_Set:
+------------------+----------+-----------------------------------------------------------------+------------------+
1 row in set (0.00 sec)
| mysql-bin.000012 |  4154828 | bas,lbtest,www-prod,www-test,hcc-gebruikers,hcc-gebruikers-test | mysql           |
Noteer de file en position<br>
+------------------+----------+-----------------------------------------------------------------+------------------+
maak mysqldump en pipe die naar de gefaalde server
1 row in set (0.00 sec)
  mysqldump --all-databases |mysql -h mysql0*-int -u jegebruikersnaam -p
</pre>
Vul je wachtwoord in en de database wordt gedump naar de ander server (dit kan effe duren).
Hierna starten we op de goede host de replicatie weer op halen bij de voormalig gefaalde DB server.
''(??wat wordt hier bedoeld??)''


<pre>
Op de andere server log je nu in en geef je het volgende commando's in mysql, vul dan het password in van repl user en position bij master_log_pos en de file bij master_log_file
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='mysql0*-int.hobby.nl', MASTER_USER='repl', '''MASTER_PASSWORD='******',''' '''MASTER_LOG_FILE='mysql-bin.000057',''' '''MASTER_LOG_POS=71061044,''' MASTER_SSL = 1, MASTER_SSL_CA = '/disk/site/etc/ssl/hobby.nl/CA-bundel.crt', MASTER_SSL_CERT = '/disk/site/etc/ssl/hobby.nl/hobby.nl.crt', MASTER_SSL_KEY = '/disk/site/etc/ssl/hobby.nl/hobby.nl.key';
</pre>
start slave;
De master_log_file en _master_log_pos haal je op van de eerder gedane 'show master status'<br>
Hierna geef je het commando 'start slave;'<br>
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>
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)
</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).
<pre>
          Slave_IO_State  : Waiting for master to send event
          Slave_IO_Running : Yes
          Slave_SQL_Running: Yes
</pre>
Als ook dit in orde is kan je ook deze host weer bijschakelen in de loadbalancer.


= links =
Nu moeten we controleren of de replicatie deze kant op loopt, dit doe je met het commando
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>
mysql> show slave status\G
http://www.neocodesoftware.com/replication/ deze handleiding heb ik later gebruikt om het een en ander te verbeteren
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql02-int.hobby.nl
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000026
          Read_Master_Log_Pos: 72292974
                Relay_Log_File: mysql01-relay-bin.000056
                Relay_Log_Pos: 72293189
        Relay_Master_Log_File: mysql-bin.000026
              '''Slave_IO_Running: Yes'''
            '''Slave_SQL_Running: Yes'''
              Replicate_Do_DB:
          Replicate_Ignore_DB: information_schema,mysql
            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: 72292974
              Relay_Log_Space: 72293447
              Until_Condition: None
                Until_Log_File:
                Until_Log_Pos: 0
            Master_SSL_Allowed: Yes
            Master_SSL_CA_File: /disk/site/etc/ssl/hobby.nl/CA-bundel.crt
            Master_SSL_CA_Path:
              Master_SSL_Cert: /disk/site/etc/ssl/hobby.nl/hobby.nl.crt
            Master_SSL_Cipher:
                Master_SSL_Key: /disk/site/etc/ssl/hobby.nl/hobby.nl.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
                  Master_UUID: f0de239e-2c10-11eb-b1d1-005056a982be
              Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
            Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
      Last_SQL_Error_Timestamp:
                Master_SSL_Crl:
            Master_SSL_Crlpath:
            Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
          Replicate_Rewrite_DB:
                  Channel_Name:
            Master_TLS_Version:
        Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.00 sec)
Controleer of '''Slave_IO_Running''' en '''Slave_SQL_Running''' op yes staan.<br>
Als dit is het geval moeten we de replicatie naar de 1e server ook herstellen. Dit doe je door dezelfde stappen te herhalen vanaf 'show master status'

Latest revision as of 12:15, 30 December 2020

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

loadbalancing

Onze servers zijn opgezet in een master-master setup, dus er kan op beide gemuteerd worden, de loadbalancer verspreid de mutatie naar de andere server.
belangrijk
Op alle clusters moet database lbtest aanwezig zijn met tabel lbtest. Deze tabel moet minimaal 1 record hebben, hier test de loadbalancer namelijk op. De usernaam voor loadbalancer en nagios is lbtest met ww Testing123! deze gebruiker heeft alleen select rechten op deze database

database clusters

dbclus01-int

Dit cluster bestaat uit mysql01 en mysql02

hcc-dbclus02-int

Dit cluster bestaat uit mysql03 en mysql04

dbclus04-int

Dit cluster bestaat uit mysql05 en mysql06

dbclus05-int

Dit cluster bestaat uit mysql07 en mysql08

configuratie

configuratie files

al deze bestanden bevinden zich in /etc/mysql/mysql.conf.d

mysqld.cnf

In deze file hebben we 1 aanpassing gedaan we hebben 

bind-address           = 127.0.0.1

voorzien van commentaar

ssl.cnf

Mysql beschikt over ssl daarom is dit ook geconfigureerd, onder andere de replicatie gebruikt het. De ssl.cnf file ziet er zo uit: 

[mysqld]
ssl-ca=/disk/site/etc/ssl/hobby.nl/CA-bundel.crt
ssl-cert=/disk/site/etc/ssl/hobby.nl/hobby.nl.crt
ssl-key=/disk/site/etc/ssl/hobby.nl/hobby.nl.key

replicatie.cnf

Bij de replicatie is het belangrijk dat de oneven servers het server id 1 hebben en de even server id 2. Er mogen nooit 2 de zelfde id's zijn. Ook is de waarde van auto-increment-offset belangrijk; bij de oneven server is dat 1 en bij even 2.

Voor oneven server ziet hij er zo uit: 

#sql mode uit voor toestaan lege velden
sql-mode                        =       ""
server-id                       =       1
replicate-same-server-id        =       0
auto-increment-increment        =       2
auto-increment-offset           =       1
relay-log                       =       mysql01-relay-bin
binlog_format                   =       "STATEMENT"
replicate-ignore-db             =       information_schema
replicate-ignore-db             =       mysql
binlog-ignore-db                =       information_schema
binlog-ignore-db                =       mysql
log_bin                         =       /var/log/mysql/mysql-bin.log
slave-skip-errors = 1062

credentials.cnf

Op de server is root voor localhost voorzien van een wachtwoord om toch mysqldump en het mysql commando te kunnen gebruiken. De eigenaar van deze file is root:mysql met permissies 640.

tunning.cnf

Deze file bevat performance tuning. Dit is server specifiek, maar er moet in ieder geval deze regel in staan : 

[mysqld]
skip-name-resolve    =       1

starten/herstellen replicatie

herstel

Bij herstellen moet je op beide servers de volgende sql commando's uitvoeren: 

stop slave;
reset slave;

Op de defecte server verwijder je vervolgens alle databases nadat je eerst een backup hebt gemaakt

start replicatie

Zorg er voor dat de database niet meer benaderd/gemuteerd wordt. Zet hem uit in de loadbalancer, zowel voor IPv4 als voor IPv6
Haal bij de goede server de log positie op: 

mysql> show master status\G

*************************** 1. row ***************************
             File: mysql-bin.000057
         Position: 71061044
     Binlog_Do_DB:
 Binlog_Ignore_DB: information_schema,mysql
Executed_Gtid_Set:
1 row in set (0.00 sec)

Noteer de file en position
maak mysqldump en pipe die naar de gefaalde server 

 mysqldump --all-databases |mysql -h mysql0*-int -u jegebruikersnaam -p

Vul je wachtwoord in en de database wordt gedump naar de ander server (dit kan effe duren).

Op de andere server log je nu in en geef je het volgende commando's in mysql, vul dan het password in van repl user en position bij master_log_pos en de file bij master_log_file 

CHANGE MASTER TO MASTER_HOST='mysql0*-int.hobby.nl', MASTER_USER='repl', MASTER_PASSWORD='******', MASTER_LOG_FILE='mysql-bin.000057', MASTER_LOG_POS=71061044, MASTER_SSL = 1, MASTER_SSL_CA = '/disk/site/etc/ssl/hobby.nl/CA-bundel.crt', MASTER_SSL_CERT = '/disk/site/etc/ssl/hobby.nl/hobby.nl.crt', MASTER_SSL_KEY = '/disk/site/etc/ssl/hobby.nl/hobby.nl.key';
start slave;

Nu moeten we controleren of de replicatie deze kant op loopt, dit doe je met het commando 

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql02-int.hobby.nl
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000026
          Read_Master_Log_Pos: 72292974
               Relay_Log_File: mysql01-relay-bin.000056
                Relay_Log_Pos: 72293189
        Relay_Master_Log_File: mysql-bin.000026
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: information_schema,mysql
           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: 72292974
              Relay_Log_Space: 72293447
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /disk/site/etc/ssl/hobby.nl/CA-bundel.crt
           Master_SSL_CA_Path:
              Master_SSL_Cert: /disk/site/etc/ssl/hobby.nl/hobby.nl.crt
            Master_SSL_Cipher:
               Master_SSL_Key: /disk/site/etc/ssl/hobby.nl/hobby.nl.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
                  Master_UUID: f0de239e-2c10-11eb-b1d1-005056a982be
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.00 sec)

Controleer of Slave_IO_Running en Slave_SQL_Running op yes staan.
Als dit is het geval moeten we de replicatie naar de 1e server ook herstellen. Dit doe je door dezelfde stappen te herhalen vanaf 'show master status'

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