Confluence Docs 3.0 : Database Setup For MySQL This page last changed on Aug 17, 2009 by ggaskell. Confluence should use an external database for production purposes, 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 purposes. This page provides instructions for installing Confluence and the open-source MySQL database on Microsoft Windows and how to set up and configure MySQL to work with Confluence. Additional instructions are also provided for migrating across any existing Confluence database content. Use this guide in conjunction with the more general Database Setup Guide for Any Database. These instructions add some important reference notes specific to MySQL.
| Please note the following points: Throughout the instructions below, the `Confluence Installation Directory` refers to the directory where you extracted the Confluence zipped installer. The MySQL Database Setup procedure below will make modifications to your default MySQL Server settings. These modifications result in: The default collation (localisation) option being changed to `utf8_bin` (that is, case-sensitive binary UTF8), such that by default, all new database tables will be created with this type of case-sensitive collation. The default MySQL database storage engine being changed to 'InnoDB'.
## Installation and Configuration Complete the following steps to install and configure Confluence and the MySQL Server: Install Confluence if you have not done so already. If you do not have an operational MySQL database server instance available, install 'MySQL Community Server' database server (version 5.0). The installation package can be downloaded from the MySQL download page or from the version 5.0 download page. Instructions for installing the MySQL 5.0 database server on Windows can found on the 'Installing MySQL on Windows' page of the MySQL web site. MySQL 5.0 is supported since Confluence 2.5 and above. Once the MySQL database server has been installed, run through the 'MySQL Server Instance Config Wizard': If you intend to connect Confluence to an existing, operational MySQL database server instance, we strongly recommend that you reconfigure this database server instance by running through the Config Wizard and initially choosing the Reconfigure Instance option. At the start of the Config Wizard (or after having chosen Reconfigure Instance), choose Detailed Configuration. Choose the type of MySQL Server that best suits your hardware requirements. This will affect the MySQL Server's usage of memory, disk and CPU resources. Refer to the relevant MySQL documentation for further information. Choose Transactional Database Only for the database usage step. This ensures that your MySQL database will use InnoDB as its default storage engine. It is highly recommended that you only use the InnoDB storage engine with Confluence. Avoid using the MyISAM storage engine as this can lead to data corruption. Set the InnoDB Tablespace settings to your requirements. (The default settings should be acceptable.) Set the approximate number of concurrent connections permitted that best suits your Confluence usage requirements. You can use one of the presets if desired or enter a number manually. Refer to the relevant MySQL documentation for further information. For the networking options, ensure the Enable TCP/IP Networking option is selected (default) and that the Enable Strict Mode option is also selected (default). Refer to the MySQL documentation on setting the networking and server SQL modes for further information. For the MySQL server instance's default character set option, choose Best Support For Multilingualism (i.e. UTF-8). For the Windows configuration option, choose whether or not to install the MySQL Server as a Windows Service. If your hardware is going to be used as a dedicated MySQL Server, you may wish to choose the Install As Windows Service (and Launch the MySQL Server automatically) options. Refer to the relevant MySQL documentation for further information. If you choose not to install the MySQL Server as a Windows Service, you will need to ensure that the database service has been started before running Confluence. Finally, select the Modify Security Settings option to enter and set your MySQL Server (root) access password. Download the latest MySQL Connector/J driver, version 5.1 or newer. Extract the `mysql-connector-java-5.x.y-bin.jar` file from the Connector J archive and place it in either the `Confluence Installation Directory/lib` or `Confluence Installation Directory/confluence/WEB-INF/lib` directories. (The `x.y` depends on exactly which version you download – MySQL updates the version number from time to time.) ### Stage 1 - MySQL Database Setup
| This procedure uses the MySQL Administrator application component of the MySQL GUI Tools package. If MySQL GUI Tools is not already installed or included in your MySQL installation, you will need to download and install this package before proceeding.
2. To create the database and user privileges: Start the 'MySQL Administrator' application and enter your login credentials when prompted to connect to the MySQL database server. If you ran through the 'MySQL Server Instance Config Wizard' in step 3 of the Installation and Configuration (above) and did not change your username, you should be able to connect to the MySQL database server using the default Username of 'root', Server Host of 'localhost' (assuming you are running MySQL Administrator on the same machine as the MySQL Server) and Password which you set in the Config Wizard. If, however, you are running MySQL for the first time and you have not yet run through the Config Wizard, or you do not recall having set your login credentials, you should be able to click OK without a Username and Password. Go to Service Control in the left pane and ensure that the MySQL Service is stopped. If not, click 'Stop Service' to stop the MySQL Service. Go to Startup Variables in the left pane and click the 'Advanced' tab. Ensure that the 'Default Collation' check box is selected, enter 'utf8_bin' for the 'Data Collation' value and click 'Apply changes'. If you did not run through the 'MySQL Server Instance Config Wizard' in step 3 of the Installation and Configuration (above), click the 'General Parameters' tab and ensure that the Default storage check box has been selected with its value set to InnoDB. Also, click the 'InnoDB Parameters' tab and ensure that the Activate InnoDB check box is selected. If not, implement these settings and click 'Apply changes' before proceeding. Go back to Service Control in the left pane and click 'Start Service' to re-start the MySQL Service (with the updated default collation settings). Go to Catalogs in the left pane, then right-click in the schema list at the bottom of this pane and select 'Create New Schema' from the pop-up menu. Call the new Schema name 'confluence' and click 'OK'. Go to User Administration in the left pane and then click 'Add new user' at the bottom of the window. Enter the MySQL username 'confluenceuser'. In fact, you can enter any legitimate MySQL username. However, the remaining steps and procedures on this page assume that you have entered the MySQL username of 'confluenceuser' at this point. If you choose a different MySQL username, substitute 'confluenceuser' in the remaining steps and procedures with your chosen MySQL username. Enter and re-confirm a password for this user and click 'Apply changes'. Right-click on the username 'confluenceuser' in the left pane and choose 'Add host from which the user can connect' from the pop-up menu. If prompted to enable the global 'Show hosts in user list' option, choose 'Yes'. Enter the host name 'localhost' and click 'OK'. Click on 'localhost' within 'confluenceuser' in the left pane. (You might need to click on the name 'confluenceuser' first). Click the 'Schema Privileges' tab and then click 'confluence' in the 'Schemata' list. Make sure that 'confluenceuser@localhost' has all privileges assigned to them by clicking the '<<' button to move the privileges from the 'Available' to the 'Assigned' list and 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 set up your Confluence MySQL database connection or to switch to using this external database: Ensure that Confluence is stopped (for example, by ensuring that the application server instance or service which is running Confluence has been stopped or terminated). 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) Start Confluence and set up the new configuration. Enter your license key and click the 'Custom Installation' button. Under the 'External Database' heading, select 'MySQL' from the dropdown list and click 'External Database'. On the next page, click 'Direct JDBC'. 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.
| If you encounter any issues in setting up your MySQL database to work with Confluence, please refer to Known Issues for MySQL.
### Related Documents Configuring Database Character Encoding Known Issues for MySQL

Document generated by Confluence on Nov 05, 2009 23:34