-1

I have a web system setup in AWS that we are building up to migrate an existing web app to. I have 2 VPCs: 1 for web and 1 for data. These VPCs are Peered and Security Groups are setup to isolate each VPCs subnet.

At the moment, there is an EC2 Ubuntu 14 + Apache 2 + PHP 5.6 machine that connects to a single MySQL master DB in the data VPC for its writes and calls to an Internal Elastic Load Balancer in the data VPC that is balancing 2 MySQL slave/replicas for its reads. Each DB is an Amazon Linux EC2 running MySQL 5.6.

Everything works fine for a few minutes (10-30'ish), but then the web server isn't able to make connections to the DBs. The loss of connection manifests itself via our PHP + CodeIgniter web application as 'no connection' errors. I've removed the slaves and still get the same issue when only reading/writing from the master.

I can temporarily resolve the issue by running FLUSH HOSTS via MySQL Workbench that is connected via SSH Tunnel through the web server to the master db. I noticed however that as soon as I do that, the following entry appears in my /var/lib/mysql/{host}.err 

[Warning] IP address 'ommitted' could not be resolved: Name or service not known

I'd think, based off that error message, that this is a network issue, but the system works fine for a little while after flushing the hosts from the master. It's also important to note that my SSH Tunnel through that same web server never has a problem, before, during, or after the web app breaking.

I'm a bit stumped on this, considering it works for a little while and then breaks. I'd rather not simply increase my max_connect_errors without knowing what exactly is causing my connect error if possible, so any and all insight is welcome and appreciated greatly!

Improve this question
3
  • 2
    This line in the MySQL doc stands out to me: "When more than max_connect_errors errors occur successively for a given host while connecting to the MySQL server, MySQL assumes that something is wrong and blocks the host from further connection requests. Flushing the host cache enables further connection attempts from the host."
    – Bacon Bits
    Dec 4, 2015 at 19:46
  • you're right that seems to be it, but why would the php app that can successfully connect at first after flushing stop successfully connecting? the creds aren't changing
    – Jon B
    Dec 4, 2015 at 21:28
  • i disagree that this was closed, but for anyone that is looking to resolve their similar issue, I finally got mine fixed without the hacky solution of increasing max_connect_errors. i've left an answer here dba.stackexchange.com/a/124024/82898
    – Jon B
    Dec 18, 2015 at 1:00

1 Answer 1

Reset to default
2
  • Maybe you have a script which is connecting to the database with the bad login/password : MySQL will increase the host error count at each failed attemp, and at max_connect_errors it will blacklist this host.

FLUSH HOSTS will just reset the error count for all hosts.

Check that all scripts are connecting with the good informations. Eventualy you can capture trafic with something like

    tcpdump -i any -s 0 -w mysql.pcap port 3306

and then analyse the capture with wireshark to check login and password.

  • You can also configure your accounts with ip or networks, not with dns names, and then start mysql with the skip-resolve option. You will not try to resove 'ommitted' anymore and will have better connection performance.
Improve this answer
1
  • thanks for this. to clarify, i omitted the ip address listed in my error. it is actually something like [Warning] IP address '1.2.3.4' could .... i don't think this is a credential thing because i work fine for a little while, but i'll double check cred accuracy
    – Jon B
    Dec 4, 2015 at 19:57

Not the answer you're looking for? Browse other questions tagged .