MKRD.info - Thorough articles on a variety of topics, and no ads!

Procedure Outline

1) Backup database in several ways, with several options.

Check the rest of my website for good database backup instructions. Additionally,

Create a proper database dump 

2) Reduce size of database

The database is usually used for such temporary content as cache, statistics, temporary files, etc. If you are using a Content Management System, check its documentation on how to purge the following from the database: administration and / or event logging cache, editing or log cache, search index, log, or history, collected statistics, any backup/archive module contents. Clear main website cache (cached copies of pages for visitors). Clear out any templates or such which you are absolutely sure you are not using.

Your website may need to be brought down and placed into a Maintenance state for the remainder of this procedure.

3) Export with latin1 being the forced "source" encoding of the dump utility, and "no set encoding" being the resulting forced encoding. This will automatically convert the old database into a UTF-8 formatted dump file.

Read my page Create a proper database dump.

Create a copy of this MySQL db dump file. We will never be working with originals.

4) View in editor

Check out my page Properly viewing UTF-8. Confirm that you can view all of your non-ASCII or non-Latin text as proper characters. Confirm that you are not seeing strange characters such as Â or �.

5) Replace encoding specification

How to convert the database encoding

6) Remove UTF replacement character �, and / or other strange codes from the database manually if necessary.

7) Remove references to old database, remove statement to create database, remove statement to drop database if necessary.

Depending on mysqldump options, your dump file may have statements to DROP (delete) or CREATE a database:

DROP DATABASE 'dbname';

CREATE DATABASE 'dbname';

This will be unnecessary, and may cause too many problems. Just leave the following statement:

USE DATABASE 'dbname';

8) Create database with proper initial specifications. Then, go into Operations tab and set the proper collation there AGAIN (to utf8_general_ci or whatever the rest of your database is). I had it incorrectly set to latin1. Click the GO button. Note: this setting will only affect ALL FUTURE operations with the database, and will not change any of the existing data of the database.

Before uploading a database dump to be imported, check the following:

View the copy in an editor which can be set to UTF-8 mode (such as OpenOffice or Microsoft Word). DO NOT SAVE.

Check that all database names are of the destination database.

Check for improper characters.

Check that the table encoding has been saved.

Check that only one database engine is specified (either InnoDB or MyISAM, but not both).

9) Import the database with the correct specifications. Do not forget that you may need to specify the hostname and the password for the database.

Importing the dump file into a MySQL database

10) Set up a proper backup and / or dump

It is a good idea to setup a Cron task to automatically create database backups. You should create at least two different encoding versions of your database backup, if your website uses non-ASCII or non-Latin characters. I have documented how to set up a Cron scheduled task elsewhere on my website.

I have prepared a Website Management Commands Master Reference which can aid you in this process.

˅˅˅ Additional valuable information is available at one of the links below: ˅˅˅