MySQL encoding and converting databases to UTF8
I found a great tip over ath the fingertip blog: Ruby and MySQL encoding flakiness
It details some shady behavior related to UTF8 / MySQL encoding and Rails. The tip worked fine but I found that my production Rails database was still in latin1 even though everything else in my app is setup for UTF8.
So after some searching I found a relatively quick way to convert your MySQL database into UTF8 encoding (Though there might be a easier method.) Basically using the following tasks:
1.) Dump the DB:
mysqldump –user=username –password=password –default-character-set=latin1 –skip-set-charset dbname > dump.sql
2.) Replace all latin1 instances with utf8:
sed -r ’s/latin1/utf8/g’ dump.sql > dump_utf.sql
3.) Delete the old DB, create a new one in UTF8:
mysql –user=username –password=password –execute=”DROP DATABASE dbname; CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;”
4.) Load the dump into the new DB:
mysql –user=username –password=password –default-character-set=utf8 dbname < dump_utf.sql
Check out the MySQL database to UTF8 how-to here
.
Posted on February 21, 2007
Filed Under Business, Daily Thoughts, Design, Hosting, PHP/MySQL, Productivity, Ruby on Rails, Startup, SugarStats, Tech, Web
Related Posts:
-
MySQL Query Analyzer Rails Plugin Released
Securing MySQL on OS X
Rails Multi-DB Connection Pool Management Goodness
Show my Ruby Gems
Rake MySQL Database Backup Task
One-click Ruby on Rails OS X development solution
Comments
3 Responses to “MySQL encoding and converting databases to UTF8”
Leave a Reply
-
Projects
-
Search it all
-
About
I'm Marston and here you'll find my rantings, rhetoric and other useful info. I dig tech, photography, quantum physics and organic food. I'm the founder of SugarStats.com and thats my days developing.
-
Flickr
www.flickr.com
This is a Flickr badge showing public photos from Marston A.. Make your own badge here.
-
Twitter
- Back from Elephant Bar. 30min wait but worth it, great service too. Getting ready for WebPlay 2008 tomorrow in Sunnyvale. 6 hrs ago
- @askmanny Glad you're feeling better. How did the SD Diabetes event go? BTW 15 hrs ago
- Great meeting this morning with Kosmix in Mt. View. Back to work now, for some reason it smells like sulfur outside though. 16 hrs ago
- 4am. Vivid and amazing dreams. Have a morning meeting in Mt. View later. Question now: start working now or go back to bed... We'll see. 23 hrs ago
- Back from SF, nice scaling Rails presentation by the guys at @newzwag. 1 day ago
- Enjoying the free WiFi at Panera and having an early dinner before heading up to Market st. Gotta hit up the Apple Store. 1 day ago
- Cruising up to SF/SOMA this afternoon, Ruby meetup at Newzwag. Think there is also a Digg meetup at Might too, don't know if I'll make that. 1 day ago
- @PelleB Nice, congrats. Too bad you couldn't launch a week ago and get a free 7 day trail from New Relic. The stats are awesome. 1 day ago
- More updates...
Another Day, Another Post…
[...] Normally I don’t write about other peoples blogs, but this one really caught my eye: [...]...
[...] If you need to convert your database tables this article at marstononline.com might be helpful. [...]
[...] original link [...]