MySQL 从 SQL 转储还原数据库时启用二进制模式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17158367/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Enable binary mode while restoring a Database from an SQL dump
提问by user1434997
I am extremely new to MySQL and am running it on Windows. I am trying to restore a Database from a dumpfile in MySQL, but I get the following error:
我对 MySQL 非常陌生，并且正在 Windows 上运行它。我正在尝试从 MySQL 中的转储文件恢复数据库，但出现以下错误：
$ >mysql -u root -p -h localhost -D database -o < dump.sql
ERROR: ASCII 'tar xf example.sql.gz
' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII 'mysqldump -u root p my_db --no-data --no-create-db --no-create-info --routines --triggers --skip-opt --set-gtid-purged=OFF > db_objects.sql
' is expected. Query: 'SQLite format 3'.
I have tried putting --binary-modein the ini file but it still gives the same error. What should I do? Please help.
我试过放入--binary-modeini 文件，但它仍然给出相同的错误。我该怎么办？请帮忙。
UPDATE
更新
As suggested by Nick in his comment I tried $ > mysql -u root -p -h localhost -D database --binary-mode -o < dump.sqlbut it gave me the following ERROR at line 1: Unknown command '\?'.It is a 500 Mb dump file, and when I view its contents using gVIM, all I can see is expressions and data which is not comprehensible.
正如尼克在他的评论中所建议的那样，我尝试过，$ > mysql -u root -p -h localhost -D database --binary-mode -o < dump.sql但它给了我以下内容ERROR at line 1: Unknown command '\?'.这是一个 500 Mb 的转储文件，当我使用 gVIM 查看其内容时，我只能看到无法理解的表达式和数据。
回答by srinivas
Unzip the file, and then import again.
解压缩文件，然后再次导入。
回答by cdarlint
I meet the same problem in windows restoring a dump file. My dump file was created with windows powershell and mysqldump like:
我在 Windows 恢复转储文件时遇到了同样的问题。我的转储文件是用 windows powershell 和 mysqldump 创建的，如：
mysqldump db > dump.sql
mysqldump db > dump.sql
The problem comes from the default encoding of powershell is UTF16. To look deeper into this, we can use "file" utility of GNU, and there exists a windows version here.
The output of my dump file is:
问题来自powershell的默认编码是UTF16。为了更深入了解这一点，我们可以使用GNU的“文件”实用工具，并且存在一个Windows版本在这里。
我的转储文件的输出是：
Little-endian UTF-16 Unicode text, with very long lines, with CRLF line terminators.
Little-endian UTF-16 Unicode 文本，带有很长的行，带有 CRLF 行终止符。
Then a conversion of coding system is needed, and there are various software can do this. For example in emacs,
然后需要进行编码系统的转换，有各种软件可以做到这一点。例如在 emacs 中，
M-x set-buffer-file-coding-system
M-x set-buffer-file-coding-system
then input required coding system such as utf-8.
然后输入所需的编码系统，例如utf-8。
And in the future, for a better mysqldump result, use:
将来，为了获得更好的 mysqldump 结果，请使用：
mysqldump <dbname> -r <filename>
mysqldump <dbname> -r <filename>
and then the output is handled by mysqldumpitself but not redirection of powershell.
然后输出由mysqldump自己处理，但不重定向powershell。
reference: https://dba.stackexchange.com/questions/44721/error-while-restoring-a-database-from-an-sql-dump
参考：https: //dba.stackexchange.com/questions/44721/error-while-restoring-a-database-from-an-sql-dump
回答by Amit Kumar Rai
In Windows machine, please follows the preceding steps.
在 Windows 机器上，请按照上述步骤操作。
- Open file in notepad.
- Click on Save as
- Select Encoding type UTF-8.
- 在记事本中打开文件。
- 点击另存为
- 选择编码类型 UTF-8。
Now source your db.
现在来源你的数据库。
回答by Ghasem Pahlavan
Extract your file with Tar archiving tool. you can use it in this way:
使用 Tar 归档工具提取您的文件。你可以这样使用它：
mysqldump -u root p my_db --no-data --no-create-db --no-create-info --routines --triggers --skip-opt --set-gtid-purged=OFF | Set-Content db_objects.sql
回答by Menios
Have you tried opening in notepad++ (or another editor) and converting/saving us to UTF-8?
您是否尝试过在记事本++（或其他编辑器）中打开并将我们转换/保存为 UTF-8？
See: notepad++ converting ansi encoded file to utf-8
请参阅：notepad++ 将 ansi 编码文件转换为 utf-8
Another option may be to use textwrangle to open and save the file as UTF-8: http://www.barebones.com/products/textwrangler/
另一种选择可能是使用 textwrangle 打开文件并将其保存为 UTF-8：http: //www.barebones.com/products/textwrangler/
回答by Ifedi Okonkwo
I had this error once, after running mysqldumpon Windows PowerShell like so:
mysqldump在 Windows PowerShell 上运行后，我遇到过一次此错误，如下所示：
gunzip < compressed-sqlfile.gz | mysql -u root -p
What I did was change it to this (pipe instead to Set-Content):
我所做的是将其更改为此（管道而不是 Set-Content）：
~$ cat mybackup.dmp
And the problem went away!
问题就解决了！
回答by Subodh Ranadive
May be your dump.sql is having garbage character in beginning of your file or there is a blank line in beginning.
可能是你的 dump.sql 在你的文件开头有垃圾字符或者开头有一个空行。
回答by Dewlance
If you don't have enough space or don't want to waste time in decompressing it, Try this command.
如果你没有足够的空间或者不想浪费时间解压，试试这个命令。
TAPE??G?"5,^}???Microsoft SQL ServerSPAD^LSFMB8..... etc...
Don't forget to replace compressed-sqlfile.gz with your compressed file name.
不要忘记用您的压缩文件名替换compressed-sqlfile.gz。
.gz restore will not work without command I provided above.
如果没有我上面提供的命令，.gz 恢复将无法工作。
回答by Hugo Miura
I had the same problem, but found out that the dump file was actually a MSSQL Server backup, not MySQL.
我遇到了同样的问题，但发现转储文件实际上是 MSSQL Server 备份，而不是 MySQL。
Sometimes legacy backup files play tricks on us. Check your dump file.
有时，旧的备份文件会欺骗我们。检查您的转储文件。
On terminal window:
在终端窗口：
CTRL + C
The result was:
结果是：
##代码##To stop processing the cat command:
要停止处理 cat 命令：
##代码##回答by Datty Wang
Its must you file dump.sql problem.Use Sequel Pro check your file ecoding.It should be garbage characters in your dump.sql.
它必须你文件 dump.sql 问题。使用 Sequel Pro 检查你的文件编码。它应该是你的 dump.sql 中的垃圾字符。
