powered by Jive Software

Migrating DB from old Spark to new Spark?

We are running on an older version of spark, on a very old system. I have new hardware, and installed the newest version of Openfire and have Spark up and running, what I need to know how to do now is to migrate user information from the old system to the new. Stuff like friends lists, conference rooms, etc.

Thank you!

Maurice

what type of database? are the openfire servers named the same?

mysql on both,

DB names:

Old env: openfire

New env: openfiredb

Thank you.

Maurice

I highly recommend you name everything the same including the db name. then export the old mysql db and iport it to the new db with openfire stopped. then compy the openfire.xml from the old server to the new server. then start openfire.

Thank you, I will change the database name on the new system and see how things migrate.

Maurice

Ok, so I think I have the DB imported, but I noticed most of the table names are different. Is there a way to check and ensure the import put everything where it was supposed to go?

Here is what I am coming from and going too:

OLD Env:
Openfire 3.5.2
Spark 2.5.8
MySQL DB
OS: CentOS release 5 (Final)

NEW Env:
Openfire 3.6.4
Spark 2.5.8
MySQL DB
OS: CentOS release 5.4 (Final)

Here is what I am seeing for tables in the mysql database (openfire);

OLD Env:

mysql> show tables;
±---------------------+
| Tables_in_openfire |
±---------------------+
| fpAgent |
| fpAgentProp |
| fpAgentSession |
| fpChatSetting |
| fpDispatcher |
| fpDispatcherProp |
| fpOfflineSetting |
| fpQueue |
| fpQueueAgent |
| fpQueueGroup |
| fpQueueProp |
| fpRouteRule |
| fpSearchIndex |
| fpSession |
| fpSessionMetadata |
| fpSessionProp |
| fpSetting |
| fpWorkgroup |
| fpWorkgroupProp |
| fpWorkgroupRoster |
| gatewayAvatars |
| gatewayPseudoRoster |
| gatewayRegistration |
| gatewayRestrictions |
| gatewayVCards |
| jiveExtComponentConf |
| jiveGroup |
| jiveGroupProp |
| jiveGroupUser |
| jiveID |
| jiveOffline |
| jivePresence |
| jivePrivacyList |
| jivePrivate |
| jiveProperty |
| jiveRemoteServerConf |
| jiveRoster |
| jiveRosterGroups |
| jiveSASLAuthorized |
| jiveSecurityAuditLog |
| jiveUser |
| jiveUserFlag |
| jiveUserProp |
| jiveVCard |
| jiveVersion |
| mucAffiliation |
| mucConversationLog |
| mucMember |
| mucRoom |
| mucRoomProp |
| ofBookmark |
| ofBookmarkPerm |
| ofBookmarkProp |
| ofConParticipant |
| ofConversation |
| ofMessageArchive |
| ofRRDs |
| pubsubAffiliation |
| pubsubDefaultConf |
| pubsubItem |
| pubsubNode |
| pubsubNodeGroups |
| pubsubNodeJIDs |
| pubsubSubscription |
±---------------------+
64 rows in set (0.00 sec)

mysql>

NEW Env:

mysql> show tables;
±---------------------+
| Tables_in_openfire |
±---------------------+
| fpAgent |
| fpAgentProp |
| fpAgentSession |
| fpChatSetting |
| fpDispatcher |
| fpDispatcherProp |
| fpOfflineSetting |
| fpQueue |
| fpQueueAgent |
| fpQueueGroup |
| fpQueueProp |
| fpRouteRule |
| fpSearchIndex |
| fpSession |
| fpSessionMetadata |
| fpSessionProp |
| fpSetting |
| fpWorkgroup |
| fpWorkgroupProp |
| fpWorkgroupRoster |
| gatewayAvatars |
| gatewayPseudoRoster |
| gatewayRegistration |
| gatewayRestrictions |
| gatewayVCards |
| ofBookmark |
| ofBookmarkPerm |
| ofBookmarkProp |
| ofConParticipant |
| ofConversation |
| ofExtComponentConf |
| ofGroup |
| ofGroupProp |
| ofGroupUser |
| ofID |
| ofMessageArchive |
| ofMucAffiliation |
| ofMucConversationLog |
| ofMucMember |
| ofMucRoom |
| ofMucRoomProp |
| ofMucService |
| ofMucServiceProp |
| ofOffline |
| ofPresence |
| ofPrivacyList |
| ofPrivate |
| ofProperty |
| ofPubsubAffiliation |
| ofPubsubDefaultConf |
| ofPubsubItem |
| ofPubsubNode |
| ofPubsubNodeGroups |
| ofPubsubNodeJIDs |
| ofPubsubSubscription |
| ofRRDs |
| ofRemoteServerConf |
| ofRoster |
| ofRosterGroups |
| ofSASLAuthorized |
| ofSecurityAuditLog |
| ofUser |
| ofUserFlag |
| ofUserProp |
| ofVCard |
| ofVersion |
±---------------------+
66 rows in set (0.00 sec)

mysql>

============================

I just want to make sure before I cut over to this new system, that everything is where its supposed to be in the DB.

Thank you!

Maurice

I am hopeing this is migrated accurately, here are the steps I took. Please let me know if you see something I missed.

On the OLD system; (with Openfire running)

mysqldump -h liger --opt openfire -p > db_dump.sql

SCP’d db_dump.sql over to the NEW system.

scp db_dump.sql username@servername:/tmp/db_dump.sql

On the NEW system, I did the following;

/etc/init.d/openfire stop

mysqladmin drop openfire

mysqladmin create openfire

at this point I have a blank database and I import the OLD systems exported database into the NEW systems database;

mysql -p -h viryeschap01 openfire < /tmp/db_dump.sql

mysql -e grant all privileges on openfire.* to ofuser@hostname identified by ‘password’

mysql -e “flush privileges”

Start Openfire and check for errors.

tail -f /opt/openfire/logs/error.log

/etc/init.d/openfire start

Now I launch the webbrowser and start the Openfire configuration steps;

 [http://10.1.1.1:9090/index.jsp](http://10.1.1.1:9090/index.jsp)

Once complete, I launch Spark and point it to the NEW server. I log in and see my buddy lists, and conference rooms, etc. They complain because they are looking for the production VIP and not the test one I have in place. Otherwise it looks good from the Spark point of vew. I can chat with others who are testing, tho we are not able to see eachother as online unless we send a new invite, it appears to be working as expected.

Here are a couple of questions.

  • Do the above steps look correct?

  • Should I be concerned with the mysql DB table differences between the old and new versions?

  • Same as the above, but for the Schema?

My fear is the jump from 3.5.2 too 3.6.4 there were some table/schema changes to the mysql DB and those may have not been converted with the process I used above.

Thanks everyone for your feedback, its been really beneficial to getting this rolled out properly.

Maurice