Import and paste sql.gz file into the database using putty

Question

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

For example, I have a database of phone numbers, whose name is numbers.sql.gz , what kind of file is this, and how can I import this file into my database?

Answer 1

The file is a compressed (compressed) SQL file, almost certainly a simple text file with the extension .sql. The first thing you need to do is copy the file to the database server using scp .. I think PuTTY is pscp.exe

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

Then ssh to your server and unzip the file using 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 does not create the database, but expects that it is already present, you will also need to add the database to the command:

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

If you have the ability to connect directly to the MySQL server from the outside, you can use the local MySQL client instead of copying and SSH. In this case, you just need a utility that can unzip .gz files on Windows. I believe 7zip does this, or you can get gzip / gunzip binaries for Windows.

Answer 2

Log in to your server using a shell program such as putty.

Type the following command at a command prompt

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

Where

DB_File_Name.sql.gz = full path to sql.gz file for import

username = your mysql username

Target_DB_Name = name of the database in which you want to import the database

When you press enter at the command line, it will prompt you for a password. Enter the MySQL password.

You are done!

Answer 3

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 for backup.

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

Answer 4

For oneliner, on linux or cygwin, you need to authenticate the public key on the host, otherwise ssh will ask for the password.

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

Or port forwarding and connecting to remote mysql using a "local" connection:

 ssh -L some_port:host:local_mysql_port user@host 

then mysql connection on your local machine with localhost: some_port .

Port forwarding will work from putty , with a similar option -L or you can configure it from the settings panel, somewhere in the tree.

Answer 5

If you have a lot of databases, they are imported, and the dumps are large (I often work with multi-gigabyte Gzip dumps).

There is a way to do this 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 he has over

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

lies in the fact that you can easily do a few without entering a password many times.

Answer 6

If you have scp then:

To move a file from local to remote:

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

To move a file from remote to local:

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

To export a mysql file without logging in to a remote system:

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

To import your mysql file with the username tooug to a remote system:

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

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

To check network access:

 $ping ipaddressofremotehost