Tâche #26178: Probleme sur le cron de backup des BDD - Distribution EOLE - Ensemble Ouvert Libre Évolutif

Tâche #26178

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

Probleme sur le cron de backup des BDD

Ajouté par Arnaud FORNEROT il y a plus de 5 ans. Mis à jour il y a plus de 5 ans.

Statut:

Fermé

Priorité:

Normal

Assigné à:

Joël Cuissinat

Version cible:

sprint 2018 50-51 Equipe MENSR

Début:

06/12/2018

Echéance:

% réalisé:

100%

Restant à faire (heures):

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"

Demandes liées

Révisions associées

Révision 5e385a5f (diff)
Ajouté par Joël Cuissinat il y a plus de 5 ans

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

Ref: #26178

Révision 81913901 (diff)
Ajouté par Joël Cuissinat il y a plus de 5 ans

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

Ref: #26178

Historique

#1 Mis à jour par Joël Cuissinat il y a plus de 5 ans

Tracker changé de Demande à Tâche
Statut changé de Nouveau à En cours
Assigné à mis à Joël Cuissinat
Tâche parente mis à #25625

#2 Mis à jour par Joël Cuissinat il y a plus de 5 ans

--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.