Mysql error ascii 0 appeared in the statements

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:

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

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

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:

What Happened?

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

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

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:

  • [ uname ] — MySQL database username
  • [ database ] — MySQL database name we’re going to restore
Читайте также:  Как сделать error 404

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:

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

Final Words

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