Using MySQL 8.0 with Gateway 10

MySQL 8.0 is required for Gateway version 10.0+. This section helps you understand the overall process to install or upgrade to Gateway 10.0 and configure MySQL 8.0 server. The links provided in this process refer to detailed documentation and the MySQL 8.0 specific-instructions are listed again in this section for your quick reference.
2
Install Gateway (Software)
Install Gateway following the instructions available here.
To configure MySQL 8.0 for Gateway, ensure that you add the lines below in
/etc/my.cnf
and restart MySQL server. Add
disable-log-bin
only if clustering is not enabled; otherwise add
log-bin-trust-function-creators=1
as shown below:
character-set-server=utf8 default-authentication-plugin=mysql_native_password disable-log-bin [or] log-bin-trust-function-creators=1
Upgrade Gateway (Software)
Upgrade Gateway following the instructions available here.
Ensure that you upgrade Gateway before upgrading MySQL. For MySQL 5.7 to 8.0 in-place upgrade, refer to Oracle documentation.
When upgrading Gateway with MySQL database, ensure that you add the below lines in
/etc/my.cnf
and restart MySQL server. Add
disable-log-bin
only if clustering is not enabled; otherwise add
log-bin-trust-function-creators=1
as shown below:
character-set-server=utf8 default-authentication-plugin=mysql_native_password disable-log-bin [or] log-bin-trust-function-creators=1
Upgrade Appliance Gateway with External MySQL
If you are using an external MySQL database and need to upgrade it, see
Upgrade Gateway with MySQL Database
section in Install Upgrade Files for RHEL/CentOS.
In CentOS7, update my.cnf file to ensure that all the parameters in
[mysqld_safe]
are considered and updated.
Manage JDBC Connections
To update details of a JDBC connection, edit the JDBC connection properties and specify the following parameter in the
JDBC URL
field to allow the public key retrieval from the server for securing the password transportation between client and server:
allowPublicKeyRetrieval=true
If you have already created users in MySQL, add the following line in
/etc/my.cnf
of MySQL server, restart MySQL, and then recreate the users:
default-authentication-plugin=mysql_native_password
If you need to create users in MySQL, add the above line in
/etc/my.cnf
of MySQL server, restart MySQL, and then create the users.
Known Issue: MySQL 8.0 Upgrade from 9.4 to 10.0
Issue:
MySQL 8.0 removed NO_AUTO_CREATE_USER SQL mode(MySQL issue). When we take a backup from MySQL 5.7 and upgrade to MySQL 8.0, we get an issue related to 'sql_mode' that can't be set to the value of 'NO_AUTO_CREATE_USER'.
Workaround:
Use the following command to remove 'NO_AUTO_CREATE_USER' in dump file by simple space:
sed -i "s/NO_AUTO_CREATE_USER//g" mysql_dump.sql