MySQL Services

Apcera provides the MySQL Service Gateway for integrating with MySQL database providers. This section describes how to implement MySQL services using the mysql service type, including:

Using MySQL services

You can integrate with a MySQL database using the MySQL Service Gateway. The mysql service type provides a bound job with a MYSQL_URI environment variable that speaks the MySQL protocol and implements a semantic pipeline.

To create a MySQL service using the MySQL Service Gateway you need to register a MySQL provider, or use one provided by Apcera. A provider represents a managed MySQL instance, including information and credentials to connect to and manage that instance. For each mysql service you create with the MySQL Service Gateway a new database is created on the target MySQL server with its own schema, a new user for accessing that database, and a new password for that user.

To connect to an existing MySQL database (external to your Apcera cluster) you can provide a trusted root certificate when registering the MySQL provider (see Connecting to an existing MySQL database using the MySQL service gateway). The root certificate is used by the MySQL semantic pipeline and service gateway to verify the connection with the external database. If you don't require a semantic pipeline or secure connection to the database you can use the Generic Service Gateway (see Connecting to an existing MySQL database using the generic service gateway).

You can also create an internal MySQL database as a job using an Apcera Package, Docker image, or Apcera Capsule.

Semantic pipeline for MySQL protocol

Apcera provides a semantic pipeline for the MySQL protocol against which you can write rules to monitor, inject logic, and control behavior within the communication channel in real time.

When you register a MySQL service using the MySQL Service Gateway a new database is created for you on the MySQL provider. When you bind a job to that service a semantic pipeline is created to broker the connection between the job and the service (which gives you ephemeral credentials).

If you need to connect to an existing MySQL database and have that connection brokered by the MySQL semantic pipeline, you can provide a trusted root certificate when creating the provider. If you don't require a semantic pipeline, you can use the Generic Service Gateway.

Requirements for setting up MySQL providers

To create a MySQL database provider, we require one user that has enough privileges for creating new users and new databases. The MySQL server name (or IP address), user name, and password are specified in the --url parameter when you create a provider for that MySQL server.

The reason that the user has to be able to create new users and databases is because each service created from a provider creates a new database on the MySQL server, a new user for accessing that database, and a new password for that user.

Registering MySQL providers

The following example demonstrates how to register a MySQL provider. The type parameter is required to determine the service gateway with which to register the provider. The specified credentials must have sufficient privileges to create new users and databases on the MySQL instance (see Requirements for setting up MySQL providers).

apc provider register mysql --type mysql --url mysql://user:password@example.us-west-2.rds.amazonaws.com:3306/ --description 'MySQL (RDS)'

The MySQL service gateway attempts to connect to the MySQL instance with the specified authentication credentials. If it's unable to connect an error is displayed in the console, for example:

Registering provider "mysql"... error
Error: unable to connect to "example.us-west-2.rds.amazonaws.com:3306": dial tcp: lookup example.us-west-2.rds.amazonaws.com on 169.254.0.8:53: no such host

Creating MySQL services and bindings

The following example demonstrates how to create a MySQL service and binding.

apc service create mydb --provider /apcera/providers::mysql --job sample-todo

Creating a MySQL service from an Apcera Package

Starting with the Apcera Platform release 2.0.0, the Apcera Community Edition will bootstrap servers and providers for NFS, MySQL, and Postgres. The MySQL and Postgres providers use the NFS service for persistence, so the databases persist even if you restart the containers that are bound to them. You can use these providers to create services and bind these services to jobs.

Creating a MySQL service from the MySQL provider creates a new, unique database that can be bound to jobs. All jobs bound to that service will share that unique database.

To verify if you have the MySQL provider type:

apc provider show /apcera/providers::mysql-provider --batch

You should see the mysql-provider MySQL provider.

If you don't see it, you can create a MySQL package using the mysql-5.6.25-apc1.conf package script and then create the provider from that. This package script can be downloaded from Github. Download a copy to get started.

Down around line 72 is the line:

MYSQL_ROOT_PASSWORD="root"

Change this to the database root password you want to use.

Note there's also a build warning at the beginning of the file about giving the compiler more memory. Heeding this warning you follow the instructions:

apc job update /apcera/stagers::compiler --memory 4G --disk 8G

Then build and upload the package with:

apc package build mysql-5.6.25-apc1.conf --name mysql-5.6.25-mine

Once it's built, verify that you have this package installed:

apc package show /apcera/pkg/packages::mysql-5.6.25-mine

You should see the package listed. To create a MySQL server within your cluster type:

apc app from package mysqlserver --package /apcera/pkg/packages::mysql-5.6.25-mine --disable-routes --batch --start-cmd "sudo /opt/apcera/mysql-5.6.25/bin/start_mysql.sh"

That builds the app and tells it that when it starts, it should run the script /opt/apcera/mysql-5.6.25/bin/start_mysql.sh as root.

Now update the app and start it:

apc app update mysqlserver --memory 512MB --disk 1GB --batch
apc app update mysqlserver --port-add 3306 --allow-egress --allow-ssh --batch
apc app start mysqlserver --batch

Create a provider for the MySQL server, using your own root password for MY_ROOT_PW.

apc provider register mysqlprovider --type mysql --job mysqlserver --port 3306 --url mysql://root:MY_ROOT_PW@ms-app --batch

At this point, data on the MySQL server is living inside of a container, and will go away if the container restarts or if the IM is rebooted. To give MySQL persistent storage that will be there no matter where on the cluster the database starts, create an NFS service from the apcfs NFS provider:

apc service create mysql-nfs --provider /apcera/providers::apcfs --batch --description="MySql service backed by NFSv4"

Bind the NFS service to the MySQL server APP:

apc service bind mysql-nfs --job mysqlserver --batch -- --mountpath /opt/apcera/mysql-5.6.25/data

You've now created a MySQL server with an NFS backend for your data. You can start creating databases for your apps using:

apc service create mysql-service --provider mysqlprovider --batch

Bind that service to apps that need a database:

apc service bind mysql-service --job my-node-todo-app

Creating a MySQL service from a Docker image

In addition to integrating with an external MySQL DB, you can also create a job in Apcera that hosts a MySQL DB and service-enable it. In this case the MySQL DB is running within your Apcera cluster as an internal service.

The following example demonstrates how to create a MySQL service using the official MySQL Docker image. Because you are deploying this MySQL DB as a job running in a container in the system, we back it with the APCFS NFS provider for persistence.

1) Use APC to download and run the official MySQL Docker image and bind it to the NFS provider:

apc docker run docker-mysql-server --restart always --image mysql --tag 5.7.11 --port 3306 --provider /apcera/providers::apcfs --env-set MYSQL_ROOT_PASSWORD=password

NOTE: You must have policy in place that allows you to pull Docker images. See Docker Policy Examples.

2) Register the Docker job as a provider:

apc provider register mysql-docker-provider --job docker-mysql-server -u mysql://root:password@docker-mysql-server

3) Create a service for the provider and bind a job:

apc service create mydb --provider mysql-docker-provider --job sample-todo

NOTE: The job "sample-todo" is an example name of an existing job.

Creating a MySQL service from a capsule

You can install a database such as MySQL on a capsule, create an app from the capsule, register the app as an internal provider, and then use this provider to create a service that jobs can bind to.

To do this you complete the following workflow:

1) Create a capsule.
2) Update the capsule OS, install MySQL, and configure the DB.
3) Snapshot the capsule to create a package.
4) Create an app (job) from the package.
5) Add the MySQL port and start the job.
6) Register the app as a provider.
7) Create a MySQL service for the provider and a binding.

By default capsules are ephemeral and do not persist data. However, using the app from package approach in this example, if the provider job goes down the MySQL Server application is persisted.

Create a capsule running Ubuntu:

A capsule is a lightweight container running an operating system. By default it has no connections outside itself. You must specify its connections explicitly.

apc capsule create mysql-cap -i linux --allow-egress

The --allow-egress option (or -ae) gives the capsule access to external IP addresses.

Install MySQL server on the capsule:

First, log in to the capsule (as root by default).

apc capsule connect mysql-cap

Next, update the packages and install the mysql-server package to act as the provider process.

# apt-get update && apt-get install mysql-server -y

Note: It may take several minutes to install MySQL from the apt repositories.

Lastly, when it's done, rewrite MySQL connectivity rules to be less picky about what connections it accepts.

# sed -i "s/bind-address\t\t= 127.0.0.1/bind-address = 0.0.0.0/1" /etc/mysql/my.cnf &&
service mysql restart &&
mysql -u root -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION; FLUSH PRIVILEGES;" &&
service mysql restart &&
exit

As in the Docker example, we rely on semantic pipelines to ensure secure connections.

Optionally, verify that MySQL is installed using the following sequence of commands:

# mysql 			// Connects to MySQL, displays version
mysql> help			// Displays MySQL help
mysql> quit			// Quits MySql
# exit 				// Exits the capsule OS

Snapshot the capsule:

apc capsule snapshot mysql-cap

This command creates a package. In the result, note the package name.

Create an app from the package:

Create a job from the package and include the MySQL start command. Disable routes so port 0 is not applied.

The syntax is as follows:

apc app from package <app-name> -p <pkg-name> [optional-args]

For example:

apc app from package mysql-app -p snapshot-mysql-cap-142670904 -dr --start-cmd 'sudo /bin/sh /usr/bin/mysqld_safe'

Where snapshot-mysql-cap-142670904 is the package name of the capsule snapshot, the -dr option disables routes, and 'sudo /bin/sh /usr/bin/mysqld_safe' is the MySQL server start command.

Expose the default mysql-server listening port:

apc app update mysql-app --port-add 3306

Start the job:

apc app start mysql-app

Promote the job to be a provider:

apc provider register mysql-provider --job mysql-app -u mysql://root:password@mysql-app

You now have a fully-functional MySQL provider. At this point you can delete the capsule using apc capsule delete.

The last step is to create a service for the provider:

apc service create mysql-service --provider mysql-provider

And bind a job to it:

apc service bind mysql-service --job my-node-todo-app

Run apc job show to verify that the app is connecting to the MySQL job via the service you created.

Connecting to an existing MySQL database

If you need to connect to an existing database on an external MySQL server you have two options:

Connecting to an existing MySQL database using the MySQL service gateway

To use the provided MySQL service gateway to connect to an external MySQL database you must provide a root certificate in PEM format when registering the provider. For example, the following APC command registers a new MySQL provider (external-mysql-db) with the specified connection string and uploads the specified PEM file (cacert.pem):

apc provider register external-mysql-db --type mysql \
--url mysql://user:password@db.example.com:3306 \
--rootcerts cacert.pem
Registering provider "external-mysql-db"... done
Success!

When registering a provider in the Web Console the Add a Provider form includes a Choose PEM File button that opens a file browser for you to select a root certificate:

PEM Upload

You can then create a service from the provider and bind the service to a job, as usual:

apc service create todos_db --provider external-mysql-db
apc service bind todos_db --job=demo-node-todo

Creating the service binding also creates a semantic pipeline job to broker the connection between the job (demo-node-todo, in this case) and the database. The semantic pipeline is configured to use the root certificate you uploaded when creating the provider to verify its connection to the external database.

Connecting to an existing MySQL database using the generic service gateway

If you don't require a semantic pipeline to broker the connection between the job and Postgres database, you can the generic service gateway to create a connection to the database. Suppose that your MySQL server is running on 192.168.10.28 and listening for connections on port 3306. The database name is unladen_swallow, the login name for the database is arthur, and the user's database password is coconut9mercia. You also have a Apcera cluster using the subnet 192.168.96.0/20 (netmask 255.255.240.0).

On the database server, grant access to arthur so that he can log in from the Apcera Platform subnet. MySQL does not understand the /20 netmask notation, but it does understand /255.255.240.0.

mysql> grant all on unladen_swallow.* to arthur@'192.168.96.0/255.255.240.0' identified by 'coconut9mercia';

Using apc, create a generic service for this database on the Apcera Platform:

apc service create myulsdb --type generic -- --url "mysql://arthur:coconut9mercia@192.168.10.28:3306/unladen_swallow"

The values above come from:

  • Apcera service name: myulsdb
  • DB User: arthur
  • DB Password: coconut9mercia
  • MySQL DB IP: 192.168.10.28
  • MySQL Port: 3306 (default for MySQL)
  • MySQL Database name: unladen_swallow

Within an app or job bound to your MySQL service there's an environment variable called MYSQLDB_URI which contains the URI of your MySQL DB. Normally you'd deploy an app, bind that your MySQL database service, and have your app parse the MYSQLDB_URI environment variable to get the user name, password, host IP, and database name that your app needs to use to connect to the database.

However, if you just want to verify that you can connect to the DB from the Apcera Platform, try creating a capsule and connect to the database manually:

apc capsule create test-mysql-capsule --allow-egress --image ubuntu --batch
apc service bind myulsdb --job test-mysql-capsule --batch
apc capsule connect test-mysql-capsule

Once you're logged into the capsule (as root), install the MySQL client:

apt-get update
apt-get install mysql-client

Get the database connection information:

echo $MYSQLDB_URI

Then connect:

mysql -u arthur -p -h 192.168.10.28 unladen_swallow

Enter your password when prompted. You should be able to enter SQL commands and interact with the database.

Backing up a MySQL database

To back up a MySQL database you need a couple of things:

  • A MySQL server to back up.
  • A container with the mysql-client package installed on it to do the backup.
  • A script to back up your database that uses the backup options you want to use.
  • Persistent storage where you can store the backups, so your backups don't disappear if you restart the container or reboot its host server.

Let's assume that:

First create a storage service from the NFS provider just to store our backups:

apc service create mysql-backup-storage --provider /apcera/providers::apcfs --description "Storage for mysql-service backups" --batch

Just so you can understand what is going on, create a capsule and bind it to both the MySQL service and the NFS service, then connect to the capsule:

apc capsule create mysql-backup-capsule --allow-egress --image ubuntu --batch
apc service bind mysql-service --job mysql-backup-capsule --batch
apc service bind mysql-backup-storage --job mysql-backup-capsule --batch -- --mountpath /backups/mysql-service
apc capsule connect mysql-backup-capsule

Once you log in type df -k. You'll see the NFS file system mounted on /backups/mysql-service. This is where you will be storing your backups.

Try typing env | grep MYSQL. You'll see environment variables set up with the connection information for your MySQL database. The one we'll be using to back up your data is MYSQL_URI.

There's a sample app for backing up MySQL databases on Github. Log out of the capsule and (download the files from this directory onto your laptop.)[https://github.com/apcera/sample-apps/tree/master/mysql-backup]. You can also use rsync to grab the entire directory:

rsync -av https://github.com/apcera/sample-apps/tree/master/mysql-backup/ ~/mysql-backup/

There are two bash scripts:

  1. bash_start.sh creates a crontab file /etc/cron.d/backup, starts a monitoring loop, and starts cron.
  2. mysql_backup.sh is executed once an hour by cron. It backs up the MySQL server that's bound to the app and saves the backup onto the NFS service that's bound to the app.

The mysql_backup.sh script pulls the database credentials from the MYSQL_URI environment variable, makes a backup of the database, compresses the output, and stores the data on the NFS file system.

The mysqldump program is what actually creates the backups. It dumps the entire database into a text file as a series of all of the SQL commands needed to create the database and load all of the data. If you unzip any of the backup files you can see the SQL commands required to restore that version.

The mysql_backup.sh script uses a number of common mysqldump options. Make sure that you are using the right options for the MySQL database that you are backing up.

To create the backup app:

cd ~/mysql-backup/
apc app create mysql-backup-app --allow-egress

Bind the database that you want to back up to the app:

apc service bind mysql-service --job mysql-backup-app

Create and bind the NFS service to the app, mounting the NFS file system at /backups/mysql-service:

apc service create mysql-backup-storage --provider /apcera/providers::apcfs --description "Storage for mysql backups"
apc service bind mysql-backup-storage --job mysql-backup-app --batch -- --mountpath /backups/mysql-service

Next, start the app as follows:

apc app start mysql-backup-app

Once the script starts it outputs a URL, which is an http route to the backup capsule. Connect to this URL with a browser and it displays the number of seconds since the last successful backup or since the app restarted, whichever is less. If a backup hasn't happened in over a day and a half it throws a 500 error and displays the number of seconds since the last successful backup. You should check this URL with your monitoring system so you are alerted if backups stop working for any reason.

Since the NFS mount point is also mounted on mysql-backup-capsule you can list the contents of /backups/mysql-service on that capsule as well, and see the backups that have been made.

If you need to change the frequency of the backups, or the mysqldump parameters, or anything else, feel free to modify the code in mysql-backup and generate a new app. If you have multiple databases that you need to back up create a separate app to back up each database and give the apps unique names.

Restoring a MySQL database from a backup

So now you've got backups running, how do you restore a backup?

  • Create a new MySQL database from your MySQL provider (a new, empty database).
  • Create a new capsule for restoring backups.
  • Bind the new database to the new capsule.
  • Mount the NFS backup volume on the new capsule so you have copies of all of the backups.
  • Log into the capsule and install mysql-client tools.
  • Pick the backup you want to restore, unzip it, and restore it to the new database.

Create a new MySQL database from your MySQL provider:

apc service create mysql-restore --provider mysql-provider --batch

Create a new capsule for restoring backups, connect the new database, mount the NFS backup volume, and connect to the capsule.

apc capsule create mysql-restore-capsule --allow-egress --image ubuntu --batch
apc service bind mysql-restore --job mysql-restore-capsule --batch
apc service bind mysql-backup-storage --job mysql-restore-capsule --batch -- --mountpath /backups/mysql-service
apc capsule connect mysql-backup-capsule

Install mysql-client tools:

apt-get update
apt-get install mysql-client

Get the db credentials from the MYSQL_URI environment variable.

echo $MYSQL_URI

The URI reveals the credentials as user : password @ host : port / database.

Pick the backup you want to restore from /backups/mysql-service, gunzip it, and restore it to the new database:

gunzip /backups/mysql-service/$FILE_TO_RESTORE.gz
mysql --user $USER --port $PORT --host $HOST -p $DATABASE < /backups/mysql-service/$FILE_TO_RESTORE

(Enter the password when prompted)

The database backup will be restored on the new database server and you'll be dumped back at a bash prompt. To verify that the backup was actually restored, log into the database and run a few queries to verify that all is well:

mysql --user $USER --port $PORT --host $HOST -p $DATABASE

Migrating a MySQL database from one data center to another data center

Now that you know how to backup and restore, migrating is just a matter of making a backup, copying it from the persistent storage at one data center to the persistent storage at another data center, and restoring the backup into a new, fresh database service.