jawsDB

JawsDB Documentation

Table of Contents

Welcome to JawsDB

JawsDB is a MySQL/MariaDB Database-as-a-Service (DBaaS) provider supplying a fully functional, fully managed, relational database for use with your application. Rather than going through the hassle of hosting, configuring, patching, and managing a database, JawsDB provides one-click delivery and management of a relational database in the cloud.

JawsDB is easy to use and requires no custom coding or libraries to interact with it. Simply select a plan that meets your needs, and we will provide you with the hostname and credentials necessary to dive in and start swimming with the big fish.

MySQL

MySQL is the world’s most widely used open-source relational database and is trusted by companies such as Google, Facebook, Twitter, and Youtube. Its power, stability, security, and wealth of online documentation has cemented it as a premiere option for start-ups and established businesses alike.

MariaDB

MariaDB is a drop-in replacement and alternative to MySQL which is the world's most widely used open-source relational database and is trusted by companies such as Google, Facebook, Twitter, and Youtube. Its power, stability, security, and wealth of online documentation has cemented it as a premiere option for start-ups and established businesses alike.

note Whenever this document includes JAWSDB_URL or jawsdb for code or terminal commands, MariaDB users would use JAWSDB_MARIA_URL and jawsdb-maria, respectively.

Provisioning

Standard provisioning

JawsDB can be attached to a Heroku application via the CLI:

callout A list of all plans available can be found here.

$ heroku addons:create jawsdb
-----> Adding jawsdb to sharp-mountain-4005... done, v18 (free)

Once JawsDB has been added, a JAWSDB_URL setting will be available in the app configuration and will contain the database connection string.

note Your database will be created from scratch and will require a few minutes to become fully available. The JAWSDB_URL config variable will contain a placeholder until the database is ready.

This variable can be confirmed at any time using the heroku config:get command.

$ heroku config:get JAWSDB_URL
mysql://username:password@hostname:port/default_schema

After installing JawsDB the application should be configured to fully integrate with the add-on.

Provisioning with custom backup/maintenance windows

JawsDB instances have a daily backup period and a weekly maintenance period that is chosen randomly from set 8-hour blocks of time. These 8-hour blocks are chosen to minimize impact based on average business operation hours. However, JawsDB also allows users of single-tenant servers (Whitetip plans and higher) to customize these windows when the instances are provisioned using custom command parameters.

callout Backup windows must be at least 30 minutes long and must not overlap.

The available command parameters are --bkpwindowstart, --bkpwindowend, --mntwindowstart, and --mntwindowend. You can choose to use only the backup parameters or just the maintenance parameters but they must be used with their twin. For instance, using only --mntwindowstart will not set a custom window. It must be used with its twin, --mntwindowend as well.

callout Backup windows are hh24:mi format from 00:00 to 23:59, and maintenance windows are Day:hh24:mi format where 'Day' is one of (Sun|Mon|Tue|Wed|Thu|Fri|Sat). All times are assumed to be in the UTC Time Zone.

$ heroku addons:create jawsdb --bkpwindowstart 00:30 --bkpwindowend 01:00 --mntwindowstart Tue:23:30 --mntwindowend Wed:00:00
-----> Adding jawsdb to sharp-mountain-4005... done, v18 (free)

<a id="local-setup"></a>
## [Local setup](#local-setup)

### Environment setup

This section offers tips for using the service in your development environment.

After provisioning the add-on it’s necessary to locally replicate the config vars so your development environment can operate against the service.

> callout
> Though less portable it’s also possible to set local environment variables using `export JAWSDB_URL=value`.

Use the Heroku Local command-line tool to configure, run and manage process types specified in your app's [Procfile](procfile). Heroku Local reads configuration variables from a `.env` file. To view all of your app's config vars, type `heroku config`. Use the following command to add the JAWSDB_URL values retrieved from heroku config to your `.env` file.

```term
$ heroku config -s | grep JAWSDB_URL >> .env
$ more .env

warning Credentials and other sensitive configuration values should not be committed to source-control. In Git exclude the .env file with: echo .env >> .gitignore.

For more information, see the Heroku Local article.

Plan Changes

Most of the time, users can upgrade their JawsDB database in-place to a different plan. However, there are two notable exceptions where a manual migration of data must be performed, moving data from their existing JawsDB database to a new JawsDB database created from scratch. Those two scenarios are listed below.

  1. Upgrades from a 'Shared' plan (such as Leopard, or Blacktip) to a single-tenant plan (such as Whitetip or greater). Because shared plans exist in multi-tenant environments, the server they are on cannot be converted to a single-tenant environment, and the complex permutations of user data make it impractical to try and move user data to a single-tenant server via automated process.
  2. Downgrades from a single-tenant server plan to any other plan of lesser storage size. For liability reasons, our vendor disallows resizing database server hard disk space which could risk data truncation and/or corruption. For this reason, any downgrades in server specs (CPU, RAM, etc) can only be done in-place to plans of the same hard disk storage size. Custom plans can be created by request.

The process for manually migrating data from PlanA to PlanB could look like the following:

  1. User provisions new JawsDB database of type PlanB
  2. User backs up their data in their JawsDB PlanA database.
  3. User loads the backed-up data onto their new PlanB database.
  4. User tests PlanB database to confirm the data is present.
  5. User points their application at PlanB database, away from PlanA.
  6. Once user is satisfied with the migration, PlanA database is deleted.

In all cases where automatic up/downgrading in-place is allowed, your data will be preserved and no migration is necessary. However, it is always a good idea to backup your database before a plan change. See the section in this documentation entitled Manual Backups with mysqldump for information on how to do this.

Application owners should carefully manage the migration timing to ensure proper application function during the migration process.

Deprovisioning

JawsDB can be removed via the Heroku Dashboard or the Heroku CLI.

warning This will destroy all associated data and cannot be undone!

$ heroku addons:destroy jawsdb
-----> Removing jawsdb from sharp-mountain-4005... done, v20 (free)

Before removing JawsDB a data backup is advised. JawsDB daily backups are only available while the add-on is attached to your account.

warning All addons owned by a Heroku application are automatically removed when the application itself is removed. Please ensure your data is backed up before deleting a Heroku application!

SSO Dashboard

JawsDB provides a Dashboard where users can view important information about their Database. This information includes:

  • Connection String
  • Credentials
  • Server Status
  • Allocated Storage
  • Backup Window
  • Maintenance Window
  • Latest Restorable Time
  • SSL Information

Users can reset their Database password from the Dashboard and they may also create, view, and delete Databites.

Databites

Once your database is listed with a status of 'AVAILABLE’, the dashboard will allow you to create Databites. As a simple, yet powerful reporting tool, Databites provide users the ability to run basic (read-only) SQL queries against their provisioned database and save those queries for later execution. Once a Databite is created, it is saved to your dashboard where it can be re-run, edited, shared publicly, or deleted with ease.

On your dashboard, under the section labeled 'Databites', select “Create New Databite”. This will direct your browser to the Databite page where custom queries can be entered into the editor which features SQL syntax highlighting. Clicking “Update” underneath the editor will execute the query against your database and return the result set in the same browser window in a tabular format.

Using the “Back” link will return you to the dashboard where your Databite will now be listed in the Databite section. Clicking on an existing Databite will redirect the browser to the Databite page and re-execute the query.

For security reasons, Databites are read-only, i.e. they do not support statements that create database objects or that alter data in your database. Think of Databites as a way to create windows into the most important information stored in your database.

Server Metrics

Users of single-tenant database plans (currently: Whitetip plans and higher), have access to graphs of important database metrics for CPU, storage, and IO. They can be accessed through the dashboard on the 'Server Status' panel.

The metric data points are spaced 5-minutes apart and can be viewed for periods of:

  • The last hour
  • The last 3 hours
  • The last day
  • The last 3 days
  • The last week

Below is a brief description of the included metrics.

  • CPU Utilization - The percentage of the CPU in use. CPU usage in database applications is typically low. A consistently high value here may indicate slow queries and the need to index tables. If indexes are already optimized, the CPU on this plan may simply be underpowered for your application. A plan upgrade should be considered.
  • Read IOPS - The average number of Read-specific IO operations per second to disk. A high value here indicates there is not enough RAM available on this plan to store the application's frequently requested data in memory.
  • Write IOPS - The average number of Write-specific IO operations per second to disk.
  • Free Storage - The free storage in GB for the server.
  • Read Latency - The average time taken per read-specific IO operation on disk.
  • Write Latency - The average time taken per write-specific IO operation on disk.
  • Disk Queue Depth - The average number of outstanding IOs (read/write requests) waiting to access the disk.
  • Network Transmit Throughput - The outgoing network traffic on the server in MB.
  • Network Receive Throughput - The incoming network traffic on the server in MB.

Using these metrics can be the first step in troubleshooting performance issues and traffic spikes. While a plan upgrade will usually solve issues with poor performance, a metric performing poorly may not necessarily indicate a need to upgrade plan. Instead, performance issues can often be fixed by better indexing or optimizing queries or making modification to database structure.

Database Backups

JawsDB is based on Amazon's AWS cloud infrastructure. Because of this, our internal backups are in an Amazon proprietary format (similar to VMWare snapshots) and cannot be downloaded. These backups are taken once per day during your server's backup window and are stored for a fixed length of time while the service is active depending on the plan purchased.

Users who have purchased a single-tenant plan (Whitetip and higher) have their snapshots listed on their JawsDB dashboard. The Snapshot ID listed there, along with the Password displayed on that same dashboard page are all that is needed to restore their database to the time when the snapshot was taken. The snapshot can be restored onto that same single-tenant plan, OR another purchased single-tenant plan. The requirement is that whichever JawsDB plan is used to host the restored snapshot must be of equal or greater storage capacity than the snapshot.

Warning: Restoring a snapshot replaces the host plan's server entirely. Including its own snapshots.

Example 1: A user purchases a Whitetip instance and needs to revert to a snapshot. The user notes the Snapshot ID of the snapshot they wish to restore to and places it in the Snapshot ID field of the form labeled Restore from Snapshot in their JawsDB Dashboard. They also take the password from their dashboard and place it in that same form's Password field. After clicking the Restore button, the Snapshot is restored onto a 2nd new single-tenant host. After the restore is complete, the user's current server is replaced entirely by the server made from the snapshot. The snapshots originally listed on the Dashboard page are now replaced by those of the newly created server.

Example 2: A user purchases a Thresher instance and wishes to restore a snapshot but without replacing their current Thresher instance. The user purchases a second plan of at least Thresher value (we'll use Tiger for this illustration), and notes the desired snapshot's Snapshot ID along with the Password of the first Thresher server. The user then navigates to the JawsDB Dashboard of the new blank Tiger server, and enters the noted Snapshot ID and Password of the Thresher instance into the relevant fields of the form labeled Restore from Snapshot and hits the Restore button. The snapshot is then restored onto a 3rd new single-tenant host. After the restore is complete, the user's newly created Tiger instance is replaced entirely by the server made from the snapshot. The snapshots originally listed on the Tiger plan's Dashboard page are now replaced by those of the newly created server.

Customers are also free to take and keep their own backups using mysqldump or some other service or utility.

Below we provide a quick primer on how to use mysqldump to create and restore backups.

Manual Backups and Migrations with mysqldump

mysqldump is a powerful client utility packaged with the MySQL clients available on the MySQL downloads page. With this utility, users can backup their database into flat files as well as restore those backups to any MySQL or compatible server of their choice.

In order to complete either operation, the connection credentials and information of the relevant server(s) must be know in advance.

Connection strings provided by JawsDB contain all of the relevant info in the following format:

mysql://username:password@hostname:port/database

username, password, hostname, port, and database will be referenced in the following sections.

The mysqldump tutorials below are simplistic and general. The full range of options and configurations that mysqldump supports can be seen here and in the tool's own man pages.

Manual Backups

The following command will backup the specified database into a local file called backup.sql

mysqldump -h hostname -u username -ppassword --no-tablespaces --set-gtid-purged=OFF database > backup.sql

Note There is no space between the -p flag and password

Manual Migrations

Backup files created with mysqldump can be loaded onto a target server the way any SQL file would be loaded. Below is a way to do this with the mysql command-line utlity.

mysql -h hostname -u username -ppassword database < backup.sql

Note again how there is no space between the -p flag and password

If receiving errors about SUPER permissions, try removing any clauses that mention a DEFINER or any unnecessary @@ variables from the backup file. Some suggestions here

FAQ

I am unable to insert records into my database. What gives?

If you are on one of JawsDB’s shared plans, you may have breached the database size allotted via your plan. When a database grows larger than the plan allows, INSERT privileges are revoked on the database user until the database is brought back into compliance. SELECT, UPDATE, and DELETE privileges are still available during this time. Once the database size is reduced back under the maximum allowed size, the INSERT privilege will be granted back.

The following can be run against the database or placed into a databite in order to see current database size in MB and whether the account currently has INSERT privileges.

select
    s.schema_name
    ,sp.grantee user
    ,cast(round(sum(coalesce(t.data_length + t.index_length, 0)) / 1024 / 1024, 3) as char) db_size_mb
    ,sp.has_insert
from
    information_schema.schemata s
    inner join
    information_schema.tables t on s.schema_name = t.table_schema
    inner join (
        select
            spi.grantee
            ,spi.table_schema
            ,max(
                case
                    when spi.privilege_type = 'INSERT' then 1
                    else 0
                end
            ) has_insert
        from
            information_schema.schema_privileges spi
        group by
            spi.grantee
            ,spi.table_schema
    ) sp on s.schema_name = sp.table_schema
group by
    s.schema_name
    ,sp.grantee
    ,sp.has_insert;

I am getting an error saying I have exceeded the 'max_questions’ resource. What is that?

The 'max_questions’ value is a limit that is placed on shared plan accounts to help preserve computing power for other shared plan users on the same server. It is essentially a limit on the number of queries that can be executed from that account in an hour. After 1 hour of reaching the limit, the limit will be reset and queries can again be executed.

Plan Max Questions
Single-tenant plans No Limit
Kitefin 3,600/hr
Leopard 18,000/hr
Blacktip 36,000/hr

How do I access my database backups on a shared plan?

JawsDB backups are in an Amazon-proprietary snapshot format and must be restored by the JawsDB team. Please see the section entitled Database Backups for more information.

Why do I get an error when attempting to change my plan?

When switching between shared plans (Kitefin Leopard, Blacktip) and single-tenant plans (Whitetip+), changing plan requires a manual migration because the data needs transfer to another server. Please check the section entitled Plan Changes to find out whether this applies to your particular situation.

Does JawsDB support Encryption at Rest?

Encryption at Rest (EAR) is a feature applying transparent AES-256 encryption to the underlying data storage of a JawsDB database and is available on a subset of our plans. It does not encrypt your data in transit, but only when it is at rest on the server. SSL should be used for encrypting traffic in transit.

Check the desired plan's description to see if EAR is supported.

Support

Heroku Account support and application runtime issues should be sent via the Heroku Support Channels.

Database specific questions and issues can be sent to JawsDB's support email address.

For news, non-critical questions, and feedback, check out the @jawsdb Twitter account