Project

General

Profile

Tâche #26178

Scénario #26198: Traitement express MEN (50-51)

Probleme sur le cron de backup des BDD

Added by Arnaud FORNEROT over 2 years ago. Updated over 2 years ago.

Status:
Fermé
Priority:
Normal
Assigned To:
Start date:
12/06/2018
Due date:
% Done:

100%

Remaining (hours):
0.0

Description

Le script /usr/share/eole/schedule/daily/pre/mysql peut partir en erreur dans le cas de grosse base
Got error: 1016: Can't open file: './gepi/mod_listes_perso_eleves.frm' (errno: 24 - Too many open files) when using LOCK TABLES

Pour éviter ce message il suffit d'ajouter l'option --single-transaction sur la ligne de commande de dump
CMD="mysqldump --defaults-file=/etc/mysql/debian.cnf --databases $databasename --flush-privileges --single-transaction --create-options -Q -c $OPTION 2>/dev/null"


Related issues

Related to eole-mysql - Tâche #16225: 2.6 : schedule/daily/pre/mysql exited with return code 1 Fermé 05/30/2016

Associated revisions

Revision 5e385a5f (diff)
Added by Joël Cuissinat over 2 years ago

mysqldump : use long opts and don't hide stderr for backups

Ref: #26178

Revision 81913901 (diff)
Added by Joël Cuissinat over 2 years ago

mysqldump : replace --lock-tables by --single-transaction for backups

Ref: #26178

History

#1 Updated by Joël Cuissinat over 2 years ago

  • Tracker changed from Demande to Tâche
  • Status changed from Nouveau to En cours
  • Assigned To set to Joël Cuissinat
  • Parent task set to #25625

#2 Updated by Joël Cuissinat over 2 years ago

  • --single-transaction

This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such
as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.

When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change
state.

While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER
TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is
performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.

To dump large tables, combine the --single-transaction option with the --quick option.

  • --lock-tables, -l

For each dumped database, lock all tables to be dumped before dumping them. The tables are locked with READ LOCAL to permit concurrent inserts in the case of MyISAM tables. For transactional
tables such as InnoDB, --single-transaction is a much better option than --lock-tables because it does not need to lock the tables at all.

Because --lock-tables locks tables for each database separately, this option does not guarantee that the tables in the dump file are logically consistent between databases. Tables in different
databases may be dumped in completely different states.

Some options, such as --opt, automatically enable --lock-tables. If you want to override this, use --skip-lock-tables at the end of the option list.

#3 Updated by Joël Cuissinat over 2 years ago

  • Related to Tâche #16225: 2.6 : schedule/daily/pre/mysql exited with return code 1 added

#4 Updated by Joël Cuissinat over 2 years ago

  • Parent task changed from #25625 to #26198

#6 Updated by Joël Cuissinat over 2 years ago

  • Status changed from En cours to Résolu
  • % Done changed from 0 to 100

#7 Updated by Gilles Grandgérard over 2 years ago

  • Status changed from Résolu to Fermé
  • Remaining (hours) set to 0.0

Also available in: Atom PDF