Archivier das mal!

Immer wieder kommt es bei Kunden vor, dass Datenbanken wachsen, da historische Inhalte nicht gelöscht oder archiviert werden. Oftmals handelt es sich bei diesen Daten um Zeitreihenwerte. Dies können beispielsweise erstellte Rechnungen oder erzeugte und gespeicherte Messwerte über mehrere Monate und Jahre sein. Ein mögliches Archivierungskonzept kann die beiden von MySQL unterstützten Funktionalitäten „Partitionierung" und „Replikation" verwenden.

Im Folgenden wird ein – an einen Kunden angelehntes – Archivierungskonzept erläutert, welches exakt diese Technologien implementiert hat.

Zahlen bitte! 

mysql> select * from rechnungen;
+--------+----------+--------+------------+------+-------+
| rechnr | kundennr | betrag | datum      | jahr | monat |
+--------+----------+--------+------------+------+-------+
|      4 |      987 |   4.25 | 2018-06-10 | 2018 |     6 |
|      1 |      123 |   7.50 | 2019-04-15 | 2019 |     4 |
|      2 |      456 |   5.75 | 2020-02-03 | 2020 |     2 |
|      3 |      789 |   4.50 | 2020-01-12 | 2020 |     1 |
|      5 |      123 |   6.50 | 2021-09-17 | 2021 |     9 |
+--------+----------+--------+------------+------+-------+
5 rows in set (0,00 sec) 

Teile und herrsche!

CREATE TABLE `rechnungen` (
  `rechnr` int(11) NOT NULL AUTO_INCREMENT,
  `kundenr` int(11) NOT NULL,
  `betrag` decimal(10,2) NOT NULL,
  `datum` date NOT NULL,
  `jahr` int(11) GENERATED ALWAYS AS (year(`datum`)) VIRTUAL,
  `monat` int(11) GENERATED ALWAYS AS (month(`datum`)) VIRTUAL,
  PRIMARY KEY (`rechnr`,`datum`)
) PARTITION BY RANGE (year(`datum`))
(PARTITION p2018 VALUES LESS THAN (2019) ENGINE = InnoDB,
 PARTITION p2019 VALUES LESS THAN (2020) ENGINE = InnoDB,
 PARTITION p2020 VALUES LESS THAN (2021) ENGINE = InnoDB); 

mysql> explain select * from rechnungen where datum between '2018-01-01' and '2018-12-31';
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | rechnungen | p2018      | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0,00 sec) 

Bitte (nicht) nachmachen!

Generell ist es die Aufgabe eines Replikations-Systems (wird bei MySQL Slave genannt), die Kommandos (Transaktionen) des führenden Systems (Master genannt) analog durchzuführen. Dazu werden die Daten (Transaktionen) aus einem Logfile vom Master auf den Slave kopiert und dort wiederholt.

Vor dem Jahreswechsel wird auf dem Master eine neue Partition für die Rechnungen hinzugefügt. Auch dieses Kommando vollzieht der Slave nach, um für die kommenden Rechnungen gewappnet zu sein.

mysql> alter table rechnungen add partition (partition p2021 values less than (2022))
Query OK, 0 rows affected (0,06 sec)
Records: 0  Duplicates: 0  Warnings: 0 

Nach dem Jahreswechsel wird im Gegenzug eine alte Partition auf dem Master gelöscht, um den Datenbestand zu verrringern. Dies soll auf dem Slave jedoch nicht passieren. Um die Replikation dieses Kommandos zu unterdrücken, wird in der entsprechenden Session das Loggen auf dem Master (für dieses eine Kommando) deaktiviert.

mysql> set sql_log_bin=off;
Query OK, 0 rows affected (0,00 sec)
 
mysql> alter table rechnungen drop partition p2018;
Query OK, 0 rows affected (0,08 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> set sql_log_bin=on;
Query OK, 0 rows affected (0,00 sec) 

Das Löschen der Partition wird somit vom Slave nicht durchgeführt. Die Daten bleiben also dort erhalten.

Im Normalfall sollte dieses „Schieflage" zu keinen Problem bei der Replikation führen. Auf dem Master können ja keine Daten manipuliert werden, die zu einem Abbruch der Replikation führen können. Natürlich gibt es Ausnahmen von diesem „Normalfall". Würde man versuchen auf dem Master eine „historische" Partition wieder anzulegen (die auf dem Slave noch existiert), so würde dies zu einem Fehler führen. Der Slave würde ja ebenfalls versuchen diese Partition anzulegen. Dies würde jedoch nicht funktionieren, da eine gleichnamige Partition bei ihm ja noch existiert.

Wenn Sie Fragen zur MySQL Replikation, zur Partitionierung, Archivierungslösungen oder großen Datenmengen im Umfeld von MySQL (oder MariaDB) haben, dann sprechen Sie uns gerne an.