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 .


These instructions apply to Confluence 7.3 and later. Using an earlier version? See Database Setup For MySQL in Confluence 7.2 and earlier.


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 utf8mb4:

      [mysqld]
      ...
      character-set-server=utf8mb4
      collation-server=utf8mb4_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, and that your database user can create and alter stored functions.

      [mysqld]
      ...
      binlog_format=row
      log_bin_trust_function_creators = 1
      ...
    • If you're using MySQL 5.7, turn off the 'derived merge' optimizer switch, as this can cause the dashboard to load slowly.

      optimizer_switch = derived_merge=off
  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 utf8mb4 COLLATE utf8mb4_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 follow the steps below for your MySQL version. 

MySQL 5.7

  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.

MySQL 8.0

You can't use MySQL 8.0 with Confluence 7.1 or earlier. 

  1. Stop Confluence.
  2. Head to Database JDBC Drivers and download the appropriate driver for MySQL 8. The driver file will be called something like mysql-connector-java-8.0.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 typeFieldDescription
Simple HostnameThis is the hostname or IP address of your database server.  
Simple PortThis is the MySQL port. If you didn't change the port when you installed MySQL, it will default to 3306.
SimpleDatabase name This is the name of your confluence database. In the example above, this is confluence
By connection stringDatabase URL

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

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

BothUsernameThis is the username of your dedicated database user. In the example above, this is confluenceuser.
BothPasswordThis 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. 

Upgrade your database and driver

If you upgrade MySQL you may also need to upgrade the database driver Confluence uses to connect to your database. Always use the driver recommended on the Database JDBC Drivers page. 

Before you begin, back up your database, Confluence installation directory and Confluence home directory. We strongly recommend you test your changes in a staging environment first. 

To upgrade your database driver:

  1. Stop Confluence.
  2. Go to <installation-directory>/confluence/WEB-INF/lib/  and delete your existing driver. It will be called something like mysql-connector-java-x.x.xx-bin.jar
  3. Drop the new driver .jar file in your <installation-directory>/confluence/WEB-INF/lib directory.
  4. Upgrade your MySQL server. 
  5. Restart Confluence.

If you're using a datasource connection, you may need to also update the driver classname in the datasource. 

Troubleshooting

  • There is a known issue when running Confluence with MySQL 8.0.29 and later due to a change to the UTF8 alias in MySQL. We're working on a fix, but if you have Confluence 7.3 or later, you can change the character set and collation to UTF8MB4 to avoid this issue. See How to Fix the Collation and Character Set of a MySQL Database manually.
  • There is a known issue with MySQL 5.7 where parts of the dashboard can take a very long time to load. You may need to turn the "derived merge" optimizer switch off in your database configuration. See  CONFSERVER-54984 - Getting issue details... STATUS  for details of the workaround.  
  • 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: Database Troubleshooting for MySQL

Last modified on May 30, 2022

Was this helpful?

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