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.

Grave

mysql -u root -p -h localhost -D database --binary-mode -o < dump.sql – Calibrate

That gives ERROR at line 1: Unknown command '\☻'. – Grave

I was getting this error but got a fresh MySQL dump and tried re-importing and it worked fine. Our MySQL dump comes in two zipped parts that have to be concatenated and then unzipped. I think the initial unzipping was interrupted, resulting in a .sql file with weird characters and encodings. The second attempt worked fine. – Proud

Answer 1

Unzip the file, and then import again.

Meantime

Do you mean zip and then unzip? – Sachi

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. – Santos

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

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. – Idiographic

face palm 🤦‍♀️🤦‍♀️🤦‍♀️🤦‍♀️ – Sematic

Same thing just happened to me ^^' Genious. – Hildegardehildesheim

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

How do you unzip a db file? – Stpeter

@Stpeter 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' – Meantime

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

Answer 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

Fisken

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! – Brushoff

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

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. – Ignore

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

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

I had the same thing and needed to script the conversion iconv -f UTF16// -t UTF-8// mysqldump.sql -o mysqldump.sql.utf8 – Dormitory

Answer 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.

Titanomachy

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. – Brethren

My file was +5GB :'( Powershell to the rescue: $utf8 = New-Object Text.Utf8Encoding($false); Get-Content .\index.htm | Out-File -Encoding $utf8 -FilePath .\index.utf8.htm; – Swordbill

Answer 4

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.

Presbytery

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 – Coincidentally

The SQL file already contains the name of the database. – Presbytery

Answer 5

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

tar xf example.sql.gz

Blondie

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. – Mont

Answer 6

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!

Pneumo

I'm getting mysqldump: Got errno 32 on – Deglutinate

See if this thread might be able to help you: #22288771 – Pneumo

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. – Deglutinate

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

Answer 7

Have you tried opening in notepad++ (or another editor) and converting/saving us to UTF-8?

See: notepad++ converting ansi encoded file to utf-8

Another option may be to use textwrangle to open and save the file as UTF-8: http://www.barebones.com/products/textwrangler/

Sear

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

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. – Theadora

Answer 8

May be your dump.sql is having garbage character in beginning of your file or there is a blank line in beginning.

Intoxicating

Answer 9

zcat /path/to/file.sql.gz | mysql -u 'root' -p your_database

Quartersaw

Easiest solution – Apiarist

Answer 10

Its must you file dump.sql problem.Use Sequel Pro check your file ecoding.It should be garbage characters in your dump.sql.

Malonylurea

Answer 11

I had the same problem, but found out that the dump file was actually a MSSQL Server backup, not MySQL.

Sometimes legacy backup files play tricks on us. Check your dump file.

On terminal window:

~$ cat mybackup.dmp 

The result was:

TAPE??G?"5,^}???Microsoft SQL ServerSPAD^LSFMB8..... etc...

To stop processing the cat command:

CTRL + C

Glottology

Answer 12

Under linux Ungzip your file using gunzip Edit your unzip sql file using

vi unzipsqlfile.sql

Remove the first binary line with esc dd go to the bottom of the file with esc shift g remove the last binary line with dd save the file esc x: Then reimport to mysql with :

mysql -u username -p new_database < unzipsqlfile.sql

I performed that with a 20go sql file from a jetbackup cpanel mysql backup. Be patient to wait vi doing the job for big files

Grabowski

Answer 13

The file you are trying to import is a zip file. Unzip the file and then try to import again.

Margoriemargot

Answer 14

I know the original posters question was solved, but I came here via Google, and the various answers eventually led me to discovering that my SQL was dumped with a different default charset than the one used to import it. I got the same error as the original question, but as our dump was piped into another MySQL client, we couldn't go the route of opening it with another tool and saving it differently.

For us, the solution turned out to be the --default-character-set=utf8mb4 option, to be used both on the call of mysqldump as well as the call to import it via mysql. Of course, the value of the parameter may differ for others facing the same problem, it's just important to keep it the same, as the servers (or the tools) default setting might be any charset.

Expression

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 – Mealymouthed

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. – Expression

Answer 15

I had a similar problem. I exported all databases with mysqldump on a PowerShell:

mysqldump -u root -p --all-databases

When I tried to import it on a PowerShell:

mysql -u root -p < .\all-databases.sql

I got an error saying something with < being reserved for future versions.

So I tried the above command with cmd and got the same error like OP.

The solution was to use PowerShell and the following command:

Get-Content '.\all-databases.sql' | &mysql.exe -u user -p

Neuromuscular

Thanks to you I realized it's better not to mysqldump over powershell. – Goyette

Answer 16

Your File should be only .sql extension, (.zip, .gz .rar) etc will not support. example: dump.sql

Bohunk

Answer 17

Old but gold!

On MacOS (Catalina 10.15.7) it was a bit weird: I had to rename my dump.sql into dump.zip and after that, i had to use finder(!) to unzip it. in terminal, unzip dump.zip oder tar xfz dump.sql[or .gz .tar ...] leads to error msgs.

Finally, finder has unziped it totally fine, after that i could import the file without problems.

Gook

Answer 18

File should be dump.sql. It could happen because you didn't extract file. If it's dump.sql.gz for example then extract file from archive.

Teflon

Answer 19

I am using Windows 10 and I tried to import a mysql database. my colleague sent me the file in db.sql.gz format. I unzipped it and tried to import it but was getting this error. Ghasem Pahlavan's solution worked for me but for a beginner, I want to add something more. from the Linux terminal go to the directory where the file is at. for me it was in the downloads folder.

cd /mnt/c/Users/your_username/Downloads

after that run this command

tar xf db.sql.gz

Note: I had installed Ubuntu before.

Zulmazulu

Answer 20

@user1434997 I see none of the answers really solved your problem, except for @Torque's. At first, I assumed the issue to be binary data orientated because of the Error message solution, but it turns out this is an error related to the method of your mysqldump.

My error looked like this, very similar to yours:

ERROR at line 1365: 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.

In my research i eventually found this post https://gist.github.com/ntamvl/a81b90221f37e064ada9efdeb9b55c42 which described a different mysql dump approach also involving using utf8 characterization which I outline below, this solved my problem and i learnt something new:

Instead of:

mysqldump -uroot -p database > utf8.dump

...followed by:

mysql -u username -p database < dump_file

You should rather:

mysqldump -uroot -p database -r utf8.dump

...followed by a database login approach for the import:

mysql -uroot -p --default-character-set=utf8 database
mysql> SET names 'utf8'
mysql> SOURCE utf8.dump

Latinalatinate

Answer 21

You can use this to fix error:

zcat {address_sql_database(.tar.gz)} | mysql -u root -p {database_name} --binary-mode

Hatpin

Why? Please explain how it anwers the question. – Dedrick