Migrating off embedded H2 database to MySQL 5.7.24

[Byron]

As Per https://confluence.atlassian.com/doc/database-configuration-159764.html :

Your Confluence installation includes an embedded H2 database, to enable you to try Confluence without setting up an external database. The embedded H2 database is only supported while you are evaluating Confluence. You must migrate to a supported external database before using Confluence as a production system.

...

Just purchased a license to turn my eval into a purchased copy. I then learned that I had to migrate off the embedded H2 DB to something else. I chose MySQL.

Unfortunately the instructions do not explain how to configure it to work with MySQL 5.7 (listed as a supported DB).

See the following link:

https://confluence.atlassian.com/doc/database-setup-for-mysql-128747.html

Under "To Configure MySQL Server", look for item 1. Run the MySQL installation wizard.

Clicking "Reconfigure" doesn't provide an "Advanced configuration" option in MySQL 5.7.24.
You have two options: Standard Alone/Cluster MySQL Replication and Sandbox InnoDB Cluster Setup (for testing only).

Please provide me the steps to get my system up and running. We rely on this everyday and it's lame that you don't have instructions on how to setup this up after I shelled out money to go from eval to production. Thanks

[Diego]

Hello there!
You should be good to go by setting the MySQL application as Server Computer and as Windows service as well. It should prevent the Confluence server form coming up without a database. It is a good idea to start the database before starting Confluence.
A few thing that you need to take in consideration when running an external database:

- the database is reachable by the application
- there is enough disk space for the database to use
- there is enough memory for the database to use
- the ports used by the database are free

You can check further information about the MySQL installer here:

- MySQL Installer Workflow

Also, there is no need to activate the plugins shown with the installer. After finishing the setup, you can end the MySQL process and apply the necessary changes to your my.ini file, as shown here:

- Database setup for MySQL

After adding or changing the necessary variables, you should be able to create the database that is going to be used. The steps that need to be taken are also described in the Database Setup document. Do not forget to get the correct MySQL driver for Confluence:

- Database JDBC Drivers

After placing the driver into <installation-directory>/confluence/WEB-INF/lib, you should be able to start the Confluence setup with the external database.

One last thing that you may need to look into is the Migrating to another database document. There you will have more information on how to migrate from H2 to external database:

- Migrating to another database

Let us know if this sheds some light into the situation!

[Byron]

Hi Diego,

Thanks for your help. 

I'm at the Database setup for MySQL step.

When you say "After adding or changing the necessary variables", are you referring to step 2.2 on this page? 

https://confluence.atlassian.com/doc/database-setup-for-mysql-128747.html

Thanks

[Byron]

Hi Diego - Patiently awaiting your response. Please respond to my answer above, posted on Wednesday. Thanks.

[Diego]

Hi Byron!

Yes, I referenced the step where we need to change or insert variable values into the my.ini/my.cnf file.

Having the correct values there is essential for the database to work with Confluence!

Let us know if there are still questions.

[Byron]

There is still a big disconnect in  your docs around how to migate to MYSQL 5.2.74 (stated as support on your website) from H2 (Which is required after purchasing a license).

Referencing step 2.2 (that you asked me to follow, skipping 2.1) on the following webpage:

https://confluence.atlassian.com/doc/database-setup-for-mysql-128747.html?_ga=2.135287158.693284802.1547222393-1113222910.1547222393

Step 2.2 states:

Edit the my.cnf file (my.ini on Windows operating systems) in your MySQL server.

There are no my.ini files anywhere on my windows confluence server.

Please get with your MYSQL specialists so we can sort this out. We need to get our system working.

[Byron]

I successfully migrated by trial confluence system running H2 embedded DB to MySQL 5.7.24. 

I want to make the process less stressful and time consuming for others.

All the steps I'm referring to can be found here:

https://confluence.atlassian.com/doc/database-setup-for-mysql-128747.html?_ga=2.135287158.693284802.1547222393-1113222910.1547222393

Diego provided a link to this using Database setup for MySQL in his response above.

Here's my update:

*** Actions I took for step 2.2 (you skip step 2.1 if you're installing MySQL from scratch for the H2 to MySQL) :

The my.ini is located here:

C:\ProgramData\mysql\MySQL Server 5.7 (not c:\windows or c:\ as indicted in all the docs I found)

Replace "5.7" in the path with the version you're running or simply go to C:\ProgramData\mysql and you'll see the MySQL Server #.# directory.

open the my.ini file as an administrator.

Then search for [mysqld] 

then search for each of the values below [mysqld] :

character-set-server=utf8
collation-server=utf8_bin
default-storage-engine=INNODB
max_allowed_packet=256M
innodb_log_file_size=2GB
transaction-isolation=READ-COMMITTED
binlog_format=row

the my.ini file on my system did not contain the following values below [mysqld], so I simply added them right below the [mysqld] heading:

innodb_log_file_size=256GB
binlog_format=row
transaction-isolation=READ-COMMITTED

*** Actions I took for step 2.3

Everything worked fine - as outlined in the link at top of this reply.

*** Actions I took for step 3

Everything worked fine - as outlined in the link at top of this reply.

*** Actions I took for step 4

Everything worked fine - as outlined in the link at top of this reply.

*** Actions I took for step 5

Everything worked fine - as outlined in the link at top of this reply.

*** Actions I took for step 6

The link doesn't explain how the Confluence setup wizard is iniaited.

It's initiated when you connect to your confluence url http://localhost:8090 the first time after installing confluence.

*** Actions I took for step 7

Everything worked fine - as outlined in link at top of this reply.

If the test fails it will tell you why. If the my.ini file doesn't contain the correct values outlined in 2.2 it will indicate it at this step.

This completes the steps outlined in the Database setup for MySQL (https://confluence.atlassian.com/doc/database-setup-for-mysql-128747.html?_ga=2.135287158.693284802.1547222393-1113222910.1547222393) step.

The next link, Migrating to another database (https://confluence.atlassian.com/doc/migrating-to-another-database-148867.html?_ga=2.65417449.693284802.1547222393-1113222910.1547222393), provided me the final steps to migrate from the Confluence Trial version's embedded H2 DB to MySQL.

[Diego]

Wonderful Byron!

Good to hear that you were able to complete the database migration with our docs.

We really appreciate you sharing the whole experience here with the Community. The Community is based on people sharing, helping and explaining their experiences!

Let the Community know if there is anything else we could help you out with.

[Byron]

 Hello,

Sent a reply via email but it bounced. Here's my response:

You wrote:

For your setup, I would recommend skipping 2.1 and going to 2.2 where you modify the configuration file. You'll want to completely install MySQL first using the installer, then make sure Confluence is stopped and proceed with 2.2.

My response:

I will skip 2.1 and 2.2, but how does MySQL need to be setup? Your docs specifically state that the InnoDB option is required for Confluence, yet the option specifically states “TEST” in the setup screen. This is not a test environment.

Here are the MySQL 5.7.24 first couple of setup screens…

Let me know what I should choose

 If I choose StandAlone MySQL / Classic MySQL Replication I have the following options:

If I choose this option, I need to choose a config type. This would be Server I believe. Click Next.

I enter the root password on the next screen. Click Check and then next.

In the Windows Service window I'm given the option to configure mysql to run as a service. I leave the default and lick next.

On the Plugins and extensions screen, do I leave the default and click Next?

The screen that follows is simply to execute the setup.

If I choose the Sandbox InnoDB Cluster Setup (for testing only) route from the main setup screen, I get these options:

 

What Cluster size do I use? Plus it specifically states “TEST”, which we’re not running. This is production.

 

Thanks

[Byron]

 Hello,

Sent a reply via email but it bounced. Here's my response:

You wrote:

For your setup, I would recommend skipping 2.1 and going to 2.2 where you modify the configuration file. You'll want to completely install MySQL first using the installer, then make sure Confluence is stopped and proceed with 2.2.

My response:

I will skip 2.1 and 2.2, but how does MySQL need to be setup? Your docs specifically state that the InnoDB option is required for Confluence, yet the option specifically states “TEST” in the setup screen. This is not a test environment.

Here are the MySQL 5.7.24 first couple of setup screens…

Let me know what I should choose

 If I choose StandAlone MySQL / Classic MySQL Replication I have the following options:

If I choose this option, I need to choose a config type. This would be Server I believe. Click Next.

I enter the root password on the next screen. Click Check and then next.

In the Windows Service window I'm given the option to configure mysql to run as a service. I leave the default and lick next.

On the Plugins and extensions screen, do I leave the default and click Next?

The screen that follows is simply to execute the setup.

If I choose the Sandbox InnoDB Cluster Setup (for testing only) route from the main setup screen, I get these options:

 

What Cluster size do I use? Plus it specifically states “TEST”, which we’re not running. This is production.

 

Thanks

[Byron]

 Hello,

Sent a reply via email but it bounced. Here's my response:

You wrote:

For your setup, I would recommend skipping 2.1 and going to 2.2 where you modify the configuration file. You'll want to completely install MySQL first using the installer, then make sure Confluence is stopped and proceed with 2.2.

My response:

I will skip 2.1 and 2.2, but how does MySQL need to be setup? Your docs specifically state that the InnoDB option is required for Confluence, yet the option specifically states “TEST” in the setup screen. This is not a test environment.

Here are the MySQL 5.7.24 first couple of setup screens…

Let me know what I should choose

 If I choose StandAlone MySQL / Classic MySQL Replication I have the following options:

If I choose this option, I need to choose a config type. This would be Server I believe. Click Next.

I enter the root password on the next screen. Click Check and then next.

In the Windows Service window I'm given the option to configure mysql to run as a service. I leave the default and lick next.

On the Plugins and extensions screen, do I leave the default and click Next?

The screen that follows is simply to execute the setup.

If I choose the Sandbox InnoDB Cluster Setup (for testing only) route from the main setup screen, I get these options:

 

What Cluster size do I use? Plus it specifically states “TEST”, which we’re not running. This is production.

 

Thanks

[Byron]

 Hello,

Sent a reply via email but it bounced. Here's my response:

You wrote:

For your setup, I would recommend skipping 2.1 and going to 2.2 where you modify the configuration file. You'll want to completely install MySQL first using the installer, then make sure Confluence is stopped and proceed with 2.2.

My response:

I will skip 2.1 and 2.2, but how does MySQL need to be setup? Your docs specifically state that the InnoDB option is required for Confluence, yet the option specifically states “TEST” in the setup screen. This is not a test environment.

Here are the MySQL 5.7.24 first couple of setup screens…

Let me know what I should choose

 If I choose StandAlone MySQL / Classic MySQL Replication I have the following options:

If I choose this option, I need to choose a config type. This would be Server I believe. Click Next.

I enter the root password on the next screen. Click Check and then next.

In the Windows Server window I'm given the option to configure mysql to run as a service. I leave the default and lick next.

On the Plugins and extensions screen, do I leave the default and click Next?

The screen that follows is simply to execute the setup.

If I choose the Sandbox InnoDB Cluster Setup (for testing only) route from the main setup screen, I get these options:

 

What Cluster size do I use? Plus it specifically states “TEST”, which we’re not running. This is production.

 

Thanks

[Shannon S]

Hi there,

I assume you are installing this on Windows? If you have a screenshot of all of the screens you run into could you share that with me? If there's a problem with the documentation I want to submit it to be corrected.

For your setup, I would recommend skipping 2.1 and going to 2.2 where you modify the configuration file. You'll want to completely install MySQL first using the installer, then make sure Confluence is stopped and proceed with 2.2.

Let me know if you have any trouble.

Regards,

Shannon

[Byron]

Hello,

Sent a reply via email but it bounced. Here's my response:

You wrote:

For your setup, I would recommend skipping 2.1 and going to 2.2 where you modify the configuration file. You'll want to completely install MySQL first using the installer, then make sure Confluence is stopped and proceed with 2.2.

My response:

I will skip 2.1 and 2.2, but how does MySQL need to be setup? Your docs specifically state that the InnoDB option is required for Confluence, yet the option specifically states “TEST” in the setup screen. This is not a test environment.

Here are the MySQL 5.7.24 first couple of setup screens…

Let me know what I should choose

 If I choose StandAlone MySQL / Classic MySQL Replication I have the following options:

If I choose this option, I need to choose a config type. This would be Server I believe. Click Next.

I enter the root password on the next screen. Click Check and then next.

In the Windows Service window I'm given the option to configure mysql to run as a service. I leave the default and click next.

On the Plugins and extensions screen, do I leave the default and click Next?

The screen that follows is simply to execute the setup.

If I choose the Sandbox InnoDB Cluster Setup (for testing only) route from the main setup screen, I get these options:

What Cluster size do I use? Plus it specifically states “TEST”, which we’re not running. This is production.

Thanks for your help. Please provide me some updated docs/steps asap.

[Shannon S]

Hello,

You actually don't want to skip 2.2, just 2.1. Step 2.2 comes after the database server has already been set up.

I've asked my colleague Diego to reply on this thread as well, as he has a few ideas on how you can get started if you're not so familiar with MySQL setup.

Please review his instructions, and let us know if you have any trouble!

Regards,

Shannon

[Byron]

Thanks Shannon. I've asked for Clarification from Diego on one setup that I believe relates to 2.2 on https://confluence.atlassian.com/doc/database-setup-for-mysql-128747.html.

You can see my question above in my response to him.

Thanks

[Byron]

Hi Shannon,

Haven't got a response from my question of Diego on Wednesday. Can you guys please reply to my inquiry so we can wrap this up?

Thanks.