2

Till now I've created and zipped mysql dumps this way:

mysqldump -u root -p --all-databases > /var/www/html/db-$(date +\%F-\%T).sql
zip /var/www/html/db-$(date +\%F-\%T).zip /var/www/html/db-*.sql
rm /var/www/html/db-*.sql

Is there a way to have it zipped directly from mysqldump and save these 2 extra rows?

3
  • Why? If this is working for you, why change anything?
    – Kaz Wolfe
    Jan 14, 2018 at 20:57
  • If I can get the same result in less code (and you sure know I can), and the code is not complicated, I'd humbly prefer that, at least in this case... The original code was actually part of much longer code I already reviewed and changed. Jan 14, 2018 at 21:01
  • Another reason to use the answer below is it can save on disk IO (no need to write out the temporary file, if you pipe it straight to zip)
    – juacala
    Dec 3, 2019 at 15:25

4 Answers 4

9

Try this:

mysqldump -u root -p --all-databases | zip /var/www/html/db-$(date +\%F-\%T).zip

You will only have one file into the zip archive having - as file name.

1
  • 1
    Don't work for me (Ubuntu 14.04). Error: "mysqldump: Got errno 32 on write". It's work only if I add - at the end.
    – Dimetry
    Sep 14, 2020 at 3:12
9
+50

The other two answers have already stated the following solution (so consider this an extension of the other two answers):

mysqldump -u root -p --all-databases | zip /var/www/html/db-$(date +\%F-\%T).zip -

This is good, but this works only on stdin/stdout. This means that the file will be stored as - inside the archive, and can only normally be extracted sanely to stdin. This means to actually extract this data from the ZIP, you will have to do this:

unzip -p myZipFile.zip > myBackup.sql

If you want to make a "normal" ZIP file (where things have a real name), you will have to do some really complicated things or just do what you're already doing so there's no net gain.

As MySQL itself is stdin-aware, you can restore directly from a backup using the below command (as an example):

unzip -p myDataBackup.zip | mysql -uroot -pMyInsecurePassword
2

You can try this

mysqldump -u root -p --all-databases | zip /var/www/html/db-$(date +\%F-\%T).zip -
3
  • Why is the ` -` right after .zip? Jan 12, 2018 at 9:39
  • it the file name in the zip file ... I tried it with another name but without success
    – Adel Kihal
    Jan 13, 2018 at 12:52
  • Oh. maybe by chance you tried this on a mac (even though we're on AskUbuntu)? :) Jan 13, 2018 at 12:53
0

For Ubuntu20 ">" sign was needed between zip and /var..

mysqldump -u root -p --all-databases | zip > /var/www/html/db-$(date +\%F-\%T).zip

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.