1. Creating and Configuring the MySQL database

For your external MySQL database to work well with Bamboo, it must be able to use the following:

• utf8 or utf8mb4 character set encoding instead of latin1
• utf8_bin or utf8mb4_bin collation
• the InnoDB storage engine

• (recommended, not required) lower_case_table_names=1

  Setting lower_case_table_names=1 might break other Atlassian applications. For more information, see the steps.

• global transaction isolation level as READ_COMMITTED
• Disable NO_AUTO_VALUE_ON_ZERO mode.

We also recommend that your MySQL database server is configured to use an InnoDB storage engine. Alternatively, you can configure Bamboo's JDBC connection to your MySQL database so that any tables that Bamboo creates in this database will be done using the InnoDB database engine.

A MySQL database administrator can easily create and configure a MySQL database for Bamboo by running the following MySQL commands:

mysql> CREATE DATABASE bamboo CHARACTER SET utf8 COLLATE utf8_bin;
mysql> GRANT ALL PRIVILEGES ON bamboo.* TO 'bamboouser'@'localhost' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;
mysql> QUIT

mysql> CREATE DATABASE bamboo CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
mysql> GRANT ALL PRIVILEGES ON bamboo.* TO 'bamboouser'@'localhost' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;
mysql> QUIT

mysql> CREATE DATABASE bamboo CHARACTER SET utf8 COLLATE utf8_bin;
mysql> CREATE USER 'bamboouser'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON bamboo.* TO 'bamboouser'@'localhost' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
mysql> QUIT

mysql> CREATE DATABASE bamboo CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
mysql> CREATE USER 'bamboouser'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON bamboo.* TO 'bamboouser'@'localhost' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
mysql> QUIT

bamboouser — the user account name for the Bamboo MySQL database. This creates an empty MySQL database for Bamboo named bamboo, where:

• localhost — the host name of the MySQL database server
• password — the password for this user account

For more information about configuring character set encoding and collation for Bamboo MySQL databases, refer to the MySQL 5 documentation — Specifying Character Sets and Collations.

SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;

2. Connecting Bamboo to the MySQL database

You can connect Bamboo to the MySQL in one of the following ways:

• using JDBC
• using a datasource

 JDBC is generally simpler and we recommend using it.

Connect using JDBC

1. Download and install the JDBC driver

The JDBC drivers for MySQL Enterprise Server are no longer bundled with Bamboo (due to licensing restrictions). You need to download and install the driver yourself.

1. Download the MySQL Connector/J JDBC driver 5.1 or 8 from the download site.

   In MySQL 8, the com.mysql.jdbc.Driver class implementing java.sql.Driver in MySQL Connector/J was deprecated. Instead, you should use the com.mysql.cj.jdbc.Driver class.

2. Extract the downloaded zip/tar.gz file.
3. Copy the mysql-connector-java-XX.XX-bin.jar file from the extracted directory to the <Bamboo installation directory>/lib  directory (create the lib/ directory if it doesn't already exist).
   If you are using the Java Service Wrapper to start your Bamboo instance (Bamboo/wrapper/run-bamboo start), copy the mysql-connector-java-XX.XX-bin.jar file to <Bamboo installation directory> /wrapper/lib directory.
4. Stop Bamboo on Windows, Linux, or Mac.
5. Restart Bamboo on Windows, Linux, or Mac.

2. Connect Bamboo to a MySQL database using JDBC

1. Run the Setup Wizard.
2. On the 'Configure database' page, choose MySQL from the 'Database Type' dropdown menu, and then select Continue.

3. Ensure that Direct JDBC connection is selected and complete the following fields (as shown in the screenshot below):
   
   Driver Class Name
   Type:
   

   • com.mysql.cj.jdbc.Driver - for MySQL Connector/J 8.0 or later
   • com.mysql.jdbc.Driver - for MySQL Connector/J 5.1

   
   Database URL
   Type the URL where Bamboo will access your database (if different from the default). Your URL must include the autoReconnect=true flag.

   • If you intend to use non-Latin characters in Bamboo, ensure that your URL includes the useUnicode=true and characterEncoding=utf8 flags.

   • If your MySQL database server is configured to use a storage engine other than InnoDB by default, ensure that your URL includes the sessionVariables=storage_engine=InnoDB flag.
     If you include all of these flags, your Database URL should look similar to:

     jdbc:mysql://localhost/bamboo?autoReconnect=true&useUnicode=true&characterEncoding=utf8&sessionVariables=storage_engine=InnoDB

     If the autoReconnect=true flag is not specified, the MySQL JDBC driver will eventually time out and Bamboo will no longer be able to communicate with the database.

     
     For more information on the URL syntax, see the MySQL documentation.

   • If you use MySQL Connector 8.0 with MySQL database 5.7, AO tables will not be created and Bamboo will not be able to start. To prevent this, include the nullCatalogMeansCurrent=true flag in your Database URL.  
     Your Database URL should look similar to:

     jdbc:mysql://instenv-2787-y7fe.c7uydxwwuprf.eu-west-1.rds.amazonaws.com:3306/bamboo?autoReconnect=true&useUnicode=true&characterEncoding=utf8&nullCatalogMeansCurrent=true

   
   User Name
   Type the username that Bamboo will use to access your database. This is bamboouser defined in section 1 (above).
   
   Password
   Type the password (if required) that Bamboo will use to access your database. This is password defined in section 1 (above). Leave this field blank if a password for the database user account was not specified.

4. Select Overwrite existing data if you wish Bamboo to overwrite any tables that already exist in the database.

5. Select Continue.

Connect using a datasource

1. Configure a datasource in your application server (consult your application server documentation for details). Please note the following:
   

   • Ensure that the JDBC URL which you configure in your application server includes the autoReconnect=true, useUnicode=true and characterEncoding=utf8 flags, such that your database URL should look similar to: jdbc:mysql://localhost/bamboo?autoReconnect=true&useUnicode=true&characterEncoding=utf8

   • If your MySQL database server is configured to use a storage engine other than InnoDB by default, also include the sessionVariables=storage_engine=InnoDB flag in this URL.

   • If the autoReconnect flag is not set, the MySQL JDBC driver will eventually time out and Bamboo will no longer be able to communicate with the database.
     For more information on the URL syntax, see the MySQL documentation.

   • Datasource example: You can see an example of using Tomcat with a MySQL database as a datasource in the following document: Tomcat and external MySQL datasource example.

2. Run the Setup Wizard and choose the Custom Installation method.

3. Choose External Database > MySQL from the list and click Continue. 

4. Choose Connect via a datasource (configured in the application server) (as shown in the screenshot below).

5. In the JNDI name field, type the JNDI name of your datasource, as configured in your application server.

   If java:comp/env/jdbc/DataSourceName does not work, try jdbc/DataSourceName (and vice versa).

6. Select Overwrite existing data if you wish Bamboo to overwrite any tables that already exist in the database.

7. Click Continue.

Screenshot 2: Setup Datasource Connection