Alternative to BM_MYSQL_SAFEDUMPS

[Toub]

BM_MYSQL_SAFEDUMPS add "--opt" to the command line: https://github.com/sukria/Backup-Manager/blob/0c6c3cf314c1df0d807adb617f6cd9e38f3b5378/lib/backup-methods.sh

But according to the documentation, this option is enabled by default, and can be disabled by using "--skip-opt": http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_opt

So it is like BM_MYSQL_SAFEDUMPS option does not have any impact.

Morever, "--opt" is a shortcut for "--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset". So the tables are locked during the backup, which is really annoying for large databases as it can take several minutes.

If you are using innoDB tables, a better option is to disable locks "--lock-tables=false" and append "--single-transactions": http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_single-transaction as suggested here: http://stackoverflow.com/questions/104612/run-mysqldump-without-locking-tables

With the master version of Backup-Manager, this can be done by using BM_MYSQL_EXTRA_OPTIONS="--lock-tables=false --single-transactions" I guess.

So, 2 suggestions:

• if I am right, the BM_MYSQL_SAFEDUMPS is unuseful (at least from MySQL 5.0). We can keep it or change the behavior (to append --lock-tables if BM_MYSQL_SAFEDUMPS=false).
• we could add an option: MYSQL_INNO_DB = true, and in this case, replace locks by single transaction in combination with BM_MYSQL_SAFEDUMPS=true

[kissifrot]

Is it still relevant with later versions of MySQL?

[alexislefebvre]

We encountered this issue when dumping a MariaDB 10 database.

We used this configuration in order to not lock the tables:

export BM_MYSQL_SAFEDUMPS="true"
export BM_MYSQL_EXTRA_OPTIONS="--skip-lock-tables --single-transaction"