MySQL, MariaDB, & Percona Settings in the Boxfile

Last Updated October 2014

The Boxfile is a yaml config file that allows you to custom-configure your app's environment. The following configuration options available in the Boxfile allow you to tailor-fit the build of your MySQL database specific to your needs. After reading this, you should understand how to:

  • How to customize your MySQL build
  • Bask in the awesomeness of MySQL and Pagoda Box

Overview of MySQL Boxfile Settings YAML

  database1:
    type: mysql
    version: 5.5
    stability: production
    topology: redundant
   
    # All settings below apply to MySQL, Percona, & MariaDB services
    mysql_plugins:
      - federated
      - audit_log
    mysql_event_scheduler: 'Off'
    mysql_max_connections: 1024
    mysql_thread_stack: '256K'
    mysql_myisam_recover: 'DEFAULT'
    mysql_max_allowed_packet:  '16M'
    mysql_max_join_size: 9223372036854775807
    mysql_table_open_cache: 64
    mysql_query_cache_limit: '1M'
    mysql_allow_suspicious_udfs: 'Off'
    mysql_ansi: 'Off'
    mysql_audit_log: 'On'
    mysql_ft_max_word_len: 84
    mysql_ft_min_word_len: 4
    mysql_ft_query_expansion_limit: 20
    mysql_ft_stopword_file: ' '
/Boxfile

MySQL Configuration Options

Type & Version

When configuring a MySQL, MariaDB, or Percona service in your Boxfile, you must define the type of the service as one of the following: mysql, mariadb, percona. You can also specify the version of MySQL to load into your database service. The following version(s) are available:

  • MySQL

  • 5.5

  • 5.6

  • Percona

  • 5.5

  • 5.6

  • MariaDB

  • 5.5

The specific patch level is determined by the "stability" config.

Version Defined on Create

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

mysql type & version YAML

  # default setting
  database1:
    type: mysql
    version: 5.6
/Boxfile
Topology

Topology defines the service's architecture and in what ways it can scale. MySQL, MariaDB, and Percona support the following topologies:

  • Single

  • Redundant

More information can be found in the Service Topologies doc.

topology YAML

  # default setting
  database1:
    type: mysql
    topology: single
/Boxfile
MySQL 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

mysql_plugins YAML

  database1:
    mysql_plugins:
      - federated
/Boxfile

audit_log and audit_log settings

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

MySQL Event Scheduler

This enables or disables MySQL's event scheduler.

Note: Even though mysql_event_schedule'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.

mysql_event_scheduler YAML

  # default setting
  database1:
    mysql_event_scheduler: 'Off'
/Boxfile
MySQL Max Connections

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

mysql_max_connections YAML

  # default setting
  database1:
    mysql_max_connections: 1024
/Boxfile
MySQL Thread Stack

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

mysql_thread_stack YAML

  # default setting
  database1:
    mysql_thread_stack: '256K'
/Boxfile
MyISAM Recover

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

mysql_myisam_recover YAML

  # default setting
  database1:
    mysql_myisam_recover: 'DEFAULT'
/Boxfile
MySQL Max Allowed Packet

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

mysql_myisam_recover YAML

  # default setting
  database1:
    mysql_max_allowed_packet:  '16M'
/Boxfile
MySQL Max Join Size

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

mysql_max_join_size YAML

  # default setting
  database1:
    mysql_max_join_size: 9223372036854775807
/Boxfile
MySQL Table Open Cache

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

mysql_table_open_cache YAML

  # default setting
  database1:
    mysql_table_open_cache: 64
/Boxfile
MySQL Query Cache Limit

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

mysql_query_cache_limit YAML

  # default setting
  database1:
    mysql_query_cache_limit: '1M'
/Boxfile
Allow Suspicious UDFs

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

mysql_allow_suspicious_udfs YAML

  # default setting
  database1:
    mysql_allow_suspicious_udfs: 'Off'
/Boxfile
MySQL ANSI

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

mysql_ansi YAML

  # default setting
  database1:
    mysql_ansi: 'Off'
/Boxfile
MySQL Audit Log

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

  • on

  • off

  • force

  • force_plus_permanent

audit_log settings and the audit_log plugin

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

mysql_audit_log YAML

  database1:
    mysql_audit_log: 'On'
    mysql_plugins:
      - audit_log
/Boxfile
FULLTEXT Maximum Word Length

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

mysql_ft_max_word_len YAML

  database1:
    mysql_ft_max_word_len: 84
/Boxfile
FULLTEXT Minimum Word Length

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

mysql_ft_min_word_len YAML

  database1:
    mysql_ft_min_word_len: 4
/Boxfile
FULLTEXT Query Expansion Limit

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

mysql_ft_query_expansion_limit YAML

  database1:
    mysql_ft_query_expansion_limit: 20
/Boxfile
FULLTEXT Stopword File

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

mysql_ft_stopword_file YAML

  database1:
    mysql_ft_stopword_file: ' '
/Boxfile

Boxfile Modifications Only Take Affect After a New Build

Whenever changes are made to a MySQL service's configuration in the Boxfile, those changes will not apply until the service is rebuilt. By default, MySQL services are not rebuilt on deploy. However, there are three options for rebuilding your service:

Enable Rebuild on Deploy

In your app dashboard, under Dev Config > Deployment Options, you can enable the option to have data services rebuild on deploy whenever changes to the service's Boxfile config are detected.

Rebuilding data services on deploy will increase deploy times, but only when changes to the service's config are detected.

Scale

Any time you scale a MySQL Service, a new instance or instances are provisioned, data is migrated, requests are routed to the new instance(s) and old instances decommissioned. The new instances are built using the modified settings in your Boxfile.

Repair

In your dashboard, click on your MySQL service to expand it's options. Each data service has a "Repair" option.

"Repairing" your service will provision a new instance (or instances) based on the settings in your Boxfile, migrate data to the new instance, then reroute requests away from the old instance to the new.

New Builds Require Data Migrations

Data migrations are required during a rebuild. Your service will temporarily go offline during the migration process. The Data Migrations During Scaling & Repairs doc has more information.

If you have any questions, suggestions, or corrections, let us know.