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 or import 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.sqlIt 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:
$ 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.sqlThen, to import the file we can use this command:
$ mysql -u [uname] -p --default-character-set=utf8 [database]
mysql> SET names 'utf8'
mysql> SOURCEdump.sql
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.
try to do liek this but it keeps giving me the same error