Carolina CloudApps: Upgrade / Migrate MySQL or MariaDB

This article covers how upgrade or migrate MySQL and MariaDB.

Prerequisite

• WordPress is the database connected application used in this example, but the process will work for other applications as well.
• Download and install the OC command line tools. Instructions are available here.
• If running on a Windows operating system, you may also want to reference our document for how to use rsync in Windows.

Local Machine Steps

Create a folder to hold database backup.

mkdir ~/mysql_backup

From your terminal, login to Cloudapps and list pods with the oc get pods command.

oc get pods

Open a remote shell to the MySQL pod with a status of "Running", using the oc rsh command.

oc rsh wordpress-mysql-1-nnrd6

Make note of the database name, username, and password, we will reuse these later. From inside the pod, we can view the environment variables to obtain these.

echo $MYSQL_USER
echo $MYSQL_PASSWORD
echo $MYSQL_DATABASE

Create a directory in the remote pod to dump the mysql backup and perform database backup. In this example WordPress will be the name of the database to export.

mkdir /var/lib/mysql/data/mysql_backup
mysqldump --add-drop-table -u root wordpress > /var/lib/mysql/data/mysql_backup/db_backup.sql

Note: If you're not sure of the database's name you can list all databases by logging into mysql as the root user and running the show databases query.

mysql -u root
mysql> show databases;

Exit the remote shell with the exit command. You should then no longer be "inside" the pod's terminal, and instead on your local machine.

Back on your local machine, copy the backup directory from the mysql pod to your local directory.

oc rsync mysql-5-pod:/var/lib/mysql/data/mysql_backup/ ~/mysql_backup/

Deploy new mysql 8.0-el8 database with persistent storage. If needed, please see this help article for assistance with creating a new database.

Deploy with the same database name (wordpress in this example), the same username, and the same password as the old mysql database.

MySQL root user Password can be left auto generated

List the pods again, and find the new database pod.

oc get pods

Now, open a new remote shell to the new MySQL 8 pod, and create a directory to copy the database export.

oc rsh mysql-8-pod
mkdir /var/lib/mysql/data/mysql_backup

Run the following MySQL commands to get the database ready for connections, then exit the sessions (note the two exits, one is to exit the MySQL session, the other is to exit the remote shell session).

mysql -u root
mysql> ALTER USER 'MYSQL_USER' IDENTIFIED WITH mysql_native_password BY 'MYSQL_PASSWORD';
exit
exit

Copy the database backup to the new MySQL 8 pod, open a remote shell and import the backup into mysql

oc rsync ~/mysql_backup/ mysql-8-pod:/var/lib/mysql/data/mysql_backup/
oc rsh mysql-8-pod

Open a remote shell with oc rsh to the new mysql 8 pod. wordpress is the name of the database in this example. It may be different in your setup.

mysql -u root wordpress < /var/lib/mysql/data/mysql_backup/db_backup.sql

Connecting Your Application to the New database

There are several ways your application may be connected to the database, environment variables, deployment configurations, or source code. Let's look at a common way: deployment configurations.

Right-click on your application, not the database pod, and select Edit Deployment Config.

Navigate to the Environment tab

Update the DATABASE_SERVICE_NAME variable with the name of your newly deployed database service.

The DATABASE_SERVICE_NAME variable is the way WordPress templates connect to the database.

This may be different based on how your application connects.

If you don't remember what you named the service you can find it under the Administrator menu → Networking → Services

If you have any questions or comments about the Carolina CloudApps platform, please send an email to cloudapps@unc.edu or submit a CloudApps Service Request.