Version: 51.0.0

Migration

This document describes options to migrate data from an older a9s MariaDB version to a newer one or to migrate for the same version. For example, how to migrate data from an a9s MariaDB 10.4 service instance to an a9s MariaDB 10.6 service instance.

info

Please note that before the release of MariaDB v10.6, the a9s Data Service was named a9s MySQL instead of a9s MariaDB although it was based on MariaDB from the beginning. Therefore, it is possible to migrate from the a9s MySQL to the a9s MariaDB.

Due to the changes stated above, you may encounter some differences between versions. So, in regards to the migration, you might see:

Consul DNS domain names. Example:
- a9s MariaDB 10.4 (old a9s MySQL): 00d89ed6c-mysql-0.node.dc1.consul.dsf2
- a9s MariaDB 10.6: 00d89ed6c-mariadb-0.node.dc1.consul.dsf2
Some binary names. Example:
- a9s MariaDB 10.4 (old a9s MySQL): mysql, mysqldump, and etc.
- a9s MariaDB 10.6: mariadb, mariadb-dump, and etc.

We will use the mariadb and mariadb-dump binaries for any version during the migration.

Initial Notes

This migration guide was tested between the following MariaDB versions.
- from 10.4.x to 10.4.x
- from 10.4.x to 10.6.x
- from 10.6.x to 10.6.x
- from 10.11.x to 10.11.x
- from 10.4.x to 10.11.x
- from 10.6.x to 10.11.x

Known Issues

There might be a performance downgrade when executing a dump operation against the a9s MariaDB instance.
The migration might take some time and it will depend on the network bandwidth, the virtual machines' capabilities, and the amount of data in the database.

caution

With MariaDB 10.11.x the default character set was changed from utf8mb3 to utf8mb4. There are some things to keep in mind when migration from an older version to MariaDB 10.11.x:

Migrated tables will keep their old character set
The new database will have the new character set by default
All new tables created later will also have the new character set

When to Migrate

As mentioned before, there is a performance downgrade during the migration; as this affects applications bound to the Service Instance being migrated the application developer must decide wisely when to migrate.

Migration

Pre-Requirements

Install MariaDB clients

Install the MariaDB clients following the MariaDB official documentation.

mariadb and mariadb-dump tools will be necessary.

info

Depending on how old the version of the database you will migrate from is, you might need to use older versions of these two utilities due to some loss of compatibility in newer ones.

Other Tools

BASH (some shell)
cat
gzip
openssl
sed or perl
pv Pipe Viewer (Optional)

Reverse Tunnel

The goal is to give the application developer access to the a9s MariaDB Service Instance; and for this, it is necessary to make an SSH tunnel to the a9s MariaDB Service Instances.

In the end, we want to achieve the scenario below.

                                                             (port 3306)
           /-----------------------------------------------> * MariaDB *
          /
* CF Application * (via SSH Tunnel)               [infrastructure network]
--------/-----------------------------------------------------------------
       /                                               [Developer network]
* Developer Machine * (local port 3306)

Create Tunnel

It is possible to access any of the a9s Data Services locally. That means you can connect with a local client to the service for any purpose such as debugging. Cloud Foundry (CF) provides a smart way to create SSH forward tunnels via a pushed application. For more information about this feature see the Accessing Apps with SSH section of the CF documentation.

First of all, you must have an application bound to the service. How to do this see Bind an application to a Service Instance.

info

cf ssh support must be enabled in the platform. Ask your platform operator if you are not sure.

Follow the section Create a Tunnel to The Service to create the reverse tunnel.

Get the MariaDB Dump From the Original MariaDB Service Instance

This section will focus on getting the MariaDB Service Instance's dump file. It must be done in the a9s MariaDB service instance the original data is present.

There are 2 options to do that:

Download MariaDB Dump
Manually Generate The MariaDB Dump

Download the MariaDB Dump

Make sure you set an encryption password for the backups using the service instance dashboard.
Create a backup using the dashboard.
Download the backup to your local machine.

info

The filename will be something like 00d89ed6c-1560352379373.

Decrypt the backup and write its contents to a file:

cat 00d89ed6c-1560352379373 | openssl enc -aes256 -md md5 -d -pass 'pass:<mytopsecretpassword>' | gunzip -c > mydump-original-instance.sql

info

Replace the value <mytopsecretpassword>.

Manually Generate the MariaDB Dump

Have Access to the MariaDB Original Service Instance

See the Reverse Tunnel section.

Gather the Necessary MariaDB Information

If you do not have the service instance key for the a9s MariaDB Service Instance, create one:

cf create-service-key <service-instance-name> key

Once you have the service instance key, retrieve its information.

cf service-key <service-instance-name> key
{
 "host": "00d89ed6c.service.dc1.consul.dsf2",
 "hosts": [
  "00d89ed6c-mariadb-0.node.dc1.consul.dsf2",
  "00d89ed6c-mariadb-1.node.dc1.consul.dsf2",
  "00d89ed6c-mariadb-2.node.dc1.consul.dsf2"
 ],
 "name": "00d89ed6c",
 "password": "a9s521026fd6966673f205d930213c91df35c9ec71b",
 "port": 3306,
 "uri": "mysql://a9sb45c6250f86899332c91f9237103:a9s521026fd6966673f205d930213c91df35c9ec71b@00d89ed6c.service.dc1.consul.dsf2:3306/00d89ed6c",
 "username": "a9sb45c6250f86899332c91f9237103"
}

Save the important information

name is equal to the name of MariaDB database. Referred to as <database_name> from here on out.
password is the password of the database user. Referred to as <password> from here on out.
username is the name of the database user. to as <username> from here on out.
host is the hostname of the database. Referred to as <host> from here on out.
port is the port to access the database. Referred to as <port> from here on out.

Create the Credentials File

Create a file with the necessary information to make requests to the MariaDB Service Instance.

Example (mylogin-original-instance.cnf):

[client]
# Remember the host points to localhost because of the reverse tunnel.
host=127.0.0.1
port=<port>
user="<username>"
password="<password>"
socket="/tmp/mysqld.sock"

info

Replace the value <password>, <port>, <username>.

For migration purposes, let's call this file mylogin-original-instance.cnf.

Dump Your Databases

Use the mariadb-dump tool to make the MariaDB dump file from the original service instance.

mariadb-dump --defaults-file=mylogin-original-instance.cnf --events --routines <database_name> > mydump-original-instance.sql

info

Replace the value <database_name>.

Perform the Migration

With the original service instance's dump file (mydump-original-instance.sql), you can start migrating to the new service instance.

Create a New Service Instance

Create a new a9s MariaDB Service Instance and ensure the MariaDB database is empty and it is not in use at the moment of the migration.

Have Access to the New a9s MariaDB Service Instance

See the Reverse Tunnel section.

Create the Credentials File

Create the credentials file for the new service instance.

Gather the Necessary MariaDB Information.
Create the Credentials File

For migration purposes, let's call this file mylogin-new-instance.cnf.

Close Connection (Optional)

It is advisable to close the connections to the database to make the migration. Please note, that the new a9s MariaDB service instance should not have any connections since it was recently created and is supposed to not be used before the migration.

You can verify that all open connections have been closed with the following command:

mariadb --defaults-file=mylogin-new-instance.cnf -e "show processlist;"

You may notice that in the following example only the current operation is in progress (show processlist;) and another client uses the database (but sleeps), which is okay.

+-----+------+-------------------+------+---------+------+----------+------------------+----------+
| Id  | User | Host              | db   | Command | Time | State    | Info             | Progress |
+-----+------+-------------------+------+---------+------+----------+------------------+----------+
| 595 | user | 172.28.28.5:50066 | NULL | Sleep   |  176 |          | NULL             |    0.000 |
| 600 | user | 172.28.28.5:50076 | NULL | Query   |    0 | starting | show processlist |    0.000 |
+-----+------+-------------------+------+---------+------+----------+------------------+----------+

If it is necessary to kill a connection you can use the following command:

mariadb --defaults-file=mylogin-new-instance.cnf -e "KILL CONNECTION <id>;" 2>/dev/null

info

Replace the value <id>.

Example:

mariadb --defaults-file=mylogin-new-instance.cnf -e "KILL CONNECTION 595;" 2>/dev/null

Normalize Dump File Before Restore

It is necessary to normalize the dump file because the name of the database and the database users are different between the original service instance and the new service instance. Hence, it is required to modify the dump file to use the correct values.

Rename the Database
Rename the Database Definer

Rename the Database

We have to replace the database name of the original service instance in the backup with the database name of the new service instance:

sed -i 's/<database_name_original_service_instance>/<database_name_new_service_instance>/g' mydump-original-instance.sql

# For MAC OS
# sed -i '' 's/<database_name_original_service_instance>/<database_name_new_service_instance>/g' mydump-original-instance.sql

info

Replace the values <database_name_original_service_instance> and <database_name_new_service_instance>.
Be aware this command changes the original file, therefore if it is not done right, it is possible to mess up the file. We suggest having a backup save safely.

Example:

# When the values are:
# <database_name_original_service_instance> = 00d89ed6c
# <database_name_new_service_instance> = f3c89edx3

sed -i 's/00d89ed6c/f3c89edx3/g' mydump-original-instance.sql

Rename the Database Definer

We have to replace the database definer of the original service instance in the backup with the database definer of the new service instance:

Verify if Definer Exists

grep "DEFINER=" mydump-original-instance.sql

If exists any occurrence of it we should normalize it. Thus, proceed with the next step.

Replace the Definer Values

info

Replacing the definer is only necessary when using the MariaDB dump file downloaded by the Dashboard.

Using sed.

sed -i 's/DEFINER=`<username-original-instance>/DEFINER=`<username-new-instance>/g' mydump-original-instance.sql

# For MAC OS
# sed -i '' 's/DEFINER=`<username-original-instance>/DEFINER=`<username-new-instance>/g' mydump-original-instance.sql

info

Replace the values <username-original-instance> and <username-new-instance>.

Example:

# When the values are:
# <username-original-instance> = a9se8eb52aa4d609f0ba0d98862b58c
# <username-new-instance> = a9sb45c6250f86899332c91f9237103

sed -i 's/DEFINER=`a9se8eb52aa4d609f0ba0d98862b58c/DEFINER=`a9sb45c6250f86899332c91f9237103/g' mydump-original-instance.sql

If you are a handling huge dump file, maybe you will get a better performance using perl:

Using perl.

perl -p -i.bak -e "s/(DEFINER=\`)<username-original-instance>(\`)/\1<username-new-instance>\2/g" mydump-original-instance.sql

info

Replace the values <username-original-instance> and <username-new-instance>.

Example:

# When the values are:
# <username-original-instance> = a9se8eb52aa4d609f0ba0d98862b58c
# <username-new-instance> = a9sb45c6250f86899332c91f9237103

perl -p -i.bak -e "s/(DEFINER=\`)a9se8eb52aa4d609f0ba0d98862b58c(\`)/\1a9sb45c6250f86899332c91f9237103\2/g" mydump-original-instance.sql

Verify the Replacement

grep "DEFINER=" mydump-original-instance.sql

All definers must point to the <username-new-instance>. Example:

CREATE DEFINER=`<username-new-instance>`@`%` PROCEDURE ...
CREATE DEFINER=`<username-new-instance>`@`%` PROCEDURE ...

Restore the MariaDB Dump in the New Service Instance

1 - Perform the following command:

mariadb --defaults-file=mylogin-new-instance.cnf <database_name> < mydump-original-instance.sql

info

Replace the values <database_name>.

2 - (Optional) In parallel, you can use another terminal and monitor the restoration in the MariaDB database.

watch -n2 'echo "show processlist;" | mariadb --defaults-file=mylogin-new-instance.cnf'

Following the Progress of the Restore Operation (Alternative)

If you want to see the progress of the restoration, you can install the Pipe Viewer tool and restore it using its tool. Thus, perform the following command to restore it:

pv mydump-original-instance.sql | mariadb --defaults-file=mylogin-new-instance.cnf <database_name>

info

Replace the values <database_name>.

Check the Database Consistency

Check the database consistency between the original MariaDB service instance and the new MariaDB service instance databases.

Check if the Database Exists

New Instance

mariadb --defaults-file=mylogin-new-instance.cnf -e "SHOW DATABASES;"

The <database_name> database must be present on the databases.

Check the Tables

Original Instance

mariadb --defaults-file=mylogin-original-instance.cnf <database_name_original_service_instace> -e "SHOW TABLES;"

New Instance

mariadb --defaults-file=mylogin-new-instance.cnf <database_name_new_service_instace> -e "SHOW TABLES;"

They must have the same result.

Check the Tables Checksum

For each table of the database, verify the checksum.

Original Instance

mariadb --defaults-file=mylogin-original-instance.cnf <database_name_original_service_instace> -e "CHECKSUM TABLE <database-table>;"

New Instance

mariadb --defaults-file=mylogin-new-instance.cnf <database_name_new_service_instace> -e "CHECKSUM TABLE <database-table>;"

They must have the same result.

Check the Events and Routines

Original Instance

mariadb --defaults-file=mylogin-original-instance.cnf -e "SHOW FUNCTION STATUS WHERE Db = '<database-name-original-instance>';"

mariadb --defaults-file=mylogin-original-instance.cnf -e "SHOW PROCEDURE STATUS WHERE Db = '<database-name-original-instance>';"

mariadb --defaults-file=mylogin-original-instance.cnf -e "SHOW EVENTS FROM <database-name-original-instance>;"

New Instance

mariadb --defaults-file=mylogin-new-instance.cnf -e "SHOW FUNCTION STATUS WHERE Db = '<database-name-new-instance>';"

mariadb --defaults-file=mylogin-new-instance.cnf -e "SHOW PROCEDURE STATUS WHERE Db = '<database-name-new-instance>';"

Be aware that some events might not be valid anymore. If so, they will not show in the new service

instance. An example is when events were created with scheduled dates in the past.

mariadb --defaults-file=mylogin-new-instance.cnf -e "SHOW EVENTS FROM <database-name-new-instance>;"

They must have the same result.

Initial Notes​

Known Issues​

When to Migrate​

Migration​

Pre-Requirements​

Install MariaDB clients​

Other Tools​

Reverse Tunnel​

Create Tunnel​

Get the MariaDB Dump From the Original MariaDB Service Instance​

Download the MariaDB Dump​

Manually Generate the MariaDB Dump​

Have Access to the MariaDB Original Service Instance​

Gather the Necessary MariaDB Information​

Create the Credentials File​

Dump Your Databases​

Perform the Migration​

Create a New Service Instance​

Have Access to the New a9s MariaDB Service Instance​

Create the Credentials File​

Close Connection (Optional)​

Normalize Dump File Before Restore​

Rename the Database​

Rename the Database Definer​

Verify if Definer Exists​

Replace the Definer Values​

Verify the Replacement​

Restore the MariaDB Dump in the New Service Instance​

Following the Progress of the Restore Operation (Alternative)​

Check the Database Consistency​

Check if the Database Exists​

Check the Tables​

Check the Tables Checksum​

Check the Events and Routines​

Initial Notes

Known Issues

When to Migrate

Migration

Pre-Requirements

Install MariaDB clients

Other Tools

Reverse Tunnel

Create Tunnel

Get the MariaDB Dump From the Original MariaDB Service Instance

Download the MariaDB Dump

Manually Generate the MariaDB Dump

Have Access to the MariaDB Original Service Instance

Gather the Necessary MariaDB Information

Create the Credentials File

Dump Your Databases

Perform the Migration

Create a New Service Instance

Have Access to the New a9s MariaDB Service Instance

Create the Credentials File

Close Connection (Optional)

Normalize Dump File Before Restore

Rename the Database

Rename the Database Definer

Verify if Definer Exists

Replace the Definer Values

Verify the Replacement

Restore the MariaDB Dump in the New Service Instance

Following the Progress of the Restore Operation (Alternative)

Check the Database Consistency

Check if the Database Exists

Check the Tables

Check the Tables Checksum

Check the Events and Routines