opendev
/
puppet-mysql_backup
Code Issues Proposed changes

Use --single-transaction for mysqldump 

InnoDB is an MVCC storage engine. This means it does not need to lock
tables in order to do a consistent read of data. Add
--single-transaction to mysqldump invocations to enable this behavior
and stop locking the tables.

Downsides to this - changes made to MyISAM tables may not be consistent
(but for the love of all that is holy we should be storing nothing in
MyISAM and neither should anyone else) and DDL commands will not be
allowed during the backup.

Change-Id: If2ea9e5a25a3cfe30c16cc646463afbd3b2a5ab5
This commit is contained in:
Monty Taylor 2017-02-15 18:19:43 -06:00
parent ec9649b4da
commit 320fe64ba8
No known key found for this signature in database
GPG Key ID: 7BAE94BC7141A594
2 changed files with 2 additions and 2 deletions
Show Stats Download Patch File Download Diff File Expand all files Collapse all files

2
manifests/backup.pp
View File

@ -37,7 +37,7 @@ define mysql_backup::backup (
cron { "${name}-backup":
ensure => present,
command => "/usr/bin/mysqldump --defaults-file=${defaults_file} --opt --ignore-table mysql.event --all-databases | gzip -9 > ${dest_dir}/${name}.sql.gz",
command => "/usr/bin/mysqldump --defaults-file=${defaults_file} --opt --ignore-table mysql.event --all-databases --single-transaction | gzip -9 > ${dest_dir}/${name}.sql.gz",
minute => $minute,
hour => $hour,
weekday => $day,

2
manifests/backup_remote.pp
View File

@ -47,7 +47,7 @@ define mysql_backup::backup_remote (
cron { "${name}-backup":
ensure => present,
command => "/usr/bin/mysqldump --defaults-file=${defaults_file} --opt --ignore-table mysql.event --all-databases | gzip -9 > ${dest_dir}/${name}.sql.gz",
command => "/usr/bin/mysqldump --defaults-file=${defaults_file} --opt --ignore-table mysql.event --all-databases --single-transaction | gzip -9 > ${dest_dir}/${name}.sql.gz",
minute => $minute,
hour => $hour,
weekday => $day,