Looking for some expert help on an internal database migration to MySQL database.

[ddimick]

Apparently I setup the trial database when doing my confluence server installation, even though I have a confluence DB on the server that I thought I had pointed the installation to.  I need some help in figuring out how to do the database migration from the internal h2db to the MySQL.  I know that there are a few general guides on Confluence and how to do this, but I am very new to this and would like some expert help.

[Nic Brough -Adaptavist- · Accepted solution]

At a very high level:

• Export your data to XML using the Confluence backup option (on the server, it's in <confluence-data/exports>)
• Stop Confluence
• Repoint Confluence to an empty MySQL database
• Restart it, and visit it in a browser
• Run through the setup, until it asks if you have some XML to import
• Feed it your XML backup file and wait a while

(Note - you might not get asked about import - that's ok, just follow it until you have an empty Confluence, then go to the backup tools and use "import from xml")

[ddimick]

Okay, that sounds easy enough except the Repoint Confluence to an empty MySQL database.  Where is the file that I need to modify to point it to the other server?

[Nic Brough -Adaptavist-]

I was doing a high level thing because there's a lot more detail and I do not know where people might get stuck at. It is quicker to type a few lines and fill in the blanks when you ask, than replicate all the Atlassian docs with detailed annotations on where.

For the database config, head to <confluence-data> again, and in that directory, you should find a plain text file called confluence.cfg.xml - edit it and find a whole load of crud you don't care about (yet), and look for each property that starts with "hibernate.connection" - url, driver, username and password will be there

[ddimick]

Okay thanks, so there is not a "url" line in there or anything that looks close, I am assuming at this point that is because I used the internal DB purely by accident.  I think I have another server that this is installed on as my test environment.  I will look at that config and see if it is in there and add the lines that are different from there.

All in all I think this will get me what I need.

Thanks.

[ddimick]

Well it looks like after trying this that I hosed it up (not surprising for me)  I had backed up the config file as well before I modified it, so I thought that just stopping the server and then writing the backup overtop of the config file would have fixed what I broke, but alas it did not.

I am considering rebuilding it all as I would like to put our wildcard cert on there at that time anyways, but am concerned about the license. 

Maybe someone send me a copy of the .xml file with the sensitive data stripped out just to make sure that I have all the lines in mine?

[Nic Brough -Adaptavist-]

Here's a pretty much off-the-shelf install:

Where you see "snippy", that's stuff that's potentially sensitive and local to this install

root@zen:/opt/data-conf# cat confluence.cfg.xml
<?xml version="1.0" encoding="UTF-8"?>

<confluence-configuration>
<setupStep>complete</setupStep>
<setupType>custom</setupType>
<buildNumber>8301</buildNumber>
<properties>
<property name="admin.ui.allow.daily.backup.custom.location">false</property>
<property name="admin.ui.allow.manual.backup.download">false</property>
<property name="admin.ui.allow.site.support.email">false</property>
<property name="atlassian.license.message">snippy</property>
<property name="attachments.dir">${confluenceHome}/attachments</property>
<property name="confluence.setup.server.id">snippy</property>
<property name="confluence.webapp.context.path">/docs</property>
<property name="hibernate.c3p0.acquire_increment">1</property>
<property name="hibernate.c3p0.idle_test_period">100</property>
<property name="hibernate.c3p0.max_size">60</property>
<property name="hibernate.c3p0.max_statements">0</property>
<property name="hibernate.c3p0.min_size">20</property>
<property name="hibernate.c3p0.preferredTestQuery">select 1</property>
<property name="hibernate.c3p0.timeout">30</property>
<property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
<property name="hibernate.connection.isolation">2</property>
<property name="hibernate.connection.password">snippy</property>
<property name="hibernate.connection.url">jdbc:postgresql://192.168.1.snippy</property>
<property name="hibernate.connection.username">snippy</property>
<property name="hibernate.database.lower_non_ascii_supported">false</property>
<property name="hibernate.dialect">com.atlassian.confluence.impl.hibernate.dialect.PostgreSQLDialect</property>
<property name="hibernate.setup">true</property>
<property name="jwt.private.key">snippy</property>
<property name="jwt.public.key">snippy</property>
<property name="lucene.index.dir">${localHome}/index</property>
<property name="synchrony.btf">true</property>
<property name="synchrony.encryption.disabled">true</property>
<property name="synchrony.proxy.enabled">true</property>
<property name="webwork.multipart.saveDir">${localHome}/temp</property>
</properties>
</confluence-configuration>

[ddimick]

Thank you for your assistance.  I have it mostly back to where I did, with the database being pointed correctly.  However, I had to nuke things and start over and now I would like to actually set up our wildcard certificate for the ssl.  I have no idea how to do this and was only luck to get the self signed thing working previously.  I have been trying to figure out cert stuff but no real luck so far.  I have a .crt, .pem, .and a pfx.  How would I use keytool to get this imported into the keystore?