Skip to main content
Version: Develop

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

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.

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

  1. Make sure you set an encryption password for the backups using the service instance dashboard.

  2. Create a backup using the dashboard.

  3. Download the backup to your local machine.

info

The filename will be something like 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
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​
  1. 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
  1. 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"
}
  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.

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.

  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.

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​

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.