Mysql – How to properly backup mediawiki database (thesql) without messing up the data

backupcharsetmediawikiMySQL

I want to backup a mediawiki database stored in a MySQL server 5.1.36 using mysqldump.

Most of the wiki articles are written in spanish and a don't want to mess up with it by creating the dump with the wrong character set.

mysql> status
--------------
...
Current database:       wikidb
Current user:           root@localhost
...
Server version:         5.1.36-community-log MySQL Community Server (GPL)
....
Server characterset:    latin1
Db     characterset:    utf8
Client characterset:    latin1
Conn.  characterset:    latin1
...

Using the following command:

mysql> show create table text;

I see that the table create statement set the charset to binary:

CREATE TABLE `text` (
  `old_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `old_text` mediumblob NOT NULL,
  `old_flags` tinyblob NOT NULL,
  PRIMARY KEY (`old_id`)
) ENGINE=InnoDB AUTO_INCREMENT=317 DEFAULT CHARSET=binary MAX_ROWS=10000000 AVG_ROW_LENGTH=10240

How should I use mysqldump to properly generate a backup for that database?

Best Answer

mysqldump and show create table are usually pretty reliable workhorses and shouldn't change encodings on their own. If they use a "binary" character set, chances are that is actually the character set given to the table (not necessarily the columns, though). Can you re-check the current table character set in a database browser?

I would try a plain, simple mysqldump into a file, restoring the dump into a mirror database, and take a close look at the special characters. Different from backups taken using phpMyAdmin, those from the command line tend to work very well in my experience.

Related Topic