Database Setup For MySQL

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

Before you start

  • See Supported Platforms to check your version of MySQL is supported. You may need to upgrade your database before installing Confluence.
  • If you're switching from another database, including the embedded evaluation database, read Migrating to Another Database before you begin.

Confluence will not work on MySQL variants such as MariaDB (CONFSERVER-29060) and Percona Server (CONFSERVER-36471)

1. Install MySQL Server

If you don't already have MySQL installed, download and install it now. See the MySQL documentation for step-by-step instructions.

2. 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.
      You must use the InnoDB storage engine with Confluence. Using the MyISAM storage engine can lead to data corruption in Confluence. 
    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 2GB:

      [mysqld]
      ...
      innodb_log_file_size=2GB
      ...
    • 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
    • Ensure that the global transaction isolation level of your Database had been set to READ-COMMITTED.

      [mysqld]
      ...
      transaction-isolation=READ-COMMITTED
      ...
    • Check that the binary logging format is configured to use 'row-based' binary logging.

      [mysqld]
      ...
      binlog_format=row
      ...
  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'.

3. Create database and database user

Once you've installed and configured MySQL, create a database user and database for Confluence as follows:

  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 (for example confluence):

    CREATE DATABASE <database-name> CHARACTER SET utf8 COLLATE utf8_bin;
  3. Create a Confluence database user (for example confluenceuser):

    GRANT ALL PRIVILEGES ON <database-name>.* TO '<confluenceuser>'@'localhost' IDENTIFIED BY '<password>';

    If Confluence is not running on the same server, replace localhost with the hostname or IP address of the Confluence server. 

4. Install Confluence

Check out the Confluence Installation Guide for step-by-step instructions on how to install Confluence on your operating system. 

5. Download and install the MySQL driver

Due to licensing restrictions, we're not able to bundle the MySQL driver with Confluence. To make your database driver available to Confluence:

  1. Stop Confluence.
  2. Head to Database JDBC Drivers and download the appropriate driver. The driver file will be called something like mysql-connector-java-5.1.xx-bin.jar
  3. Drop the .jar file in your <installation-directory>/confluence/WEB-INF/lib directory.
  4. Restart Confluence then go to http://localhost:<port> in your browser to continue the setup process.

6. Enter your database details

The Confluence setup wizard will guide you through the process of connecting Confluence to your database. 

Use a JDBC connection (default)

JDBC is the recommended method for connecting to your database.

The Confluence setup wizard will provide you with two setup options:

  • Simple - this is the most straightforward way to connect to your database.
  • By connection string - use this option if you want to specify additional parameters and are comfortable constructing a database URL. 

Depending on the setup type, you'll be prompted for the following information.

Setup type Field Description
Simple  Hostname This is the hostname or IP address of your database server.  
Simple  Port This is the MySQL port. If you didn't change the port when you installed MySQL, it will default to 3306.
Simple Database name  This is the name of your confluence database. In the example above, this is confluence
By connection string Database URL

The database URL is entered in this format:
jdbc:mysql://<hostname>:<port>/<database>

For example:
jdbc:mysql://localhost:3306/confluence

Both Username This is the username of your dedicated database user. In the example above, this is confluenceuser.
Both Password This is the password for your dedicated database user.

Use a JNDI datasource

If you want to use a JNDI datasource, see Configuring a datasource connection for the steps you'll need to take before you set up Confluence, as the setup wizard will only provide the option to use a datasource if it detects a datasource in your Tomcat configuration. 

7. Test your database connection

In the database setup screen, hit the Test connection button to check:

  • Confluence can connect to your database server
  • the database character encoding, collation, isolation level and storage engine are correct
  • your database user has appropriate permissions for the database.

Once the test is successful, hit Next to continue with the Confluence setup process. 

Troubleshooting

  • If Confluence complains that it is missing a class file, you may have placed the JDBC driver in the wrong folder.

  • 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: Known issues for MySQL

Last modified on May 9, 2018

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.