Import and insert sql.gz file into database with putty

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?

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.

If you have scp then:

To move your file from local to remote:

$scp /home/user/file.gz user@ipaddress:path/to/file.gz 

To move your file from remote to local:

$scp user@ipaddress:path/to/file.gz /home/user/file.gz

To export your mysql file without login in to remote system:

$mysqldump -h ipaddressofremotehost -Pportnumber -u usernameofmysql -p  databasename | gzip -9 > databasename.sql.gz

To import your mysql file withoug login in to remote system:

$gunzip < databasename.sql.gz | mysql -h ipaddressofremotehost -Pportnumber -u usernameofmysql -p 

Note: Make sure you have network access to the ipaddress of remote host

To check network access:

$ping ipaddressofremotehost

If you've got many database it import and the dumps is big (I often work with multigigabyte Gzipped dumps).

There here a way to do it inside mysql.

$ mkdir databases
$ cd databases
$ scp user@orgin:*.sql.gz .  # Here you would just use putty to copy into this dir.
$ mkfifo src
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.41-0
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database db1;
mysql> \! ( zcat  db1.sql.gz > src & )
mysql> source src
.
.
mysql> create database db2;
mysql> \! ( zcat  db2.sql.gz > src & )
mysql> source src

The only advantage this has over

zcat db1.sql.gz | mysql -u root -p 

is that you can easily do multiple without enter the password lots of times.

If the mysql dump was a .gz file, you need to gunzip to uncompress the file by typing $ gunzip mysqldump.sql.gz

This will uncompress the .gz file and will just store mysqldump.sql in the same location.

Type the following command to import sql data file:

$ mysql -u username -p -h localhost test-database < mysqldump.sql password: _

Creating a Dump File SQL.gz on the current server

$ sudo apt-get install pigz pv

$ pv | mysqldump --user=<yourdbuser> --password=<yourdbpassword> <currentexistingdbname> --single-transaction --routines --triggers --events --quick --opt -Q --flush-logs --allow-keywords --hex-blob --order-by-primary --skip-comments --skip-disable-keys --skip-add-locks --extended-insert --log-error=/var/log/mysql/<dbname>_backup.log | pigz > /path/to/folder/<dbname>_`date +\%Y\%m\%d_\%H\%M`.sql.gz

Optional: Command Arguments for connection

--host=127.0.0.1 / localhost / IP Address of the Dump Server
--port=3306

Importing the dumpfile created above to a different Server

$ sudo apt-get install pigz pv

$ zcat /path/to/folder/<dbname>_`date +\%Y\%m\%d_\%H\%M`.sql.gz | pv | mysql --user=<yourdbuser> --password=<yourdbpassword> --database=<yournewdatabasename> --compress --reconnect --unbuffered --net_buffer_length=1048576 --max_allowed_packet=1073741824 --connect_timeout=36000 --line-numbers --wait --init-command="SET GLOBAL net_buffer_length=1048576;SET GLOBAL max_allowed_packet=1073741824;SET FOREIGN_KEY_CHECKS=0;SET UNIQUE_CHECKS = 0;SET AUTOCOMMIT = 1;FLUSH NO_WRITE_TO_BINLOG QUERY CACHE, STATUS, SLOW LOGS, GENERAL LOGS, ERROR LOGS, ENGINE LOGS, BINARY LOGS, LOGS;"

Optional: Command Arguments for connection

--host=127.0.0.1 / localhost / IP Address of the Import Server
--port=3306

mysql: [Warning] Using a password on the command line interface can be insecure. 1.0GiB 00:06:51 [8.05MiB/s] [<=> ]

The optional software packages are helpful to import your database SQL file faster

• with a progress view (pv)
• Parallel gzip (pigz/unpigz) to gzip/gunzip files in parallel

for faster zipping of the output