Export and import a MySQL database with the right character set

What is a character set?

A character set is the code a database uses to store letters and symbols. Think of it as a secret alphabet book. If the export uses one book and the import reads with another, letters turn into odd signs like "é". Common sets are utf8mb4 and latin1.

You need SSH access. On a dedicated server you have it. SSH is a safe way to type commands on a remote server.

Step 1: Find the current character set

1. Log in to MySQL.

   mysql -u root -p

   This opens MySQL and asks for the password.
2. Check the database.

   SELECT DEFAULT_CHARACTER_SET_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'mydb';

   This shows the set for the database named mydb.
3. Type exit to leave MySQL.

Step 2: Export

1. Run this command with your own names.

   mysqldump -u root -p --default-character-set=utf8mb4 mydb > mydb.sql

   This saves the database to a file called mydb.sql, using utf8mb4. Use the same set you found in step 1 if it is different, such as latin1.

Step 3: Import

1. Make an empty database with the right set.

   mysql -u root -p -e "CREATE DATABASE newdb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;"

   This makes newdb. A collate is the rule for sorting and comparing letters.
2. Import the file.

   mysql -u root -p --default-character-set=utf8mb4 newdb < mydb.sql

   This loads the saved data into newdb.

Check the result

Open your website or run a SELECT on a table with special letters. If you see odd signs, import again with the correct set. Make sure the set in your site's config file matches too.

Quick recap

• A character set decides how letters are stored.
• Use the same set when you export and when you import.
• Use --default-character-set with mysqldump and mysql.
• Back up the target database before importing.