4

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_%H-%M-%S).zip

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .