Confluence should use an external database for production usage, as the embedded database does not offer full transactional integrity in the event of sudden power loss. Confluence Standalone with MySQL is well-tested and easily configured for production.

This document outlines setting up Confluence with the open-source MySQL database on Microsoft Windows, migrating across any existing content. Use this guide in conjunction with the more general Database Setup Guide for Any Database. These instructions add some reference notes specific to MySQL.

Migration Instructions

Install the following software:

  1. Install Confluence if you have not done so already.
    (info) If MySQL Administrator is not included in your installation, download the MYSQL GUI Tools package.

  2. Install the MySQL 'Community' Database Server, version 5.0, from the mySQL download page. This install includes mySQL Administrator.
    (info) MySQL 5.0 is supported since Confluence 2.5 and above.

  3. Download the latest mySQL Connector/J driver, version 5.1 or newer.

The instructions refer to two particular directories:

  • The Confluence Installation Directory is the directory where you unzipped the Confluence install download.
  • The Confluence Home Directory is the directory where Confluence stores its data, which you set by editing the confluence-init.properties file in Confluence Installation Directory/confluence/WEB-INF/classes

Stage 1 - mySQL Database Setup

データベースおよびユーザー権限を作成するには:

  1. Start the MySQL Adminstrator and use it connect to the mySQL database server. If you are running mySQL for the first time and are unsure of the login settings, just click OK to connect using the defaults.
  2. Go to User Administration and click the 'New User' button at the bottom of the window.
  3. Call the user 'confluenceuser' and give them a password, click 'Save Changes'.
  4. Right click on the name 'confluenceuser' and choose 'Add Host From Which The User Can Connect' from the pop-up menu. Enter the host 'localhost'.
  5. Go to Catalogs, right click in the schema list at the bottom of the left hand column of the window, and select 'Create New Schema' from the pop-up menu. Call the new Schema 'confluencedb'.
  6. Go back to User Adminstration, click on 'confluenceuser' text, not the icon. Then select the 'localhost' which appears under 'confluenceuser' . Now go to the Schema Privileges tab located on the RHS of the window. Here select confluencedb, and make sure the user has all priviledges assigned to them by clicking the '<<' button to move the priviledges from the 'Available' to the 'Assigned' list. Click "Apply Changes".

Stage 2 - For Users With Existing Data Only

This stage is only required if you have existing Confluence content you wish to transfer:

  1. Manually create an XML backup of Confluence under Administration -> Backup & Restore. If you have less than 100MB of attachments, check 'Backup attachments' when creating the backup. If you have over 100MB of attachments, you should not check the 'Backup attachments' and instead you should manually copy the /attachments directory in your Confluence home to another location. This attachments directory can then be copied into the new home directory as describe later
  2. Download the backup file to a backups folder
  3. Confluence を停止します。
  4. Open your Confluence home directory and copy the plugin-cache (plugins in ver 2.2.* and earlier) subdirectory to the backups folder

Stage 3 - Database Connection Setup

To switch to using the external database:

  1. Confluence を停止します。
  2. Edit Confluence Installation Directory/WEB-INF/classes/confluence-init.properties and change the confluence.home property to point to a new directory. e.g. if you had 
    confluence.home=c:/confluencedata
    You could change it to: 
    confluence.home=c:/confluencedata_mysql
    This is your new Confluence Home Directory. (The name doesn't have to end in _mysql – that's just an example)
  3. Copy the file mysql-connector-java-3.1.xx-bin.jar from the directory where you unpacked Connector/J to Confluence Installation Directory/WEB-INF/lib. (the xx depends on exactly which version you download – MySQL updates the version number from time to time)
  4. Start Confluence and set up the new configuration
    1. You'll be asked for your licence key again. Enter it, and click the 'Custom Installation' button.
    2. Under the 'External Database' heading, choose MySQL from the dropdown list and click the 'External Database' button.
    3. On the next page, click the 'Direct JDBC' button.
    4. Change the database URL by changing the database name 'confluence' to 'confluencedb', so the URL looks like this: jdbc:mysql://localhost/confluencedb?autoReconnect=true
    5. Enter confluenceuser in the User Name field, and the password you chose earlier in the Password field
    6. Click the Next button. If you get the error message Could not successfully test your database: : Server connection failure during transaction. Due to underlying exception: 'java.sql.SQLException: Access denied for user 'confluenceuser'@'localhost' (using password: YES)' verify that you have properly given the confluenceuser user all the right permissions when connecting from localhost

Stage 4 - For Users With Existing Data Only

To re-import your backup and plugin-cache:

  1. At the 'Load Content' page, choose 'Restore From Backup', browse for the backup you created and restore it. Otherwise choose either the example or empty site as you wish.
  2. Confluence を停止します。
  3. Open your backups folder and copy the plugin-cache (plugins in ver 2.2.* and earlier) subdirectory to the new Confluence home directory

Done!

Related Documents

Configuring Database Character Encoding
Known Issues for MySQL

Except where otherwise noted, content in this space is licensed under a Creative Commons Attribution 2.5 Australia License.