MySQL Backup with UTF-8 Data
I’ve never given character sets and encoding much time. I half way understand how it works, which for me is unusual. I normally have to understand things at a level that lets me talk to the subject fairly well. And sadly, this one I don’t. I don’t have the time or energy to really dive into it right now, but it is on my list. I will talk briefly about what I have found in the last 24 hours about this.
MySQL allows you set to set the character-set and collation to utf-8. To my understanding this will allow you to store UTF-8 data in your database. This makes it really nice when you have users copying and pasting data into your CMS. It will store the special double-quotes, trademark symbols, and pretty much anything else you can throw at it. No big deal to backup up, right? Well I did an export within PHPMyAdmin and using the mysqldump utility. Neither one worked, and I was pissed. And actually so was the client because I didn’t catch it before we launched the site. So last night I was frantically searching Google as fast as I could (notice I wasn’t searching the web, I was searching Google – that should make an interesting blog post for another time). And I eventually found my answer. It had to do with the mediums inbetween the export and import to support utf-8 characters – Great.
So without having to use a 3rd party app, or write some code, I was able to figure out how to do it with myqldump. Instead of using the greater than symbol to redirect the output into a file, mysqldump has a switch -r to use to redirect output into a file. They say to use it on Windows machines, so I kind of felt dirty for using in the linux command line :/ But I did, and guess what, an import using virtualmin on the production server and everything worked, the data came across cleanly.
From the brief reading that I did, using the power of output redirection at the unix command line can break UTF-8 encoding. I guess it kind of makes sense if the server or terminal you are using doesn’t support it. But it isn’t something I would really think about.
Just for those wondering what I ended up doing. I normally do a mysqldump like
mysqldump -u user_name -p database_name > mydump.sql
This time around, i just had to change it to
mysqldump -u user_name -p database_name -r mydump.sql
So simple I could throw up.
