How to use mysqldump, pv, and zip command together?

Question

I want to use mysqldump to get a table from a remote MySQL server, then compress this downloaded file using zip. While downloading, I wish I can view the progress using pv.

Can I do the above things with | in one line of command?

These are what I've tried:

mysqldump -uuser_name -ppassword -hremote_address --routines my_database my_table | pv | zip > my_database_my_table.sql.zip

The problem with this command is that when executing unzip my_database_my_table.sql.zip, I got - as the name of the output file. I wish I could determine the file's name when I execute zip command.

Is it possible to set the name of the inflated file?

mysqldump -uuser_name -ppassword -hremote_address --routines my_database my_table | pv | zip my_database_my_table.sql > my_database_my_table.sql.zip

This command gives me mysqldump: Got errno 32 on write error.

Answer 1

Here is how I used them together:

mysqldump -u db_user -pdb_password db_name| pv | zip > backup.zip

Here is https://stackoverflow.com/a/50985546/3778130 full automatic backup script if someone is interested.

Hope it helps someone down the road.

Answer 2

A quick man zip shows that you can use -O or --output-file to specify the output name. That should do you.

Answer 3

In order to be able to get the progress out of pv, it needs to know the full size of the data. Unfortunately, by piping it to mysqldump it does not have that kind of data.

I would recommend to dump the data first and transfer and restore it later.

1. Dump it.
2. pv mysqldump.sql | zip > ~/mysqldump.sql.zip
3. scp remotehost:/home/folder/mysqldump.sql ./
4. unzip it
5. pv mysqldump.sql | mysql -u<dbuser> -p <dbname>

Answer 4

I am not familiar with pv, but my zip documentation states for zip to use the stdin, the dash must be specified. So i use this :

mysqldump --user=username --password=password mydatabase | zip mydatabase.zip -