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
For example, 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.
If you are on the May 8th, 2012 or newer Engine Yard stack you can use Option A, otherwise you need to use Option B. Run the command cat /etc/fstab |grep mysql.d on your database instance to determine if you have the required stack version. If you have the required stack version, you should see /db/mysql.d /etc/mysql.d none bind and you can use Option A.
- Option A
On the newer Engine Yard stack the path /etc/mysql.d is linked to /db/mysql.d, which exists as part of your database EBS volume. With this configuration, you can place the file in /etc/mysql.d/ manually and it will persist within your snapshots automatically. In this situation, Custom Chef is only required if you want a different configuration for your master and replica(s).
- 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 for that file under /etc/mysql.d using a deploy hook.
Note: If you are on an older Engine Yard stack, we highly recommend testing and applying the available updates in a non-production environment and then applying them to production.
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 because those snapshots would contain 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 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.
Priyanka Bhotika
Comments