Creating Custom MySQL Users & Databases with Nanobox

Nanobox makes it easy to provision a MySQL database with your application. All you have to do is include a data component in your boxfile.yml that uses the nanobox/mysql Docker image and Nanobox will build the database for you when developing locally and when you deploy to production. This whole process is covered in the Create a MySQL Database guide.

Learn More About Nanobox

By default, MySQL databases are provisioned with root and nanobox users and a gonano database. These are ready to go when the database starts up, so you can plan on having them and code accordingly. But what if you want custom users and/or databases? You can always create them after your database is up and running, but that means everyone else who runs the project will have to too.

Luckily, the Nanobox MySQL image lets you specify custom users and databases that get created any time your database is built.

Custom Users/Privileges/Databases

data.db:
  image: nanobox/mysql:5.7
  config:
    users:
      - username: customuser
        meta:
          privileges:
            - privilege: ALL PRIVILEGES
              'on': gonano.*
              with_grant: true
            - privilege: ALL PRIVILEGES
              'on': customdb.*
              with_grant: true
          databases:
            - gonano
            - customdb

User Config Options

The Nanobox MySQL image ships with a lot of boxfile-configurable options, one of which, users, lets you define users and databases. The following options are available for each user:

username

Defines the custom user's username.

meta

Defines the meta information for the user.

privileges

Defines the user's privileges for databases and tables.

privilege

Defines the specific MySQL user privilege allowed for the table specified in the 'on' config).

'on'

Defines the database and table (database.table) to which the privilege applies.

with_grant

Allows the user to pass their privileges to other users.

databases

Tells Nanobox to what databases to create when provisioning MySQL.

Generated User Passwords

Whenever you create a custom user, Nanobox will generate a password for the user along with an environment variable for the password using the following pattern:

# Pattern
COMPONENT_ID_USERNAME_PASS

# Examples
# - Component ID is based on the ID of the MySQL component in your boxfile.yml
# - Username uses the 'username' provided in the users config
DATA_DB_CUSTOMUSER_PASS
DATA_MYSQL_MYUSER_PASS
DATA_WHATEVER_AWESOMESAUCE_PASS

Custom Users & Databases in Practice

As a practical example, below is a boxfile.yml for a simple Node.js app that includes a MySQL database with a custom user and database.

run.config:
  engine: nodejs

web.site:
  start: yarn start

data.db:
  image: nanobox/mysql:5.7
  config:
    users:
      - username: myuser
        meta:
          privileges:
            - privilege: ALL PRIVILEGES
              'on': customdb.*
              with_grant: true
          databases:
            - customdb

I can plan on the customdb database, my custom myuser user, and the auto-generated password environment variable all existing when MySQL starts up and configure my connection accordingly.

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : process.env.DATA_DB_HOST,
  user     : 'myuser',
  password : process.env.DATA_DB_MYUSER_PASS,
  database : 'customdb'
});

That's it! Easy peasy.

Posted in MySQL