database-mysql-configure

How To Configure MySQL Server

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, ie. terminated and recreated. As such, making configuration changes there can be difficult as it requires a Chef recipe to make line edits in the configuration file after it’s been generated by the standard Chef run on instance startup.

However, that default MySQL configuration file also contains an !includedir directive (see: http://dev.mysql.com/doc/refman/5.0/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.

Example

Let’s say you want to have mysqld (the MySQL server program, itself) start up with a non-default value for it’s 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 is a good idea

• Option A

Have Chef render a file to the include folder.

• Option B

Place the file on /db volume (such as /db/mysql.d/) and then create a symlink with a [[deploy hook|use-deploy-hooks-with-engine-yard-cloud]].

Dynamic Change

After you’ve got 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|http://dev.mysql.com/doc/refman/5.0/en/mysqld-option-tables.html]] you can simply make the configuration on the fly and avoid a restart.

Follow these steps to add your configuration change 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>';
  

Where you’d enter the dynamic variable your wanting to set as <varname> and the new value as <varvalue>.

Read more about the [[SET command|http://dev.mysql.com/doc/refman/5.0/en/set-option.html]].

Note: When changing dynamic system variables like this, existing connections will not automatically detect the new values. This is because system variable values for database sessions (connections) are set at connection time. If you want your application connections to pick up on the new setting(s) immediately you will 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 Chef recipes are not run before MySQL is started. So you’ll need to either add the option dynamically or restart MySQL after the instance is fully started.

So if you clone, restart or resize an instance, make sure you keep this in mind.

Every other time you restart MySQL, your configuration in the /etc/mysql.d/ folder is used.