Database Setup For MySQL

1. 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.
2. Run 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.
   1. At the start of the Config Wizard (or after having chosen Reconfigure Instance), choose Detailed Configuration.
   2. 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.
   3. 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.
   4. Set the InnoDB Tablespace settings to your requirements.(The default settings are acceptable).
   5. 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.
   6. 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.
   7. For the MySQL server instance's default character set option, choose 'Best Support For Multilingualism' (i.e. UTF-8).
   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.
   9. Finally, select the 'Modify Security Settings' option to enter and set your MySQL Server (root) access password.
3. Install the database drivers, if needed:

   MySQL JDBC Drivers bundled with Confluence

   The JDBC drivers for this database are bundled with Confluence. You do not have to download or install any JDBC drivers to use this database with Confluence, if you are using a direct JDBC connection. If you are connecting via a datasource, you will still need to download and install the drivers manually. See Database JDBC drivers for more information on the bundled JDBC drivers.
   

   • If you're not sure which connection you're using, it's most likely JDBC. A JNDI resource must be configured manually, as described in Configuring a MySQL Datasource in Apache Tomcat.
   • If you are configuring a datasource to connect to your MySQL database, you will need to place the jar file (i.e. mysql-connector-java-5.x.y-bin.jar where x.y depends on the driver version) in <confluence install>/WEB-INF/lib (for Confluence 2.10 onwards) or <confluence install>/common/lib (for earlier versions). Information and links to the appropriate database drivers are available on Database JDBC drivers.
4. Configure your MySQL server's settings by editing MySQL's my.cnf file (often named my.ini on Windows operating systems). Locate the [mysqld] section in the file add/modify the following parameters:
   • Specify the default character set to be UTF-8:

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

   • Set the default storage engine to InnoDB:

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

   • Specify/increase the value of max_allowed_packet to be at least 32M:

     [mysqld]
     ...
     max_allowed_packet=32M
     ...
     

   • For 3.5 to 3.5.5, specify/change the value for transaction-isolation to be READ-COMMITTED:

     [mysqld]
     ...
     transaction-isolation=READ-COMMITTED
     ...
     

     (Refer to MySQL Option Files for detailed instructions on editing my.cnf and my.ini.)
5. Restart your MySQL server for the changes to take effect:
   • On Windows, use the Service Manager to restart the service
   • On Linux, depending on your setup you will need to run either "/etc/init.d/mysqld stop", "/etc/init.d/mysql stop" or "service mysqld stop" followed by the same command with "stop" replaced with "start"
   • On Mac OS X, run "sudo /Library/StartupItems/MySQLCOM/MySQLCOM restart"

1. Run the "mysql" command as a MySQL super user. The default is "root" with a blank password.
2. Create an empty Confluence database schema by running

   CREATE DATABASE confluence;

3. Create the Confluence database user by running

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

   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.

To support international languages in Confluence, you should verify that the newly created database is using UTF-8 encoding and re-examine the JDBC URL settings (configured in Stage 3).

1. 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 described later.
2. Download the backup file to a backups folder.

1. Ensure that Confluence is stopped (for example, by ensuring that the application server or service which is running Confluence has been stopped or terminated).
2. If you haven't started Confluence yet, you can skip this step. If you have set up Confluence with the built-in (HSQLDB) database, 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)
3. Start Confluence and set up the new configuration.
   1. Enter your license key and click the 'Custom Installation' button.
   2. Under the 'External Database' heading, select 'MySQL' from the dropdown list and click 'External Database'.
   3. On the next page, click 'Direct JDBC'.
   4. Enter confluenceuser in the User Name field, and the password you chose earlier in the Password field
   5. 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.
   6. (optional) If you previously backed up your Confluence data, you can choose to restore it 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.