This server will be upgraded at 3pm Sydney time on December 3rd (December 2nd, 8pm PST) and will be down for up to 30 minutes.
This documentation relates to the latest version of Confluence.
If you are using an earlier version, please go to the documentation home page and select the relevant version.

Database Setup For MySQL

Added by Tom Davies, last edited by Tim Wong on Nov 20, 2008  (view change) show comment
All Versions
Click for all versions
Confluence 2.10 Documentation

Index

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.
    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.
    MySQL 5.0 is supported since Confluence 2.5 and above.

    Because the default Storage Engine for MySQL doesn't enforce referential integrity, address this by passing the '--default-storage-engine=InnoDB' option when starting mysql. Check the MySQL documentation for more information. You can do the change in the my.cnf configuration file.

    A common problem with MySQL is the max packet size restriction, which can result in an "Object Error" message when installing certain plugins. Set the max packet size setting when starting the database to prevent this.

    The remaining Known Issues for MySQL are worth a review as well.



  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

To create the database and user privileges:

  1. Start the MySQL Administrator 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 Administration, 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 privileges from the 'Available' to the 'Assigned' list. Click "Apply Changes".
    To support international languages in Confluence, you should verify the newly created database is using UTF-8 encoding and re-examine the JDBC URL settings (configured in Stage 3).

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. Stop Confluence

Stage 3 - Database Connection Setup

To switch to using the external database:

  1. Stop 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-5.x.y-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 license 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. Stop Confluence

Related Documents

Configuring Database Character Encoding
Known Issues for MySQL

Labels

mysql mysql Delete
database database Delete
mysql-setup mysql-setup Delete
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.

Comments (12)

 

  1. Nov 17, 2005

    Jenny Liu says:

    Does anyone know of a similar tutorial for Confluence running on Linux systems? ...

    Does anyone know of a similar tutorial for Confluence running on Linux systems? Thanks in advance!

    1. Nov 18, 2005

      Jens Schumacher says:

      Jenny, The procedure for linux will be very similar. The main difference is tha...

      Jenny,

      The procedure for linux will be very similar. The main difference is that you won't have the MySQL Administrator Tool.

      Instructions on how to install MySQL on Linux can be found here:
      http://dev.mysql.com/doc/refman/5.0/en/linux-rpm.html

      Step2 onwards is very similar to what you will have to do on linux. The only difference is that the path to your installation on linux looks different.

      Cheers,
      Jens

  2. Apr 30, 2006

    James Matheson says:

    A couple of points:  The confluence setup wizard expects the database sch...

    A couple of points: 

    • The confluence setup wizard expects the database schema to be called 'confluence' rather than 'confluencedb'.   This is not a big problem because the setup wizard value can be changed during setup.
    • The Linux version of the mysql-admin GUI application seems to have a bug (as of writing) where it can't change user privileges.  If you encounter this bug, you may need to use the following mysql commands to setup the privileges manually (substitute your password for <the password>): 
      > GRANT ALL PRIVILEGES ON *.* to 'confluenceuser'@'localhost.localdomain' identified by '<the password>';
> FLUSH PRIVILEGES;

  3. Sep 12, 2006

    Alex Holtz says:

    Assuming you have MySQL installed and running with a password for the root mysql...

    Assuming you have MySQL installed and running with a password for the root mysql user, you can perform the linux side of the MySQL setup entirely from the command-line with the following:

    1.  Login to the mysql client as the root mysql user: 
      $ mysql -u root -p;
    2. Create the database: 
      CREATE DATABASE <dbname>;
    3. Grant the privileges: (I am specifically avoiding the global *.* notation because this would grant privileges across all databases.) 
      GRANT ALL PRIVILEGES ON <dbname>.* TO 'confluenceuser'@'<hostname>' IDENTIFIED BY '<password>';
      • Replace <dbname> with the name of the database; you should probably use 'confluence' unless you have a good reason to use something else.
      • Replace <hostname> with the hostname of the server that will connect to the database (this will also be the hostname of the server running confluence)
      • Replace <password> with the desired password.
    4. Flush Changes: 
      FLUSH PRIVILEGES;

  4. Jul 09, 2007

    Pavel Chilman says:

    MySQL 'Community' Database Server version 4.1.x no longer includes mySQL Adminis...

    MySQL 'Community' Database Server version 4.1.x no longer includes mySQL Administrator. However, I was able to download it separately as part of MySQL GUI Tools Bundle for 5.0

  5. Aug 28, 2007

    Rex Kidwell says:

    Has anyone determined the MySQL (or Oracle) roles/privileges needed to install a...

    Has anyone determined the MySQL (or Oracle) roles/privileges needed to install and configure the Confluence database? 

    • GRANT ALL PRIVILEGES on *.* 
      Results in the account having full access to all databases on server.  The account is more or less the same as the MySQL root account.
    • GRANT ALL PRIVILEGES on confluence.* 
      Results in the account having full access to the database, confluence.

    A related question is, has anyone determined the MySQL (or Oracle) roles/privileges needed to allow the Confluence application to run on a MySQL (or Oracle) database?  The difference being, it may take full access to import a database but after the import is done you could remove import permissions. 

    Some corporate IT organizations don't allow this level (open ended) of access without pulling a few teeth.

  6. Nov 16, 2007

    Anonymous says:

    Can anyone tell me if there are any know issues with Confluence and a MySQL set ...

    Can anyone tell me if there are any know issues with Confluence and a MySQL set up with two-way replication.

    1. Nov 19, 2007

      Tony Cheah Tong Nyee says:

      Hi there, I believe you meant to say supporting Database fail-over? If this is ...

      Hi there,

      I believe you meant to say supporting Database fail-over? If this is the case, I am sorry to say that Confluence does not support such scenario. You may be interested to look at the following feature request regarding Confluence supporting Database fail-over:

      Feel free to cast your vote to increase its popularity and add yourself as a watcher for future updates. You may also add comments to the feature request to share your idea and truly reflect the importance of this feature to you.

      Cheers,
      Tony

  7. Jan 01, 2008

    Joe Kraska says:

    Gentlemen, This guide has an error. The instructions tell me to name the datab...

    Gentlemen,

    This guide has an error.

    The instructions tell me to name the database 'confluencedb', but the default URL (the one that comes up on the web based autoconfigurator for confluence 2.6.2) is just 'confluence' for the db.

    While I was able to get past this, one of these two things is wrong.

    Joe Kraska

    San Diego CA

    USA

    1. Jan 02, 2008

      Ming Giet Chong says:

      Hi Joe, By default, Confluence provides a sample URL during the Database Connec...

      Hi Joe,

      By default, Confluence provides a sample URL during the Database Connection Setup:

      jdbc:mysql://localhost/confluence?autoReconnect=true

      User can specify database names up to their liking such as confluence, confluencedb or any other names. The above database URL, confluence is simply a reference to the Confluence database created. Hence, users will need to change the URL accordingly.

      Regards,
      MG

  8. yesterday at 09:35:26

    Anonymous says:

    Hello, I don't understand why one has to set the default_storage_engi...

    Hello,

    I don't understand why one has to set the default_storage_engine for your entire MySQL server to InnoDB?  I use Hibernate a lot and the Confluence Hibernate configuration should be specifying the property hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect and when doing the schema creation Hibernate will do CREATE TABLE ... ( ... ) ENGINE=InnoDB.

    The problem is that in a centralized MySQL server setup in an organization other applications/users will have databases on the same MySQL server and then they have to be adversely affected if you set default_storage_engine=InnoDB.

    Is there a way to not have to set this server parameter?

    thank you

    1. yesterday at 10:48:21

      Anonymous says:

      Hello again, Thought of a simple option: is it possible to set the default_stor...

      Hello again,

      Thought of a simple option: is it possible to set the default_storage_engine=innodb before installation and configuration and then after its all done unset it?  And then of course remember to have to redo this during upgrades?

Add Comment