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.
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.dsf2a9s 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
to10.4.x
- from
10.4.x
to10.6.x
- from
10.6.x
to10.6.x
- from
10.11.x
to10.11.x
- from
10.4.x
to10.11.x
- from
10.6.x
to10.11.x
- from
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.
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.
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
orperl
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.
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
Make sure you set an encryption password for the backups using the service instance dashboard.
Download the backup to your local machine.
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
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"
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
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.
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
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
- 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
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
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
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
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>
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>';"
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.