Why Monit does not work with my MySQL configuration file?

Question

I'm using a custom configuration file for mysql in /etc/mysql/conf.d/myfile.cnf

The contents of the file

[client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english skip-external-locking bind-address = 127.0.0.1 bulk_insert_buffer_size = 8M connect_timeout=10 interactive_timeout=50 join_buffer=1M key_buffer=250M max_allowed_packet=16M max_connect_errors=10 max_connections=100 max_heap_table_size = 32M myisam_sort_buffer_size=96M query_cache_limit = 4M query_cache_size = 250M query_cache_type = 1 query_prealloc_size = 65K query_alloc_block_size = 128K read_buffer_size=1M read_rnd_buffer_size=768K #record_buffer_size=1M #safe-show-database #skip-innodb #skip-locking skip-networking sort_buffer=1M table_cache=4096 thread_cache_size=1024 thread_concurrency=8 tmp_table_size = 32M wait_timeout=500 # for slow queries, comment when not used #log-slow-queries=/var/log/mysql-slow.log #long_query_time=1 #log-queries-not-using-indexes [mysqld_safe] nice = -5 open_files_limit = 8192 pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock [mysqldump] quick max_allowed_packet = 16M [myisamchk] key_buffer = 64M sort_buffer = 64M read_buffer = 16M write_buffer = 16M 

What's wrong?

If you use the configuration file default for mysql, Monit runs smoothly

My sistem is Debian Squeeze 6.0.x and Mysql Server version: 5.1.66-0+squeeze1 (Debian)

The error is monit status: "status Connection failed" and "Port Response time connection failed to 127.0.0.1:3306 [DEFAULT via TCP]"

My configuration Monit Mysql

... check process mysql with pidfile /var/run/mysqld/mysqld.pid group database start program = "/etc/init.d/mysql start" stop program = "/etc/init.d/mysql stop" if failed host 127.0.0.1 port 3306 then restart if 5 restarts within 5 cycles then timeout ... 

Answer 1

On Ubuntu 20.04 LTS, I found that pid-file and socket were commented out in /etc/mysql/mysql.conf.d/mysqld.cnf. I uncommented those two lines then restarted mysql with service mysql restart and monit with service monit restart and monit was successfully able to monitor the mysqld process.

Answer 2

My guess is it is this line

if failed host 127.0.0.1 port 3306 then restart 

MySQL will block a host if it hits 10 (I think) bad connections in a row. I think it's working until the 10th check then it starts to fail. There is nothing wrong with the rest of it. Try fixing it like this

if failed host 127.0.0.1 port 3306 protocol mysql then restart 

You should also run

mysqladmin flush-hosts 

That will clear the banned hosts