Real world SQL dumps (MySQL MariaDB Postgres backup)

zpaqfranz, unlike zpaq, does support stdin/stdout, making it the absolute best tool for SQL dumps, such as backups created with mysqldump

Let's see a quick-and-dirty example: dumping everything in an encrypted archive

First run

mysqldump -uroot -ppippo --all-databases | c:\zpaqfranz\zpaqfranz a z:\archived_backup.zpaq thedump.sql -stdin -key pippo

zpaqfranz v58.2a-JIT-GUI-L,HW BLAKE3,SHA1/2,SFX64 v55.1,(2023-03-21)
DETECTED SHA1/2 HW INSTRUCTIONS
franz:-key                               (hidden)
franz:-hw -stdin
40587: REBUILDING STDIN filename to thedump.sql
40828: IMPORT FROM STDIN
Creating z:/archived_backup.zpaq at offset 32 + 0
Adding stream with 32 T Add 4 (4.00 B) 1 files (0 dirs), 32 T @ 2023-04-22 14:39:53
1 +added, 0 -removed.

32 + (1.478.188.850 -> 1.478.188.846 -> 244.543.748) = 244.543.780 @ 74.01 MB/s

19.141 seconds (000:00:19) (all OK)

OK, the ~1.4GB DB become ~244MB (whatever, you can improve compression with -msomething)

Now

Second run (exactly the same, yep crontab I am speaking to you...)

mysqldump -uroot -ppippo --all-databases | c:\zpaqfranz\zpaqfranz a z:\archived_backup.zpaq thedump.sql -stdin -key pippo

zpaqfranz v58.2a-JIT-GUI-L,HW BLAKE3,SHA1/2,SFX64 v55.1,(2023-03-21)
DETECTED SHA1/2 HW INSTRUCTIONS
franz:-key                               (hidden)
franz:-hw -stdin
z:/archived_backup.zpaq:
1 versions, 1 files, 2.846 frags, 91 blks, 244.543.780 bytes (233.21 MB)
40587: REBUILDING STDIN filename to thedump.sql
40828: IMPORT FROM STDIN
Updating z:/archived_backup.zpaq at offset 244.543.780 + 0
Adding stream with 32 T Add 4 (4.00 B) 1 files (0 dirs), 32 T @ 2023-04-22 14:41:20
1 +added, 0 -removed.

244.543.780 + (1.478.188.850 -> 69.422 -> 16.120) = 244.559.900 @ 87.00 MB/s

16.219 seconds (000:00:16) (all OK)

The new "snapshot" (aka: DB version) takes ~16KB
Yes, 16KB!

OK, now insert some rows

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.3.7-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [zglobale]> INSERT INTO pratiche ( fornitore ) SELECT id * rand() FROM pratiche;
Query OK, 1 row affected (0.003 sec)
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [zglobale]> INSERT INTO pratiche ( fornitore ) SELECT id * rand() FROM pratiche;
Query OK, 2 rows affected (0.002 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [zglobale]> INSERT INTO pratiche ( fornitore ) SELECT id * rand() FROM pratiche;
Query OK, 4 rows affected (0.002 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [zglobale]> INSERT INTO pratiche ( fornitore ) SELECT id * rand() FROM pratiche;
Query OK, 8 rows affected (0.002 sec)
Records: 8  Duplicates: 0  Warnings: 0

MariaDB [zglobale]> INSERT INTO pratiche ( fornitore ) SELECT id * rand() FROM pratiche;
Query OK, 16 rows affected (0.002 sec)
Records: 16  Duplicates: 0  Warnings: 0

MariaDB [zglobale]> INSERT INTO pratiche ( fornitore ) SELECT id * rand() FROM pratiche;
Query OK, 32 rows affected (0.001 sec)
Records: 32  Duplicates: 0  Warnings: 0

MariaDB [zglobale]>

Now

third run (exactly the same)

mysqldump -uroot -ppippo --all-databases | c:\zpaqfranz\zpaqfranz a z:\archived_backup.zpaq thedump.sql -stdin -key pippo

zpaqfranz v58.2a-JIT-GUI-L,HW BLAKE3,SHA1/2,SFX64 v55.1,(2023-03-21)
DETECTED SHA1/2 HW INSTRUCTIONS
franz:-key                               (hidden)
franz:-hw -stdin
z:/archived_backup.zpaq:
2 versions, 2 files, 2.847 frags, 94 blks, 244.559.900 bytes (233.23 MB)
40587: REBUILDING STDIN filename to thedump.sql
40828: IMPORT FROM STDIN
Updating z:/archived_backup.zpaq at offset 244.559.900 + 0
Adding stream with 32 T Add 4 (4.00 B) 1 files (0 dirs), 32 T @ 2023-04-22 14:53:48
1 +added, 0 -removed.

244.559.900 + (1.478.197.541 -> 974.917.921 -> 176.703.752) = 421.263.652 @ 83.77 MB/s

16.906 seconds (000:00:16) (all OK)

OK, now the db is changed, turning the archive to 421MB

Let's what's inside...

Z:\>zpaqfranz l z:\archived_backup.zpaq -key pippo -all

zpaqfranz v58.2a-JIT-GUI-L,HW BLAKE3,SHA1/2,SFX64 v55.1,(2023-03-21)
DETECTED SHA1/2 HW INSTRUCTIONS
franz:-key                               (hidden)
franz:-all                                      4
franz:-hw
z:/archived_backup.zpaq:
3 versions, 3 files, 4.724 frags, 155 blks, 421.263.652 bytes (401.75 MB)


- 2023-04-22 14:39:53                   0       0001| +1 -0 -> 244.543.748
- 2023-04-22 16:39:53       1.478.188.846 A     0001|thedump.sql
- 2023-04-22 14:41:20                   0       0002| +1 -0 -> 16.120
- 2023-04-22 16:41:20       1.478.188.846 A     0002|thedump.sql
- 2023-04-22 14:53:48                   0       0003| +1 -0 -> 176.703.752
- 2023-04-22 16:53:48       1.478.197.537 A     0003|thedump.sql

        4.434.575.229 (4.13 GB) of 4.434.575.229 (4.13 GB) in 6 files shown
          421.263.652 compressed

0.063 seconds (00:00:00) (all OK)

We have now 3 different full-backup inside the archive

Recap

zpaqfranz, using zpaq's de-duplication technology, takes forever-to-forever dumps in very little space, with about zero effort

Paradoxically, the more frequent the backups, the less space they will occupy, as they will vary less over time

If you need -stdout, you can include -stdout too

mysqldump -uroot -ppippo --all-databases | c:\zpaqfranz\zpaqfranz a z:\ugo.zpaq copia.sql -stdin -stdout

Compression ratio will become worse, but you can use something like this (to restore version 2)...

zpaqfranz x z:\ugo.zpaq copia.sql -until 2 | mysql -uroot -ppippo (whatever...)