Enable binary mode while restoring a Database from an SQL dump

Question

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 -u root -p -h localhost -D database -o < dump.sql
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: 'SQLite format 3'.

I have tried putting --binary-mode in the ini file but it still gives the same error. What should I do? Please help.

UPDATE

As suggested by Nick in his comment I tried $ > mysql -u root -p -h localhost -D database --binary-mode -o < dump.sql but 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.

Solution 1

Unzip the file, and then import again.

Solution 2

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

Solution 3

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.

Solution 4

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

tar xf example.sql.gz

Solution 5

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!

View more solutions

Comments

[Radu]

I'm getting mysqldump: Got errno 32 on

[Ifedi Okonkwo]

See if this thread might be able to help you: stackoverflow.com/questions/22288271/…

[Radu]

Thank you. The issue was that I exported the db with an old version of phpmyadmin on an old mysql server. Not sure why but half of the database was exported in clear text and the other half gzip-ed.

[J86]

Do you mean zip and then unzip?

[Timothy L.J. Stewart]

mysqldump <dbname> -r <filename> anyone using Windows or DOS systems this is the solution. UTF-8 file conversion is a distraction. Use the -r option, which directs the output to the filename and handles CRLF carriage return linefeed (\r\n) that windows puts in files, this is where the problem is. Thanks for the Excellent Solution!

[Peter Majeed]

On a practical note, I got around this after creating the file in Powershell by converting the generated file to UTF-8 using Notepad++.

[MotsManish]

This is how it worked for me, unzip the db.sql.gz, you will get db.sql, rename it again to db.sql.gz, don't zip it, just rename it, then unzip again to db.sql and now you will get the right file to import.

[Abhijeet Nagre]

Thanks. This did the trick for me. Open the file in NotePad++. Encoding > Convert To UTF 8.

[tusar]

Also note the significant change in file size after you 'save As' the existing .sql file with utf-8 encoding ! Almost half of the size compared to given file. In my case the mysqldump was taken using a Windows Power Shell, that program messed up the encoding.

[Joshua Pinter]

@MotsManish Seriously? I thought this was a joke. I'll give it a shot and see if that works.

[Shafiq al-Shaar]

This worked when I compressed a file to .tar.bz2 using linux and used WinRAR to extract it on Windows. I don't get it. Nice.

[sam452]

This answer, if I hadn't dug in, would have saved me hours of searching for the correct answer. Wish I could upvote more than once.

[seanbreeden]

This was the answer for me. At first, I gunzipped .sql.gz file whic resulted in the "binary" error when importing. Turned out the file was tar/gzipped so I had to tar xvf the file first then it let me import it.

[Rambatino]

face palm 🤦‍♀️🤦‍♀️🤦‍♀️🤦‍♀️

[Loïc Pennamen]

Same thing just happened to me ^^' Genious.

[Yunnosch]

Why? Please explain how it anwers the question.

[Harry Mantheakis]

This worked for me for an SQL backup-file that had been created by running mysqldump via Powershell. The Poweshell output was UTF-16. Changing to UTF-8 solved the problem and allowed me to restore my detabase from the backup-file.

[Augusto Gonzalez]

I feel really ashamed. But actually this was helpful. It works!

[Stephen R]

I did the same as @PeterMajeed . A quick convert-and-save with NotePad++ allowed me to restore an existing file

[Romeo Patrick]

Would you mind sharing the entire string you wrote? As I am having the same situation as you. I am though still not sure why it's not working for me. it's on the same server, trying to make a staging of a website with the mysqldump -uUSER -p user_db | gzip > user_db_$(date +"%Y%m%d_%H%M").sql.gz then trying to import it using gunzip -c user_db_datetime.sql.gz | mysql -uUSER -p user_db

[Torque]

Our string would not be helpful to you, as it is a huge collection of various custom settings. The way you describe your situation, my answer would not apply: my problem arose from the dumping computer/connection being a different setup than the restoring one, so we needed to specify the default charset in order to force them to be identical.

[Jeff.H]

How do you unzip a db file?

[srinivas]

@Jeff.H you can actually rename it to .zip or .gz and use winzip or any other unzip tool. You might have to experiment unzipping the content also, because for some weird reason I have the content zipped also when I exported a dump. Or on terminal you can use 'untar'

[lisburnite]

this is the best answer for me, not sure why the accepted answer has 200+ votes compared to this one

[Muhammad Talal]

thats poor. What if there's a very large file?? should be able to import the zip

[fudu]

You should add database_name after the command, so it will import the sql file to that database, or it will cause error. Ex: gunzip < compressed-sqlfile.gz | mysql -u root -p your_database_name

[phylib]

Thanks a lot. I know why I usually use Linux...

[Akif]

Easiest solution