Moved a WordPress website database or any website using MySQL from one server to another and getting funky/weird characters? The Character set setting is probably to Blame

When moving a WordPress website or just about any website that uses MySQL as its database, you need to pay attention to the character set used for Collation in the MySQL database you are moving from and on the database you are moving to.

Otherwise, if you export a MySQL database that has a “Latin1” character set, MySQL will use its default UTF-8 character set during the export process. When you import this SQL dump into a new MySQL database, you’re most likely going to see all sorts of funky characters all over the site.

Cleaning it up is not an easy task and depends on how large a database you have.

There are several tutorial on the web on how to convert any MySQL database to UTF-8.  In general UTF-8 is a better option as a character set for WordPress especially for international site. But doing this conversion is a longer job.

If your job right now is to move the contents of a MySQL database from one to another, then read on. Most often you just want your website to function fine. Converting a site to UTF-8 is not a pressing issue.

First check what kind of Collation you have set on your the MySQL database you wish to move.

In PHPMyAdmin you can see the Collation setting under “Operations”.

In my test case it was “latin1_swedish_ci“.

So the first order of business is to dump the MySQL database and set the character set while doing so.

This is the command line syntax for dumping a SQL file from a MySQL database:

mysqldump –default-character-set=latin1 -uUSERNAME -pPASSWORD DATABASENAME > YOURSQLFILE.SQL

The “ALL-CAPS” indicate places where you’ll need to fill in your own MySQL database’s info.

Once you have finished exporting this SQL file.

You need to create a new MySQL database suitable for import. Make sure that the Collation is also set to “latin1_swedish_ci” under “Operations” using PHPMyAdmin.

Now import the file you exported using this syntax:

mysql -p -uUSERNAME DATABASENAME < YOURSQLFILE.SQL

The CAPS indicates details you need to supply. You will be prompted for the password.

Once import is complete, you will test if everything is ok by editing the MySQL connection details in whatever application you are working on moving.

For WordPress it is wp-config.php