MySQL 使用 putty 将 sql.gz 文件导入并插入到数据库中

Import and insert sql.gz file into database with putty

提问by babak faghihian

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

我想sql.gz使用 SSH将文件插入到我的数据库中。我该怎么办？

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?

例如，我有一个来自电话号码的数据库，名称为numbers.sql.gz，这种类型的文件是什么以及如何将此文件导入到我的数据库中？

采纳答案by Michael Berkowski

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

该文件是一个 gzipped（压缩）SQL 文件，几乎可以肯定是一个扩展名为 .sql 的纯文本文件。您需要做的第一件事是通过 scp 将文件复制到您的数据库服务器。我认为 PuTTY 是 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

然后通过 SSH 连接到您的服务器并使用以下命令解压缩文件 gunzip

user@serverhostname$  gunzip numbers.sql.gz
user@serverhostname$  ls 

numbers.sql

Finally, import it into your MySQL database using the <input redirection operator:

最后，使用<输入重定向运算符将其导入 MySQL 数据库：

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:

如果 numbers.sql 文件没有创建数据库但希望已经存在一个数据库，则您还需要在命令中包含该数据库：

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 binariesfor Windows.

如果您能够从外部直接连接到您的 MySQL 服务器，那么您可以使用本地 MySQL 客户端，而不必复制和 SSH。在这种情况下，您只需要一个可以在 Windows 上解压缩 .gz 文件的实用程序。我相信 7zip 会这样做，或者您可以获得Windows的gzip/gunzip 二进制文件。

回答by seshu

Login into your server using a shell program like putty.

使用像 putty 这样的 shell 程序登录到您的服务器。

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

DB_File_Name.sql.gz= 要导入的 sql.gz 文件的完整路径

username= your mysql username

username= 你的 mysql 用户名

Target_DB_Name= database name where you want to import the database

Target_DB_Name= 要导入数据库的数据库名称

When you hit enter in the command line, it will prompt for password. Enter your MySQL password.

当您在命令行中按 Enter 键时，它会提示输入密码。输入您的 MySQL 密码。

You are done!

你完成了！

回答by feeela

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-backups，并使用以下代码进行备份。

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

回答by Raul

For an oneliner, on linux or cygwin, you need to do public key authentication on the host, otherwise ssh will be asking for password.

对于 oneliner，在 linux 或 cygwin 上，您需要在主机上进行公钥认证，否则 ssh 将要求输入密码。

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:

或者进行端口转发并使用“本地”连接连接到远程 mysql：

ssh -L some_port:host:local_mysql_port user@host

then do the mysql connection on your local machine to localhost:some_port.

然后在本地机器上将mysql 连接到localhost:some_port。

The port forwarding will work from puttytoo, with the similar -L option or you can configure it from the settings panel, somewhere down on the tree.

端口转发也可以从putty工作，使用类似的 -L 选项，或者您可以从设置面板中配置它，位于树的某个位置。

回答by sarathkm

If you have scp then:

如果你有 scp 那么：

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:

要在不登录远程系统的情况下导出您的 mysql 文件：

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

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

要在不登录远程系统的情况下导入您的 mysql 文件：

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

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

注意：确保您可以通过网络访问远程主机的 ipaddress

To check network access:

要检查网络访问：

$ping ipaddressofremotehost

回答by Raj Kumar

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

如果 mysql 转储是一个 .gz 文件，您需要通过键入 $ gunzip mysqldump.sql.gz 来进行 gunzip 解压缩文件

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

这将解压缩 .gz 文件，并将 mysqldump.sql 存储在同一位置。

Type the following command to import sql data file:

输入以下命令导入sql数据文件：

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

$ mysql -u 用户名 -p -h localhost test-database < mysqldump.sql 密码：_

回答by rjt1224

Creating a Dump File SQL.gz on the current server

在当前服务器上创建转储文件 SQL.gz

$ 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] [<=> ]

mysql：[警告] 在命令行界面上使用密码可能不安全。1.0GiB 00:06:51 [8.05MiB/s] [<=>]

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

可选软件包有助于更快地导入数据库 SQL 文件

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

• 带有进度视图 (pv)
• 并行 gzip (pigz/unpigz) 到 gzip/gunzip 文件并行

for faster zipping of the output

更快地压缩输出

回答by rgammans

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

如果你有很多数据库，它会导入并且转储很大（我经常使用多千兆字节的 Gzipped 转储）。

There here a way to do it inside mysql.

这里有一种方法可以在 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.

是您可以轻松地进行多次操作，而无需多次输入密码。