6

I've been attempting to compress my mysqldump output via 7z using the pipe operator (I have seen this question, but its answer uses xz not 7z). This is what I have tried so far:

mysqldump -u root -p Linux_Wiki | 7z > backup.sql.7z

and:

mysqldump -u root -p Linux_Wiki | 7za > backup.sql.7z

and:

mysqldump -u root -p Linux_Wiki | '7za a' > backup.sql.7z

and:

mysqldump -u root -p Linux_Wiki | `7za a` > backup.sql.7z

All four failed, but I am sure I have p7zip installed, after all the last of these attempts gave this output:

Enter password: bash: 7-Zip: command not found
mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect
8

First store the password in a file called .my.cnf in the users home directory with the following format:

[mysqldump]
password=secret

Then, you have to use mysqldump without the -p flag to dump a mysql database (it now uses the password from the file):

mysqldump -u root database | 7z a -si backup.sql.7z
  • The a flag of 7z adds to the archive
  • -si means to read from the standard input (from the anonymous pipe).
  • Sorry, rofl, I forgot the dump (I have edited my question accordingly), I did use mysqldump from the command-line though. Your code, when I substituted Linux_Wiki as my database I got the output: Enter password: 7-Zip [64] 9.38 beta Copyright (c) 1999-2014 Igor Pavlov 2015-01-03 p7zip Version 9.38.1 (locale=en_AU.UTF-8,Utf16=on,HugeFiles=on,4 CPUs,ASM) Error: backup.sql.7z is not supported archive System error: Operation not permitted mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect – BH2017 Sep 25 '15 at 11:17
  • I think you missed a > after -si. But even when I added the > I got the error: Enter password: mysqldump: Got errno 32 on write – BH2017 Sep 25 '15 at 11:18
  • @BrentonHorne No, it is meant without a >. The error comes from the mysqldump command which cannot connect to the database: Access denied for user 'root'@'localhost' (using password: NO). The username or password is wrong. – chaos Sep 25 '15 at 11:20
  • It didn't give me a chance to provide my password. It just gave me that error, before I could even enter my password. – BH2017 Sep 25 '15 at 11:21
  • @BrentonHorne See my edit – chaos Sep 25 '15 at 11:27
0

Some people may frown upon this practice, but you can put the password on the command line, like so:

mysqldump -u root -pmyrootpassword database | 7z a -si backup.sql.7z

It has to be placed immediately after the -p argument.

0

If you want to use pipes with 7zip algorithm, you should consider using xz instead of 7z.

xz uses exactly the same algorithm as 7z but it can be used just like gzip (or bzip2). xz and 7zip were designed by the same author.

mysqldump -u root -p Linux_Wiki | xz -9 > backup.sql.xz

The GUI of 7zip understands xz.

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.