MySQL Config Options

MySQL components are configured in your boxfile.yml. All available configuration options are outlined below.

Important Notes About Configuration Changes

Whenever configuration changes are made to your MySQL boxfile.yml config, in order to apply those changes, Nanobox must provision a new MySQL node.

Changes When Working Locally

When working in dev and dry-run, this will replace the existing node, wiping all data in the database. Data will need to be re-seeded.

Changes to a Production Database

When config changes are made to a production database, a new node is provisioned and data is migrated (this process must be explicitly triggered and will not be done automatically on deploy). There will be slight downtime as data is synced between the old and new node(s), but the process is designed to minimize this as much as possible. More information is available in the Data Migrations During Scaling & Repairs doc.

Config Options

data.mysql:
  image: nanobox/mysql:5.6
  config:
    plugins:
      - federated
      - audit_log
    event_scheduler: 'Off'
    max_connections: 1024
    thread_stack: '256K'
    myisam_recover: 'DEFAULT'
    max_allowed_packet:  '16M'
    max_join_size: 9223372036854775807
    table_open_cache: 64
    query_cache_limit: '1M'
    allow_suspicious_udfs: 'Off'
    ansi: 'Off'
    audit_log: 'On'
    ft_max_word_len: 84
    ft_min_word_len: 4
    ft_query_expansion_limit: 20
    ft_stopword_file: ' '
    users:
      - username: root
        meta:
          privileges:
            - privilege: ALL PRIVILEGES
              'on': "*.*"
              with_grant: true
      - username: nanobox
        meta:
          privileges:
            - privilege: ALL PRIVILEGES
              'on': gonano.*
              with_grant: true
            - privilege: ALL PRIVILEGES
              'on': testing.*
              with_grant: true
            - privilege: ALL PRIVILEGES
              'on': blah.*
              with_grant: true
            - privilege: PROCESS
              'on': "*.*"
              with_grant: false
            - privilege: SUPER
              'on': "*.*"
              with_grant: false
          databases:
            - gonano
            - testing

MySQL Version

You should append the MySQL version number to your image with a :. The following versions are available:

  • 5.5
  • 5.6
  • 5.7

Note: Due to version compatibility constraints, MySQL versions cannot be changed after the service is created. To use a different version, you’ll have to create a new MySQL service and manually migrate data.

# default setting
data.mysql:
  image: nanobox/mysql:5.6

plugins

This allows you to specify what MySQL plugins to load into your database service. The following plugins are available:

  • archive
  • blackhole
  • federated
  • audit_log
data.mysql:
  image: nanobox/mysql
  config:
    plugins:
      - federated

Note: When using the audit_log plugin, you must also specify a audit_log setting in your Boxfile.

event_scheduler

This enables or disables MySQL’s event scheduler.

Note: Even though event_scheduler‘s default is “Off”, it can still be enabled through a SQL query in your database. Setting it to “On” just enables the event scheduler when the database is provisioned.

# default setting
data.mysql:
  image: nanobox/mysql
  config:
    event_scheduler: 'Off'

max_connections

View dev.mysql.com documentation for definition and configuration options.

# default setting
data.mysql:
  image: nanobox/mysql
  config:
    max_connections: 1024

thread_stack

View dev.mysql.com documentation for definition and configuration options.

# default setting
data.mysql:
  image: nanobox/mysql
  config:
    thread_stack: '256K'

myisam_recover

View dev.mysql.com documentation for definition and configuration options.

# default setting
data.mysql:
  image: nanobox/mysql
  config:
    myisam_recover: 'DEFAULT'

myisam_recover

View dev.mysql.com documentation for definition and configuration options.

# default setting
data.mysql:
  image: nanobox/mysql
  config:
    max_allowed_packet:  '16M'

max_join_size

View dev.mysql.com documentation for definition and configuration options.

# default setting
data.mysql:
  image: nanobox/mysql
  config:
    max_join_size: 9223372036854775807

table_open_cache

View dev.mysql.com documentation for definition and configuration options.

# default setting
data.mysql:
  image: nanobox/mysql
  config:
    table_open_cache: 64

query_cache_limit

View dev.mysql.com documentation for definition and configuration options.

# default setting
data.mysql:
  image: nanobox/mysql
  config:
    query_cache_limit: '1M'

allow_suspicious_udfs

View the dev.mysql.com documentation for definition and configuration options.

# default setting
data.mysql:
  image: nanobox/mysql
  config:
    allow_suspicious_udfs: 'Off'

ansi

View the dev.mysql.com documentation for definition and configuration options.

# default setting
data.mysql:
  image: nanobox/mysql
  config:
    ansi: 'Off'

audit_log

View the dev.mysql.com documentation for definition and configuration details. Below are the following options:

  • on
  • off
  • force
  • force_plus_permanent

Note: In order to specify a audit_log setting, you must also include the audit_log mysql plugin in your Boxfile.

data.mysql:
  image: nanobox/mysql
  config:
    audit_log: 'On'
    plugins:
      - audit_log

ft_max_word_len

View the dev.mysql.com documentation for definition and configuration options.

data.mysql:
  image: nanobox/mysql
  config:
    ft_max_word_len: 84

ft_min_word_len

View the dev.mysql.com documentation for definition and configuration options.

data.mysql:
  image: nanobox/mysql
  config:
    ft_min_word_len: 4

ft_query_expansion_limit

View the dev.mysql.com documentation for definition and configuration options.

data.mysql:
  image: nanobox/mysql
  config:
    ft_query_expansion_limit: 20

ft_stopword_file

View the dev.mysql.com documentation for definition and configuration options.

data.mysql:
  image: nanobox/mysql
  config:
    ft_stopword_file: ' '

Custom Users/Permissions/Databases

You can create custom users with custom permissions as well as additional databases.

data.mysql:
  image: nanobox/mysql
  config:
    users:
      - username: root
        meta:
          privileges:
            - privilege: ALL PRIVILEGES
              'on': "*.*"
              with_grant: true
      - username: nanobox
        meta:
          privileges:
            - privilege: ALL PRIVILEGES
              'on': gonano.*
              with_grant: true
            - privilege: ALL PRIVILEGES
              'on': testing.*
              with_grant: true
            - privilege: ALL PRIVILEGES
              'on': blah.*
              with_grant: true
            - privilege: PROCESS
              'on': "*.*"
              with_grant: false
            - privilege: SUPER
              'on': "*.*"
              with_grant: false
          databases:
          - gonano
          - testing

For each custom user specified, Nanobox will generate an environment variable for the user’s password using the following pattern:

# Pattern
COMPONENT_ID_USERNAME_PASS

# Examples

## Custom user config 1
data.mysql:
  config:
    users:
      - username: myuser

## Generated password evar 1
DATA_MYSQL_MYUSER_PASS

## Custom user config 2
data.db:
  config:
    users:
      - username: dbuser

## Generated password evar 2
DATA_DB_DBUSER_PASS

Request MySQL boxfile.yml Configs

If there’s a setting you’d like to modify that is typically handled in the my.cnf, please let us know by creating a new issue on the MySQL image project on Github.