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.

mysql -u root -p -h localhost -D database --binary-mode -o < dump.sql — Nick, Jun 17 '13 at 23:17
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. — Joshua Pinter, Jul 19 '18 at 23:05

score 328 · Accepted Answer · answered May 09 '14 at 16:48

328

Unzip the file, and then import again.

answered May 09 '14 at 16:48

srinivas

4,778
2
32
43

3

Do you mean zip and then unzip? – J86 Jan 17 '17 at 19:58
23

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. – MotsManish Jan 30 '17 at 06:37
1

@MotsManish Seriously? I thought this was a joke. I'll give it a shot and see if that works. – Joshua Pinter Jul 19 '18 at 22:42
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. – Shafiq al-Shaar Nov 02 '18 at 18:47
17

face palm ‍♀️‍♀️‍♀️‍♀️ – Rambatino Feb 07 '19 at 22:39
Same thing just happened to me ^^' Genious. – Loïc Pennamen Mar 01 '19 at 10:23
I feel really ashamed. But actually this was helpful. It works! – Augusto Gonzalez Feb 06 '20 at 22:06
2

How do you unzip a db file? – Jeff.H Aug 25 '20 at 17:55
@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' – srinivas Aug 26 '20 at 09:26
1

thats poor. What if there's a very large file?? should be able to import the zip – Muhammad Talal Mar 03 '21 at 10:39

score 85 · Answer 2 · edited Apr 13 '17 at 12:42

85

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

edited Apr 13 '17 at 12:42

Community

1
1

answered Nov 20 '13 at 03:46

cdarlint

1,485
16
14

1

mysqldump -r 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! – Timothy L.J. Stewart Jan 18 '17 at 02:07
7

On a practical note, I got around this after creating the file in Powershell by converting the generated file to UTF-8 using Notepad++. – Peter Majeed Jan 21 '17 at 14:05
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. – sam452 Dec 28 '18 at 17:13
I did the same as @PeterMajeed . A quick convert-and-save with NotePad++ allowed me to restore an existing file – Stephen R Apr 02 '20 at 21:23
this is the best answer for me, not sure why the accepted answer has 200+ votes compared to this one – andrewm Feb 25 '21 at 16:17
I had the same thing and needed to script the conversion `iconv -f UTF16// -t UTF-8// mysqldump.sql -o mysqldump.sql.utf8` – Greg Bosen Aug 11 '22 at 19:38

score 39 · Answer 3 · edited Jan 05 '17 at 05:34

39

In Windows machine, please follows the preceding steps.

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

Now source your db.

edited Jan 05 '17 at 05:34

Sibeesh Venu

18,755
12
103
140

answered Jan 05 '17 at 04:38

Amit Kumar Rai

399
3
4

1

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. – Harry Mantheakis Jan 09 '20 at 12:39
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; – JDC Jan 30 '23 at 12:00

Dewlance · Answer 4 · 2021-04-08T04:24:49.093

15

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.

edited Apr 08 '21 at 04:24

answered Nov 04 '18 at 11:41

Dewlance

441
4
7

1

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` – fudu Nov 19 '21 at 07:49
The SQL file already contains the name of the database. – Dewlance Oct 22 '22 at 05:12

score 10 · Answer 5 · answered Jan 18 '16 at 06:41

10

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!

answered Jan 18 '16 at 06:41

Ifedi Okonkwo

3,406
4
33
45

I'm getting mysqldump: Got errno 32 on – Radu Jan 02 '17 at 07:12
See if this thread might be able to help you: http://stackoverflow.com/questions/22288271/mysqldump-got-errno-32-on-write – Ifedi Okonkwo Jan 02 '17 at 13:04
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. – Radu Jan 09 '17 at 19:20
Thanks a lot. I know why I usually use Linux... – phylib Feb 24 '22 at 17:26

score 10 · Answer 6 · answered May 11 '17 at 17:27

10

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

tar xf example.sql.gz

answered May 11 '17 at 17:27

Ghasem Pahlavan

661
9
20

1

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. – seanbreeden Jan 21 '19 at 16:39

score 8 · Answer 7 · edited May 23 '17 at 11:47

8

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/

edited May 23 '17 at 11:47

Community

1
1

answered Jun 18 '13 at 00:31

Menelaos

23,508
18
90
155

3

Thanks. This did the trick for me. Open the file in NotePad++. Encoding > Convert To UTF 8. – Abhijeet Nagre Feb 22 '17 at 12:46
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. – tusar Jun 04 '18 at 12:11

score 5 · Answer 8 · answered May 08 '15 at 12:55

5

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

answered May 08 '15 at 12:55

Subodh Ranadive

331
1
3
17

score 4 · Answer 9 · answered Feb 26 '20 at 07:54

4

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

answered Feb 26 '20 at 07:54

Nguyễn Anh Tuấn

1,023
1
12
19

Easiest solution – Akif Mar 31 '22 at 12:31

score 3 · Answer 10 · answered Oct 20 '14 at 03:22

3

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

answered Oct 20 '14 at 03:22

Datty Wang

47
2

score 3 · Answer 11 · answered Jul 15 '15 at 21:29

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

score 2 · Answer 12 · answered Jun 23 '20 at 19:27

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

score 1 · Answer 13 · answered Nov 13 '18 at 08:50

1

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

answered Nov 13 '18 at 08:50

Javaid Mir

41
1
6

score 1 · Answer 14 · answered Feb 08 '22 at 11:32

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

Thanks to you I realized it's better not to mysqldump over powershell. — mgo, May 25 '23 at 15:04

score 0 · Answer 15 · answered Oct 23 '17 at 01:02

0

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

answered Oct 23 '17 at 01:02

Ibrahim Akbar

119
1
8

score 0 · Answer 16 · answered Jan 23 '20 at 09:04

0

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.

answered Jan 23 '20 at 09:04

Torque

3,319
2
27
39

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` – Romeo Patrick Jun 21 '20 at 15:08
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. – Torque Jun 24 '20 at 08:06

score 0 · Answer 17 · answered Oct 13 '20 at 17:01

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.

score 0 · Answer 18 · answered Feb 03 '23 at 07:31

0

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.

answered Feb 03 '23 at 07:31

Aliaksei Korshyn

11
2

score -1 · Answer 19 · edited Oct 09 '19 at 23:43

-1

You can use this to fix error:

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

edited Oct 09 '19 at 23:43

Das_Geek

2,775
7
20
26

answered Oct 09 '19 at 20:44

Ali

1

2

Why? Please explain how it anwers the question. – Yunnosch Oct 09 '19 at 20:53

Enable binary mode while restoring a Database from an SQL dump

19 Answers19

Linked