Kann ich Sie mal „anpumpen“?

Ein Überblick über mysqlpump 

Bereits ab der Version MySQL 5.7.8 hat sich zu dem altbewährten Backup-Tool mysqldump ein neues Werkzeug gesellt: mysqlpump.

Nicht nur dem Namen nach sind sich die beiden Binaries ähnlich, sondern auch bezüglich ihres Einsatzzweckes, dem Erstellen von logischen Backups (oder auch DB-Exporten).

Doch was ist der Grund für ein zweites Werkzeug? Laut MySQL-Entwicklern ist eines der Hauptziele von mysqlpump das Parallelisieren von Backup-Strömen. Um bei der Entwicklung dieses Features so frei wie möglich zu sein, entschied man sich für eine Neuentwicklung und nicht für die Weiterentwicklung von mysqldump.

Zweistromland

Ab Werk teilt mysqlpump den Backup-Prozess in zwei Teile (Parameter —default-parallelism=2). Zusätzlich gibt mysqlpump automatisch Statusinformationen über den Backup-Vorgang in der Konsole aus (Parameter --skip-watch-progress, wenn dies nicht gewünscht ist). Die folgenden Beispiele beruhen auf einem Testserver der MySQL-Version 8.0.19. Gesichert werden die drei Datenbanken „Development", „Integration" und „Produktion". Die DB „Produktion" hat dabei den dreifachen Datenbestand im Gegensatz zu den anderen beiden DBs:

mysql> select round(sum(data_length)/1024/1024) DB_in_MB, table_schema 
        from information_schema.tables 
        where table_schema in ('production', 'integration', 'development') group by table_schema;
+----------+--------------+
| DB_in_MB | TABLE_SCHEMA |
+----------+--------------+
|       75 | production   |
|       25 | integration  |
|       25 | development  |
+----------+--------------+
3 rows in set (0,01 sec) 

bash> mysqlpump -ubackup -pbackup -Smysqld.sock -h192.168.56.101 -P3310 \|
--set-gtid-purged=off —include-databases=production,integration,development > /tmp/backup.sql

Dump progress: 0/1 tables, 250/693832 rows
Dump progress: 0/2 tables, 412500/1405495 rows
Dump progress: 0/2 tables, 963750/1405495 rows
Dump progress: 1/3 tables, 1563204/4251787 rows
Dump progress: 2/3 tables, 2393408/4251787 rows
Dump progress: 2/3 tables, 3711908/4251787 rows
Dump completed in 5768 

Zusätzlich kann man über die Systemtabelle „processlist" in Erfahrung bringen, mit welcher DB die beiden Threads gerade beschäftigt sind:

mysql> select id, user, db, command, info from processlist where user = 'backup';

+-------+--------+------+---------+---------------------------------------------------------------------------------+
| id    | user   | db   | command | info                                                                            |
+-------+--------+------+---------+---------------------------------------------------------------------------------+
| 45888 | backup | NULL | Query   | SELECT `val_a`,`val_b`  FROM `development`.`data`                               |
| 45890 | backup | NULL | Query   | SELECT `val_a`,`val_b`  FROM `integration`.`data`                               |
| 45887 | backup | NULL | Query   | SHOW COLUMNS IN `events_statements_summary_by_digest` FROM `performance_schema` |
+-------+--------+------+---------+---------------------------------------------------------------------------------+
3 rows in set (0,04 sec)

mysql> select id, user, db, command, info from processlist where user = 'backup';
+-------+--------+------+---------+---------------------------------------------------+
| id    | user   | db   | command | info                                              |
+-------+--------+------+---------+---------------------------------------------------+
| 45888 | backup | NULL | Query   | SELECT `val_a`,`val_b`  FROM `production`.`data`  |
| 45890 | backup | NULL | Query   | SELECT `val_a`,`val_b`  FROM `integration`.`data` |
| 45887 | backup | NULL | Sleep   | NULL                                              |
+-------+--------+------+---------+---------------------------------------------------+
3 rows in set (0,00 sec)

mysql> select id, user, db, command, info from processlist where user = 'backup';
+-------+--------+------+---------+--------------------------------------------------+
| id    | user   | db   | command | info                                             |
+-------+--------+------+---------+--------------------------------------------------+
| 45888 | backup | NULL | Query   | SELECT `val_a`,`val_b`  FROM `production`.`data` |
| 45890 | backup | NULL | Sleep   | NULL                                             |
| 45887 | backup | NULL | Sleep   | NULL                                             |
+-------+--------+------+---------+--------------------------------------------------+
3 rows in set (0,00 sec) 

Im folgenden Beispiel kann man gut sehen, dass sich der Backup-Prozess zunächst um die beiden kleineren Datenbanken kümmert.

Nachdem die erste kleinere DB (hier „Development") gesichert ist, beginnt der Prozess 45888 mit der Sicherung von „Production". Diese Sicherung läuft aufgrund der Größe am längsten. 

Durchflussbegrenzer

Zur optimalen Steuerung des Backups kann es sinnvoll sein, die Ressourcen in Abhängigkeit der DB-Größen zu steuern (andere Ressourcen-Zuteilungen, z.B. nach Priorität, können natürlich auch sinnvoll sein):

bash> mysqlpump -ubackup -pbackup -Smysqld.sock -h192.168.56.101 -P3310 --set-gtid-purged=off --include-databases=production,integration,development --parallel-schemas=3:production --default-parallelism=2 > /tmp/backup.sql
 

Für das oben stehende Beispiel werden zwei unterschiedliche Queues erzeugt, denen eine unterschiedliche Anzahl an Threads zugewiesen wird:

• Queue für die DB/das Schema „Produktion"; bekommt 3 Threads
• Default Queue (für alle anderen „Schemata"; hier „Development" & „Integration"); bekommt 2 Threads

mysql> select id, user, db, command, info from processlist where user = 'backup' order by info;
+-------+--------+------+---------+----------------------------------------------------+
| id    | user   | db   | command | info                                               |
+-------+--------+------+---------+----------------------------------------------------+
| 48426 | backup | NULL | Sleep   | NULL                                               |
| 48427 | backup | NULL | Sleep   | NULL                                               |
| 48429 | backup | NULL | Query   | SELECT `val_a`,`val_b`  FROM `development`.`data2` |
| 48430 | backup | NULL | Query   | SELECT `val_a`,`val_b`  FROM `integration`.`data`  |
| 48436 | backup | NULL | Query   | SELECT `val_a`,`val_b`  FROM `production`.`data2`  |
| 48437 | backup | NULL | Query   | SELECT `val_a`,`val_b`  FROM `production`.`data3`  |
| 48435 | backup | NULL | Query   | SELECT `val_a`,`val_b`  FROM `production`.`data`   |
+-------+--------+------+---------+----------------------------------------------------+ 

Jetzt ist sehr schön zu erkennen, dass mit drei Threads die DB „Production" gesichert wird. Die anderen beiden DBs werden mit einem Thread gesichert.

Wasserstandsmeldungen

Bevor das neue Produkt eingesetzt wird, sollten jeder DBA zunächst einmal einen Blick auf die Dokumentation werfen. Zwar klingt der Name des neuen Produktes sehr analog zu dem altbekannten Produkt „mysqldump" und auch viele Parameter sehen ähnlich aus.

Jedoch gibt es bereits bei sehr einfachen Backup-Konfigurationen diverse Unterschiede, die man bedenken sollte. So exkludiert mysqlpump per Default z.B. einige Systemdatenbanken („performance_schema", „ndbinfo", „sys", „information_schema") und/oder Userinformationen (weder die Accounts noch die Berechtigungen werden automatisch gesichert).

User werden bei mysqlpump als Objekte gesichert und müssen dafür definiert werden. Damit hat man die Möglichkeit, die User unabhängig von der Systemdatenbank „mysql" und im Kontext der jeweilig zugehörigen Applikationsdatenbank zu sichern. Im folgenden Beispiel wird die DB „Development" inklusive des Users „Developer" gesichert.

bash> mysqlpump -ubackup -pbackup -Smysqld.sock --set-gtid-purged=off  --exclude-databases=% --include-databases=development --include-users=Developer > /tmp/dev.sql

bash> grep -i "^CREATE USER" /tmp/dev.sql

CREATE USER ‚Developer‘@‚localhost' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$rLwJ@wu6QaxPkCc73xueiYV1/ZC7qK9ePQ0HQIOt4Yafkr6giDaDUVvatP5' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1; 

Eine weitere erfreuliche Funktion ist die eingebaute Komprimierungsoption (Parameter—compress und--compression-algorithms). Damit lassen sich die Backups direkt komprimiert auf die Platte schreiben.

Probleme mit dem Sperrwerk

Aber es gibt auch Schattenseiten. Zugunsten der Performance-Steigerungen durch die Parallelisierung gibt es aktuell keinen Synchronisierungspunkt zu Beginn des Backups der jeweiligen Threads. Aus diesem Grund existiert kein Parameter, der eine Logposition (—master-data; oder etwas Äquivalentes) in das Backup schreibt. Damit kann über dieses Tool kein Wiederaufsatzpunkt für ein Recovery aus Binary Logs gewährleistet werden (zumindest nicht direkt). Dieses Manko kann aber unter Umständen auf anderen Wegen gelöst werden (z.B. in einem Master-Slave-Konstrukt, in dem der Slave gesichert wird).

Fazit:

Es lohnt sich sicherlich, ein paar Minuten in das neue Werkzeug zu investieren. Insbesondere bei großen Datenbanken kann der Einsatz sinnvoll sein. Allerdings gibt es auch viele andere Möglichkeiten, MySQL-Datenbanken in einer kritischen Größenordnung zu sichern (z.B. Snapshots, MySQL Enterprise Backup, ….).

Weitere Informationen zu mysqlpump finden Sie in der Dokumentation.