MySQLdump leads to exceeding max connections. Skip-Quick as a solution?
1
vote
1
answer
819
views
Every night I do a full mysqldump of a 17G DB in MySQL 5.7.32. This worke for years, now I am doing some heavy load on inserts during the night which caused at exactly the backup time the connections to rise to max_connections which led to connections errors.
As the Server has enough RAM 64G (30G free), I increased max_connections from 150 to 300 in a first reaction.
However looking at the dump command I found the option -- quick (also enabled by default) which tells me that it is exporting row by row.
--single-transaction --routines --quick --compact
I am thinking of changing this to
--skip-quick.
but dare to change this since I would need to check the restore again and this is very time consuming.
Looking at the connections over time I also noticed that there are some interruptions around that time period. So maybe connections stack up since there is a block during MySQLdump?
MySQL error log shows a large amount of the following error, although not at this time points but continuously throughout the day:
Aborted connection 63182018 to db: 'mydb' user: 'test' host: 'myhost' (Got an error reading communication packets)
How would you approach this problem?



Asked by merlin
(323 rep)
Dec 27, 2020, 08:08 AM
Last activity: Aug 5, 2025, 02:05 AM
Last activity: Aug 5, 2025, 02:05 AM