Knowledge Base/Engine Yard Cloud Documentation/Manage your Database

Configure a MySQL Server

Engine Yard
posted this on February 16, 2012 10:02 AM

Updated: February 14th, 2014

The default MySQL configuration file, etc/mysql/my.cnf, is managed by Chef and is completely recreated with defaults every time an instance is restarted (that is, terminated and recreated). Configuration changes can be difficult because you need a Chef recipe to make line edits in the configuration file, and that is after it has been generated by the standard Chef run at instance startup time.

However, that default MySQL configuration file also contains an !includedir directive (see: http://dev.mysql.com/doc/refman/5.6/en/option-files.html) for /etc/mysql.d/. This allows you to place files in that directory with MySQL program configuration values that will be sourced after the default values in /etc/mysql/my.cnf on startup (or restart) of any MySQL program.

Note: The Engine Yard MySQL databases are created with utf8_generic_ci. This default collation is not configurable. To use a different collation, you must drop and recreate the database using your preferred collation before loading the data.

Example

Let’s say you want to have mysqld (the MySQL server program, itself) start up with a non-default value for its innodb_buffer_pool_size value, in this case 1536M (1.5G). Place this in a file:

[mysqld] 
innodb_buffer_pool_size = 1536M

Then save your file read-able, or owned by the mysql system user, with a .cnf extension; my_custom.cnf.

  • Option A

As of the May 8th, 2012 (or newer) Engine Yard stack, you can place the file in /db/mysql.d/ manually. In this situation, Custom Chef is only required if you want a different configuration for your master and replica(s).

To determine if you have the required stack version, run the cat /etc/fstab |grep mysql.d command on your database instance. If you have the required stack version, you should see /db/mysql.d /etc/mysql.d none bind. If you do not see this response, you need to test and apply the available updates to your stack or use one of the alternative approaches.

  • Option B

If you are on stacks prior to May 8th, 2012, place the file on /db volume (such as /db/mysql.d/) and then create a symlink with a deploy hook.

To determine if you are on a stack prior to May 8th, 2012, run the cat /etc/fstab |grep mysql.d command on your database instance. If you do NOT see /db/mysql.d /etc/mysql.d none bind, it means you are on a stack prior to May 8th, 2012 and can follow the instructions in Option C.

Dynamic Change

After you have your configuration changes in place, do you need to restart the MySQL server? It depends.

If the option you’re setting is a Dynamic option, you can simply make the configuration on the fly and avoid a restart.

Follow these steps to add your configuration changes to the system dynamically.

  • SSH into your MySQL instance.

  • Log into the MySQL console.

    mysql -u root -p 

Your root MySQL password is the same as your deploy user password.

  • From the MySQL prompt type:

    mysql> SET GLOBAL <varname> = '<varvalue>'; 

Enter the dynamic variable you want to set as <varname> and the new value as <varvalue>.

For more information about the SET statement, see the MySQL documentation.

Note: When changing dynamic system variables, existing connections will not automatically detect the new values. This is because system variable values for database sessions (connections) are set at the connection time. If you want your application connections to pick up on the new setting(s) immediately, you need to restart your mongrels so that they establish new database connections.

Non-Dynamic Change

If your configuration isn’t dynamic (as determined above) you need to reboot MySQL.

/etc/init.d/mysql stop 
/etc/init.d/mysql start

One Caveat

When you create a new instance, custom Chef recipes are not run before MySQL is started. That's not an issue with instances that booted from snapshots since those snapshots would contain any files previously written to /db/mysql.d via the bind mount at /etc/mysql.d (described above) and we do run that mount before starting MySQL.

However, a new database instance that is not booted from a snapshot (that is, a db_master instance in a fresh environment) will not have any custom configuration files present until the first custom cookbook run, which doesn't occur until after MySQL is started. That means that you'll then need to also make the changes dynamically or restart MySQL as described above.

More information

For more information about...See...
Configuring and deploying an Engine Yard Cloud application in general Engine Yard Cloud Documentation
Database tasks Manage your Database
MySQL MySQL documentation
MySQL Release Notes MySQL 5.6 Release Notes
MySQL versioning policies MySQL Versioning Policies
Engine Yard DB upgrade policies Database Version Upgrade Policies

If you have feedback or questions about this page, add a comment below. If you need help, submit a ticket with Engine Yard Support.