Contents

• Introduction
• Download the MySQL Client
• Migrate Your Database
  • Step 1: Stage Your Application Code Changes
  • Step 2: Declare a Maintenance Window (optional)
  • Step 3: Export Your MySQL 5.7 Database
  • Step 4: Import Your Database Into MySQL 8
  • Step 5: Update Connection String and Verify
  • Step 6: Notify the ITS Database Team
• Getting Help

Introduction

The recommended migration procedure with the command line uses the mysqldump and mysql utilities. There are different ways of obtaining these utilities and this documentation provides recommendations for Windows and MacOS. If you obtain these utilities in a different way and encounter an issue, please try the recommended procedure before contacting the ITS Database team for assistance.

The documentation for these utilities is available at:

• mysqldump - A Database Backup Program
• mysql - The MySQL Command-Line Client

The host that performs the migration must be on the campus network or connected to the campus VPN.

More information about the MySQL 5.7 retirement and migration to MySQL 8 is available at MySQL 5.7 to MySQL 8 Migration Overview.

Download the MySQL Client

To perform the migration, you will need to use mysqldump to export your MySQL 5.7 database and mysql to import it into your MySQL 8 database. If you do not already have these on the host that will be performing the migration, they will need to be installed.

For MacOS, install mysql-client via Homebrew. If you need installation assistance, contact your TSC or system administrator.

For Windows, download and install MySQL Workbench Community Edition (the utilities are included in the download). You can download without creating an Oracle Web account by clicking the No thanks, just start my download link near the bottom of the page. If you need installation assistance, contact your TSC or system administrator.

Migrate Your Database

These steps will migrate your ITS MySQL 5.7 database to ITS MySQL 8 using command line utilities. If a MySQL 5.7 database is empty in a particular environment, it does not need to be migrated to MySQL 8, however you should still verify you can connect to that environment's MySQL 8 database.

It is recommended to read these instructions in their entirety before beginning.

The examples shown in these steps will migrate a sample database from the MySQL 5.7 Dev environment to the MySQL 8 Dev environment. The same steps can be repeated for the Qual and Prod environments by changing the service URLs as appropriate. Details for the sample database in the examples below are:

Step 1: Stage Your Application Code Changes

To make the migration process smoother, stage your application code changes by inserting commented-out code that can be uncommented when you are ready to switch over to MySQL 8. The first staged change will be updating the MySQL connection string's service URL from the MySQL 5.7 URL to the corresponding environment's MySQL 8 URL. The URLs will change as follows:

Next, if your connection requires encryption, you will need to stage changes to use the new MySQL 8 SSL files, which can be downloaded from MySQL 8 SSL Files. Most applications only require the SSL CA certificate to encrypt its connection to MySQL. However, if your application used the MySQL 5.7 SSL certificate and SSL key, stage changes for these files too.

Step 2: Declare a Maintenance Window (optional)

If you do not need a maintenance window, proceed to the next step.

The migration will require downtime for your application since the application will need to be stopped in order to prevent any updates to your database while the migration is happening. If you require a maintenance window, schedule one and then wait until the window begins before continuing. The maintenance window should be scheduled so it is long enough to perform the migration, verify the migration was successful, and revert the migration if it was not successful. Small databases (less than 1 GB in size) typically take less than 10 minutes to migrate. Larger databases (several GB in size) could take 30 minutes or more. You can estimate how long your Prod migration will take by copying its data to your Dev or Qual environment and then performing a migration there.

Step 3: Export Your MySQL 5.7 Database

When you are ready to begin your migration, first stop your application to prevent it from trying to read or write to your database while it is being migrated. Then, on the host performing the migration, prepare to export the database using one of these options:

• For Windows, open a command prompt as an Administrator and navigate to where MySQL Workbench is installed. This location should contain the mysqldump and mysql command line utilities. For example:
  

  cd "C:\Program Files\MySQL\MySQL Workbench 8.0 CE"

• For Mac, open a terminal and verify mysqldump and mysql are in your path. If they are not, navigate to the bin directory for mysql-client, which should contain the mysqldump and mysql command line utilities. For example:
  

  cd /usr/local/opt/mysql-client/bin

On both Windows and MacOS, use the mysqldump utility to export your database using one of the below options (note the right-angle bracket ">" near the end of the command). The admin_user is the name of the administrative user for your database. It will usually be your 4-character department code followed by 3 digits. The valid values for service_url are mysqlprod01.austin.utexas.edu, mysqlqual01.austin.utexas.edu, or mysqldev01.austin.utexas.edu. The valid values for port are 3306, 3307, or 3308 (most customers will use 3306).

• If your connection requires encryption, include the MySQL 5.7 CA Certificate when exporting (some operating systems may also require the --ssl-mode=REQUIRED option):
  

  mysqldump -u [admin_user] -p -h [service_url] -P[port] --opt --routines --triggers --set-gtid-purged=OFF --no-tablespaces --column-statistics=0 --ssl-ca=[path_to_ca_cert] [database_name] > dump.sql

• If your connection does not require encryption, export with:
  

  mysqldump -u [admin_user] -p -h [service_url] -P[port] --opt --routines --triggers --set-gtid-purged=OFF --no-tablespaces --column-statistics=0 [database_name] > dump.sql

For example, to export the sample database over an encrypted connection, you would execute:

mysqldump -u itsy002 -p -h mysqldev01.austin.utexas.edu -P3306 --opt --routines --triggers --set-gtid-purged=OFF --no-tablespaces --column-statistics=0 --ssl-ca=C:\certs\ca-mysqldb02-cert.pem itsy002_migrate > dump.sql

Step 4: Import Your Database Into MySQL 8

You're now able to take the export of your database and import it into MySQL 8. Using the same command prompt (Windows) or terminal (MacOS), use the mysql utility to import your database using one of the below options (note the left-angle bracket "<" near the end of the command). The admin_user is the name of the administrative user for your database. It will usually be your 4-character department code followed by 3 digits. The valid values for service_url are mysqlprod02.austin.utexas.edu, mysqlqual02.austin.utexas.edu, or mysqldev02.austin.utexas.edu. The valid values for port are 3306, 3307, or 3308 (most customers will use 3306).

• If your connection requires encryption, include the MySQL 8 CA Certificate when importing (some operating systems may also require the --ssl-mode=REQUIRED option):
  

  mysql -u [admin_user] -p -h [service_url] -P[port] --ssl-ca=[path_to_ca_cert] [database_name] < dump.sql

• If your connection does not require encryption, import with:
  

  mysql -u [admin_user] -p -h [service_url] -P[port] [database_name] < dump.sql

For example, to import the sample database over an encrypted connection, you would execute:

mysql -u itsy002 -p -h mysqldev02.austin.utexas.edu -P3306 --ssl-ca=C:\certs\its-mysql-ca-cert-2023.pem itsy002_migrate < dump.sql

Step 5: Update Connection String and Verify

With the database migration complete, you can update your application code to activate your staged changes, however, keep your original MySQL 5.7 connection string by commenting it out in case you need to revert back to MySQL 5.7. The updated connection string will allow your application to connect to your MySQL 8 database. Start your application and then execute your customized Database Upgrade - Customer Testing Plan to verify your application behaves as expected with MySQL 8. It is recommended to prevent your customers from accessing your application (if possible) while testing is underway to ensure the test results are consistent.

If the results of your Customer Testing Plan show you need to revert the migration, update your application code to change back to the MySQL 5.7 connection string. This will allow your application to connect to your MySQL 5.7 database. You can then troubleshoot the issue or make application changes before trying the migration again. Note that any changes made to the data in your MySQL 8 database prior to reverting will not be reflected in your MySQL 5.7 database. Before trying to migrate again, you should delete any objects (tables, views, and routines) from your MySQL 8 database.

Step 6: Notify the ITS Database Team

Once you have verified the migration has completed successfully and you do not plan on performing any more migrations to a particular MySQL 8 environment, go to the MySQL 8 Migration Status page and notify the ITS Database team that your migration for this environment is complete. If the ITS Database team is not notified that a database migration is complete, we will send reminders as the General Migration Phase approaches its end.

Getting Help

If you have any questions regarding the MySQL 5.7 retirement or migration to MySQL 8, open a ticket to the ITS Database team by emailing db-requests@its.utexas.edu.