How do I load a sql.gz file to my database? (importing)

mysql database gzip import

825

Solution 1

zcat /path/to/file.sql.gz | mysql -u 'root' -p your_database

> will write the output of the mysql command on stdout into the file myfile.sql.gz which is most probably not what you want. Additionally, this command will prompt you for the password of the MySQL user "root".

Solution 2

To display a progress bar while importing a sql.gz file, download pv and use the following:

pv mydump.sql.gz | gunzip | mysql -u root -p <database name>

In CentOS/RHEL, you can install pv with yum install pv.

In Debian/Ubuntu apt-get install pv.

In MAC, brew install pv

Solution 3

The simplest way is to unzip the database file before importing. Also as mentioned by @Prof. Moriarty you shouldn't be specifying the password in the command (you'll be asked for the password). This command taken from webcheatsheet will unzip and import the database in one go:

gunzip < myfile.sql.gz | mysql -u root -p mydb

Solution 4

If you get an error from zcat, in which the error message contains the file name with an extra suffix .Z, then try using gzcat instead, as described at https://stackoverflow.com/questions/296717/zcat-wont-unzip-files-properly

Solution 5

On macOS, I used this:

zcat < [Database].sql.gz | mysql -u root -p [Database Name in MySQL]

Enter your password, and voila!

View more solutions

825

sejal patel

Updated on September 18, 2022

Comments

sejal patel over 1 year

I want to set a opacity in textbox in windows phone 7.1. I want to set opacity 20% for the background with #503E28 color code and 50% for foreground with #3B2D1E color code in the textbox in windows phone 7.1.
- Admin over 7 years
  
  Even ignoring the gzip side of the question, your arrow is pointing the wrong way...
Mayur Bhayani almost 14 years

As a good security practice, I would put my password on the command line, I would let mysql ask for it.
joschi almost 14 years

Or even better: create ~/.my.cnf with the credentials. ;)
sejal patel over 11 years

thank you very much.. @keyboardP.. and can you tell me how can i change a opacity value in color for forgroung and background color?...
sejal patel over 11 years

and in read only textbox how can i apply a forground and background color.. in wp7.1?
keyboardP over 11 years

The first two numbers (80 and 33) represent the alpha value. To calculate the number, get the % value, divide it by 100 and multiply by 255 (since 255 is the highest value). Convert that number to hex (either by code or using an online converter) and that will be the first number. For example, you wanted 20% opacity, so that would be ((20/100) * 255) and the convert that result to hex (which is 33). To apply the readonly colours, change the TextBox style as shown here msdn.microsoft.com/en-us/library/cc645061%28VS.95%29.aspx
sejal patel over 11 years

thank you so much.. i have a one problem.. if i set a forground and background color to textbox its display fine and i also set this color from .cs page on gotfouse event then its slightly changed..
sejal patel over 11 years

txtName.Background = new SolidColorBrush(new Color() { A = 0x15, R = 0x50, B = 0x3E, G = 0x28 }); i am changed using this way so its sligtly changed when i give it from design side and codebehind side... can you tell me why this is happened?..
keyboardP over 11 years

When you create a new Color, the range for A is 0 to 255. Therefore, 20% would be 51 which is 0x33. So change A from 0x15 to 0x33 and see if that fixes it.
keyboardP over 11 years

What if you do this instead? txtName.Background = new SolidColorBrush(Color.FromArgb(51, 80, 62, 40));
sejal patel over 11 years

this is also not working.. actually what i want to doing is.. first in intially textbox forground and backgroung color is above way and when i typed any text then both apacity change means background should be bit lighter and background slightly dark and after enter it should be in intially state... i want to do only that's why i need to change from codebehind in gotfocus and lostfocus..
sejal patel over 11 years

sorry @KeyboardP " new SolidColorBrush(Color.FromArgb(51, 80, 62, 40));" is working... but how you count this Argb value, related to "3B2D1E" hex code?
bafromca over 9 years

You just need to extend the command like so: pv mydump.sql.gz | gunzip | mysql -u root -p your_database. The accepted answer uses this approach.
bafromca over 9 years

As @Prof. Moriarty explans, you can modify the command to not use the password via zcat /path/to/file.sql.gz | mysql -u 'root' -p your_database. It will know the last parameter is the database you wish to use, not your password.
toon81 about 9 years

pv seems to be in the Ubuntu repos too (at least in 12.04 LTS it is), but again you need to do sudo apt-get install pv to get it. Thanks Banjer, this is perfect for big database imports!
Siddhartha about 9 years

Also, mydb needs to be created before importing. This doesn't create the db for you.
ryantuck over 8 years

i found my piping gunzip on a 10GB compressed file caused my import to freeze. not sure if that's due to memory constraints or something but i'd err on the side of doing one step at a time in the future.
icc97 over 8 years

@RyanTuck That is pushing the limits of these kind of processes :)
Cristiano Mendonça about 8 years

I had to run pv mydump.sql.gz | gunzip | mysql -u root my_database_name. That was because I was importing tables and I don't have a password set for my root user
score almost 8 years

In MAC, brew install pv
George over 7 years

To slightly correct @Prof.Moriarty's comment, a good security practice would be to not put my password on the command line (where it will get stored in history, or seen over your shoulder), and let MySQL ask for it. The -p flag alone will cause MySQL to ask at a prompt for the password.
joschi over 7 years

Good point. I have updated the original answer.
malhal over 7 years

How would you include some additional SQL e.g. to disable foreign key checks before the file?
Dmitri DB over 7 years

+1 for something that leaves the damn database dump compressed
rooby over 6 years

@Siddhartha That depends on the sql dump file. Sometimes they include create database statements.
Siddhartha over 6 years

@rooby that makes sense.
wsams over 6 years

If you run into issues with zcat you may also try gzcat. On my MacBook I ran into the error: zcat: can't stat: file.sql.gz (file.sql.gz.Z): No such file or directory
mopsyd almost 6 years

This chokes on mac because for whatever reason, OSX always appends a .Z to the file name. You can use gunzip -c in place of zcat though and it works fine.
DarkCowboy almost 6 years

Hi. From a performance point of view, is it faster to import the dump from the one-liner way? Or is it faster to uncompress to file.sql, then import? Thank you in advance for your opinion.
joschi almost 6 years

@DarkCowboy That depends on whether your CPU, your disk I/O, or the database I/O (which might be a combination of the previous two) is the bottleneck.
Marinos An over 5 years

It would be nice if they created sourcegz mysql command.
Banee Ishaque K almost 5 years

Any GUI tools for this purpose?
Muhammad Omer Aslam over 2 years

pv file.sql.gz |zcat| mysql -u 'root' -p your_database to see progress
Soheil over 2 years

my database is about 10 GB (data+index) and the .gz file is about 1.5 GB. how long may this process of importing take?
bfontaine over 2 years

@Soheil there’s no way one can answer that question without knowing the format of your data and the tech specs of your server.
Soheil over 2 years

@bfontaine yes! more data was needed to make a judgement. I was using 24GB RAM, 6 core CPU and my tables had many indices, it took about 5 hours!