MySQL

How to Resolve Very Big Dump File of MySQL

I usually backup the database by mysqldump, but the plain SQL dump file was getting larger and larger and took most of the free space.

Finally, I found a solution to this problem. To backup the database, we can directly pipe the output stream to a zip file, no intermediate dump file involved:

[root@test ~]# mysqldump -u root -p'password' database_name | gzip > dump_file.gz

To restore the database, we can use the command to unzip the dump file and pipe the output stream to the database.

[root@test ~]# gunzip < dump_file.gz | mysql -u root -p'password' -D database_name

This could save a lot of space. In my case, the data compression ratio is around 10.

3 thoughts on “How to Resolve Very Big Dump File of MySQL

    1. Thanks for your reminder. But I think the file extension is irrelevant, I usually use the commands to backup and restore tables without problems.

    2. No, it is an sql file, so it should be file.sql.gz, only if it is a tar file would it be file.tar.gz. In any case, the extension is only a label to make it convenient to you (the user) to know what format the file is in.

Leave a Reply

Your email address will not be published. Required fields are marked *