MySQL Backup - Add option to allow single-transaction mode for huge InnoDB databases
Summary (Feature Request)
Running DB Backups on sites with large databases will cause the database being locked for some time and thus make the underlying app not usable.
This is due to the current mysqldump
command being executed.
For sites only having InnoDB tables MySQL recommends to run mysqldump with --quick
AND --single-transaction
for huge databases.
As this flag can lead to inconsistent states when MyISAM used, I would suggest to add this as an option.
Steps to reproduce
- Enable the DB backup on a huge DB
- Check the sites at the time of the DB dump, they will be unresponsive due to the locked database as long as
mysqldump
run
Correct behaviour
The DB dump should not effect the websites uptime
Proposed fix
- Add a "Huge Database?" Checkbox to the backup options in the website config
- If enabled use this command / add
--single-transaction
to themysqldump
call
$command = "mysqldump -h ? -u ? -p? -c --add-drop-table --create-options --quick --single-transaction --max_allowed_packet=512M " . $mysqldump_routines . " --result-file=? ?";
References
https://serversforhackers.com/c/mysqldump-with-modern-mysql
https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_single-transaction