13

I am trying to restore a mysql db using a .sql.gz file. I am using mySql console to run a command because file size is too large for phpMyAdmin. Command I am using is

gunzip C:/Vik/Gya/Source/beed_2013-04-06.sql.gz | mysql -u root -p bd

where root is the user id. There is no password for root. bd is the database to which I am trying to import. mysql is running on my local machine (Windows 8). I have a wamp setup.

This is the error I am getting:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'gunzip C:/Vikalp/Gyankosh/Source/beedictionary_2013-04-06.sql | mysql -u root -p' at line 1.

halfer
  • 19,824
  • 17
  • 99
  • 186
Vikalp Jain
  • 583
  • 2
  • 11
  • 25

6 Answers6

69

You need -c option (output to stdout)

gunzip -c xxx.sql.gz |mysql -u root -p
CRUSADER
  • 5,486
  • 3
  • 28
  • 64
kisoft
  • 699
  • 5
  • 2
  • 3
    This is the right answer to why `gunzip C:/Vik/Gya/Source/beed_2013-04-06.sql.gz | mysql -u root -p bd` won't work from the command line, but the actual chosen answer is correct that the error message and the the actual question indicate that the user is logged in to MySQL which is why the command didn't work. This answer however is how to do things properly from the command line piping from gunzip. – George Jun 17 '14 at 12:35
  • 4
    also interesting to not that `gunzip` is just a synonym for `gzip -d` or `gzip --decompress`, so the command could be `gzip -dc file.sql.gz | mysql -u user -p dbname` – Jon Surrell Feb 27 '15 at 11:09
23

While Kisoft´s answer is the correct one, I just wanted to point out that you don´t need the -c, it works just fine as it is. this command will unzip the database dump and import it into the database at the same time. 

gunzip < output.sql.gz | mysql -u <username> -p<password> <database>
Comocho00
  • 375
  • 4
  • 9
4

If you type gunzip and you get a SQL syntax error that complaints about gunzip, you are already logged into the mysql console. The mysql console is not a general purpose shell!

You are using Windows and I suspect you haven't installed gzip in your computer (it isn't a builtin utility). It's a classical Unix tool but you can find binaries for Windows. Install it and run your original command with a couple of tweaks:

  1. Make sure you're in Windows prompt (C:\>)

  2. Redirect gunzip result to stdout rather than a file:

    gunzip --stdout C:/Vik/Gya/Source/beed_2013-04-06.sql.gz | mysql -u root -p bd

Alternatively, you can run the dump from within MySQL promt (mysql>) if you uncompress it first (you don't need specifically command-line gzip, most GUI archivers such as 7-Zip support this format):

mysql> \. C:/Vikalp/Gyankosh/Source/beedictionary_2013-04-06.sql
Álvaro González
  • 142,137
  • 41
  • 261
  • 360
  • Thanks. This worked. I unzipped .gz file and then used mysql console. Command I used was : 'mysql> use bd mysql>source C:/Vikalp/Gyankosh/Source/beedictionary_2013-04-06.sql' – Vikalp Jain Apr 21 '13 at 05:31
  • 3
    That is naive solution, and it works if your backup is small, but there is no need to gunzip the file on disk. Just pipe to mysql like what CRUSADER said. – Alex F Mar 06 '14 at 14:21
  • If you already have a sql file, yu can also use `mysql my_db – Alex F Mar 06 '14 at 14:22
2

you do not need to gunzip just: zcat myfile.gz | mysql -uuser -ppassword mydatabase it is faster this way

Mirel
  • 21
  • 1
1

Your answer is already here

phpMyAdmin: Can't import huge database file, any suggestions?

Under php.ini file, normally located in c:\xampp\php or wampp whatever you called

post_max_size=128M
upload_max_filesize=128M

Changing value there will get you what you want.Good luck Dont forget to restart , apache and mysql .

Community
  • 1
  • 1
Wayne Tun
  • 590
  • 7
  • 24
  • 1
    I did this. This gets past first hurdle of increasing max upload size. But if the files is pretty big, like in my case, it causes phpmyadmin to time out. I went with the second approach suggested by @AlvaroGVicario and it worked. – Vikalp Jain Apr 21 '13 at 05:29
  • 1
    Hm what about 21G file? – bksi May 12 '15 at 00:32
  • I had this usecase - 21G file for database. I don't see nothing funny about that. Sometimes web interface couldn't be used for these actions. – bksi May 20 '16 at 11:57
0

Try this following steps to restore db using .gz files:

1. Run command : gunzip C:/Vik/Gya/Source/beed_2013-04-06.sql.gz

This will uncompress the .gz file and will just store beed_2013-04-06.sql in the same location.

2. Type the following command to import sql data file:
      mysql -u username -p bd < C:/Vik/Gya/Source/beed_2013-04-06.sql
kd12
  • 1,291
  • 1
  • 13
  • 22