I have a v5.7.42 MySQL server (community) running on Ubuntu 20.04.6 LTS - My issue is about every two months or so , the site will show
could not establish connection to database, service mysql status still shows as running, however as soon as I issue service mysql restart the site returns back online (ie database correct again).
my.cnf looks like this:
pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = /var/log/mysql/error.log bind-address = 0.0.0.0 symbolic-links=0 # these below i have now added trying to troubleshoot this issue # (it has not re-occured yet, but that is normal as its once every few months): log-error-verbosity = 1 max_connections = 512 innodb_buffer_pool_size = 1147483648 max_heap_table_size = 64777216 tmp_table_size = 64777216 slow_query_log = ON slow_query_log_file = /var/log/mysql/mySQL_slow_querys.log
(NB - I have a hardware firewall in front of this server that only allows a single publicIP to access :3306 , this one external server/pubIP is doing the "sync" / replication i describe further down)
I have confirmed that these additions to
/etc/mysql/conf.d/mysql.cnf are live/applied based on commands like:
SHOW VARIABLES LIKE "%log%";
The DB that needs to be "synced / updated" several times a day, is ONLY read from on this web server side, no writes / inserts nor updates. (is a listing of cars, which php then generates some web pages from, for public www access via apache (Apache/2.4.41)
The database is fairly small, Named "CarsLiveDB" and phpMA 5 shows: 6x tables, total size of DB: 17.7MiB and 58k rows (overhead 48bytes)
the way the updates (sync) to this database are done is as follows (due to additional reasons I'm ideally, not looking to make major changes to this method/structure, but can tweak it- it has been working for many years, the only change was about 10 months ago we moved to a new physical server with more up to date OS (ubuntu) and newer versions of the entire stack, from a older server running centos6.7
offsite server runs a windows app called "ESF Database Migration Toolkit Pro" to update/sync 3x tables from a local MSSQL (msft) server/db TO this mysql database named "CarsUpdateDB"
once that done, a bash script on the mysql server is run that:
check that a table on CarsUpdateDB contains more than 13k rows (ie check that the sync ran completely/correctly) if so then:
loop through the 3x table names and execute:
mysql -u RenameDBscript -s -N -e "use CarsLiveDB;drop table CarsLiveDB.$tableName;"; mysql -u RenameDBscript -s -N -e "use CarsUpdateDB;rename table CarsUpdateDB.$table to testIMPORT.$tableName;";
This runs about 20 times a day and as I stated 99.9% of the time there are no issues , it does feel like the SQL hang up occurs when there's a tad more traffic on the site, but it hasn't happened frequently enough for me to say that's 100% related.
I wish i could have better logging of mysql - But everything I have found online seems to relate to mysql logging of queries which I don't think is the issue here.
The …/mysql/error.log Does not contain anything related to this issue, its pretty bare in that it only contains basic messages related to when I restart mysql service- nothing from when the issue occurs.
Can anyone suggest how I should increase my SQL's logging so that I can get a better indication of what is causing this issue, or any other suggestions ?
I did also increase ulimit to 4096 , from the 1024 it was showing prior. This is a dedicated server for only this one site, it has 16gb of ECC D3 memory - is a supermicro X9 , 1cpu server motherboard- disks: 2x Intel s3700 enterprise SSD sata drives in software Raid1 ( MDADM for boot and / ) - iostat does not show disk as the issue (and no iowait issues)
I have Telegraf -> offsite InfluxDB monitoring (System, Apache Scoreboard, Mysql), and free Mem never goes below 13GB free, Load Avg almost never above 0.5 - Please see img below that shows what happens to stats when the mysql issue occurs (is around time of 18:00 that the issue occurs):