Parsing Mysql configuration file

[brumia]

Hi,

first sorry for my bed english

So…, I have about 30 instance of Mysql. I am looking some kind of parser which help me made cleanup witch this file. Because In all of this file everything look diffrent and its difficulte to administer.

thx
bartek

[miguelangelnieto]

I don’t know of any tool that can work as a parser to cleanup my.cnf files… but maybe pt-mysql-summary can help you. At the end of the report it shows the my.cnf but without comments or blank lines. This is the original my.cnf:

[mysqld]##datadir needed for xtrabackup#datadir=/var/lib/mysql## PXC configuration#wsrep_cluster_name = PXCwsrep_cluster_address = gcomm://#wsrep_cluster_address = gcomm://192.168.1.202,192.168.1.203wsrep_provider = /usr/lib64/libgalera_smm.sowsrep_provider_options = 'gcache.name=pxc_cache;gcache.size=512M’wsrep_sst_method = xtrabackupwsrep_retry_autocommit = 2wsrep_slave_threads = 6wsrep_node_name = PXC1wsrep_node_address = 192.168.1.201## User for xtrabackup#wsrep_sst_auth=rootbinlog_format = ROWserver-id = 201innodb_locks_unsafe_for_binlog = 1innodb_autoinc_lock_mode = 2innodb_flush_log_at_trx_commit = 2

And this is what pt-mysql-summary shows:

[mysqld]datadir = /var/lib/mysqlwsrep_cluster_name = PXCwsrep_cluster_address = gcomm://wsrep_provider = /usr/lib64/libgalera_smm.sowsrep_provider_options = 'gcache.namewsrep_sst_method = xtrabackupwsrep_retry_autocommit = 2wsrep_slave_threads = 6wsrep_node_name = PXC1wsrep_node_address = 192.168.1.201wsrep_sst_auth = rootbinlog_format = ROWserver-id = 201innodb_locks_unsafe_for_binlog = 1innodb_autoinc_lock_mode = 2innodb_flush_log_at_trx_commit = 2

Hope this helps.

There is another tool that can maybe help you to deal with those 30 instances, pt-config-diff.

http://www.percona.com/doc/percona-toolkit/2.1/pt-config-dif f.html

With this tool you can get diffs on MySQL configuration comparing different servers. This tool can connect to remote hosts and get the differences in variable configurations. There are some examples of how to use this tool:

http://www.percona.com/doc/percona-toolkit/2.1/pt-config-dif f.html#usage

This is not a parser to cleanup the my.cnf but can help you see the differences.

[scott.nemes]

As a side note, with that large of a MySQL deployment, I would look into some sort of configuration management software like Puppet or Chef. Once that is setup, you can easily manage MySQL users, my.cnf settings, backup scripts, etc., all from one central location. For the my.cnf specifically, you can setup variables by hostname (i.e. InnoDB buffer pool size), and then have it build out the my.cnf from a template using those variables.

We currently use Puppet, but are looking to migrate to Chef here in the near future.

http://wiki.opscode.com/display/chef/Home
http://puppetlabs.com/