Managing MySQL Configuration Files
It is good practice to manage changes to MySQL configuration files (/etc/my.cnf) by using a version control system. I usually use a home-brewed (not brewed by me!) svn+cfengine application to propagate my.cnf (and other configuration file) changes to defined classes of machines (classes are based on application role, replication role, etc).
When managing hundreds of different database servers with dozens of roles, templating my.cnf becomes a necessity! If I have to change a variable for a given class, that can mean editing a dozen my.cnf files on as many servers. Not a productive use of time!!! Usually, the only parameters that differ between database hosts in the same class are the replication options. As such, I find it useful to have a base template for each class of machine and use the !include startup option to specify host-specific startup options.
Here is a sample of what I would have as a base my.cnf for billing-class MySQL Replication Slaves:
[mysqld]
# Default Configuration For billing-class MySQL Replication Slaves
#############################################################################
# GENERAL STORAGE ENGINES
#############################################################################
skip-bdb
default-storage-engine = InnoDB
#############################################################################
# InnoDB
#############################################################################
innodb_file_per_table = 1
innodb_log_file_size = 256M
innodb_buffer_pool_size = 12G
innodb_flush_method = O_DIRECT
#############################################################################
# MyISAM
#############################################################################
key_buffer_size = 32M
#############################################################################
# Query Cache
#############################################################################
query_cache_type = 1
query_cache_size = 64M
#############################################################################
# REPLICATION
#############################################################################
!include /etc/my_replication.cnf
#############################################################################
# LOGGING
#############################################################################
log-err = /var/lib/mysql_logs/err.log
log-slow = /var/lib/mysql_logs/slow.log
long-query-time = 10000
log-queries-not-using-indexes
#############################################################################
# MAINTENANCE & RECOVERY
#############################################################################
myisam_recover = FORCE,BACKUP
And then I would store host-specific replication options in a smaller file:
[mysqld]
#############################################################################
# REPLICATION
#############################################################################server-id = 100
...
To re-iterate, templating MySQL configuration files can help ensure consistency between servers of the same class as well as making it easier to push changes to all relevant hosts instead of doing it individually.
September 4th, 2008 at 1:19 pm
Hi Ryan,
There are a few problems with the above, although I agree with the basic premise. They are:
1. You can’t use defaults-extra-file in that way, it can only be used on the command line itself, never in a cnf file. In addition, it must always be one of the first options on the command line (along with defaults-file, skip-defaults, etc.). See !include and !includedir instead (as of 4.1.14 and 5.0.4, I added them to make Yahoo!’s configuration more sane).
2. In the file that you would !include or use with defaults{,-extra}-file you must always still include [group] lines, as includes are always “context-less”.
3. You have a typo: innno
4. The query_cache_type variable cannot be set to “ON”, you would want to set it to “1″ instead.
5. Please don’t use master_host et al.
Regards,
Jeremy
September 4th, 2008 at 1:52 pm
Thanks Jeremy, I had merged two separate posts about using option files:)
I’ve also updated the typos and whatnot.