Import and insert sql.gz file into database with p

问题:

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?

回答1:

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.

回答2:

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!

回答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 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

回答4:

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.

回答5:

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

回答6:

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.

回答7:

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: _

回答8:

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