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

All Versions

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

With the release of Confluence 2.10, MySQL 4.1.x is no longer supported and does not work by default. There is however, a workaround to enable MySQL 4.1.x.

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 'confluence'.
  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 confluence, 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/confluence/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/lib or Confluence Installation Directory/confluence/WEB-INF/lib. (the x.y 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. Enter confluenceuser in the User Name field, and the password you chose earlier in the Password field
    5. 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 (28)

 

  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. Dec 01, 2008

    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. Dec 01, 2008

      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?

      1. Jan 20

        Ming Giet Chong says:

        Hi, Please note that this is the MySQL known issue as described here. You are ...

        Hi,

        Please note that this is the MySQL known issue as described here. You are recommend to set "InnoDB" for the storage engine. See here for storage engine setting.

        Hope this helps.

        Regards,
        MG

  9. Dec 08, 2008

    Anonymous says:

    Step 1 Stage 5 should say "Call the new Schema 'confluence'." to match the defau...

    Step 1 Stage 5 should say "Call the new Schema 'confluence'." to match the default JDBC connection URL. You'll get a MySQLNonTransientConnectionException if the database name isn't properly specified in the JDBC connection URL.

    1. Dec 08, 2008

      Anonymous says:

      err... Stage 1 Step 5

      err... Stage 1 Step 5

  10. Jan 22

    Tom Moore says:

    This document has errors on the installation directory parameters.. I.e. their i...

    This document has errors on the installation directory parameters.. I.e. their is no WEB-INF in the root home folder, but rather here:

    Confluence Installation Directory/confluence/WEB-INF/lib

    That being said, putting the mysql jar in that directory doesn't work with 2.10.1 apparently.

    UPDATE:

    The correct location for the mysql jar file is actually Confluence Installation Directory/lib

    Please update the doc

    1. Jan 27

      Giles Gaskell [Atlassian Technical Writer] says:

      Hi Tom, Thanks for your comment. The documentation above has been corrected in ...

      Hi Tom,

      Thanks for your comment. The documentation above has been corrected in line with your comments.

      Please be aware that installing the MySQL Connector/J driver file (mysql.jar) to either Confluence Installation Directory/confluence/WEB-INF/lib or Confluence Installation Directory/lib should work on your Confluence Standalone installation. However, it depends on how this driver has been configured to work with Confluence.

      If configuring the MySQL Connector/J driver within your application server (Apache Tomcat) that runs Confluence, the driver file should be installed in Confluence Installation Directory/lib.

      If configuring the MySQL Connector/J driver within Confluence itself, the driver file can be installed in Confluence Installation Directory/confluence/WEB-INF/lib.

      Hope this information helps.

      Best regards,

      Giles Gaskell
      Technical Writer
      ggaskell@atlassian.com
      ATLASSIAN - http://www.atlassian.com

  11. Mar 11

    Alex Jurkiewicz says:

    Where is hostname / username / password stored by Confluence after you tell it w...

    Where is hostname / username / password stored by Confluence after you tell it where MySQL is?

    1. Mar 11

      Azwandi Mohd Aris says:

      The database connection details are stored in the "confluence.cfg.xml", resides ...

      The database connection details are stored in the "confluence.cfg.xml", resides in the Confluence Home Directory.

  12. Mar 19

    Anonymous says:

    Hello All, a basic question on adding a data source for team hosted confluence ...

    Hello All,

    a basic question on adding a data source for team hosted confluence implementation, how do I add a mysql data source, site administration doesnt show any option for it,

    Please help.

    Thanks.

    Nambi B

    1. Mar 29

      Azwandi Mohd Aris says:

      Hi Nambi, To add a MySQL JNDI datasource, you will need to modify it through th...

      Hi Nambi,

      To add a MySQL JNDI datasource, you will need to modify it through the Confluence Context Descriptor (i.e server.xml for Confluence standalone). I am afraid that this option is not available for Confluence Team Hosted users as it will require a modification to the filesystem.

      Cheers,
      Azwandi

      1. Apr 03

        Anonymous says:

        Hello Azwandi, Thanks for your response, since this SQL Plug-In is not supporte...

        Hello Azwandi,

        Thanks for your response, since this SQL Plug-In is not supported by Confluence, and how would I approach this to get the MYSQL datasource added to the team hosted solution? any thoughts?

        Thanks.

        Nambi.

        1. Jun 03

          Azwandi Mohd Aris says:

          Hi Nambi, You can refer to this documentation for plugins installation request....

          Hi Nambi,

          You can refer to this documentation for plugins installation request. Please note that we may not be able to fulfil your requests, as it is a shared hosting. There is also a documentation on that here.

          Cheers,
          Azwandi

          1. Apr 23

            Anonymous says:

            Hi Azwandi, Since our's is hosted by confluence, I have approached them to enab...

            Hi Azwandi,

            Since our's is hosted by confluence, I have approached them to enable the configuration changes to add the MySQL data sources and it's in progress.

            appreciate your replies.

            -Nambi

  13. Apr 17

    Anonymous says:

    After i copy the jdbc jar to both lib and WEB-INF/lib, it still gives an error: ...

    After i copy the jdbc jar to both lib and WEB-INF/lib, it still gives an error:
    The following error(s) occurred:
    The specified database driver was not found. If you are running Confluence Standalone, please ensure that you have copied your database's JDBC driver into the common/lib directory. Otherwise, consult your application server documention for instructions on how to add the drivers to your server classpath.
    You will need to restart your server after installing the drivers. (Setup will resume at the beginning of the database setup step).

    What caused this?

    I'm configuring it for mysql and i'm able to connect jira installed on the same server to connect mysql successfully.

    Thanks,

    1. May 06

      Arie Murdianto says:

      Hi, If you configure Confluence database configuration in JNDI, you need to put...

      Hi,

      If you configure Confluence database configuration in JNDI, you need to put the driver into Application servier's lib directory. Please give a try to put the driver to <Confluence install>/lib (I presume that you use Confluence standalone version 2.10.x). You may want to raise this problem to Atlassian's support system:

      Cheers,

  14. May 21

    Anonymous says:

    I'm a newb to SQL so please forgive my ignorance, but I need to query ...

    I'm a newb to SQL so please forgive my ignorance, but I need to query an SQL Database located on another server on the network from Conlfuence.  I want to display the results of that query on a Confluence page.  Can anyone help?

    1. May 21

      Somayya El Gemei says:

      Hi there, You will need to install the SQL plugin from the Plugin Repository.&n...

      Hi there,

      You will need to install the SQL plugin from the Plugin Repository.  It displays the result table or values from the SQL query formatted in a table on a Confluence page. Here is the link to the Plugin Documentation page.

Add Comment


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