Cloud
Data Center 5.10
Versions

Database Setup For MySQL

Database Configuration

On this page

In this section

Related content

  • No related content found

Still need help?

The Atlassian Community is here for you.

Ask the community

This page provides instructions for configuring Confluence to use the MySQL database.

Step 1. Check the prerequisites

Check the following before you start:

Step 2. Install MySQL Server

If you do not already have an operational MySQL database server, install 'MySQL Community Edition'. Download the installation package from the MySQL download page and follow the instructions in the MySQL documentation.

On this page:

Related pages:

Step 3. Configure MySQL Server

In this step, you will configure your MySQL database server.

Note: If you intend to connect Confluence to an existing MySQL database server, we strongly recommend that you reconfigure this database server by running through the configuration steps in the MySQL installation wizard as described below .

To configure MySQL Server:

  1. Run the MySQL installation wizard:
    1. If you are connecting Confluence to your existing MySQL server, choose Reconfigure Instance.
    2. Choose Advanced Configuration.
    3. 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 MySQL documentation for further information.
    4. Choose Transactional Database Only to ensure 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.
    5. Set the InnoDB Tablespace settings to your requirements. (The default settings are acceptable.)
    6. Set the approximate number of concurrent connections permitted to suit your Confluence usage requirements. You can use one of the presets or enter a number manually. Refer to the MySQL documentation for further information.
    7. For the networking options, ensure the Enable TCP/IP Networking and Enable Strict Mode options are selected (default). Refer to the MySQL documentation on setting the networking and server SQL modes for further information.
    8. For the MySQL server's default character set, choose Best Support For Multilingualism (in other words, UTF-8). This will ensure Confluence's support for internationalization. For more information, see Configuring Database Character Encoding.
    9. 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 options to Install As Windows Service (and Launch the MySQL Server automatically). Refer to the MySQL documentation for further information.
      Note: 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.
    10. Select Modify Security Settings to enter and set your MySQL Server (root) access password.
  2. Edit the my.cnf file (my.ini on Windows operating systems) in your MySQL server. Locate the [mysqld]section in the file, and add or modify the following parameters:
    (Refer to MySQL Option Files for detailed instructions on editing my.cnf and my.ini.)
    Locate the [mysqld]section in the file, and add or modify the following parameters:

    • Specify the default character set to be UTF-8:

      [mysqld]
...
character-set-server=utf8
collation-server=utf8_bin
...

    • Set the default storage engine to InnoDB:

      [mysqld]
...
default-storage-engine=INNODB
...

    • Specify the value of max_allowed_packet to be at least 256M:

      [mysqld]
...
max_allowed_packet=256M
...

    • Specify the value of innodb_log_file_size to be at least 256M for MySQL 5.5 and below:

      [mysqld]
...
innodb_log_file_size=256M
...

      NB: This should be set to at least 2G for MySQL 5.6 and above.

    • Ensure the sql_mode parameter does not specify NO_AUTO_VALUE_ON_ZERO

      // remove this if it exists
sql_mode = NO_AUTO_VALUE_ON_ZERO
  3. Restart your MySQL server for the changes to take effect:
    • On Windows, use the Windows Services manager to restart the service.
    • On Linux:
      • Run one of the following commands, depending on your setup: '/etc/init.d/mysqld stop' or '/etc/init.d/mysql stop' or 'service mysqld stop'.
      • Then run the same command again, replacing 'stop' with 'start'.
    • On Mac OS X, run 'sudo /Library/StartupItems/MySQLCOM/MySQLCOM restart'.

Step 4. Set up your MySQL database and user

In this step you will create a database within MySQL to hold your Confluence data, and a database user with authority to access that database.

To create the database and user privileges:

  1. Run the 'mysql' command as a MySQL super user. The default user is 'root' with a blank password.

  2. Create an empty Confluence database schema by running this command:

    CREATE DATABASE confluence CHARACTER SET utf8 COLLATE utf8_bin;

  3. Create the Confluence database user by running this command. Replace 'confluenceuser' and 'confluencepass' with a username and password of your choice. If Confluence is not running on the same server as your MySQL database server, replace 'localhost' with the hostname or IP address of the Confluence server:

    GRANT ALL PRIVILEGES ON confluence.* TO 'confluenceuser'@'localhost' IDENTIFIED BY 'confluencepass';

Step 5. Install Confluence

Install Confluence if you have not done so already. See the Confluence Installation Guide. Stop immediately after the installation, before opening the Confluence Setup Wizard in your browser, and follow the steps below.

If you have already got part-way through the Confluence Setup Wizard, stop at the database setup step and follow the steps below. You will be able to restart the setup wizard at the same step later.

Step 6. Download and install the MySQL database driver

If you are upgrading Confluence to a later version, and you are already using the recommended MySQL driver (JDBC Connector/J 5.1), you can skip the instructions in this section. The Confluence upgrade task will automatically copy over your existing driver to the upgraded installation.

If you are installing Confluence, or you are upgrading Confluence and not using the recommended MySQL driver (JDBC Connector/J 5.1), follow the steps below.

Choose whether you will set up a direct JDBC connection or a datasource connection to MySQL, to suit your environment. If unsure, choose direct JDBC.

To set up a direct JDBC connection:

If you plan to set up a direct JDBC connection to MySQL, you will need to copy the MySQL JDBC driver to your Confluence installation.

  1. Get the MySQL driver:
    • If you are installing Confluence, download the recommended MySQL driver . Links to the appropriate database drivers are available on this page: Database JDBC Drivers.
      You can download either the .tar.gz or the .zip archive. Extract the driver JAR file (for example, mysql-connector-java-x.x.x-bin.jar, where x.x.x is a version number) from the archive.
    • If you are upgrading Confluence to a later version, and you are not using the recommended MySQL driver (JDBC Connector/J 5.1), copy the driver JAR file from your existing Confluence installation before you upgrade. The driver will be in the <Confluence installation> /confluence/WEB-INF/lib folder.
  2. Copy the driver JAR file to the  <Confluence installation> /confluence/WEB-INF/lib folder in your new or upgraded Confluence installation.

To set up a datasource connection:

If you plan to set up a datasource connection to MySQL, follow the steps described in Configuring a MySQL Datasource in Apache Tomcat.

Step 7. Check settings for internationalization

If you are using a existing database, use the status command to verify database character encoding information. The results should be UTF-8. See Configuring Database Character Encoding.

Step 8. Set up your database connection in the Confluence Setup Wizard

Start Confluence, and go to the Confluence Setup Wizard in your browser. Follow these steps to set up the new configuration:

  1. Follow the initial steps in the Confluence Setup Guide.
  2. When prompted to choose an evaluation or production installation, choose production installation.
  3. When prompted to choose an embedded or external database, select MySQL from the dropdown list and choose External Database.
    (Note: you'll see a warning that a driver is required. You downloaded or copied the driver in step 6 so you can ignore this warning) 
  4. Choose either the direct JDBC or the datasource connection, to suit the choice you made earlier when setting up the MySQL database driver.
    • For the JDBC connection: Enter the username (for example, confluenceuser) and password you chose earlier. 
    • For a datasource connection: Set the JNDI name to java:comp/env/jdbc/confluence

Congratulations! Confluence is now using your MySQL database to store its data.

Troubleshooting

  • If you get the following error message, verify that you have given the confluenceuser user all the required database permissions when connecting from localhost.

    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)'
  • The following page contains common issues encountered when setting up your MySQL database to work with Confluence: Database Troubleshooting for MySQL
  • If Confluence complains that it is missing a class file, you may have placed the JDBC driver in the wrong folder.
  • If none of the above describes your issue, please create a support ticket at http://support.atlassian.com and be sure to include your logs (found in <CONFLUENCE-INSTALLATION>/logs and <CONFLUENCE-HOME>/logs).
  • There's currently a known issue for MySQL 5.5  CONF-37829 - Getting issue details... STATUS
Last modified on Jun 6, 2016

Was this helpful?

Yes
No
Provide feedback about this article

In this section

Related content

  • No related content found
Powered by Confluence and Scroll Viewport.