Handleiding voor het Implementeren van een Failover-Systeem voor MySQL Database Cluster Inleiding Deze handleiding is bedoeld voor het IT Operations Team met een gemiddeld niveau van ervaring. Het beschrijft stapsgewijs hoe u een betrouwbare failover-architectuur voor een MySQL Database Cluster opzet, inclusief hardware- en softwarekeuzes, configuratie, testen en bewaking. Doel is maximale beschikbaarheid, gegevensintegriteit en minimale downtime. 1. Architectuurkeuzes 1.1. Cluster Topologie - Master-Slave Replica’s: Een primaire master met één of meer lees- of standby-slave servers. - Multi-Master (optioneel): voor high write-availability, complexer en vereist geavanceerde oplossingen zoals Galera Cluster. Voor eenvoud en stabiliteit kiezen wij voor een Master-Slave setup met failover. 1.2. Failover Strategie - Automatische failover met tools zoals MHA (MySQL High Availability), Orchestrator of ProxySQL. - Load balancing via ProxySQL of HAProxy. 1.3. Geografische Overwegingen - Locatie van servers: bij voorkeur op verschillende datacenters of zones voor bescherming tegen uitval. 2. Benodigde Hardware en Software 2.1. Hardware - Minimaal drie servers: * 1 voor de master (met voldoende CPU, RAM en opslag) * 2 voor slaves (voor replicatie en failover) - Opslag: SSD voor snelle I/O - Netwerk: betrouwbare en gesegregeerde verbindingen tussen servers 2.2. Software - MySQL Server (bijvoorbeeld MySQL 8.0) - Failover management tool (bijvoorbeeld MHA, Orchestrator) - Load balancer (HAProxy of ProxySQL) - Monitoring tools (Nagios, Prometheus, Grafana) 3. Configuratiestappen 3.1. MySQL Installatie en Configuratie - Installeer MySQL op alle servers. - Configureer de master: [mysqld] server-id=1 log_bin=binlog binlog_format=ROW enforce_gtid_consistency=ON gtid_mode=ON master_info_repository=TABLE relay_log_info_repository=TABLE - Op slaves: [mysqld] server-id=2 (voor tweede server), 3, etc. replicate_same_site=1 relay_log=relay-log log_bin=binlog 3.2. Replicatie Instellen - Zet de master klaar: - Maak een gebruikersaccount voor replicatie: CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; - Op slaves: - Voer in: CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='repl', MASTER_PASSWORD='repl_password', MASTER_AUTO_POSITION=1; - Start replicatie: START SLAVE; - Controleer: SHOW SLAVE STATUS\G 3.3. Failover Automatisering met MHA - Installeer MHA Manager en Node: sudo apt-get install mha4mysql-manager mha4mysql-node - Configureer mha.cnf: [server default] user=mha_user password=mha_password ssh_user=admin [server1] HOST=master_ip [server2] HOST=slave_ip - Test de connectiviteit en initialiseer de MHA repository. 3.4. Load Balancer Configuratie - Configureer HAProxy of ProxySQL: - HAProxy voorbeeld: frontend mysql_front bind *:3306 default_backend mysql_back backend mysql_back server master_server master_ip:3306 check server slave_server slave_ip:3306 check - Voor automatische failover, pas de backend dynamisch aan via scripts of Orchestrator. 4. Testprocedures 4.1. Failover Test - Simuleer uitval door de master uit te schakelen. - Controleer of MHA of Orchestrator de slave promoot tot nieuwe master. - Verifieer dat de applicaties verbinding maken met de nieuwe master. 4.2. Replicatie Controle - Voeg nieuwe data toe op de master. - Controleer of data correct wordt gerepliceerd naar de slaves. 4.3. Load Balancing - Voer query’s uit en observeer of ze correct worden verdeeld. - Test de automatische switch bij uitval. 5. Bewakingsmechanismen 5.1. Monitoring - Gebruik Prometheus met MySQL exporters om status te monitoren. - Stel alerts in voor replication lag, server downtime, en diskgebruik. 5.2. Logging - Configureer uitgebreide logging voor MySQL en failover tools. - Houd logs regelmatig na voor afwijkingen. 5.3. Periodieke Testen - Plan maandelijkse failover drills. - Controleer herstelprocedures en documenteer eventuele problemen. 6. Valkuilen en Tips - Zorg voor consistente GTID-configuratie om replicatieproblemen te voorkomen. - Houd rekening met replicatie lag; stel maximale lagwaarden in. - Test failover scenario’s in een staging omgeving voordat in productie. - Automatiseer zoveel mogelijk, maar houd handmatige controle als fallback. - Documenteer alle configuraties en wijzigingen. 7. Onderhoud en Optimalisatie - Houd MySQL up-to-date met security patches. - Optimaliseer queries en indexen op de database. - Monitor resources en schaal indien nodig. - Review en update failover scripts en procedures jaarlijks. Conclusie Een goed geïmplementeerd failover-systeem verhoogt de beschikbaarheid en betrouwbaarheid van uw MySQL Database Cluster. Volg deze handleiding, test regelmatig en blijf alert op optimalisatiemogelijkheden. Bij complexe situaties of onduidelijkheden, raadpleeg gespecialiseerde MySQL- en high availability-experts. Heeft u verdere vragen of behoefte aan specifieke voorbeelden?

### Technische Handleiding: Implementatie MySQL Database Cluster Failover-Systeem **Doelgroep:** IT Operations Team (Intermediate niveau) **Scope:** High-availability architectuur met automatische failover voor MySQL databases --- ## 1. Architectuurkeuzes **Aanbevolen Topologie:** - **Primary-Secondary Replicatie** met 3 nodes - Gebruik van **MySQL Group Replication** of **Percona XtraDB Cluster** - Implementatie van **ProxySQL** als connection router - **On-premise** of **cloud-gebaseerde** implementatie mogelijk **Architectuurcomponenten:** ``` [Applicatie] → [ProxySQL Load Balancer] → [MySQL Node 1 (Primary)] ↓ [MySQL Node 2 (Secondary)] ↓ [MySQL Node 3 (Secondary)] ``` **Keuzemotivatie:** - Group Replication biedt native automatische failover - ProxySQL zorgt voor transparante failover voor applicaties - Oneven aantal nodes voorkomt split-brain scenario's --- ## 2. Benodigde Hardware/Software **Hardware Requirements (per node):** - CPU: 8+ cores - RAM: 16GB+ (afhankelijk van dataset) - Storage: SSD met minstens 2x database grootte - Netwerk: 10Gbps interconnect **Software Stack:** - MySQL 8.0+ of Percona Server 8.0 - ProxySQL 2.0+ - Corosync/Pacemaker (optioneel) - Monitoring: Prometheus + Grafana --- ## 3. Gedetailleerde Configuratiestappen ### Stap 1: MySQL Group Replication Setup **Node 1 Configuratie (/etc/mysql/my.cnf):** ```ini [mysqld] server_id=1 gtid_mode=ON enforce_gtid_consistency=ON master_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE log_slave_updates=ON log_bin=binlog binlog_format=ROW transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" loose-group_replication_start_on_boot=off loose-group_replication_local_address= "node1:33061" loose-group_replication_group_seeds= "node1:33061,node2:33061,node3:33061" loose-group_replication_bootstrap_group=off ``` **Group Replication Initialisatie:** ```sql -- Op alle nodes INSTALL PLUGIN group_replication SONAME 'group_replication.so'; -- Alleen op eerste node SET GLOBAL group_replication_bootstrap_group=ON; START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_group=OFF; -- Andere nodes joinen START GROUP_REPLICATION; ``` ### Stap 2: ProxySQL Configuratie **Basisconfiguratie:** ```sql INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, 'node1', 3306), (1, 'node2', 3306), (1, 'node3', 3306); INSERT INTO mysql_replication_hostgroups VALUES (1, 2, 'cluster1'); LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; ``` ### Stap 3: Automatische Failover Instellen **Health Check Script:** ```bash #!/bin/bash # /usr/local/bin/mysql_failover.sh PRIMARY_NODE=$(mysql -h proxy -P 6032 -u admin -padmin -N -e "SELECT hostname FROM runtime_mysql_servers WHERE status='ONLINE' AND hostgroup_id=1 LIMIT 1;") if [ -z "$PRIMARY_NODE" ]; then # Failover logica mysql -h proxy -P 6032 -u admin -padmin -e "CALL mysql_servers_sync()" fi ``` --- ## 4. Testprocedures ### Failover Test Scenario's **Test 1: Geplande Failover** ```bash # Simuleer onderhoud op primary node mysql -h node1 -e "SET GLOBAL group_replication_switch_to_single_primary_mode();" # Controleer of node2 nieuwe primary wordt ``` **Test 2: Ongeplande Failover** ```bash # Simuleer node crash systemctl stop mysql@node1 # Monitor automatische failover watch -n 1 "mysql -h proxy -P 6032 -u admin -padmin -e 'SELECT * FROM runtime_mysql_servers;'" ``` **Test 3: Netwerkpartitie** ```bash # Simuleer netwerkproblemen iptables -A INPUT -s node2 -j DROP # Verifieer cluster gedrag ``` --- ## 5. Bewakingsmechanismen **Essentiële Metrics:** - Group Replication lidmaatschap status - Replicatie lag (seconds_behind_master) - Query throughput - Connection pool utilization **Prometheus Queries:** ```yaml - name: mysql_replication_status rules: - alert: MySQLReplicationDown expr: mysql_global_status_wsrep_cluster_status != 1 for: 1m ``` **Grafana Dashboard:** - Cluster status overzicht - Replicatie performance - Failover geschiedenis --- ## 6. Veelvoorkomende Valkuilen **1. Split-Brain Scenario:** - **Oorzaak:** Netwerkpartitie met even aantal nodes - **Preventie:** Gebruik oneven aantal nodes + quorum mechanisme **2. Replicatie Lag:** - **Symptoom:** Trage writes op secondary nodes - **Oplossing:** Optimaliseer netwerk + gebruik parallelle replicatie **3. Connection Pool Exhaustion:** - **Symptoom:** "Too many connections" errors - **Oplossing:** Configureer connection pooling in ProxySQL --- ## 7. Onderhoud en Optimalisatie **Dagelijkse Checks:** ```sql -- Replicatie status SELECT * FROM performance_schema.replication_group_members; -- Replicatie lag SHOW SLAVE STATUS; ``` **Maandelijkse Optimalisatie:** - Review query performance - Optimaliseer database indices - Update cluster configuratie **Backup Strategie:** ```bash # Physical backup met XtraBackup innobackupex --backup --slave-info --safe-slave-backup /backup/ ``` --- ## 8. Noodprocedures **Handmatige Failover:** ```sql -- Forceer failover naar specifieke node SET GLOBAL group_replication_set_as_primary('node2-uuid'); ``` **Cluster Herstel:** ```bash # Bootstrap nieuw cluster mysqld --defaults-file=/etc/mysql/my.cnf --initialize-insecure START GROUP_REPLICATION; ``` --- ## 9. Documentatie en Contact **Belangrijke Links:** - MySQL Group Replication documentatie - ProxySQL configuratie gids - Interne escalation procedures **Support Matrix:** - Level 1: Intern team (24/7 monitoring) - Level 2: Database administrators - Level 3: Vendor support --- **Opmerking:** Test alle failover-scenario's uitgebreid in staging omgeving voordat u naar productie gaat. Houd regelmatig disaster recovery drills om team readiness te waarborgen. Deze handleiding dient als basisconfiguratie - pas parameters aan naar gelang uw specifieke workload en infrastructuur requirements.