How to compress a mysql dump using 7z via a pipe?
11,203
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 of7z
adds to the archive -si
means to read from the standard input (from the anonymous pipe).
Related videos on Youtube
![identify](https://i.stack.imgur.com/uLhvt.jpg?s=256&g=1)
Author by
identify
Updated on September 18, 2022Comments
-
identify almost 2 years
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
-
identify almost 9 yearsSorry, rofl, I forgot the
dump
(I have edited my question accordingly), I did usemysqldump
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
-
identify almost 9 yearsI think you missed a
>
after-si
. But even when I added the>
I got the error:Enter password: mysqldump: Got errno 32 on write
-
chaos almost 9 years@BrentonHorne No, it is meant without a
>
. The error comes from themysqldump
command which cannot connect to the database:Access denied for user 'root'@'localhost' (using password: NO)
. The username or password is wrong. -
identify almost 9 yearsIt didn't give me a chance to provide my password. It just gave me that error, before I could even enter my password.
-
chaos almost 9 years@BrentonHorne See my edit
-
identify almost 9 yearsBy my 'home directory' do you mean
~/
? I log in using the mysql root account, so should I store it in/root
? -
chaos almost 9 years@BrentonHorne yes
~/.my.cnf
. You can remove it after the operation. -
identify almost 9 yearsI'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
-
chaos almost 9 years@BrentonHorne The file
backup.sql.7z
still persists in the directory from previous attempts and is most probably empty; remove it.