{{tag>atlassian jira mysql}}

====== Installing mysql5.7 for Jira ======
===== Install MySQL 5.7 replacing MariaDB 10.1 and enabling emojis and special chars support =====

   systemctl stop jira.service
   systemctl stop confluence.service
   mysqldump -u jiradbuser -p --opt --single-transaction jiradb > jiradb.sql
   mysqldump -u confluencedbuser -p --opt --single-transaction confluencedb > confluencedb.sql

===== Remove any MariaDB-/MySQL-related packages including config files =====

   apt purge `dpkg -l|grep maria|awk '{print $2};'`
   apt purge `dpkg -l|grep mysql|awk '{print $2};'`
   rm /etc/apt/preferences.d/mariadb-enterprise.pref
   apt autoremove

===== Install MySQL prerequisites =====

   apt install dirmngr
   apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 5072E1F5
   echo "deb http://repo.mysql.com/apt/debian/ stretch mysql-5.7" >> /etc/apt/sources.list
   apt update
   apt install mysql-server

===== Tweak MySQL settings =====

   systemctl stop mysql
   cat<<EOF >>/etc/mysql/mysql.conf.d/mysqld.cnf
   default-storage-engine = INNODB
   character-set-server = utf8mb4
   collation-server = utf8mb4_unicode_ci
   innodb_large_prefix = ON
   innodb_log_file_size = 256M
   innodb_default_row_format = DYNAMIC
   innodb_file_format = Barracuda
   innodb_buffer_pool_size = 512M
   max_allowed_packet = 256M
   transaction-isolation = READ-COMMITTED
   EOF
   
   systemctl start mysql

===== Create databases and users =====

   mysql -p
   CREATE DATABASE jiradb CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
   CREATE DATABASE confluencedb CHARACTER SET utf8 COLLATE utf8_bin;
   GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,REFERENCES,ALTER,INDEX on jiradb.* TO 'jiradbuser'@'localhost' IDENTIFIED BY 'password';
   GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,REFERENCES,ALTER,INDEX on confluencedb.* TO 'confluencedbuser'@'localhost' IDENTIFIED BY 'password';

===== Restore backup =====

   use jiradb;
   source /root/backup/jiradb.sql;

===== Change collation and charset for tables and columns =====

   SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;')       FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'jiradb' AND TABLE_TYPE = 'BASE TABLE';
   SELECT CONCAT('ALTER TABLE ', C.TABLE_NAME, ' CHANGE ', C.COLUMN_NAME, ' ', C.COLUMN_NAME, ' ', C.COLUMN_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;') as queries
   FROM information_schema.COLUMNS as C
   LEFT JOIN information_schema.TABLES as T
   ON C.TABLE_NAME = T.TABLE_NAME
   WHERE C.COLLATION_NAME IS NOT NULL AND C.TABLE_SCHEMA='jiradb' AND T.TABLE_TYPE='BASE TABLE';

   use confluencedb;
   source /root/backup/confluencedb;
   quit;

   systemctl restart mysql

===== Check character set and collation =====

<code mysql>
SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+
</code>

   cp mysql-connector-java-5.1.47-bin.jar /opt/atlassian/jira/lib/
   cp mysql-connector-java-5.1.47-bin.jar /opt/atlassian/confluence/lib
   /opt/atlassian/jira/bin/config.sh

   vim /var/atlassian/application-data/jira/dbconfig.xml

Replace

   <url>jdbc:mysql://address=(protocol=tcp)(host=localhost)(port=3306)/jiradb?sessionVariables=default_storage_engine=InnoDB</url>

with

   <url>jdbc:mysql://address=(protocol=tcp)(host=localhost)(port=3306)/jiradb?useUnicode=true&characterEncoding=UTF8&useSSL=false&sessionVariables=default_storage_engine=InnoDB</url>

Ignore incorrect SSL warning: https://bugs.mysql.com/bug.php?id=80198

   systemctl start jira.service
   systemctl start confluence.service