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