| Subcribe via RSS

OmniSQL 0.0.7 Released

October 28th, 2008 | No Comments | Posted in MySQL, MySQL Administration, OmniSQL

OmniSQL (a command line tool for DBAs needing to issue ad-hoc queries against sharded data) version 0.0.7 is officially released.

Instead of logging in separately to multiple databases to issue the same query, groups of databases can be specified in a configuration file and queries will be automatically issued against all targeted MySQL instances.

Let me know of any bugs found or features you would like to see in upcoming releases!

Download at http://code.google.com/p/omnisql/.

CHANGE LOG
- Fixed Bug #3: Script failure when only one group is defined
- Fixed Bug #4: Too Slow (now it forks)
- Fixed Bug #5: Host-Specific parameters don't work
- INCOMPATIBLE CHANGE: Changed format of config file
- INCOMPATIBLE CHANGE: Use XML::SimpleObject instead of XML::Simple
- Added group summary (Issue #6)

Tags: ,

Managing MySQL Configuration Files

September 3rd, 2008 | 2 Comments | Posted in MySQL, MySQL Administration

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.

Tags: ,