How can I preserve Unicode characters in `mysqdump`?

August 28, 2006 11:55 AM

How can I make backups of a MySQL database (via mysqldump) and preserve Unicode characters (such as em dashes)?

I use mysqldump and rsync to make backups of my TextDrive account. One of the forums I run, however, often includes unencoded Unicode entities, such as em dashes and bullets.

Because these characters aren't straight ASCII, restoring from previous mysqldump backups has mangled them, producing gibberish but preserving the other, straight ASCII text in the database.

My current mysqldump call looks like this: /usr/local/bin/mysqldump -uuser -ppassword --quote-names --complete-insert --extended-insert --quick --lock-tables=false --skip-add-locks --all-databases | gzip > db.sql. What's necessary to preserve these Unicode characters?

Thanks.

posted by cmyers to Computers & Internet (8 answers total)

Best answer: <unhelpful remark>Use a real database?</unhelpful remark>

A little searching (actually, just stuffing "mysqldump unicode" into google) suggests that the argument --default-character-set=utf8 is what you want.
posted by pharm at 12:03 PM on August 28, 2006

What pharm said, but you might also look into using mysqlhotcopy instead. If you're working with large datasets, re-importing the mysqldump output can take a long time (2 hours minutes for one of my customer's databases!) whereas mysqldump is almost instant.
posted by SpecialK at 12:12 PM on August 28, 2006

err whereas mysqlhotcopy is almost instant.

(I've been writing code for far too long already today. :( )
posted by SpecialK at 12:12 PM on August 28, 2006

Use a binary dump.
posted by devilsbrigade at 12:26 PM on August 28, 2006

And yes, use a real database.
posted by devilsbrigade at 12:27 PM on August 28, 2006

Response by poster: Sorry if the answer to my question was self-obviating. I did search, both here and via Google, but I didn't think to go for as simple as a string as "mysqldump google" would be adequate.

Re mysqlhotcopy: The largest of the databases is only about 2.3 MB, and the statement dumps give me greater flexibility among hosts and setups.

Re MySQL: I know that it's a blunt instrument. My only other option is PostgreSQL, which I don't know but will look into.

Thanks, and sorry for not looking much past my own nose.
posted by cmyers at 12:50 PM on August 28, 2006

oh dammit... mysqldump doesn't preserve unicode?! is idontlikewords gonna have to choke a bitch??
posted by idontlikewords at 1:51 PM on August 28, 2006

Hm... After a bit of monkeying around to remove some invalid comments that mysqldump inserted into my backups (which it is good that this thread prompted me to do), I was just able to restore a mysqldump backup with unicode chars, japanese chars and etc. All my tables are MyISAM and the charset is utf8.

Here's the command I used to create the backup:

mysqldump -h hostname.com -u username -ppassword --skip-comments --skip-lock-tables database-name > backup.sql

And here's what I used to restore:

mysql --user=username --password=password database < backup.sql
posted by idontlikewords at 2:30 PM on August 28, 2006