migrate-mariadb-to-mariadbent.html.md.erb

---
title: Migrate from legacy MariaDB to MariaDB Ent
owner: Services
---


We have a completely new [MariaDB](https://docs.developer.swisscom.com/service-offerings/mariadb.html) service in store for you. It comes with many improvements (e.g. MariaDB version 10) and we recommend to upgrade as soon as possible.

<pre class="terminal">
$ 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
</pre>

*Important*: Please make sure that you have the latest [cf CLI](https://github.com/cloudfoundry/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:

<pre class="terminal">
$ cf create-service mariadbent usage my-db-new
$ cf bind-service my-awesome-app my-db-new
</pre>

Next, stop your app:

<pre class="terminal">
$ cf stop my-awesome-app
</pre>

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

On macOS, you can use [Homebrew](https://brew.sh/). For other systems, please refer to your package manager.

<pre class="terminal">
# macOS only
$ brew install mariadb
</pre>

Next, we'll use [`cf ssh`](https://docs.developer.swisscom.com/devguide/deploy-apps/ssh-services.html) 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](https://docs.cloudfoundry.org/concepts/asg.html) to be able to connect to the new MariaDB Enterprise.

<pre class="terminal">
# 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
</pre>

Now, we'll need to create [serivce keys](https://docs.developer.swisscom.com/devguide/services/service-keys.html) for our service instances to get access credentials for them. To get these, run the following commands:

<pre class="terminal">
# 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
{ ... }
</pre>

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

<pre class="terminal">
cf ssh proxy-app -L 13000:&lt;old-db-host&gt;:&lt;old-db-port&gt;
</pre>

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

<pre class="terminal">
$ mysqldump --protocol TCP --port 13000 --user &lt;old-db-username&gt; --password --single-transaction --skip-add-locks &lt;old-db-database&gt; &gt; /tmp/my-db-dump.sql
Enter password: &lt;old-db-password&gt;
</pre>

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:

<pre class="terminal">
$ tail -f /tmp/my-db-dump.sql
</pre>

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:

<pre class="terminal">
$ cf ssh proxy-app -L 13000:&lt;new-db-host&gt;:&lt;new-db-port&gt;
</pre>

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:

<pre class="terminal">
mysql --protocol TCP --port 13000 --user &lt;new-db-username&gt; --password --show-warnings &lt;new-db-database&gt; &lt; /tmp/my-db-dump.sql
Enter password: &lt;new-db-password&gt;
</pre>
Maybe you see errors or warnings. For example:

<pre class="terminal">
ERROR 1044 (42000) at line 660 in file: 'my-db-dump.sql': Access denied for user '&lt;new-db-username&gt;'@'%' to database '&lt;new-db-database&gt;'
Query OK, 0 rows affected (0.07 sec)
</pre>

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] (https://docs.developer.swisscom.com/devguide-sc/support.html).

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

<pre class="terminal">
$ cf unbind-service my-awesome-app my-db
$ cf rename-service my-db my-db-old
$ cf rename-service my-db-new my-db
</pre>

*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

<pre class="terminal">
$ cf start my-awesome-app
</pre>

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

<pre class="terminal">
# Optional
$ cf restage my-awesome-app
</pre>

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

<pre class="terminal">
$ cf delete-service-key my-db migration
$ cf delete-service-key my-db-old migration
$ cf delete-service my-db-old
</pre>

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