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
- Semantic pipeline for MySQL protocol
- Requirements for setting up MySQL providers
- Registering MySQL providers
- Creating MySQL services and bindings
- Creating a MySQL service from an Apcera Package
- Creating a MySQL service from a Docker image
- Creating a MySQL service from a capsule
- Connecting to an existing MySQL database
- Backing up a MySQL database
- Restoring a MySQL database from a backup
- Migrating a MySQL database from one data center to another data center
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
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:firstname.lastname@example.org: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
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
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:
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 packageapproach 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
-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
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]
apc app from package mysql-app -p snapshot-mysql-cap-142670904 -dr --start-cmd 'sudo /bin/sh /usr/bin/mysqld_safe'
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
apc job showto 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:
- Use the MySQL service gateway to create a provider and upload a root certificate for your MySQL database. The MySQL service gateway uses the root certificate to validate its connection to the MySQL server when creating services (database tables) and service binding. The MySQL semantic pipeline uses the root cert to validate its connection to the database. See Connecting to an existing MySQL database using the MySQL service gateway.
- Use the generic service gateway to create a connection to the database. In this case the connection from the job to database is not secure and is not brokered by a MySQL semantic pipeline. See Connecting to an existing MySQL database using the generic service gateway.
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:email@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:
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
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
mysql> grant all on unladen_swallow.* to arthur@'192.168.96.0/255.255.240.0' identified by 'coconut9mercia';
apc, create a generic service for this database on the Apcera Platform:
apc service create myulsdb --type generic -- --url "mysql://arthur:firstname.lastname@example.org: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:
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:
- We have built a MySQL provider on the cluster named
- We've created a service from this provider called
- We've bound this service to a bunch of jobs which store their data in the database managed by the
- We want to back up the database from this service.
- We also have a persistent filesystem provider called
apcfsthat provides persistent, NFS-backed storage.
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.
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
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:
bash_start.shcreates a crontab file
/etc/cron.d/backup, starts a monitoring loop, and starts cron.
mysql_backup.shis 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.
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.
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.
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
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
- 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
apt-get update apt-get install mysql-client
Get the db credentials from the
MYSQL_URI environment variable.
The URI reveals the credentials as
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.