
This add-on is operated by JAWS DB, LLC
MariaDB, the open source drop-in replacement for MySQL now available on Heroku
JawsDB Maria
Last updated March 30, 2020
Table of Contents
- Provisioning the add-on
- Local setup
- Database Backups
- Backup/import data from JawsDB Maria or another MariaDB database
- Dashboard
- Troubleshooting
- Migrating between plans
- Removing the add-on
- Connection strategies
- Using JawsDB Maria with Rails
- Using JawsDB Maria with PHP
- Using JawsDB Maria with Python/Django
- Using JawsDB Maria with Java
- Using JawsDB Maria with Node.js
- FAQ
- Support
JawsDB Maria is an add-on for providing a fully functional MariaDB database for use with your Heroku application.
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.
JawsDB Maria is simple to use and requires no custom client libraries to interface with. Adding a JawsDB Maria plan to your Heroku app gives access to a fully-functional MariaDB database with no barriers or hoops to jump through. Connect to your instance the way you would to any other MariaDB or MySQL server.
Provisioning the add-on
Standard provisioning
JawsDB Maria can be attached to a Heroku application via the CLI:
A list of all plans available can be found here.
$ heroku addons:create jawsdb-maria
-----> Adding jawsdb-maria to sharp-mountain-4005... done, v18 (free)
Once JawsDB Maria has been added, a JAWSDB_MARIA_URL
setting will be available in the app configuration and will contain the MariaDB connection string.
Your MariaDB database will be created from scratch and will require a few minutes to become fully available. The JAWSDB_MARIA_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_MARIA_URL
mysql://username:password@hostname:port/default_schema
After installing JawsDB Maria the application should be configured to fully integrate with the add-on.
Provisioning with custom backup/maintenance windows
JawsDB Maria 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 Maria also allows users of single-tenant servers (Whitetip plans and higher) to customize these windows when the instances are provisioned using custom command parameters.
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.
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-maria --bkpwindowstart 00:30 --bkpwindowend 01:00 --mntwindowstart Tue:23:30 --mntwindowend Wed:00:00
-----> Adding jawsdb-maria 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.
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. 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.
$ heroku config -s | grep JAWSDB_MARIA_URL >> .env
$ more .env
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.
Database Backups
Like Heroku, 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.
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 takes 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
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.
Backup/import data from JawsDB Maria or another MariaDB database
This section assumes you have MariaDB or MySQL installed in a local environment where you have access to the mysqldump
and mysql
commands on a terminal.
For the purposes of this section of the tutorial, let’s assume the connection information of the old database is represented by the following.
Hostname: OLDHOST
Username: OLDUSER
Password: OLDPASS
Database: OLDDATABASE
If your connection is in a MariaDB or MySQL connection string, it would look similar to the following.
mysql://OLDUSER:OLDPASS@OLDHOST/OLDDATABASE?…
Backup existing database
The first step is to back up the existing database. To do this with mysqldump, you would issue the following command from a terminal.
$ mysqldump -h OLDHOST -u OLDUSER -pOLDPASS OLDDATABASE > backup.sql
Note that the old password must come immediately after the -p
option. You may also use -p
alone instead of -pOLDPASS
in which case you will be prompted for the password after submitting the command.
You now have a backup of your database in your terminal’s working directory in a file called backup.sql
Migrating the backup to JawsDB Maria
Your JAWSDB_MARIA_URL
configuration variable will contain the connection information in the following manner.
mysql://NEWUSER:NEWPASS@NEWHOST:3306/NEWDATABASE
To load your backup file into the new JawsDB Maria database, simply issue the following command from the same directory that you saved backup.sql
$ mysql -h NEWHOST -u NEWUSER -pNEWPASS NEWDATABASE < backup.sql
Note again how NEWPASS
must immediately follow -p
Your JawsDB Maria database will now contain the backed up data from your previous database.
Dashboard
Status information about your instance can be viewed by going to Dashboard > Resources > Add-ons and clicking JawsDB Maria. This will take you to a personalized page with information about your particular database. The dashboard can also be accessed via the CLI:
$ heroku addons:open jawsdb-maria
Opening jawsdb-maria for sharp-mountain-4005…
Databites
Once your database is listed with a status of ‘AVAILABLE’, the dashboard will allow you to create Databites. Similar to Heroku’s own “Dataclips”, Databites provide users the ability to run basic 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, 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 do not support statements that create database objects or that alter data in your database. Think of Databites as a way to create quick custom reports on any application that uses your JawsDB Maria database.
Troubleshooting
If the JAWSDB_MARIA_URL
config var is not updated after a reasonable period of time, visit the Heroku Dashboard and select your application. Then, select JawsDB Maria from the Add-ons menu. If your MariaDB connection URL is there, you can use it to manually set your DATABASE_URL
config var via the CLI or in Dashboard > Settings > Config Variables.
If it is not displayed, there was an error provisioning your database. Please contact us through the Heroku Support channels.
Migrating between plans
JawsDB Maria 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 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 ‘Backup/Import Data from another MariaDB Database’ for information on how to do this.
Application owners should carefully manage the migration timing to ensure proper application function during the migration process.
Upgrading/downgrading multi-tenant plans (Kitefin, Leopard, Blacktip)
Upgrading and downgrading between multi-tenant plans can be done through a CLI command.
Use the heroku addons:upgrade
command to migrate to a new plan.
$ heroku addons:upgrade jawsdb-maria:newplan
-----> Upgrading jawsdb-maria:newplan to sharp-mountain-4005... done, v18 ($49/mo)
Your plan has been updated to: jawsdb-maria:newplan
Automatic upgrading from a multi-tenant plan to a single-tenant plan is not currently supported. To move to a single-tenant plan, backup your existing database with a tool such as mysqldump
, provision a new single-tenant instance (using the heroku addons:create
syntax), and load the output file from mysqldump
into the new database. Make sure to update the config variable name to the name of the previous value of the multi-tenant config variable so your applications can use the new database without being reconfigured.
Upgrading single-tenant plans (Whitetip and higher)
Upgrading to a higher storage single-tenant plan can be done through a CLI command.
Non-alpha plans may be unavailable during the upgrade.
Use the heroku addons:upgrade
command to migrate to a new plan.
$ heroku addons:upgrade jawsdb-maria:newplan
-----> Upgrading jawsdb-maria:newplan to sharp-mountain-4005... done, v18 ($49/mo)
Your plan has been updated to: jawsdb-maria:newplan
Downgrading single-tenant plans (Whitetip and higher)
At this time, downgrading of single-tenant plans is not allowed through the addons:upgrade
command. To move to a lower plan, please provision a plan of the size you want and follow the steps in this documentation for backing-up/importing from a MariaDB database to move your data to the new plan. Once you confirm that your new database is functioning correctly, simply remove the original plan.
Plans supporting Encryption at Rest
Encryption at Rest (EAR) is a new feature applying transparent AES-256 encryption to the underlying data storage of a JawsDB Maria database. It is supported for new provisions and upgrades for plans that had EAR at the time of their provision. Upgrading to an EAR-supporting plan from a non-EAR plan will not bestow EAR on the upgraded server. Current users of EAR-supporting plans from prior to the EAR feature-addition would need to provision a new version of their plan and migrate in order to gain Encryption at Rest.
Removing the add-on
JawsDB Maria can be removed via the Heroku Dashboard or the Heroku CLI.
This will destroy all associated data and cannot be undone!
$ heroku addons:destroy jawsdb-maria
-----> Removing jawsdb-maria from sharp-mountain-4005... done, v20 (free)
Before removing JawsDB Maria a data backup is advised. JawsDB Maria daily backups are only available while the add-on is attached to your account.
Connection strategies
This article 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 Maria 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'
If using the mysql2
gem, use the mysql2://
scheme instead:
$ 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 Maria is now ready to use with your Rails application. Issue the following command to set up the database according to your migrations.
$ heroku run rake db:create db:migrate
Using JawsDB Maria 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 MariaDB/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 Maria 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 Maria instance.
Using JawsDB Maria 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 Maria with Node.js
You can connect to your JawsDB Maria 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 Maria.
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();
FAQ
I am unable to insert records into my database. What gives?
If you are on one of JawsDB Maria’s shared plans, you may have exceeded 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 reinstated.
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.
Cached queries do not count against the query limit of ‘max_questions’. Using cached queries is a good way around this limit.
Single-tenant plans (such as Whitetip and above) carry no ‘max_questions’ limits.
How do I access my database backups?
JawsDB Maria 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.
I get an error when trying to upgrade or downgrade my database plan. Why is that?
In some specific situations, changing plan requires a manual migration. Please check the section entitled “Migrating between plans” to find out whether this applies to your particular situation.
Support
All JawsDB Maria support and runtime issues should be submitted via one of the Heroku Support channels. Any non-support related issues or product feedback is welcome at our email address and our Twitter account.