Import and insert sql.gz file into database with putty

Login into your server using a shell program like putty.

Type in the following command on the command line

zcat DB_File_Name.sql.gz | mysql -u username -p Target_DB_Name

where

DB_File_Name.sql.gz = full path of the sql.gz file to be imported

username = your mysql username

Target_DB_Name = database name where you want to import the database

When you hit enter in the command line, it will prompt for password. Enter your MySQL password.

You are done!

The file is a gzipped (compressed) SQL file, almost certainly a plain text file with .sql as its extension. The first thing you need to do is copy the file to your database server via scp.. I think PuTTY's is pscp.exe

# Copy it to the server via pscp
C:\> pscp.exe numbers.sql.gz user@serverhostname:/home/user

Then SSH into your server and uncompress the file with gunzip

user@serverhostname$  gunzip numbers.sql.gz
user@serverhostname$  ls 

numbers.sql

Finally, import it into your MySQL database using the < input redirection operator:

user@serverhostname$  mysql -u mysqluser -p < numbers.sql

If the numbers.sql file doesn't create a database but expects one to be present already, you will need to include the database in the command as well:

user@serverhostname$  mysql -u mysqluser -p databasename < numbers.sql

If you have the ability to connect directly to your MySQL server from outside, then you could use a local MySQL client instead of having to copy and SSH. In that case, you would just need a utility that can decompress .gz files on Windows. I believe 7zip does so, or you can obtain the gzip/gunzip binaries for Windows.

Without a separate step to extract the archive:

# import gzipped-mysql dump
gunzip < DUMP_FILE.sql.gz | mysql --user=DB_USER --password DB_NAME

I use the above snippet to re-import mysqldump-backups, and the following for backing it up.

# mysqldump and gzip (-9 ≃ highest compression)
mysqldump --user=DB_USER --password DB_NAME | gzip -9 > DUMP_FILE.sql.gz

For an oneliner, on linux or cygwin, you need to do public key authentication on the host, otherwise ssh will be asking for password.

gunzip -c numbers.sql.gz | ssh user@host mysql --user=user_name --password=your_password db_name

Or do port forwarding and connect to the remote mysql using a "local" connection:

ssh -L some_port:host:local_mysql_port user@host

then do the mysql connection on your local machine to localhost:some_port.

The port forwarding will work from putty too, with the similar -L option or you can configure it from the settings panel, somewhere down on the tree.