How to convert ISO to UTF8 charset for database VBB forum

We've just successfully migrated a 6m post VB forum cross server and charset and as I spent a fair amount of time working this out I thought I'd share. There are apparently third party tools to do this but I've read mixed reviews about them.

I found a good step by step on the charset changeover here Converting vBulletin to UTF-8 though there are some errors in what he's done.

To manually make the transition on a linux box, you'll need shell access and here's what needs to be done...

1. Shut-off forums in admin panel.
2. Shell into your box and dump all the mysql data minus binary image data
   
   Quote:

   # mysqldump -u DBUSERNAME -p --opt --skip-set-charset --ignore-table=VBULLETINDBNAME.customavatar --ignore-table=VBULLETINDBNAME.customprofilepic --ignore-table=VBULLETINDBNAME.picture --ignore-table=VBULLETINDBNAME.socialgroupicon --ignore-table=VBULLETINDBNAME.socialgrouppicture VBULLETINDBNAME > VBULLETINDBNAME.sql

3. Dump the image data
   
   Quote:

   #mysqldump -u DBUSERNAME -p --opt --default-character-set=latin1 VBULLETINDBNAME customavatar customprofilepic picture sigpic socialgroupicon socialgrouppicture > VBULLETINDBNAME_images.sql

4. Convert all the characters in the data dump to UTF8 via iconv
   
   Quote:

   #iconv -c -f windows-1251 -t utf-8 VBULLETINDBNAME.sql > VBULLETINDBNAME_utf8.sql

5. Change the import script to make sure it imports as UTF8
   
   Quote:

   #sed –i 's/latin1/utf8/g' VBULLETINDBNAME_utf8.sql #sed –i 's/latin1/utf8/g' vogue_forums_untouchable_images.sql

6. Create your new database on the second server *Obviously make sure your new database is setup with UTF8 as the default collation*
7. Import your converted dump into the db you just created.
   
   Quote:

   #mysql -u NEWDBUSER -p –default-character-set=utf8 NEWVBDATABASENAME < VBULLETINDBNAME_utf8.sql #mysql -u NEWDBUSER -p –default-character-set=utf8 NEWVBDATABASENAME < VBULLETINDBNAME_images.sql

8. Now if you've changed domains you'll need to update the settings tables... you can get away with the following - there's two db values you need to change first one is pretty simple:
   
   Quote:

   mysql> UPDATE setting set value = 'http://hackingart.com' where varname = 'bburl'

9. The second update is stored as a serilaized value in the datastore table so you need to do a bit of work to get the right return value:
   First grab the data you need to change from the old DB
   
   Quote:

   mysql>select data from VBULLETINDBNAME.datastore where title = 'options'

   Grab the returning value and throw it in the following php (once you've changed the your new domain bits) script.. then grab the output:
   
   PHP Code:

   # MYSQL: select data from VBULLETINDBNAME.datastore where title = 'options'
$the_object = 'PUT RESULT OF ABOVE MYSQL QUERY';
$the_object = unserialize($the_object);
$the_object['homeurl'] = 'http://hackingart.com';
$the_object['bburl'] = 'http://hackingart.com';
$the_object['cookie_domain'] = 'hackingart.com';
echo serialize($the_object);

10. Update the datastore table with the output from the above script
    
    Quote:

    mylsql> UPDATE datastore set data = 'OUTPUT FROM ABOVE - ESCAPE THE SINGLE QUOTES ETC' where title = 'options';

11. Load up a clean vbulletin latest release in a new vhost on second server
12. change the /includes/config.php on the new install to reflect the new environment
13. run through the vbulletin upgrade process at newserver.xxx/install/upgrade.php (disregard warning about wrong version)
14. Update thread counters (admin -> maintenance -> update counters -> thread then post)
15. Go to admin panel -> address all issues. (if any hang, log out and in again and they will be shown again for you to run)
16. If you can't login: Upload the DO_NOT_UPLOAD folder and reset the cookie_domain using the newserver.xxx/DO_NOT_UPLOAD/tools.php script
17. If forum not displaying use the tools (and you've updated thread counts etc in admin/maintenance) reset the caches using the tools.php page
18. update the default character set (Admin CP -> Languages & Phrases -> Language Manager -> [Edit Settings] -> HTML Character Set) change ISO-8991 to UTF8
19. delete the DO_NOT_UPLOAD folder from your server
20. delete the install directory from server

Now you're be right to go with a UTF8 forum