AWS MariaDB 10.x to MySQL 5.7 “downtime” upgrade instructions using mysqldump
Article ID: 297405
Updated On:
Products
Issue/Introduction
Prerequisites
- Tanzu Application Service (TAS) 2.6 or higher
- Existing RDS database instance running MariaDB 10.0, 10.1, 10.2, 10.3, and 10.4
- The ability to create a new RDS database instance running MySQL 5.7
- A jumpbox with at least 50 GB free, and mysql client and mysqldump
This procedure was tested using mysql and mysqldump clients packaged on Ops Manager 2.10.7. For best experience, use the following versions of these clients:
- mysql Ver 15.1 Distrib 10.4.17-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
- mysqldump Ver 10.18 Distrib 10.4.17-MariaDB, for debian-linux-gnu (x86_64)
For the procedure to migrate Ops Manager BOSH director, please refer to the following article: AWS MariaDB 10.1 upgrade procedure for BOSH Director.
TAS Service Availability
The following lays out TAS Service availability during this procedure.
Platform API Availability
Platform APIs may not be available during the procedure, see the specific details below.
UAA API
Unauthenticated users will not be able to log in during the migration. Users with valid CF CLI access tokens or Apps Manager sessions will continue to be able to perform read-only actions.
Implicit and auth-code token grants cannot be processed in read-only mode, but client credentials grants will be successful. If you need emergency read-only admin access to CF while in read-only mode, we suggest:
- Logging into the CF CLI using the "autoscaling_api" client (whose password can be retrieved from Ops Manager)
- Another client with the "cloud_controller.admin_read_only" scope. You need to have authenticated at least once as that client prior to the database being placed in read-only mode to be able to do this (otherwise the Cloud Controller will error when trying to create the user).
CloudFoundry API
Assuming you have a valid API token, you will still be able to read platform state. However, there may be some minor issues reading platform state during the migration. For example, the number of running app instances may appear as "?" during the migration. This does not affect running instances of apps.
Once the source database is placed in read-only mode it will no longer be possible to update system state. This includes operations such as "cf push", "cf scale", "cf start/stop", etc.
CF SSH
Since using “cf ssh” depends on the UAA auth-code flow, you will be unable to use “cf ssh” while the database is in read-only mode.
CredHub API
It is unlikely that applications need access at runtime (after startup) to CredHub. Applications that depend on CredHub may be unable to restart during the migration.
Autoscaling
Autoscaling is going to be paused because the Autoscaler cannot scale apps in read-only mode.
Eventual consistency "weird" behavior
While groups of VMs are being redeployed using the target database, such as CloudController, some write requests will begin to succeed again. These requests will write their data to the target writable database, and later read requests that happen to hit the source read-only database will not be able to see that newly written data. This should resolve quickly once all members of an instance group have been redeployed. We do not anticipate any dangerous side effects during this time.
Platform App Availability
Standard configurations of TAS should not experience any application downtime or routability downtime during the migration. Diego Cells and edge routers are not affected by the database migration and therefore, applications already running should remain running and routable.
Apps Using UAA for SSO
Because implicit and auth code grants do not work with a read-only UAA, users of SSO-based apps will not be able to create new sessions. This would likely cause these types of applications to experience downtime.
Resolution
Procedure
Note: This procedure only covers migrating the TAS tile databases. Please check for other, co-located databases before proceeding.
1. Verify that you have a MariaDB 10.x RDS (source) instance and a MySQL 5.7 RDS (target) instance. Make sure that the MySQL instance you create is at least as large as your MariaDB instance.
2. Put your source MariaDB in read only mode.
WARNING: After this point, the platform APIs will be partially unavailable. See the section above for more information.
3. If the source MariaDB is currently using the "default" parameter group, you need to first create an editable parameter group.
Create a new parameter group using the AWS CLI:
aws rds \ create-db-parameter-group \ --db-parameter-group-name mariadb-read-only --db-parameter-group-family mariadb10.1 \ --description "MariaDB read only"
4. Assign this new parameter group to the source MariaDB 10.1 instance:
(Option 1) Using AWS Console GUI:
c. Click on the source MariaDB 10.1 instance
d. Click on “Modify”
e. In the “Database options” section, set the “DB parameter group” to the parameter group you just created, it should be in the dropdown.
f. Click “Continue” at the bottom
g. Select “Apply immediately”
h. Click on “Confirm”
(Option 2) Using AWS CLI:
aws rds \ modify-db-instance \ --db-instance-identifier <db id> \ --db-parameter-group-name mariadb-read-only \ --apply-immediately
5. The parameter group status should soon say “applying”. You can see the status by opening the “Configuration” tab for your database instance and looking for the “Parameter group” field.
If the status gets to “Pending reboot”, reboot your DB.
(Option 1) Using AWS Console GUI:
Click on Actions > Reboot > Confirm
(Option 2) Using AWS CLI:
aws rds reboot-db-instance --db-instance-identifier <db id> When the reboot is done, the parameter group status should be “in-sync”
6. Update the source MariaDB's parameter group to enable read_only mode.
(Option 1) Using AWS Console GUI:
a. Open the RDS service
b. Click on “Parameter groups” on the left pane
c. Click on the parameter group assigned to the source MariaDB instance
d. Click on “Edit parameters”
e. Type “read_only” in the filter bar
f. Locate the “read_only” parameter and set its value to “1”. You might see other parameters with the string “read_only” in their names, such as innodb_read_only or tx_read_only, but you can ignore them.
g. Click “Save changes” at the top
(Option 2) Using AWS CLI:
aws rds modify-db-parameter-group \ --db-parameter-group-name mariadb-read-only \ --parameters "ParameterName='read_only',ParameterValue=1,ApplyMethod=immediate"
The status of the parameter group in the DB instance configuration page should now say “applying”.
Wait for the status to be “in-sync”. It should only take a few seconds but you might need to refresh the AWS console page after a couple seconds to see that the status was updated
7. SSH to Ops Manager for use of mysqldump and mysql utilities.
Create the dump of the MariaDB database. We recommend you run this on a jump box on the same network as the database with a fast disk.
To validate row count for your tables, run the following command:
~$ mysql -h <MY TARGET DB HOSTNAME> -u admin -p -e "SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '<DATABASE NAME>';"
Note: You can run this command at the end of the migration to confirm it was successful. The counts might be slightly different because of how the counts are approximated. To get a precise count for a table t in the database db, then run the command: SELECT COUNT(*) FROM db.t;
8. Create the dump of your MariaDB schema:
mysqldump \ --single-transaction \ -h <MY SOURCE DB HOSTNAME> \ -u <MY SOURCE DB ADMIN USERNAME> \ -p \ --databases account app_usage_service autoscale ccdb credhub diego locket networkpolicyserver nfsvolume notifications routing silk uaa \ > /tmp/MariaDB_dump.sql
9. Populate the target MySQL 5.7 database by running the created dump:
mysql \ -h <MY TARGET DB HOSTNAME> \ -u <MY TARGET DB ADMIN USERNAME> \ -p \ < /tmp/MariaDB_dump.sql
10. In Ops Manager, execute the following steps:
c. Update the “Hostname” field to now point to the MySQL 5.7 DB
d. Update the “TCP port” to be the one you chose for the DB (normally 3306)
e. Specify usernames and passwords that can access each of the databases on the target MySQL
f. Click on “Save”
g. Ops Manager will verify that it can connect to the databases, and there shouldn’t be any errors
h. Navigate to the main page
i. Click on “REVIEW PENDING CHANGES”
j. Ensure that the ALL errands are selected in the “ERRANDS” section. (TAS apps need to be re-pushed)
k. Click on “APPLY CHANGES”
l. Wait for the redeploy to finish and then you are all done
Rollback Procedure
If there is an issue during the migration and you need to roll back to the source MariaDB database, you will need to return the source MariaDB to be writable.
1. Disable read_only mode for source MariaDB's parameter group:
a. In AWS Console, open the RDS service
b. Click on “Parameter groups” on the left pane
c. Click on the parameter group assigned to the source MariaDB instance
d. Click on “Edit parameters”
e. Type “read_only” in the filter bar
f. Locate the “read_only” parameter and set its value to “0”. You might see other parameters with the string “read_only” in their names, such as innodb_read_only or tx_read_only, but you can ignore them.
g. Click “Save changes” at the top
2. The status of the parameter group in the database instance configuration page should now say “applying”.
3. Wait for the status to be “in-sync”. It should only take a few seconds but you might need to refresh the AWS console page after a couple seconds to see that the status was updated
What about migrating from AWS MariaDB v10.2 or v10.3?
While we designed this originally for AWS MariaDB 10.1 only, we've tried this procedure migrating v10.3 to MySQL 5.7 and it works. Feel free to use it on v10.2 or v10.3 MariaDB instances as well.
Feedback
