How to Fix Error ASCII while Restoring Database from MySQL Dump

689
Share:
how-to-fix-error-ascii-while-restoring-database-from-mysql-dump

As a sysadmin, it's our duty to regularly backup production database. In MariaDB or MySQL we can do this easily by using mysqldump utility, like below:

$ mysqldump --opt -u [uname] -p[pass] --all-databases > [dump.sql]

Where:

  • [uname] - MySQL database username
  • [pass] - MySQL database password for user [uname]
  • [dump.sql] - MySQL database dump target filename

Then we can restore the database dump file by using this command:

$ mysql -u [uname] -p[pass] < [dump.sql]

That should be enough for most of the cases. As a matter of fact, I've been using it for so many years. I even published an article in this blog about backup and restore MySQL database.

But a few days ago, I had an issue where the restore process stopped halfway. There was an error message said:

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.

What Happened?

It seems that the file is not what MySQL expected. When I checked the database dump file with file utility:

$ file dump.sql

It says "UTF-8 Unicode text, with very long lines" like below:

UTF-8 Unicode text, with very long lines

From this error message we know that the common and quickest way of dumping a database with mysqldump does not treat UTF-8 encoding right.

The Fix

For this case, we already have the database dump file to be imported, but it's partially working due to incorrect UTF-8 encoding. We still can use this database dump file using mysql client:

$ mysql -u [uname] -p --default-character-set=utf8 [database]
mysql>  SET names 'utf8'
mysql>  SOURCE dump.sql
...

Where:

  • [uname] - MySQL database username
  • [database] - MySQL database name we're going to restore

Assuming the database dump filename is dump.sql, MySQL should be able to import it correctly.

The Correct Way

To make sure we have a working database dump for UTF-8 encoded character set, use this command to create a database dump:

$ mysqldump -u [uname] -p [database] -r dump.sql

Then, to import the file we can use this command:

$ mysql -u [uname] -p --default-character-set=utf8 [database]
mysql>  SET names 'utf8'
mysql>  SOURCE
dump.sql

Final Words

I hope that you now know how to install dump and restore UTF-8 encoded MySQL / MariaDB database. If you run into any issues or have any feedback feel free to drop a comment below.

Case: import atp raptek dump to mariadb in harimau

Tags Linux
Share:

0 comment

Leave a reply

Your email address will not be published. Required fields are marked *