Migrate from MariaDBEnt to MySQL

Page last updated:

We have a completely new MySQL Service in store for you. It comes with dedicated clusters and many improvements. We recommend to move on as soon as possible.

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

First, create a the new service instances according to your needs: MySQL Service

Next, stop your app:

$ cf stop my-awesome-app

As a next step, we’ll have to install mysqldump and mysql-cli which are contained in mysql package.

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

# macOS only
$ brew install mysql

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 MariaDBEnt 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 MySQL service.

# 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 (make sure to use a database (without mtls) and not a cluster service-instance)
$ 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 MySQL service. 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 MySQL service. 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, that PRIMARY_KEY is now enforced on each table. Further restrictions you find in MySQL Service Limitations

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

Now, we can unbind the old MariaDBEnt 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 mysql-database instead of mariadbent 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 MySQL database

$ 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 MySQL service. Congrats! Please repeat for all apps using the old one.