Installing mysql5.7 for Jira

 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

 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

 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

 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

 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';

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

 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

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 |
+--------------------------+--------------------+

 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