tickets #160086
closedMySQL/Galera backups incomplete (again)
0%
Description
In https://progress.opensuse.org/issues/155032 I resolved the missing database backups. Or so I thought.
Databases:
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| chocolate |
| conference_osem |
| etherpad |
| information_schema |
| limesurvey |
| matomo |
| members |
| mirrorcache |
| mysql |
| performance_schema |
| redmine |
| redmine_test |
| suse_hackweek |
| sys |
| webforums |
| webforums2 |
| wiki_cn |
| wiki_cs |
| wiki_de |
| wiki_el |
| wiki_en |
| wiki_en_test |
| wiki_es |
| wiki_fr |
| wiki_hu |
| wiki_it |
| wiki_ja |
| wiki_languages |
| wiki_nl |
| wiki_old_de |
| wiki_old_en |
| wiki_pl |
| wiki_pt |
| wiki_ru |
| wiki_sv |
| wiki_tr |
| wiki_zh |
| wiki_zh_tw |
| wsrep |
| xbttracker |
+--------------------+
40 rows in set (0.001 sec)
Backup:
crameleon@mybackup:/home/crameleon> sudo ls -l /backup/20240504/
total 3069664
-rw-r----- 1 root root 852 May 4 03:00 chocolate.sql.xz
-rw-r----- 1 root root 4260248 May 4 03:00 conference_osem.sql.xz
-rw-r----- 1 root root 106788020 May 4 03:00 etherpad.sql.xz
-rw-r----- 1 root root 866524 May 4 03:02 limesurvey.sql.xz
-rw-r----- 1 root root 3030961632 May 4 03:03 matomo.sql.xz
-rw-r----- 1 root root 3782 May 4 03:00 my.cnf
drwxr-x--- 2 root root 164 May 4 03:00 my.cnf.d
-rw-r----- 1 root root 381988 May 4 03:19 mysql.sql.xz
-rw-r----- 1 root root 7768 May 4 03:19 percona.sql.xz
-rw-r----- 1 root root 44708 May 4 03:19 sys.sql.xz
-rw-r----- 1 root root 848 May 4 03:19 test.sql.xz
Updated by crameleon 8 months ago
It seems the issue is the MariaDB replica on mybackup.i.o.o only replicating some of the databases:
mybackup (DB backup):~ # mariadb
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| chocolate |
| conference_osem |
| etherpad |
| information_schema |
| limesurvey |
| matomo |
| mysql |
| percona |
| performance_schema |
| sys |
| test |
+--------------------+
11 rows in set (0.006 sec)
Updated by crameleon 8 months ago
- Related to tickets #155032: MySQL/Galera backups incomplete added
Updated by crameleon 8 months ago
- Blocks tickets #160053: Request for old forums backup (vBulletin) added
Updated by crameleon 8 months ago
It seems the full dump cannot be imported, because mybackup.i.o.o runs out of memory before completion.
The sync script does not handle this condition, and proceeds to configure the slave - which then happily replicates the few databases which did manage to get imported before the crash.
Updated by crameleon 8 months ago
I extended the old 256M swap partition with a 2GB swap file, and adjusted the import call:
- time mariadb < "$BACKUPFILE"
+ time mariadb -e "SET autocommit=0; SOURCE $BACKUPFILE; COMMIT;"
Now it no longer runs out of memory, but out of "disk" space on the MySQL tmpdir, which is configured to a 500M tmpfs, and hence memory as well ...
Updated by crameleon 8 months ago ยท Edited
- Status changed from In Progress to Resolved
I tried it with a 8G swap file and zram as well, it only helped marginally. I presume it would need similar amounts of memory as the Galera nodes, but I don't think it's reasonable for a backup server to have such large amounts of memory if it's only needed for the initial import which we quite rarely need to do.
Since the dump logic is rather dated anyways, I now refactored the scripts to use Mariabackup and rsync instead:
galera1: /root/bin/backup_for_slave_sync_v2.sh
mybackup: /root/bin/restore_backup_v2.sh
This now works with barely any memory load, and feels much faster too.
For another task: we should manage these scripts in Salt or a package.