Skip to main content
Version: Develop

Forking and Migration

This document describes the options to fork a service instance from a specific backup or migrate data to a new Service Instance that could be running the same version or a newer one of MariaDB.

Fork a Service Instance.

To fork an existing a9s MariaDB Service Instance from a sepcifc backup, you can follow the instructions in Fork a Service Instance

caution

As of the current release, This approach for forking is only tested when forking to a new instance that uses the same GA version.

Manual Migration

This section describes how to manually migrate data from a Service Instance running an older a9s MariaDB version to one running a newer version, or to a Service Instance running the same version.

For example, how to migrate data from an a9s MariaDB 10.6 Service Instance to an a9s MariaDB 10.11 service instance.

Migration Paths
  • This migration guide was tested between the following MariaDB versions.
    • 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.

Prerequisites

The following prerequisites are necessary:

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 to migrate from is, it might be necessary to use older versions of these two utilities due to some loss of compatibility in newer ones.

Other Tools

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

Create an SSH Tunnel

Create a 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)

It is possible to access any of the a9s Data Services locally. This means that a local client can connect to the Service Instance 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 Cloud Foundry documentation.

First of all an application must be bound to the Service Instance. For more information, see Bind an application to a Service Instance.

info

cf ssh support must be enabled in the platform. Ask the Platform Operator for further details.

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 the MariaDB Dump

  1. Ensure that encryption password for the backups using the Service Instance dashboard has been set.

  2. Create a backup using the dashboard.

  3. Download the backup to the local machine.

info

The filename will a name similar to 00d89ed6c-1560352379373.

  1. 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

Manually Generate the MariaDB Dump

It is necessary to have access to the Original a9s MariaDB Service Instance. For more information, see the Reverse Tunnel section.

Follow the steps below to gather the necessary information from the a9s MariaDB Service Instance:

  1. If there is no Service Instance key for the a9s MariaDB Service Instance, create one:
cf create-service-key <service-instance-name> key
  1. Once the Service Instance key is ready, retrieve its information.
cf service-key <service-instance-name> key
Output
{
"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"
}
  1. 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. For migration purposes, this file will be called mylogin-original-instance.cnf.

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"

Dump the 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

Perform the Migration

With the original Service Instance's dump file (mydump-original-instance.sql), migration to the new Service Instance can start.

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.

  1. Gather the Necessary MariaDB Information.

  2. 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.

Verify that all open connections have been closed with the following command:

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

It is worth noting 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 the following command can be used:

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

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

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
  • 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.

  1. Verify if the definer exists:

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

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

  2. 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

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

When a handling huge dump file, a better performance might be achieved by 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
  1. 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
  1. (Optional) Another terminal and monitor the restoration in the MariaDB database in parallel.
watch -n2 'echo "show processlist;" | mariadb --defaults-file=mylogin-new-instance.cnf'
Following the Progress of the Restore Operation (Alternative)

To see the progress of the restoration, the Pipe Viewer tool can be installed and used to restore the database, via the following command:

pv mydump-original-instance.sql | mariadb --defaults-file=mylogin-new-instance.cnf <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 - Command
mariadb --defaults-file=mylogin-new-instance.cnf -e "SHOW DATABASES;"
info

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

Check the Tables

Original Instance - Command
mariadb --defaults-file=mylogin-original-instance.cnf <database_name_original_service_instace> -e "SHOW TABLES;"
New Instance - Command
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 - Command
mariadb --defaults-file=mylogin-original-instance.cnf <database_name_original_service_instace> -e "CHECKSUM TABLE <database-table>;"
New Instance - Command
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 - Commands
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 - Commands
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>';"
info

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.