This add-on is operated by JAWS DB, LLC

MariaDB, the open source drop-in replacement for MySQL now available on Heroku

Last updated July 21, 2022

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.

Provisioning

Standard provisioning

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

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

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

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

$ heroku config:get JAWSDB_MARIA_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.

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.

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

## 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_MARIA_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_MARIA_URL values retrieved from heroku config to your `.env` file.

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

For more information, see the Heroku Local article.

Plan Changes

JawsDB provides situational support for automatic upgrades and downgrades between plans. See the below sections for when an up/downgrade may be possible. In all cases where automatic up/downgrading is allowed, your data should be preserved. 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.

Depending on whether a desired plan is a single-tenant or multi-tenant (Shared) plan and whether the storage of the desired plan is greater or less than the current plan, a user may be able to change their plan automatically by following the relevant process flow of the platform where JawsDB was purchased.

In order to tell whether an automatic plan-change is supported, please reference the table below keeping in mind the current plan and the desired plan.

Deprovisioning

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

$ heroku addons:destroy jawsdb
-----> Removing jawsdb-maria 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.

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 --no-tablespaces -h hostname -u username -ppassword database > backup.sql

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

If using MySQL v.8, you may need to add –column-statistics=0 to your mysqldump command

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.

HINT: Cached queries do not count against the query limit of 'max_questions’. Taking advantage of the cache is a good way around this limit.

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?

In some specific situations, changing plan requires a manual migration. 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 new 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

Connection strategies

This section will provide examples for how to connect in several popular languages. The most common method will be to update your DATABASE_URL environment variable with the value stored in the JAWSDB_MARIA_URL variable, but one can also parse the JAWSDB_MARIA_URL variable directly if desired.

Using JawsDB with Rails

Ruby on Rails applications can begin using their provisioned database by resetting their DATABASE_URL environment variable with the value held by their JAWSDB_MARIA_URL. Heroku will autogenerate the database.yml changes needed to connect from there. This can be done from your app’s Dashboard page under 'Settings’. It can also be done through the command line interface.

First, grab the current JAWSDB_MARIA_URL from your Heroku account.

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

Next, use the command line interface to set your DATABASE_URL parameter to the value returned by the previous command.

$ heroku config:set DATABASE_URL=mysql://username:password@hostname:port/default_schema

$ heroku config:set DATABASE_URL=mysql2://username:password@hostname:port/default_schema

It is generally recommended to use the mysql2 gem instead of the mysql gem. To install this gem, include the following line in your Gemfile.

gem 'mysql2'

Update application dependencies with bundler.

$ bundle install

JawsDB is now ready to use with your Rails application. Issue the following command to setup the database according to your migrations.

$ heroku run rake db:create db:migrate

Using JawsDB with PHP

Connection information for connecting via PHP can be extracted from the JAWSDB_MARIA_URL configuration variable in the following way.

$url = getenv('JAWSDB_MARIA_URL');
$dbparts = parse_url($url);

$hostname = $dbparts['host'];
$username = $dbparts['user'];
$password = $dbparts['pass'];
$database = ltrim($dbparts['path'],'/');

These variables can then be used in your preferred method for connecting to a MySQL database. MySQLi and PDO methods are shown below.

MySQLi (Object-Oriented Example)

// Create connection
$conn = new mysqli($hostname, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connection was successfully established!";

MySQLi (Procedural Example)

// Create connection
$conn = mysqli_connect($hostname, $username, $password, $database);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connection was successfully established!";

PDO

try {
    $conn = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
    }
catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }

Using JawsDB with Python/Django

As with Rails, Python/Django applications can begin using their provisioned database by resetting their DATABASE_URL environment variable with the value held by their JAWSDB_MARIA_URL. Heroku will autogenerate the settings.py changes needed to connect from there. This can be done from your app’s Dashboard page under ‘Settings’. It can also be done through the command line interface.

First, grab the current JAWSDB_MARIA_URL from your Heroku account.

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

Next, use the command line interface to set your DATABASE_URL parameter to the value returned by the previous command.

$ heroku config:set DATABASE_URL='mysql://username:password@hostname:port/default_schema'

No extra work is needed. All subsequent deployments of your application will use your new JawsDB instance.

Using JawsDB with Java

Java using JDBC

The connection information necessary for creating a JDBC connection is all in the JAWSDB_MARIA_URL and can be extracted with code like the following.

private static Connection getConnection() throws URISyntaxException, SQLException {
    URI jdbUri = new URI(System.getenv("JAWSDB_MARIA_URL"));

    String username = jdbUri.getUserInfo().split(":")[0];
    String password = jdbUri.getUserInfo().split(":")[1];
    String port = String.valueOf(jdbUri.getPort());
    String jdbUrl = "jdbc:mysql://" + jdbUri.getHost() + ":" + port + jdbUri.getPath();

    return DriverManager.getConnection(jdbUrl, username, password);
}

Using JawsDB with Node.js

You can connect to your JawsDB database using Node.js via the node-mysql module. Below is an example of how to do this using the environment variable that is set automatically by JawsDB.

var mysql = require('mysql');
var connection = mysql.createConnection(process.env.JAWSDB_MARIA_URL);

connection.connect();

connection.query('SELECT 1 + 1 AS solution', function(err, rows, fields) {
  if (err) throw err;

  console.log('The solution is: ', rows[0].solution);
});

connection.end();