comment terminer le dump de la sauvegarde quotidienne de mysql sans dépassement de délai ?

Je suis d'accord avec theotherreceive ; le verrouillage est probablement ce qui cause les timeouts. Si c'est le cas, vous pouvez créer un esclave mysql et faire des dumps de celui-ci (de préférence sur un matériel séparé, mais peu importe). Cela empêchera votre base de données principale d'être verrouillée pendant les vidages, et l'esclave rattrapera le maître une fois les vidages terminés.

La méthode que j'utilise (pour Postgres sur FreeBSD) est la suivante :

Configurer un serveur esclave (Vous en avez un, juste au cas où votre base de données principale serait frappée par la foudre, n'est-ce pas ?)
Assurez-vous que le répertoire de données de l'esclave se trouve sur son propre système de fichiers (cela facilite la vie par la suite !).

1. Au moment de la sauvegarde, arrêter le serveur esclave (Replication Stops)
2. Réaliser un instantané du système de fichiers du FS de données (5 à 10 secondes).
3. Redémarrer l'esclave (la réplication reprend)
4. Montez le snapshot et sauvegardez son contenu comme vous le souhaitez.
   • Comme il s'agit d'un instantané, il ne change pas, et comme il a été réalisé lorsque votre BD a été arrêtée, il est garanti d'être dans un bon état de repos.
5. Démontez et supprimez l'instantané.

Cela serait identique pour MySQL.

Si votre serveur/FS ne peut pas faire de snapshots, déplacez l'étape 3 à la fin et omettez les étapes 2 et 5 (la réplication est arrêtée pendant la durée de la sauvegarde, mais vous avez la garantie d'une sauvegarde cohérente, et comme il s'agit d'un esclave, vos clients ne savent même pas que cela se passe).

Si toutes vos tables sont InnoDB, alors vous pouvez mysqldump avec --single-transaction. Cela déchargera la base de données dans un état cohérent tout en permettant à d'autres mises à jour de vos tables de se produire dans d'autres transactions. J'ai migré un certain nombre de systèmes vers InnoDB juste pour avoir cette fonctionnalité.

Si toutes vos tables sont InnoDB, vous pouvez utiliser --single-transaction. Cela ne verrouillera la base de données que brièvement, en s'appuyant sur l'instantané cohérent interne disponible dans une transaction pour terminer la sauvegarde.

Si ce n'est pas le cas, vous devrez faire quelque chose d'autre ; si vous êtes sous Linux, vous pouvez utiliser un instantané LVM à condition que vous utilisiez LVM ET que vous ayez de l'espace pour un instantané (vous aurez toujours besoin de vider les tables pendant que vous faites l'instantané, mais l'instantané est rapide).

Si votre périphérique de stockage sous-jacent (par exemple, un contrôleur RAID) prend en charge les instantanés, vous pouvez également en utiliser un.

Utilisez-vous InnoDB ? Si oui, jetez un coup d'œil à l'outil xtrabackup de Percona. Ou plus précisément, utilisez leur innobackupex script qui enveloppe xtrabackup et ajoute la prise en charge du vidage de toutes les tables MyISAM, entre autres choses.

Il peut faire des sauvegardes en ligne (sans verrouillage pour InnoDB) et la sortie est un répertoire de données mysql valide que vous pouvez copier en place pour restaurer, ce qui rend les restaurations beaucoup plus rapides que la restauration d'un dump.

Il prend également en charge le streaming des sauvegardes vers d'autres machines, les sauvegardes incrémentielles, et est tout simplement un outil très robuste et utile.

Je l'utilise pour sauvegarder des bases de données extrêmement chargées de l'ordre de 500 Go et plus sans aucun problème et avec un retard de réplication très faible, même lorsque la charge est particulièrement élevée.

Voici un exemple d'utilisation qui prend une sauvegarde et applique ensuite les journaux dans la sauvegarde pour la rendre viable pour la restauration :

innobackupex /var/backups/db
innobackupex --apply-log --use-memory=1G /var/backups/db

La restauration de cette sauvegarde sur une boîte ressemblerait alors à ceci (le chemin du répertoire de données mysql peut varier selon la distribution) :

cp -r /var/backups/db /var/lib/mysql/data
chown -R mysql:mysql /var/lib/mysql/data

La documentation de Percona pour cet outil est assez bonne, vous pouvez donc en savoir plus à ce sujet :

http://www.percona.com/doc/percona-xtrabackup/innobackupex/innobackupex_script.html

Quel que soit votre choix, assurez-vous de le tester, y compris la partie restauration !