Chapter 4. Configuring an external MySQL database Important When you externalize databases from a Red Hat 3scale API Management deployment, this means to provide isolation from the application and resilience against service disruptions at the database level. The resilience to service disruptions depends on the service level agreements (SLAs) provided by the infrastructure or platform provider where you host the databases. This is not offered by 3scale. For more details on externalizing of databases offered by your chosen deployment, see the associated documentation. Red Hat supports 3scale configurations that use an external MySQL database. However, the database itself is not within the scope of support. This guide provides information for externalizing the MySQL database. This is useful where there are several infrastructure issues, such as network or filesystem, using the default system-mysql pod. Prerequisites Access to an OpenShift Container Platform 4.x cluster using an account with administrator privileges. A 3scale instance installation on the OpenShift cluster. See Installing 3scale API Management on OpenShift . An external (that is not part of the 3scale installation) MySQL database, configured according to the External MySQL database configuration . To configure an external MySQL database, perform the steps outlined in the following sections: External MySQL database configuration Externalizing the MySQL database Rolling back 4.1. External MySQL database configuration When creating an external MySQL database, you need to configure it as explained below. MySQL database user The connection string that is used to configure the database connection (see System database secret to learn where to configure the connection string) for the external MySQL database must be in the following format: {DB_PASSWORD} and {DB_PORT} are optional. The user with username {DB_USER} must be created and granted all privileges to the database indicated as {DB_NAME} . Example commands for creating a user: In case of a new installation of 3scale, if the database {DB_NAME} does not exist, it will be created by the installation scripts. Binary logging configuration In case binary logging is enabled on the MySQL server, and the database user doesn't have the SUPER privilege, the global system variable log_bin_trust_function_creators must be set to 1 . This is required because 3scale uses stored procedures and triggers. Alternatively, if you choose to set SUPER privilege for the database user, note that it is deprecated as of MySQL 8.0 and will be removed in a future version of MySQL. See MySQL documentation for more information. 4.2. Externalizing the MySQL database Use the following steps to fully externalize the MySQL database. Warning This will cause downtime in the environment while the process is ongoing. Procedure Login to the OpenShift node where your 3scale On-premises instance is hosted and change to its project: Replace <user> , <url> , and <3scale-project> with your own credentials and the project name. Follow the steps below in the order shown to scale down all the pods. This will avoid loss of data. Stop 3scale On-premises From the OpenShift web console or from the command line interface (CLI), scale down all the deployment configurations to zero replicas in the following order: apicast-wildcard-router and zync for versions before 3scale 2.6 or zync-que and zync for 3scale 2.6 and above. apicast-staging and apicast-production . system-sidekiq , backend-cron , and system-searchd . 3scale 2.3 includes system-resque . system-app . backend-listener and backend-worker . backend-redis , system-memcache , system-mysql , system-redis , and zync-database . The following example shows how to perform this in the CLI for apicast-wildcard-router and zync : Note The deployment configuration for each step can be scaled down at the same time. For example, you could scale down apicast-wildcard-router and zync together. However, it is better to wait for the pods from each step to terminate before scaling down the ones that follow. The 3scale instance will be completely inaccessible until it is fully started again. To confirm that no pods are running on the 3scale project use the following command: The command should return No resources found . Scale up the database level pods again using the following command: Ensure that you are able to login to the external MySQL database through the system-mysql pod before proceeding with the steps: <system_mysql_pod_id> : The identifier of the system-mysql pod. The user should always be root. For more information see External MySQL database configuration . The CLI will now display mysql> . Type exit , then press return . Type exit again at the prompt to go back to the OpenShift node console. Perform a full MySQL dump using the following command: Replace <system_mysql_pod_id> with your unique system-mysql pod ID . Validate that the file system-mysql-dump.sql contains a valid MySQL level dump as in the following example: Scale down the system-mysql pod and leave it with 0 (zero) replicas: Find the base64 equivalent of the URL mysql2://root:<password>@<host>/system , replacing <password> and <host> accordingly: Create a default 'user'@'%' on the remote MySQL database. It only needs to have SELECT privileges. Also find its base64 equivalents: Replace <password> with the password for 'user'@'%' . Perform a backup and edit the OpenShift secret system-database : URL : Replace it with the value from [step-8] . DB_USER and DB_PASSWORD : Use the values from the step for both. Send system-mysql-dump.sql to the remote database server and import the dump into it. Use the command to import it: Use the command below to send system-mysql-dump.sql to the remote database server and import the dump into the server: Ensure that a new database called system was created: Use the following instructions to Start 3scale On-premises , which scales up all the pods in the correct order. Start 3scale On-premises backend-redis , system-memcache , system-mysql , system-redis , and zync-database . backend-listener and backend-worker . system-app . system-sidekiq , backend-cron , and system-searchd 3scale 2.3 includes system-resque . apicast-staging and apicast-production . apicast-wildcard-router and zync for versions before 3scale 2.6 or zync-que and zync for 3scale 2.6 and above. The following example shows how to perform this in the CLI for backend-redis , system-memcache , system-mysql , system-redis , and zync-database : The system-app pod should now be up and running without any issues. After validation, scale back up the other pods in the order shown . Backup the system-mysql DeploymentConfig object. You may delete after a few days once you are sure everything is running properly. Deleting system-mysql DeploymentConfig avoids any future confusion if this procedure is done again in the future. 4.3. Rolling back Perform a rollback procedure if the system-app pod is not fully back online and the root cause for it could not be determined or addressed after following step 14 . Edit the secret system-database using the original values from system-database-orig.bkp.yml . See [step-10] : USD oc edit secret system-database Replace URL , DB_USER , and DB_PASSWORD with their original values. Scale down all the pods and then scale them back up again, including system-mysql . The system-app pod and the other pods to be started after it should be up and running again. Run the following command to confirm all pods are back up and running: USD oc get pods -n <3scale-project> 4.4. Additional information For more information about 3scale and MySQL database support, see Red Hat 3scale API Management Supported Configurations .

Commands:
mysql2://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}
CREATE USER 'exampleuser'@'%' IDENTIFIED BY 'examplepass'; GRANT ALL PRIVILEGES ON exampledb.* to 'exampleuser'@'%';
oc login -u <user> <url> oc project <3scale-project>
oc scale deployment/apicast-wildcard-router --replicas=0 oc scale deployment/zync --replicas=0
oc get pods -n <3scale_namespace>
oc scale deployment/{backend-redis,system-memcache,system-mysql,system-redis,zync-database} --replicas=1
oc rsh system-mysql-<system_mysql_pod_id> mysql -u root -p -h <host>
oc rsh system-mysql-<system_mysql_pod_id> /bin/bash -c "mysqldump -u root --single-transaction --routines --triggers --all-databases" > system-mysql-dump.sql
head -n 10 system-mysql-dump.sql -- MySQL dump 10.13 Distrib 8.0, for Linux (x86_64) -- -- Host: localhost Database: -- ------------------------------------------------------ -- Server version 8.0 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */;
oc scale deployment/system-mysql --replicas=0
echo "mysql2://root:<password>@<host>/system" | base64
echo "user" | base64 echo "<password>" | base64
oc get secret system-database -o yaml > system-database-orig.bkp.yml oc edit secret system-database
mysql -u root -p < system-mysql-dump.sql
mysql -u root -p -se "SHOW DATABASES"
oc scale deployment/backend-redis --replicas=1 oc scale deployment/system-memcache --replicas=1 oc scale deployment/system-mysql --replicas=1 oc scale deployment/system-redis --replicas=1 oc scale deployment/zync-database --replicas=1
oc edit secret system-database
oc get pods -n <3scale-project>