timhj
06-22-2009, 10:00 PM
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 (http://megaz.arbuz.com/2009/04/09/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...
Shut-off forums in admin panel.
Shell into your box and dump all the mysql data minus binary image data
# 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
Dump the image data
#mysqldump -u DBUSERNAME -p --opt --default-character-set=latin1 VBULLETINDBNAME customavatar customprofilepic picture sigpic socialgroupicon socialgrouppicture > VBULLETINDBNAME_images.sql
Convert all the characters in the data dump to UTF8 via iconv
#iconv -c -f windows-1251 -t utf-8 VBULLETINDBNAME.sql > VBULLETINDBNAME_utf8.sql
Change the import script to make sure it imports as UTF8
#sed ?i 's/latin1/utf8/g' VBULLETINDBNAME_utf8.sql
#sed ?i 's/latin1/utf8/g' vogue_forums_untouchable_images.sql
Create your new database on the second server *Obviously make sure your new database is setup with UTF8 as the default collation*
Import your converted dump into the db you just created.
#mysql -u NEWDBUSER -p ?default-character-set=utf8 NEWVBDATABASENAME < VBULLETINDBNAME_utf8.sql
#mysql -u NEWDBUSER -p ?default-character-set=utf8 NEWVBDATABASENAME < VBULLETINDBNAME_images.sql
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:
mysql> UPDATE setting set value = 'http://YOURNEWDOMAIN' where varname = 'bburl'
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
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:
# 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://YOUR NEW DOMAIN';
$the_object['bburl'] = 'http://YOURNEWDOMAIN';
$the_object['cookie_domain'] = 'YOURNEWDOMAIN';
echo serialize($the_object);
Update the datastore table with the output from the above script
mylsql> UPDATE datastore set data = 'OUTPUT FROM ABOVE - ESCAPE THE SINGLE QUOTES ETC' where title = 'options';
Load up a clean vbulletin latest release in a new vhost on second server
change the /includes/config.php on the new install to reflect the new environment
run through the vbulletin upgrade process at newserver.xxx/install/upgrade.php (disregard warning about wrong version)
Update thread counters (admin -> maintenance -> update counters -> thread then post)
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)
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
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
update the default character set (Admin CP -> Languages & Phrases -> Language Manager -> [Edit Settings] -> HTML Character Set) change ISO-8991 to UTF8
delete the DO_NOT_UPLOAD folder from your server
delete the install directory from server
Now you're be right to go with a UTF8 forum! YAY. don't you feel good.
I found a good step by step on the charset changeover here (http://megaz.arbuz.com/2009/04/09/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...
Shut-off forums in admin panel.
Shell into your box and dump all the mysql data minus binary image data
# 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
Dump the image data
#mysqldump -u DBUSERNAME -p --opt --default-character-set=latin1 VBULLETINDBNAME customavatar customprofilepic picture sigpic socialgroupicon socialgrouppicture > VBULLETINDBNAME_images.sql
Convert all the characters in the data dump to UTF8 via iconv
#iconv -c -f windows-1251 -t utf-8 VBULLETINDBNAME.sql > VBULLETINDBNAME_utf8.sql
Change the import script to make sure it imports as UTF8
#sed ?i 's/latin1/utf8/g' VBULLETINDBNAME_utf8.sql
#sed ?i 's/latin1/utf8/g' vogue_forums_untouchable_images.sql
Create your new database on the second server *Obviously make sure your new database is setup with UTF8 as the default collation*
Import your converted dump into the db you just created.
#mysql -u NEWDBUSER -p ?default-character-set=utf8 NEWVBDATABASENAME < VBULLETINDBNAME_utf8.sql
#mysql -u NEWDBUSER -p ?default-character-set=utf8 NEWVBDATABASENAME < VBULLETINDBNAME_images.sql
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:
mysql> UPDATE setting set value = 'http://YOURNEWDOMAIN' where varname = 'bburl'
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
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:
# 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://YOUR NEW DOMAIN';
$the_object['bburl'] = 'http://YOURNEWDOMAIN';
$the_object['cookie_domain'] = 'YOURNEWDOMAIN';
echo serialize($the_object);
Update the datastore table with the output from the above script
mylsql> UPDATE datastore set data = 'OUTPUT FROM ABOVE - ESCAPE THE SINGLE QUOTES ETC' where title = 'options';
Load up a clean vbulletin latest release in a new vhost on second server
change the /includes/config.php on the new install to reflect the new environment
run through the vbulletin upgrade process at newserver.xxx/install/upgrade.php (disregard warning about wrong version)
Update thread counters (admin -> maintenance -> update counters -> thread then post)
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)
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
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
update the default character set (Admin CP -> Languages & Phrases -> Language Manager -> [Edit Settings] -> HTML Character Set) change ISO-8991 to UTF8
delete the DO_NOT_UPLOAD folder from your server
delete the install directory from server
Now you're be right to go with a UTF8 forum! YAY. don't you feel good.