Seeing Weird Characters On Your WordPress Blog? WP DB Migration Is What You Need…
I know how frustrating it is to see Weird Characters in your blog post. The weird characters that hinder the reading experience of a reader. I have been through all this, tried many different solutions present online but it was all futile. Until I landed upon a solution which is safe and easy to implement.
If you are looking for an easy solution to quickly get rid of all those weird characters you are seeing on your blog then you will love this article. In this article, I’m going to share that ONE solution that will solve your problem and get rid of unwanted characters.I Just Found The Best Article On The Web Explaining How to Get Rid Of Weird/Funny Characters From My WordPress Blog. Click To Tweet
Cause Of Weird Characters
The cause of these weird characters is no more than Character Encoding. You might have heard of different character encodings (UTF8, UTF16, UTF32, Latin1, etc…) when it comes to the computer world. Well, this is one of those simple encoding problems where your computer expects data to be in some other form than what it actually is.
You could relate to this problem in the real world as communication gap between two people speaking two different but similar languages. You might understand most of the words in the conversation but there will always be a few words that will remain alien to both of you. This is what is happening on your blog right now.
I know this because I have been there. I searched all over the internet to find the solution and these were all the listed problems that I came across. There could be a lot of reason behind this encoding problem. Some of the common reasons that are listed on the internet are:
- I started seeing weird characters after I upgraded my WordPress blog to a newer version…
- I just moved my blog to a new host and started seeing all those weird characters (Ã Ã¢ â¬â¢ ¢?) on the blog…
- ported entire data to another database, etc…
Reasons could be many, but the solution to all those problems is the same.
What causes those funny/weird characters to pop up on your WordPress blog?
By default, the WordPress create tables in whatever encoding your database charset have. Most probably your database charset encoding would be Latin1 because mine was the same.
WordPress expects data to be in UTF8 which you can see in
WordPress did not provide a default UTF8 encoding before creating tables into the database for the first time. This results in wrong charset encoding. That is what has become the root cause for those funny characters on your blog.
You must be thinking, ah well, why can’t we simply tell WordPress about the database charset?
Exactly, this is one simple solution to all your problem. You could simply tell WordPress that the database tables have Latin1 encoding so translate the content from Latin1 before displaying it to the user.
But, there is one problem, it is not a full proof solution. You will still be saving UTF8 encoded content in Latin1 encoded tables and this will cause trouble at some time. The best option would be to eliminate the problem from the root.
Why not convert the tables to UTF8 encoding once and for all…
The best solution would be to permanently convert Latin1 tables to UTF8. There are a two recommended ways by which you can convert Latin1 to UTF8.
- UNIX Commands on your server
I will be covering both the parts in a minute but before let me brief you about the entire process in plain English :p
This is a three-step process. First, you will have to export your database into a SQL file with UTF-8 encoding. Secondly, you will have to create a new database with UTF-8 collation. This will become the database for your blog so choose a nice name. Third and the last step would be to import the UTF-8 encoded SQL file into your new database.
Once you have performed all the necessary steps, you will point your blog to your newly created UTF-8 database and you will no longer see those funny characters on your blog.
Now, let’s get to work and migrate your WP DB from Latin1 to UTF8.
WP DB Migrate From Latin1 to UTF-8 (Using PHPMyAdmin)
I’m sure you will have access to your PHPMyAdmin dashboard. Almost every web host provider provides PHPMyAdmin to manage MySql database. This comes bundled with CPanel and I’m sure whatever host you are using, you will have access to PHPMyAdmin. (If you wish to perform the same using UNIX commands then move to second part of this article).
Step #1. Export Existing Database with UTF-8 Encoding
With PHPMyAdmin, you get a wonderful UI to export data. You just have to perform certain clicks and your data will be exported safely.
Go to Export tab on your PhpMyAdmin dashboard and select all the tables. These tables along with the data will be exported to a SQL file. Make sure you select all the tables and data to export.
Navigate to the Object Creation Section and select the following. Make sure you tick the AUTO_INCREMENT value and IF NOT EXISTS options. Refer the image below,
Last but not the least, Navigate to the Data Creation Option and select the following options. Once you have made the selection, click on the GO button and save the file on your computer file system. A dialog box will pop up, simply give a meaning full name to the file and save it.
Cool, you have just exported your entire database into UTF-8 encoding. Next, you will create a new database and import this file into the new database.
Step #2. Create New Database With UTF-8 Collation
Great Job! You have successfully exported your entire database into a UTF-8 encoded data. It is time to create a new database with UTF-8 collation.
Click on the NEW link from the side navbar and it will take you to
Create Database page as you can see below. Provide a meaningful name to your new database and select
UTF8_bin as collation and hit the
This will create a New Database in MySql with UTF8 collation.
Step #3. Import UTF-8 Encoded Data To Your Newly Created Database
Next and the final step towards migrating the Latin1 encoded database to UTF-8 is to import recently exported data from your old database into newly created one.
Select your New Database from the list.
Navigate to the Import tab from the top navigation bar where you will see an option to import SQL file.
Click on the Choose File button and select the previously exported SQL file from the dialog box that pops up.
Click on the GO button at the very bottom and the importing process will start. It will take a few seconds depending upon the size of your SQL file. Once done you will have a new database with your old data in the UTF-8 encoding. All the hard work is done here. You have created a UTF-8 encoded database for your blog.
The only thing left to do is to tell WordPress to point to this newly created database instead of the old one. This is very simple to do. For this, you must have access to your WordPress files hosted on the server. You can access those files with the help of Filezilla (a free FTP client). You can click here if you do not know how to connect to your hosting server using Filezilla.
Moving forward, once you have connected to the server, navigate to the root folder where WordPress is installed and open
Find your old database name and replace it with the new one.
Great Work! You have successfully ported your entire Latin1 Database to UTF-8. Wait, you first need to go to your website and check whether everything is working in the way it is expected to work. If everything is working fine then quickly share this post now 🙂
- SpeedUp Your WordPress Website For Google PageSpeed Insight
- How to Embed Mailchimp Form In Your Blog Post For Maximum Page Load Speed
- 25 Free Must Have WordPress Plugins Of All Time
WP DB Migrate From Latin1 to UTF-8 (Using UNIX Commands)
To be honest, this way of doing things is safer than doing it with PHPMyAdmin. With PhpMyAdmin, a lot of things happens behind the scene which we are not aware. This may corrupt our data and make it an unpleasant experience.
However, if you are doing everything by yourself with the help of UNIX commands then there is a very less possibility of doing mistakes. Atleast you will know what you are doing.
PhpMyAdmin is good for people who are on shared hosting and do not have access to their servers directly. But if you have access to the server then doing everything manually should be the best option for you.
Doing it with commands is easier to follow. You just have to fire commands in the same listed order. Now, that you have gone through the PHPMyAdmin way of doing this thing, it would make more sense to understand what actually goes behind the scene. Let’s start the migration process :p
Step #1. Make a Backup of Your Entire Database
The first thing that we do before performing any actions on the database is BACKUP. You should take a backup of your entire database if anything goes wrong.
Copy and paste the below command into your server. Replace the BOLD text with your database specifics.
-- Backup Database to a .sql file
mysqldump --opt --default-character-set=latin1 --skip-extended-insert --user root --password my-database-name -r exp-my-database-name-latin1.sql --log-error=log-mysqldump-my-database.txt
After firing the above command, a new SQL file will be created in the same folder with the following name
exp-my-database-name-latin1.sql. To check the contents of your folder, fire command.
This will list the entire contents of the folder. See the file at the end which has been created. This file contains your entire database in Latin1 format. This is the entire backup of the database.
Step #2. Convert Existing Data to SQL file in UTF-8 Encoding
This is the continuation of the same first step that we did with PHPMyAdmin. You have already exported your entire database, now you will have to convert the contents of the SQL file into a UTF-8 encoded SQL file. Don’t worry this is also very simple. You just have to fire below command,
-- Replace latin charset with utf8 charset and create new .sql file with utf8 encoding
replace "CHARSET=latin1" "CHARSET=utf8" "SET NAMES latin1" "SET NAMES utf8" < exp-my-database-name-latin1.sql > exp-my-database-name-utf8.sql
This command will create a UTF-8 encoded content in the new file named
Step #3. Create New Database with UTF-8 Encoding
In this step, you will create a new Database with UTF-8 collation. This will contain all the data encoded in the UTF-8 format that recently converted from Latin1. Go ahead and fire the below command to create a new database with UTF-8 collation.
Replace the BOLD text with your database name. This database will become the main database for your blog so choose a meaningful name.
-- create new database that will hold data with utf8 encoding
CREATE DATABASE MY-DATABASE-NAME_UTF8 CHARACTER SET UTF8 COLLATE UTF8_BIN;
This will create a new database in MySql with UTF-8_BIN collation and UTF8 charset. You can check that by firing below command,
SELECT SCHEMA_NAME 'DATABASE', DEFAULT_CHARACTER_SET_NAME 'CHARSET', DEFAULT_COLLATION_NAME 'COLLATION' FROM INFORMATION_SCHEMA.SCHEMATA;
This will show the list of all the database listed with their charset and collation. Just like in the image below,
Here you can check the difference between your previous and current database. Your database charset and collation will be different than the newly created database.
Step #5. Import UTF-8 Encoded Data into Your Newly Created Database
This is the final step in the equation. You have backed up your database, converted it to UTF8, created the new database, now the final step is to import that UTF8 encoded data into your new database. Go ahead and import the entire UTF8 database into your new database.
Do not forget to replace the BOLD words with your database specifics.
-- Export utf8 encoded data to your new database.
MYSQL --DEFAULT-CHARACTER-SET=UTF8 --USER=ROOT --PASSWORD='MY-DATABASE-PASSWORD' MY-DATABASE-NAME_UTF8 < EXP-MY-DATABASE-NAME-UTF8.SQL;
Woohoo! You have just migrated your entire Latin1 encoded database to UTF8 encoded database without corrupting or damaging any data. Now head back to the Final Step section to replace old database name with the new one in the
- Port Existing Website to WordPress – The Best Way
- Checklist to Follow After Moving Your Website From HTTP to HTTPS
The purpose of this post is to get rid of those weird and funny characters that show on your WordPress blog. I’m pretty sure that by migrating your existing Latin1 database to UTF8 will solve your problem and disappear those funny characters from your blog post.
In the worst case scenario, if it does not solve the problem then it would mean that somehow those characters have been stored in your database. So you can check github gist to fire those SQL commands on your database and see if it works or not.
If this post solves your problem then click on those social sharing buttons and spread the word.