MySQL

Last Updated October 2014

MySQL puts the M in LAMP stack and is a tried-and-true staple of open-source projects. Creating and using MySQL databases on Pagoda Box is extremely simple, but there are some things to take note of. After reading this doc, you will be familiar with:

  • How to create a MySQL database
  • How to connect to your database
  • Things to know when using MySQL on Pagoda Box

Creating a MySQL Database

Creating a MySQL database can be done either in your dashboard or from your Boxfile.

Through the Dashboard

To create a MySQL database through your dashboard, click on "New Data Service," then click the MySQL icon.

You're given the option to name your database and select which MySQL version you'd like use. If no name is provided, we will create one for you. You're also give a choice of "topology" which defines the architecture of your MySQL service. More on this below and in the Service Topologies doc.

Click "Create Service" to begin provisioning your MySQL database.

Through the Boxfile

To create a MySQL database through the Boxfile, include a database service with the type specified as mysql. You can also specify a topology, but it isn't required. More on topologies below.

MySQL Database in the Boxfile YAML

  database1:
    type: mysql
    topology: single
/Boxfile

On your next deploy, the database will be created.

Configuring Your MySQL Database

All environment configuration is handled in the Boxfile. The MySQL, MariaDB, & Percona Settings in the Boxfile doc walks through all of the configurable MySQL options.

Connecting to Your MySQL Database

Connecting services in your application to a MySQL database isn't any different on Pagoda Box than any other host. Each database has specified connection credentials used to connect your codebase to your database. You can get the credentials in the App Dashboard. Simply click on your database service and go to the "Connection" tab.

There are two approaches to including connections credentials in your codebase – hard-coding the credentials or using the auto-generated environment variables.

Hard-Coding Credentials

You can hard-code your database connection credentials into your codebase, but there are some drawbacks to this approach:

  1. Connection credentials are viewable in your repo - On Pagoda Box, this doesn't compromise the security of your database.

  2. You have to create your database before you can get the credentials - To hard-code your database credentials, you first need to know what they are and you can't do that until you create the database. This isn't a critical problem. It's more of a workflow issue.

Using Automated Environment Variables

Whenever you create a MySQL database on Pagoda Box, we automatically generate environment variables for each connection credential:

DATABASE1_NAME
DATABASE1_USER
DATABASE1_PASS
DATABASE1_HOST
DATABASE1_PORT

Note: The service ID (DATABASE1) changes depending on which database you're connecting to.

Including environment variables in place of connection credentials in your codebase allows you to avoid storing the actual credentials in your repo and sets connection details before the database is even created. If you launch a database from your Boxfile with the credential environment variables in place, on the next deploy, your database will be created and your app will automatically be able to connect to it.

Check out the Environment Variables doc for more information.

Managing Your MySQL Database

You have a few options when it comes to managing the data inside your MySQL database. The following docs walk through each:

Through a Pagoda Tunnel
Through an SSH Connection
Installing phpMyAdmin in Your App (Not Recommended)

Making MySQL Redundant

Data stored in your database is incredibly important and ideally will always be available. Adding redundancy to your MySQL service is the best way to ensure your data is always available. To make your MySQL database redundant, use the "redundant" topology when creating your database.

If your database has already been created, the topology can be changed in your dashboard.

With the Redundant topology, a second MySQL instance is provisioned and master-master replication between the two instances. If the primary instance every fails or becomes unresponsive, the second will act as a hot failover and take over handling requests until the primary instance can be repaired and/or restarted.

Things to Know When Using MySQL

Avoid Using MyISAM Tables

MyISAM was once the default storage engine for MySQL but has since been replaced with InnoDB, mainly because of MyISAM's loss of integrity at higher concurrency. MyISAM is non-transactional, meaning it cannot queue or manage concurrent queries. If a MyISAM table is ever updated by multiple queries at once, it will lock the table and potentially your entire database. We discourage using MyISAM tables unless you really understand how they work and how to mitigate the risk of table locks while using them.

MyISAM Tables Not Allowed in Redundant Databases

Becuase MyISAM is non-transactional, it cannot be used in a redundant architecture. The constant reads and writes performed in the sync operation would cause constant table locks, resulting in an almost non-functional database. Because of this, MyISAM tables cannot be created or used in redundant MySQL databases.

MySQL Loves RAM

Whenever you create a new MySQL database, it's RAM usage will typically be higher than expected. This is totally normal. We've found MySQL will use most of the available RAM until around 128mb become available.

To be as performant as possible, MySQL stores as much of your data as in can in RAM. Because of this, a general rule of thumb when using MySQL is to have at least enough RAM to hold your entire uncompressed dataset.

MySQL Loves to Hold On to Resources

Many times you'll find that long after a spike in resource usage, your MySQL will continue to "run hot" for an extended period of time. Whenever MySQL is under load and using a lot of resources, it holds onto those resources in the form of buffer and cache, even after the demand is long gone. So even though your MySQL database isn't under heavy load, it's resource usage will still appear to be high. If demand does pick up again, MySQL will drop whatever it's holding in the buffer and cache to make resources available for increased resource usage.

Auto-Increment is Set to 2 in Redundant MySQL Databases

For redundant MySQL databases on Pagoda Box, the auto-increment is set to 2 and cannot be changed. This is done to prevent any conflicts between the two running MySQL instances.

If you have any questions, suggestions, or corrections, let us know.