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      gt mysql -u root -p -h localhost -D database -o  lt  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    gt  mysql -u root -p -h localhost -D database --binary-mode -o  lt  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

User · Answer

Your File should be only  sql extension    zip   gz  rar  etc will not support  example  dump sql

User · Answer

I meet the same problem in windows restoring a dump file  My dump file was created with windows powershell and mysqldump like   mysqldump db  gt  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  lt dbname gt  -r  lt filename gt    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

User · Answer

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

User · Answer

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

User · Answer

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  gt  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

User · Answer

If you don t have enough space or don t want to waste time in decompressing it  Try this command   gunzip  lt  compressed-sqlfile gz   mysql -u root -p   Don t forget to replace compressed-sqlfile gz with your compressed file name    gz restore will not work without command I provided above

User · Answer

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

User · Answer

Extract your file with Tar archiving tool  you can use it in this way     tar xf example sql gz

User · Answer

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

User · Answer

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  lt  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

User · Answer

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

User · Answer

You can use this to fix error    zcat  address sql database  tar gz     mysql -u root -p  database name  --binary-mode

User · Answer

Unzip the file  and then import again

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

zcat  path to file sql gz   mysql -u  root  -p your database

[mysql] Enable binary mode while restoring a Database from an SQL dump

Examples related to mysql

Examples related to database

Examples related to mysqldump

Examples related to database-restore