Over at Atlassian Answers I’ve noticed a few people reporting problems installing Confluence with MySQL and being told to use Postgres instead, but using a different database is not always an option and in my experience Confluence does work perfectly well with MySQL. I needed to complete another install of Confluence with MySQL recently and these are the steps that I used. I hope people find them useful.

Configure the MySQL Server
It’s recommended to use UTF8 character encoding for the database and this can be set in my.cnf (Linux) or my.ini (Windows) with the following settings under the [mysqld] section:

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

For older versions of MySQL the default storage engine is MyISAM, but this can lead to data corruption as it is not a transactional storage engine and it is recommended that you use the transactional InnoDB storage engine instead. Again this can be set in the [mysqld] section of my.cnf or my.ini with the following:

[mysqld]
...
default-storage-engine=INNODB
innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
...

Finally in my.cnf or my.ini you need to increase max_allowed_packets to at least 32MB with the following setting:

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

After making these changes to my.cnf or my.ini save the file and restart the MySQL server.

Create the MySQL Database
Log in to MySQL and run the following SQL statement:

mysql> CREATE DATABASE confluence CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 1 row affected (0.00 sec)

Check Character Encoding
Check that the database you have created is using the correct character encoding by running the following SQL statements:

mysql> use confluence;
Database changed
mysql> status;

and you should see the following information listing all the characterset settings as utf8:

Connection id:		23
Current database:	confluence
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.5.18-log MySQL Community Server (GPL)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8
Db     characterset:	utf8
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/tmp/mysql.sock
Uptime:			12 min 5 sec

Create the Database User
Create the database user that Confluence will use to access the database with the following SQL statements:

mysql> GRANT ALL PRIVILEGES ON confluence.* to confluenceuser@'localhost' identified by '<your password>';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

where <your password> is the password that you wish to use.

Configure Confluence to use MySQL
The following assumes you have already installed Confluence using the standard installation instructions and have started Confluence.

After entering your License key select Production Installation and that the External Database will be MySQL:

Next, select Direct JDBC Connection to connect directly to the database:

Enter the connection details:

JDBC URL: jdbc:mysql://<hostname>:<port>/<database>?autoReconnect=true&useUnicode=true&characterEncoding=utf8&sessionVariables=storage_engine%3DInnoDB
Username: confluenceuser
Password: <your password>

where  <hostname>, <port>, <database> and <your password> are the values for your environment, e.g.:

jdbc:mysql://localhost:3306/confluence?autoReconnect=true&useUnicode=true&characterEncoding=utf8&sessionVariables=storage_engine%3DInnoDB

If all of the information is correct Confluence will connect to your database, create the necessary database tables and you will be presented with the following screen and can select Example Site to start using Confluence:

If you get an error message similar to the following:

the most common causes are:

• Incorrect username/password
• Incorrect database name
• Incorrect port for the database
• Incorrect hostname for the database
• Network/firewall issue between Confluence and MySQL

mysql -u <username> -p

mysql> use confluence;
Database changed

mysql -u root -p

mysql> select user, host from user;
+----------------+------------------------------------+
| user           | host                               |
+----------------+------------------------------------+
| root           | 127.0.0.1                          |
| root           | ::1                                |
| confluenceuser | localhost                          |
...

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| confluence         |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database confluence;
Query OK, 0 rows affected (0.06 sec)

mysql> drop user confluenceuser@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

# The MySQL server
[mysqld]
port            = 3306

mysql> GRANT ALL PRIVILEGES ON confluence.* to 'confluenceuser'@'localhost%' identified by '<the password>';
mysql> FLUSH PRIVILEGES;

telnet <mysql host> <port>

telnet mysql.example.com 3306

Connected to mysql.example.com.
Escape character is '^]'.

Share