PlanetMysql.ru — информация о СУБД MySQL

I want to keep a record of the configuration of the MySQL servers I manage. The configuration of some servers differs from others and over time the configuration may vary, partly as a result of upgrades in the mysql version or the use of the particular mysql instance, so tracking this is important.

Configuration items in MySQL can be thought of in 2 separate parts: the static configuration files which determine the behaviour of the server when it starts up (my.cnf) and the running configuration of the server in question. The latter information is usually obtained by running SHOW GLOBAL VARIABLES and SHOW SLAVE STATUS if the server is a slave.

I’d also like to compare the 2 sets of configuration so I can see if a local change has been made to the running server which is not reflected in its configuration file. I might want to correct this, or at least be aware of it.

However, collecting and comparing this configuration information is not as easy to do completely as one would really like. There are various inconsistencies which make this task troublesome.

• Getting the default configuration out of the system is hard: mysqld --help --verbose sort of shows you this information but you have to filter out the junk.
• Use of dashes ('-') in variable names in my.cnf or command line parameter names vs '_' in SHOW GLOBAL VARIABLES.
• 5.5.16 seems to try to ‘partially’ startup when it shouldn’t.  See bug#63187.
• You can not query the default configuration information from the running server.
• SHOW SLAVE STATUS uses mixed-case variable names vs SHOW GLOBAL VARIABLES which uses lower-case variable names.
• SHOW GLOBAL VARIABLES uses lower-case variable names vs SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES which shows them in upper-case.
• Some variables get their multiple values sorted (others don’t)
• Conversion of sql_mode special values is not consistent or clear.
• my.cnf may use letter multipliers to indicate size (K, M, G), show global variables uses actual numbers.
• my.cnf uses 0/1 vs SHOW GLOBAL VARIABLES using OFF/ON for certain switches. In some cases just having the variable defined implies ‘= 1′.
• Some directory paths provided without a trailing ‘/’ in my.cnf have the trailing ‘/’ added in SHOW GLOBAL VARIABLES.
• Some variables can take relative paths (relative to datadir) whereas SHOW GLOBAL VARIABLES shows the full path.
• Some variable names differ in my.cnf compared to the equivalent setting in SHOW GLOBAL VARIABLES.
• Some variable names have changed with newer versions of MySQL yet in many cases no warning is given that the new (correct?) variable name should be used and the old name is deprecated.
• Not all my.cnf settings, DEFAULT SETTINGS (234 values) are shown in SHOW GLOBAL VARIABLES (312 values) in 5.5.16.
• Some settings are incomplete (log_bin does not provide the full name of the binlog files set in my.cnf).
• etc…

So all of these issues means that if I want to look at the /etc/my.cnf files and compare that to the running configuration it’s pretty hard to do well.  That may not seem like such a big deal until you realise that many mysql servers may have an uptime over a year. In that time the configuration may have evolved and this may mean you need to bring down mysql to adjust its configuration or alternatively see the difference and accept it’s not such a big issue and can be corrected the next time the server is restarted.

I’ve brought this up with Oracle support but guess that many in the community are so used to this they think it’s normal. If you manage several servers then it really is helpful to be able to collect this information and also see if servers run with the expected settings. Not doing so may mean that performance is affected and no-one notices.

So I’d like to ask that Oracle consider addressing this issue and remove some of the aforementioned inconsistencies, and perhaps also providing something like a SHOW DEFAULT SETTINGS or INFORMATION_SCHEMA.DEFAULT_SETTINGS output.  If you are in a company which goes through a lot of change and that includes the servers that are being managed, it will be much easier to ensure the servers are configured properly and if they were to address that.

Note software from other places makes this really easy and that can be very handy. Postfix‘s postconf shows the current configuration, but when called with -n will only show the non-default options and postconf -d shows all default settings.  So this certainly can be done.

Numerous times now, I’ve seen people have troubles creating MySQL services on Windows manually (using ’sc’), whether it be for mysqld itself, MySQL Proxy, or the MySQL Enterprise Monitor and/or Agent.

The proper syntax for ’sc’ can get tricky when you have spaces in pathnames, which is very common in Windows, and the need for –defaults-file (which means two paths each potentially containing spaces).

So, if you have spaces in both your binpath and your path to –defaults-file, then the following syntax will work for you (all on a single line):

sc create MySQLEnterpriseMonitorAgent
binpath= "\"C:\Program Files\MySQL\Enterprise\Agent\bin\mysql-monitor-agent.exe\"
--defaults-file=\"C:\Program Files\MySQL\Enterprise\Agent\etc\mysql-monitor-agent.ini\""
DisplayName= "MySQL Enterprise Monitor Agent" start= "auto"

Note that you could easily use the exact same syntax to install a Windows service for the MySQL server itself (using mysqld.exe and my.ini instead, along with their correct paths). Similarly, this could be also used to create a Windows service for MySQL Proxy too. Just point binpath to mysql-proxy-svc.exe, and instead of –defaults-file=, use –proxy-lua-script= (in fact, I actually had a post on this close to a year ago, but my example only had one path that contained a space, so the above syntax was not necessary).

At any rate, the above command should output somthing similar to the following:

[SC] CreateService SUCCESS

For reference, here is my terminal session:

C:\Users\Chris>sc create MySQLEnterpriseMonitorAgent
binpath= "\"C:\Program Files\MySQL\Enterprise\Agent\bin\mysql-monitor-agent.exe\"
--defaults-file=\"C:\Program Files\MySQL\Enterprise\Agent\etc\mysql-monitor-agent.ini\""
DisplayName= "MySQL Enterprise Monitor Agent" start= "auto"
[SC] CreateService SUCCESS

Hope this helps.

While some may think I’m daft, I have a legitimate reason for wanting to crash mysqld. However first we need to find a way to crash it.

Great thanks to Alan K, Mark L, Harrison and Hartmut on #mysql-dev for several suggestions and a config option I was unaware of. My investigation even lead to a documentation bug logged as #51739.

My first thought was to find a known bug and if necessary install the correct version to test that. A good one was suggested, Bug #48508 which fails on several versions that I will use to demonstrate with, however the simplest way is to issue kill -11

By default, no core file will be produced which is what I’m seeking but with the right options this is possible. First, the user running mysqld probably has a core file limit size of 0.

$ ulimit -c
0

You can fix this with with ulimit or you can specify this in the [mysqld_safe] section with core-file-size=unlimited

$ ulimit -c unlimited
$ ulimit -c
unlimited

The option I was not aware of is you also have to also specify core-file in your my.cnf

[mysqld]
core-file

I also for my CentOS 5.4 installation ran the following kernel commands, but this may be unnecessary.

sudo /sbin/sysctl -w kernel.core_pattern="core"
sudo /sbin/sysctl -w fs.suid_dumpable= 1

It is now easy to produce a core file.

$ bin/mysqld_safe &
$ killall -11 mysqld
$ bin/mysqld_safe: line 137:  2656 Segmentation fault      (core dumped) ...
100304 16:46:43 mysqld_safe Number of processes running now: 0
100304 16:46:43 mysqld_safe mysqld restarted
$ find . -name "core*"
./data/core.99999

NOTE: Do no run killall on a multi-instance server. I use this syntax here only for simplicity in presentation. It is best to run ps and kill the appropriate pid.

On a side note, I also tried to produce a core on Mac OS X without success. I’d still like to document that way, so if anybody can assist please ping me.

I was actually exploring inotify-tools for something else, but they can also be handy for seeing what goes on below a mysqld process. inotify hooks into the filesystem handlers, and sees which files are accessed. You can then set triggers, or just display a tally over a certain period.

It has been a standard Linux kernel module since 2.6.13 (2005, wow that’s a long time ago already) and can be used through calls or the inotify-tools (commandline). So with the instrumentation already in the kernel, apt-get install inotify-tools is all you need to get started.

 # inotifywatch -v -t 20 -r /var/lib/mysql/* /var/lib/mysql/zabbix/*
Establishing watches...
Setting up watch(es) on /var/lib/mysql/mysql/user.frm
OK, /var/lib/mysql/mysql/user.frm is now being watched.
[...]
Total of 212 watches.
Finished establishing watches, now collecting statistics.
Will listen for events for 60 seconds.
total  modify  filename
2371   2371    /var/lib/mysql/relay-log.info
2148   2148    /var/lib/mysql/master.info
1157   1157    /var/lib/mysql/ib_logfile0
24     24      /var/lib/mysql/zabbix/
24     24      /var/lib/mysql/zabbix/history.ibd
8      8       /var/lib/mysql/zabbix/trends_uint.ibd
6      6       /var/lib/mysql/zabbix/items.ibd
5      5       /var/lib/mysql/ibdata1

This is just a limited example from a dev box, but you can see the benefit. You can see which files have been accessed, in what way, and how many times over the specified period. Consequently this provides the most insight if you’re using innodb-file-per-table (or MyISAM) rather than a single InnoDB tablespace. But of course it depends a bit on what you’re looking for.