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
Community
  • 1
Josh Pinto
  • 3,483
  • 15
  • 52
  • 87

3 Answers3

11

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).
chaos
  • 47,463
  • 11
  • 118
  • 144
  • 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``` – Josh Pinto 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` – Josh Pinto 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. – Josh Pinto Sep 25 '15 at 11:21
  • @BrentonHorne See my edit – chaos Sep 25 '15 at 11:27
  • By my 'home directory' do you mean `~/`? I log in using the mysql root account, so should I store it in `/root`? – Josh Pinto Sep 25 '15 at 11:29
  • @BrentonHorne yes `~/.my.cnf`. You can remove it after the operation. – chaos Sep 25 '15 at 11:30
  • I'm afraid I'm still getting errors ```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 errno 32 on write ``` – Josh Pinto Sep 25 '15 at 11:32
  • @BrentonHorne The file `backup.sql.7z` still persists in the directory from previous attempts and is most probably empty; remove it. – chaos Sep 25 '15 at 11:35
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.

chaos
  • 47,463
  • 11
  • 118
  • 144
TFR3
  • 1
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.

Vouze
  • 829
  • 5
  • 8