Enable binary mode while restoring a Database from an SQL dump

Question

Unzip the file, and then import again.

I meet the same problem in windows restoring a dump file. My dump file was created with windows powershell and mysqldump like:

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:

Little-endian UTF-16 Unicode text, with very long lines, with CRLF line terminators.

Then a conversion of coding system is needed, and there are various software can do this. For example in emacs,

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

then input required coding system such as utf-8.

And in the future, for a better mysqldump result, use:

mysqldump <dbname> -r <filename>

and then the output is handled by mysqldump itself but not redirection of powershell.

reference: https://dba.stackexchange.com/questions/44721/error-while-restoring-a-database-from-an-sql-dump

In Windows machine, please follows the preceding steps.

1. Open file in notepad.
2. Click on Save as
3. Select Encoding type UTF-8.

Now source your db.

Extract your file with Tar archiving tool. you can use it in this way:

tar xf example.sql.gz

I had this error once, after running mysqldump on Windows PowerShell like so:

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

What I did was change it to this (pipe instead to Set-Content):

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

And the problem went away!

If you don't have enough space or don't want to waste time decompressing it, Try this command.

gunzip < compressed-sqlfile.gz | mysql -u root -p

Don't forget to replace compressed-sqlfile.gz with your file name.

.gz restore will not work without the command I provided above.