I (well, my cron script) tried
killall mysqldump and that didn't end very well - mysql server stopped accepting connections after a while.
It was Debian Jessie machine with
Usage scenario was:
there was a long running (few hours)
SELECTquery which was either really that slow or the client that sent it was having problems (query state was
Sending data), but all the other queries were happily coming and going (only the load was perhaps little higher).
in the night backup was being run with
mysqldump --max_allowed_packet=2147483648 --hex-blob --single-transaction --master-data --routines --order-by-primary --databases db1 db2 db3... | pigz -p8 > backup.sql.gz. It never finished, probably because it was waiting for the
SELECTabove to finish first (guessing here - it was only thing which looked out of ordinary, and same setup worked OK for months).
cron job run in the morning, which had
killall -q mysqldumpwhich was supposed to safely terminate backup in case it was not finished by set time (notifying the admin to examine and fix the problem later), thus allowing people to continue working with mysql server normally.
result however was full connection table and thus no user able to log in to mysql server. There was
FLUSH /*!40101 LOCAL */ TABLESquery stuck in
Waiting for table flushand hundreds of
SELECTqueries stuck in same
Waiting for table flushstate.
in addition, admin killing
LOCK TABLESmysql query didn't help, as other SELECT queries remained in
Waiting for table flush(which seems to be intended behavior?)
Restarting mysql server finally "fixed" the problem. However, Wanting to avoid this situation (and emergency admin interventions) repeating , I'd like to safely terminate mysqldump backup in Debian Jessie mysql-5.5.55 (or upcoming Debian Stretch mariadb-10.1.23-8). Is there a way?
If not, what are other options for accomplishing mysql backup and avoiding server load in the morning (which is - in this case - almost as bad as completely hung server)?
(I'd like to stay with Debian Stable packages if at all possible)