It's very easy to save MySQL data to a compressed file by using a named pipe.
You need to have two bash terminals open, A & B, presumably in screen/tmux sessions since you are likely interested in large tables that take a long time to export.
Terminal A, Step 1: Create the FIFO named pipe (make sure MySQL can write to it):
mkfifo "/path/to/data.csv"
sudo chown mysql "/path/to/data.csv"
Terminal A, Step 2: Have zstd read from the FIFO pipe and write to an output file:
zstd -o "/tmp/data.csv.zst" < "/path/to/data.csv" &Terminal B: Save the MySQL data:
SELECT * FROM your_table
INTO OUTFILE '/path/to/data.csv'
CHARACTER SET UTF8MB4
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n';
The only issue would be the NULL columns, which you can deal with manually by using IFNULL when constructing your query.
