mysql无法还原数据库,无法将MYSQL备份还原到新数据库

I successfully created a mysqldump file myDump.sql of a myDb1 database using guidelines from this thread. Also I created a second database myDb2, navigated to the directory containing myDump.sql and trying to restore it into the new database myDb2 but failing, Two methods I tried:

> mysql -u root -p myDb2 < myDump.sql;

> -- entered password

and:

> mysql -u root -p

mysql> -- entered password

mysql> USE myDb2;

mysql> SOURCE myDump.sql;

Both have the same error message:

ERROR:

ASCII '\0' 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 '\0' is expected. Query: ' ■-'.

I'd also like to know if I need to use the same database name as the old db for the new one. I tried with a different and same names, but with this same result error.

解决方案

This is probably caused by coding systems.

My dump file is generated using redirection (">") in powershell and I encountered the same problem. The output redirection generated a file with UTF-16 Little endian.

However, this can be solved by converting the dumpfile into utf-8. This can be done in emacs as:

M-x set-buffer-file-coding-system

Then save the file and import again.

The coding system of a file can be detected using GNU "file" utility, and it also available in windows and can be found here: http://gnuwin32.sourceforge.net/packages/file.htm

For future use, a better dump command like:

mysqldump -r