Using Compression in mysqldump Output

When using `mysqldump`, the `-C` or `–compress` option allows you to compress the output of the dump file, which can be beneficial for reducing storage space and speeding up data transfer, especially over networks.

Here are a few examples of how to use compression with `mysqldump`:

Example 1: Compressing a database dump and saving it locally:
mysqldump -u username -p database_name -C > database_dump.sql.gz

In this example, `-C` instructs `mysqldump` to compress the output using gzip (`*.gz` extension). The resulting dump file `database_dump.sql.gz` will be compressed.

Example 2: Directly restoring a compressed dump to a MySQL server:
gzip -dc database_dump.sql.gz | mysql -u username -p database_name

This command first decompresses (`gzip -dc`) the compressed dump file `database_dump.sql.gz` and pipes (`|`) it directly to `mysql` for restoration.

Example 3: Compressing and transferring a database dump over SSH:
mysqldump -u username -p database_name -C | gzip | ssh user@remote_host 'cat > /path/to/database_dump.sql.gz'

Here, `mysqldump` compresses the dump, pipes it to `gzip`, and then sends it over SSH to `remote_host`, where it’s saved as `database_dump.sql.gz`.

Example 4: Using compression with `pv` (pipe viewer) to monitor progress:
mysqldump -u username -p database_name -C | pv | gzip > database_dump.sql.gz

This command shows the progress of the data transfer (`pv`), compresses it with `gzip`, and saves it as `database_dump.sql.gz`.

Example 5: Compressing a database dump and encrypting it:
mysqldump -u username -p database_name -C | gzip | openssl enc -aes-256-cbc -salt -out database_dump.sql.gz.enc

Here, the output is compressed with `gzip` and then encrypted using `openssl` with AES-256-CBC, resulting in `database_dump.sql.gz.enc`.

Verification: To verify if the `mysqldump` command executed successfully, you can check the existence and size of the output file. For example, after running:
ls -lh database_dump.sql.gz
This command should list the compressed dump file `database_dump.sql.gz` with its size, confirming that the compression operation was successful.

Read Also

Using Complete INSERT Statements in mysqldump
Generating Compact Output in mysqldump
Setting MySQL Dump Compatibility Mode
Including Comments in mysqldump Output
Adding ANALYZE TABLE Statements in mysqldump Output