blocks|key|332597|text|该文件是一个gzipped+(压缩)+SQL文件，几乎可以肯定是一个以.sql为扩展名的纯文本文件。您需要做的第一件事是通过scp将文件复制到数据库服务器。I+think+PuTTY's+is+pscp.exe|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|332598|#+Copy+it+to+the+server+via+pscp
C:\>+pscp.exe+numbers.sql.gz+user@serverhostname:/home/user|code-block|syntax|javascript|332599|然后通过SSH连接到您的服务器，并使用gunzip解压文件|style|CODE|332600|user@serverhostname$++gunzip+numbers.sql.gz
user@serverhostname$++ls+

numbers.sql|332601|最后，使用<输入重定向操作符将其导入到MySQL数据库中：|332602|user@serverhostname$++mysql+-u+mysqluser+-p+<+numbers.sql|332603|如果numbers.sql文件没有创建数据库，但希望已经存在一个数据库，则还需要在命令中包含该数据库：|332604|user@serverhostname$++mysql+-u+mysqluser+-p+databasename+<+numbers.sql|332605|如果您能够从外部直接连接到您的MySQL服务器，那么您可以使用本地MySQL客户端，而不必进行复制和SSH。在这种情况下，你只需要一个可以在Windows上解压.gz文件的工具。我相信7zip可以做到这一点，或者你可以获得Windows的gzip/gunzip+binaries。|332606|entityMap|0|LINK|mutability|MUTABLE|url|http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html|1|http://www.gzip.org/^0|26|R|0|0|0|J|6|0|0|5|1|0|0|0|0|3B|K|1|0^^$0|@$1|2|3|4|5|6|7|18|8|@]|9|@$A|19|B|1A|1|1B]]|C|$]]|$1|D|3|E|5|F|7|1C|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|1D|8|@$A|1E|B|1F|K|L]]|9|@]|C|$]]|$1|M|3|N|5|F|7|1G|8|@]|9|@]|C|$G|H]]|$1|O|3|P|5|6|7|1H|8|@$A|1I|B|1J|K|L]]|9|@]|C|$]]|$1|Q|3|R|5|F|7|1K|8|@]|9|@]|C|$G|H]]|$1|S|3|T|5|6|7|1L|8|@]|9|@]|C|$]]|$1|U|3|V|5|F|7|1M|8|@]|9|@]|C|$G|H]]|$1|W|3|X|5|6|7|1N|8|@]|9|@$A|1O|B|1P|1|1Q]]|C|$]]|$1|Y|3|-4|5|6|7|1R|8|@]|9|@]|C|$]]]|Z|$10|$5|11|12|13|C|$14|15]]|16|$5|11|12|13|C|$14|17]]]]

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.. <a href="http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html" rel="noreferrer">I think PuTTY's is pscp.exe</a>

<pre><code># Copy it to the server via pscp
C:\&gt; pscp.exe numbers.sql.gz user@serverhostname:/home/user
</code></pre>

Then SSH into your server and uncompress the file with <code>gunzip</code>

<pre><code>user@serverhostname$ gunzip numbers.sql.gz
user@serverhostname$ ls 

numbers.sql
</code></pre>

Finally, import it into your MySQL database using the <code>&lt;</code> input redirection operator:

<pre><code>user@serverhostname$ mysql -u mysqluser -p &lt; numbers.sql
</code></pre>

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:

<pre><code>user@serverhostname$ mysql -u mysqluser -p databasename &lt; numbers.sql
</code></pre>

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 <a href="http://www.gzip.org/" rel="noreferrer">gzip/gunzip binaries</a> for Windows.

blocks|key|549163|text|如果没有单独的步骤来解压归档文件：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|549164|#+import+gzipped-mysql+dump
gunzip+<+DUMP_FILE.sql.gz+%7C+mysql+--user=DB_USER+--password+DB_NAME|code-block|syntax|javascript|549165|我使用上面的代码片段重新导入mysqldump-backups，并使用以下代码对其进行备份。|549166|#+mysqldump+and+gzip+(-9+≃+highest+compression)
mysqldump+--user=DB_USER+--password+DB_NAME+%7C+gzip+-9+>+DUMP_FILE.sql.gz|549167|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Without a separate step to extract the archive:

<pre><code># import gzipped-mysql dump
gunzip &lt; DUMP_FILE.sql.gz | mysql --user=DB_USER --password DB_NAME
</code></pre>

I use the above snippet to re-import mysqldump-backups, and the following for backing it up.

<pre><code># mysqldump and gzip (-9 ≃ highest compression)
mysqldump --user=DB_USER --password DB_NAME | gzip -9 &gt; DUMP_FILE.sql.gz
</code></pre>

blocks|key|549221|text|使用像putty这样的shell程序登录到您的服务器。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|549222|在命令行中键入以下命令|549223|zcat+DB_File_Name.sql.gz+%7C+mysql+-u+username+-p+Target_DB_Name|code-block|syntax|javascript|549224|哪里|549225|DB_File_Name.sql.gz+=要导入的sql.gz文件的完整路径|offset|length|style|CODE|549226|username+=您的mysql用户名|549227|Target_DB_Name+=要导入数据库的数据库名称|549228|当您在命令行中输入enter时，它将提示输入密码。输入您的MySQL密码。|549229|你的任务完成了！|549230|entityMap^0|0|0|0|0|0|J|0|0|8|0|0|E|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|12|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|13|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|14|8|@$M|15|N|16|O|P]]|9|@]|A|$]]|$1|Q|3|R|5|6|7|17|8|@$M|18|N|19|O|P]]|9|@]|A|$]]|$1|S|3|T|5|6|7|1A|8|@$M|1B|N|1C|O|P]]|9|@]|A|$]]|$1|U|3|V|5|6|7|1D|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1E|8|@]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1F|8|@]|9|@]|A|$]]]|Z|$]]

Login into your server using a shell program like putty.

Type in the following command on the command line

<pre><code>zcat DB_File_Name.sql.gz | mysql -u username -p Target_DB_Name
</code></pre>

where

<code>DB_File_Name.sql.gz</code> = full path of the sql.gz file to be imported

<code>username</code> = your mysql username

<code>Target_DB_Name</code> = 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!

blocks|key|549286|text|如果您有很多数据库，那么它会导入，转储文件也很大(我经常使用几千兆字节got格式转储文件)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|549287|在mysql中有一个方法可以做到这一点。|549288|$+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|code-block|syntax|javascript|549289|它唯一的优势是|549290|zcat+db1.sql.gz+%7C+mysql+-u+root+-p+|549291|不需要多次输入密码就可以轻松完成多项操作。|549292|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

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.

<pre><code>$ 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&gt; create database db1;
mysql&gt; \! ( zcat db1.sql.gz &gt; src &amp; )
mysql&gt; source src
.
.
mysql&gt; create database db2;
mysql&gt; \! ( zcat db2.sql.gz &gt; src &amp; )
mysql&gt; source src
</code></pre>

The only advantage this has over

<pre><code>zcat db1.sql.gz | mysql -u root -p 
</code></pre>

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

blocks|key|332822|text|如果您有scp，则：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|332823|要将文件从本地移动到远程，请执行以下操作：|332824|$scp+/home/user/file.gz+user@ipaddress:path/to/file.gz+|code-block|syntax|javascript|332825|要将文件从远程移动到本地，请执行以下操作：|332826|$scp+user@ipaddress:path/to/file.gz+/home/user/file.gz|332827|要在不登录远程系统的情况下导出mysql文件，请执行以下操作：|332828|$mysqldump+-h+ipaddressofremotehost+-Pportnumber+-u+usernameofmysql+-p++databasename+%7C+gzip+-9+>+databasename.sql.gz|332829|要在未登录到远程系统的情况下导入mysql文件，请执行以下操作：|332830|$gunzip+<+databasename.sql.gz+%7C+mysql+-h+ipaddressofremotehost+-Pportnumber+-u+usernameofmysql+-p+|332831|注意:请确保您有权通过网络访问远程主机的of地址|332832|要检查网络访问，请执行以下操作：|332833|$ping+ipaddressofremotehost|332834|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|13|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|14|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|15|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|16|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|17|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|18|8|@]|9|@]|A|$G|H]]|$1|Q|3|R|5|6|7|19|8|@]|9|@]|A|$]]|$1|S|3|T|5|F|7|1A|8|@]|9|@]|A|$G|H]]|$1|U|3|V|5|6|7|1B|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1C|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|F|7|1D|8|@]|9|@]|A|$G|H]]|$1|10|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]]|11|$]]

If you have scp then:

To move your file from local to remote:

<pre><code>$scp /home/user/file.gz user@ipaddress:path/to/file.gz 
</code></pre>

To move your file from remote to local:

<pre><code>$scp user@ipaddress:path/to/file.gz /home/user/file.gz
</code></pre>

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

<pre><code>$mysqldump -h ipaddressofremotehost -Pportnumber -u usernameofmysql -p databasename | gzip -9 &gt; databasename.sql.gz
</code></pre>

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

<pre><code>$gunzip &lt; databasename.sql.gz | mysql -h ipaddressofremotehost -Pportnumber -u usernameofmysql -p 
</code></pre>

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

To check network access:

<pre><code>$ping ipaddressofremotehost
</code></pre>

blocks|key|332901|text|如果mysql转储是一个.gz文件，则需要通过键入$+gunzip+mysqldump.sql.gz来使用gunzip解压该文件|type|unstyled|depth|inlineStyleRanges|entityRanges|data|332902|这将解压缩.gz文件，并将mysqldump.sql存储在相同的位置。|332903|键入以下命令以导入sql数据文件：|332904|$+mysql+-u用户名-p+-h本地主机测试数据库<+mysqldump.sql密码：_|332905|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

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 &lt; mysqldump.sql password: _

I want to insert a <code>sql.gz</code> file into my database with SSH. What should I do?

For example I have a database from telephone numbers that name is <code>numbers.sql.gz</code>, what is this type of file and how can I import this file into my database?

Import and insert sql.gz file into database with putty

SQL数据库

数据库

服务器

Windows

MySQL

我想使用SSH将一个sql.gz文件插入到我的数据库中。我该怎么办？例如，我有一个名为numbers.sql.gz的电话号码数据库，这种文件是什么类型的，如何将该文件导入到我的数据库中？

问使用putty向数据库导入和插入sql.gz文件
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用putty向数据库导入和插入sql.gz文件EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用putty向数据库导入和插入sql.gz文件
EN