Migrate from legacy MariaDB to MariaDB Ent

Page last updated:

We have a completely new MariaDB service in store for you. It comes with many improvements (e.g. MariaDB version 10) and we recommend to upgrade as soon as possible.

$ cf marketplace | grep mariadb
mariadb       small*, medium*, large*, free      MariaDB SQL database v5.5.4
mariadbent    usage*                             MariaDB Galera Cluster SQL database v10.1.x

Important: Please make sure that you have the latest cf CLI installed before you follow this migration guide.

First, create a new MariaDB Ent instance and bind it to your app using the old MariaDB service:

$ cf create-service mariadbent usage my-db-new
$ cf bind-service my-awesome-app my-db-new

Next, stop your app:

$ cf stop my-awesome-app

As a next step, we’ll have to install the MariaDB client (which contains mysqldump)

On macOS, you can use Homebrew. For other systems, please refer to your package manager.

# macOS only
$ brew install mariadb

Next, we’ll use cf ssh to access our services. As the proxy, you can either use an existing running app (Don’t use an app which is bound to your MariaDB service. These need to remain stopped for data consistency reasons.) in the same space or just push a dummy app if you don’t have one. This app will only be used to SSH into so we get access to our service instances.

Important: Please make sure that the app you are using has been pushed or restarted within the last month since it needs to use the latest security groups to be able to connect to the new MariaDB Enterprise.

# Only needed if you don't have a running app in your space
$ git clone https://github.com/swisscom/cf-default-app-staticfile.git
$ cd cf-default-app-staticfile
$ cf push proxy-app --no-route

Now, we’ll need to create serivce keys for our service instances to get access credentials for them. To get these, run the following commands:

# Old database
$ cf create-service-key my-db migration
$ cf service-key my-db migration
{ ... }

# New database
$ cf create-service-key my-db-new migration
$ cf service-key my-db-new migration
{ ... }

We’ll use these credentials in the following steps, so remember how to get them. Please replace the terms in <...> with the respective value from these service keys.

Next, create an SSH tunnel to your old DB through the proxy app (or any other app you intend to use).

cf ssh proxy-app -L 13000:<old-db-host>:<old-db-port>

Now that we’re connected, we can dump the old database using mysqldump. Open a new terminal and run the following command:

$ mysqldump --protocol TCP --port 13000 --user <old-db-username> --password --single-transaction --skip-add-locks <old-db-database> > /tmp/my-db-dump.sql
Enter password: <old-db-password>

If you run into an error like bash: mysqldump: command not found, please be aware that you have to run the mysqldump command on your local machine in a new terminal window.

After the dump has successfully been created, please make sure the last line is something like -- Dump completed on 2017-08-15 11:06:17 by running the following command:

$ tail -f /tmp/my-db-dump.sql

Next, create an SSH tunnel to the new MariaDB. Open the terminal where you have the SSH connection open, press ctrl+d and run the following command:

$ cf ssh proxy-app -L 13000:<new-db-host>:<new-db-port>

Now we’ll use the mysql CLI to restore the dump we created in the new MariaDB. Switch back to your other terminal and run:

mysql --protocol TCP --port 13000 --user <new-db-username> --password --show-warnings <new-db-database> < /tmp/my-db-dump.sql
Enter password: <new-db-password>

Maybe you see errors or warnings. For example:

ERROR 1044 (42000) at line 660 in file: 'my-db-dump.sql': Access denied for user '<new-db-username>'@'%' to database '<new-db-database>'
Query OK, 0 rows affected (0.07 sec)

If you do get errors, jump to the respective line number and investigate. One significant difference is that, in new MariaDB, the statement LOCK TABLES is not supported anymore.

If you need support, please ask on Stack Overflow #swisscomdev.

Now, we can unbind the old MariaDB service and rename the new database to use the real name:

$ cf unbind-service my-awesome-app my-db
$ cf rename-service my-db my-db-old
$ cf rename-service my-db-new my-db

Important: The new service will be under mariadbent instead of mariadb in the VCAP_SERVICES environment variable. If your app code uses these names in the JSON structure to bind to the app, please change it accordingly.

Now it’s time to start your app again with the new MariaDB

$ cf start my-awesome-app

For some apps, you’ll also need to do a restage so they can pick up all environment variables properly:

# Optional
$ cf restage my-awesome-app

As soon as everything is working properly, you can remove the service keys and the old database:

$ cf delete-service-key my-db migration
$ cf delete-service-key my-db-old migration
$ cf delete-service my-db-old

You have now migrated your app to the new MariaDB service. Congrats! Please repeat for all apps using the old one.