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?

Accepted Answer

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.

Import and insert sql.gz file into database with putty

Solution

Problem