Import and insert sql.gz file into database with putty

Question

I want to insert a sql.gz file into my database with SSH. What should I do?

For example I have a database from telephone numbers that name is numbers.sql.gz, what is this type of file and how can I import this file into my database?

Answer 1

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!

Answer 2

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.