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

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.