Fixing Sequel PRO SQL Encoding Error For Imported SQL files

Glasses in front of monitor

I use Sequel Pro for administering databases on local development environment.

I encountered an error where the database import would reach half way and the importing process prompts an error  about the encoding being incorrect. Although the database file was encoded in UTF-8 and it seems that the autodetect failed and does not work.

There seems to be an encoding mismatch when exporting a MySQL database from a Linux environment and then imported into Sequel Pro on Mac.

Export and Import MySQL Databases in Terminal

Tobias Fischer Unsplash

Export database

Export your database to create a backup using the following command:

$ mysqldump --opt --hex-blob [-h host] -u [user] -p [database] > [database].sql

Compress with gzip

You can also compress your database on-the-fly with gzip:

mysqldump --opt --hex-blob [-h host] -u [user] -p [database] | gzip -c > [database].sql.gz

Show progress

To show progress, you can use pv:

Setup a local web development environment with XAMPP for MacOS

XAMPP is an easy to install Apache distribution containing MariaDB, PHP and Perl.

XAMPP  is a popular open-source software stack that provides a development environment for web applications and is completely free. The XAMPP open source package has been set up to be incredibly easy to install and to use. It includes an Apache web server containing MySQL or MariaDB and PHP (including Perl), which is the stack you primarily need for web development.