3

I am trying to import zipped database files into Mysql using command prompt using the following command

7z < backup.sql.7z | mysql -u root test

The root user don't have any password associated with it.
test is my target blank database. I use 7zip for unzipping purpose. The zipped database i.e. backup.sql.7z is located in D drive.

But it's giving the following error enter image description here

So, instead I used the following command

7z < backup.7z | mysql -u root test

Note: This time I am using backup.7z instead of backup.sql.7z

But then I get the following error enter image description here

Clearly there's something wrong with my SQL syntax.

What will be the correct syntax to use then ?

Improve this question

2 Answers 2

Reset to default
6

I needed to import from a compressed file as well, and stumbled upon your question. After a bit of messing around, I found that this worked for me:

7z x -so backup.7z | mysql -u root test

x is the extraction command
-so makes 7-zip write to stdout

Improve this answer
2
  • yes, this one really works.. THANK YOU very much @Wang. Do you have some other workable script which will do the exact work for other types of zip application like winzip, gz etc. ? THANK YOU again.
    – Subrata
    Commented Jul 12, 2012 at 5:04
  • 2
    Just a note on a trouble I've been struggling with - if you need to supply mysql with a password, using -p password doesn't work, you must type it without space: -ppassword. Maybe it could help someone.
    – Czechnology
    Commented Jul 26, 2012 at 19:11
0

Nothing wrong with your syntax, it's just a limitation with 7zip. It's better to use xz in this case, which doesn't put extraneous junk in stdout, or directly call the 7z.dll with your favorite programming language. 7z.exe is really meant for archive management, rather than unix-style piping, and Igor is very reluctant to change that.

If you try a plain 7z < somefile.7z you'll immediately see that all you get back is a usage list.

Improve this answer
2
  • I also did xz −−decompress −−stdout backup.sql.gz | mysql -u root test but got the error xz: backup.sql.zip: File format not recognized
    – Subrata
    Commented Jun 29, 2012 at 19:10
  • xz understands .xz. gzip understands .gz. If you're trying to script something to import lots of different backup types, you need to parse the extension; if you're doing it by hand you just need to use the right tool for the job.
    – SilverbackNet
    Commented Jul 1, 2012 at 17:29

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