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.

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'