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:
- Install Confluence if you have not done so already.
If MySQL Administrator is not included in your installation, download the MYSQL GUI Tools package.
- 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. |
- 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:
- 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.
- Go to User Administration and click the 'New User' button at the bottom of the window.
- Call the user 'confluenceuser' and give them a password, click 'Save Changes'.
- 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'.
- 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'.
- 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:
- 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
- Download the backup file to a backups folder
- Stop Confluence
Stage 3 - Database Connection Setup
To switch to using the external database:
- Stop Confluence
- 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)
- 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)
- Start Confluence and set up the new configuration
- You'll be asked for your license key again. Enter it, and click the 'Custom Installation' button.
- Under the 'External Database' heading, choose MySQL from the dropdown list and click the 'External Database' button.
- On the next page, click the 'Direct JDBC' button.
- Change the database URL by changing the database name 'confluence' to 'confluencedb', so the URL looks like this: jdbc:mysql://localhost/confluencedb?autoReconnect=true
- Enter confluenceuser in the User Name field, and the password you chose earlier in the Password field
- 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:
- 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.
- Stop Confluence
Related Documents
Configuring Database Character Encoding
Known Issues for MySQL
Comments (12)
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!
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
Apr 30, 2006
James Matheson says:
A couple of points: The confluence setup wizard expects the database sch...A couple of points:
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:
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
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?
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.
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.
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.
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
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
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:
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
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
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