Conseils pour sauvegarde/duplication MySQL

Version imprimable

26/11/2018, 09h55
Goony89

Conseils pour sauvegarde/duplication MySQL

Bonjour,
J'ai une problèmatique mais je ne sais pas quelle piste choisir.

J'ai un NAS QNAP TS-419P II qui sert à diverses sauvegardes.
J'ai d'autres serveurs (QNAP TS-469U & Ubuntu) sur lesquels il y a des bases MySQL (envrion 5Go en tout).
Je souhaite que ces bases soit régulièrement "dupliquées" sur le serveur MySQL du TS-419P II, afin que je puisse récupérer facilement les données en cas de problème (il y a déjà une sauvegarde backup-manager, mais ce n'est pas pratique en cas de petit problème).
Je préférerais une "duplication" régulière (chaque nuit) plutôt qu'en temps réel, en cas de mauvaise manipulation des données sur les serveurs de prod.
NB : si possible, j'aimerais aussi dupliquer les utilisateurs et leurs droits.

J'envisage la réplication (https://jgrondin.developpez.com/arti...ication-MySQL/), je ne sais pas si on peut faire une réplication quotidienne, et non en temps réel.
Vue la galère pour modifier le my.cnf sur le QNAP et ma peur de faire une erreur, je préférerais être sûr que c'est la bonne solution avant de m'y lancer.
Peut-être qu'une tâche CRON qui execute un script serait plus adaptée ? Peut-être y a-t-il une méthode plus facile et adaptée ?

Merci de vos conseils

PS : Versions de MySQL & phpMyAdmin différentes, et système QNAP assez verrouillé.
27/11/2018, 11h58
Artemus24

Salut Goony89.

Citation:

Envoyé par Goony89

J'ai une problématique mais je ne sais pas quelle piste choisir.

En ski, le mieux quand on est débutant est la piste verte. :mrgreen:

Citation:

Envoyé par Goony89

Je préférerais une "duplication" régulière (chaque nuit) plutôt qu'en temps réel, en cas de mauvaise manipulation des données sur les serveurs de prod.

Dans le reste de votre message, vous parlez de réplication de vos bases.
Il faut savoir que cela se fait en temps réel, et d'après ce que j'ai compris, cela ne vous intéresse pas.
Pourtant, c'est une solution en cas de plantage plus pratique que le backup !

Citation:

Envoyé par Goony89

Vue la galère pour modifier le my.cnf sur le QNAP et ma peur de faire une erreur

Avant de faire quoi que ce soit, dupliquez votre fichier "my.cnf".

De toute façon la réplication ne touche pas votre base de données.
La réplication va créer un fichier de rotation (binary log) contenant toutes vos interventions sur la base de données, en temps réel.
Il y a des points de reprises, que vous pouvez utilisez en cas de plantage.
La manipulation n'est pas très compliquer.

Citation:

Envoyé par Goony89

je préférerais être sûr que c'est la bonne solution avant de m'y lancer.

Il n'y a pas de bonnes solutions, juste une solution adaptée à ce que vous cherchez à faire.

Pourquoi faire une duplication ? Votre réponse est :

Citation:

Envoyé par Goony89

afin que je puisse récupérer facilement les données en cas de problème

C'est le mot "facilement" qui m'interpelle ! Je suppose que vous entendez par là, une manipulation sans contrainte.

Il y aura toujours un temps d'inactivité entre le moment où il y a le plantage du serveur mysql et son redémarrage .
Celui-ci doit être le minimum afin de ne pas impacter les utilisateurs.

Avant d'envisager des solutions compliquées, le mieux est de connaitre la raison de ce plantage.
Par exemple, vous devez régulièrement faire de la maintenance sur vos bases de données.
Cela dépend de l'activité. Par exemple, un fois par semaine, voire tous les soirs.
C'est à ce moment que l'on peut envisager de faire la sauvegarde.

Si vous ne désirez pas trop vous casser la tête, il y a la technologie RAID qui est onéreuse, financièrement parlant.
Pourquoi ? Pour obtenir le mode mirroring, vous devez doubler tous vos disques dur.
Mais en cas de plantage système, vous n'avez presque rien à faire, juste changer le disque dur qui est en cause.
Mais cette solution ne vous protège pas contre un plantage applicatif.

Ensuite, qu'est-ce que vous êtes prêt à perdre ?
Dans votre message, vous êtes prêt à perdre une journée d'activité. Pourquoi pas.
Mais sachant cela, vos utilisateurs devront faire la reprise de la journée perdue, en même temps que la saisie du jour.
Autrement dit, deux fois plus de travail en cas de plantage.

Le plus simple est de faire un backup du disque dur.
Cette solution est quand même a envisager dans tous les cas.

Autre solution, est d'utiliser la crontab associée à un script réalisant le backup (mysqldump) de votre base de données.
Cela peut se faire deux fois par jour, entre midi et le soir, sans trop pénaliser vos utilisateurs.

Sinon, le mieux est d'utiliser la réplication qui sera en temps réel.
Quand on sait manipuler les fichiers "binary log", la reprise est facile à faire !

Mais la bonne question est la nature de votre plantage.
Si le disque rend l'âme, une sauvegarde de celui-ci est la seule solution envisageable pour ne pas tout perdre.
Sauf avec la technologie RAID où il vous reste toujours un disque opérationnelle.

Si c'est un problème applicatif, genre problème d'intégrité, il faut chercher la cause et envisager la solution de reprise qui peut être longue.

Ou encore, si c'est juste un arrêt de votre serveur, suite à un petit problème sans gravité, la réplication ou la dernière sauvegarde est la solution.

@+
27/11/2018, 16h23
Goony89

Bonjour,
merci de m'aider.
Pour le ski, je vais attendre encore quelques semaines :P

J'ai mis "duplication" entre guillemets car je ne sais pas s'il s'agit d'un terme consacré ou d'un abus de langage.
Les NAS sont déjà en RAID, et le serveur Ubuntu est une VM dans un VMWare en SaaS. En plus, il y a des sauvegardes quotidiennes des fichiers et un dump via backup-manager.
Je ne m'inquiète pas trop en cas de plantage de serveur ou de disque défectueux, c'est extrêmement rare et le patron comprendrait que la production soit stoppée le temps de la restauration.

Il s'agit effectivement d’accéder facilement à des données pas trop vieilles, pour les cas où je fais moi-même une boulette dans phpMyAdmin.
Par exemple, un matin mal réveillé, j'ai modifié une table importante en une requête 8O C'était un peu la galère de télécharger le dump de 2Go pour l'exécuter sur une base locale et récupérer ce dont j'avais besoin :ouin:
C'est pourquoi il ne faut pas tout de suite propager les modifications. Si la réplication permet ça, alors ça me va.

J'ai préparé une tâche CRON avec un mysqldump puis une injection du dump dans la base de secours. Je n'ai pas encore pu vérifier si les options que j'ai choisies conviennent car l'injection est extrêmement longue, ce qui me fait penser que ce n'est pas la meilleure méthode ;)
27/11/2018, 19h14
Artemus24

Salut Goony89.

Citation:

Envoyé par Goony89

... pour les cas où je fais moi-même une boulette dans phpMyAdmin.

Tel que vous l'expliquez, nous entrons dans les mauvaises manipulations.

Il est strictement interdit de venir bidouiller directement dans le serveur mysql de production.
D'où, et je comprends mieux, votre terme "duplication".
En général, il y a trois environnements, qui sont :
--> développement,
--> test,
--> production.

En gros, vous désirez faire des tests, sans tout casser en production.

Citation:

Envoyé par Goony89

Par exemple, un matin mal réveillé, j'ai modifié une table importante en une requête C'était un peu la galère de télécharger le dump de 2Go pour l'exécuter sur une base locale et récupérer ce dont j'avais besoin

Et donc, vous aimez travailler sans filet !!!

Plusieurs remarques :

1) une base de données de 2Go ?
Cela ne serait pas un peu trop important, comme volumétrie ?
Avez-vous envisager de créer des partitions pour les tables les plus volumineuses ?

2) Avez-vous besoin d'autant ?
Vous devriez archiver ce qui est le plus ancien, si cela n'est plus utilisé.
D'une part, cela va vous faire gagner du temps en terme de performance, et d'autre part, vous gagnerez en volumétrie au cas où vous aurez besoin de restaurer votre base de données.

3) arrêtez de travailler directement avec phpmyadmin.
Apprenez à utiliser des scripts mysql.
Je travaille sous windows 10 pro, et j'utilise le batch windows pour lancer la console mysql.
Et dans cette console, j'appelle un script que je peux stocker et réutiliser quand j'en ai besoin.
Par exemple, tout ce qui concerne la maintenance.

4) arrêtez de travailler directement en production.
Si vous ne pouvez pas faire autrement, apprenez à utiliser le mode transactionnelle.
Si votre bidouille n'est pas conforme à ce que vous attendez, vous pouvez détricoter ce que vous venez de faire.
Cela évite de gros problème !!!

5) avant de basculer en production, faites vos tests dans un environnement qui ne craint rien.
D'où l'intérêt d'avoir un environnement de test, pour justement tester votre script avant de passer en production.
Bien sûr, la grosse boulette est aussi à envisager en test. Dans ce cas, pensez aussi à revenir à la façon dont vous écrivez votre script.

6) pour la duplication, le mieux est d'utiliser la crontab, avec un script mysql qui va créer la sauvegarde de votre base de données, de la veille.
Cela se fait par la commande "mysqldump", et vous n'êtes pas obligé de tout prendre. Vous pouvez sélectionner ce dont vous avez besoin.
Je pense que c'est la meilleure solution, vu que vous n'avez pas besoin d'avoir le traitement du jour.

7) apprenez à travailler correctement.
Par exemple, vous avez besoin de faire une grosse modification en production.
Faites les choses étape par étape.
--> extrayez vos lignes et stockez les dans une base temporaire.
Oui, on peut avec mysql, travaillez dans deux bases de données sous le même serveur mysql.
--> dans votre base temporaire, faites votre bidouille.
--> vérifiez ce que vous avez fait, par quelqu'un d'autre si possible.
Je dis cela, car vérifier par vous même vos propres erreurs d'interprétation ne va pas les rendre valide.
--> et quand la bidouille est correcte, alors vous pouvez basculer en production.
Je sais, c'est lourd à mettre en oeuvre, mais cela vous garantie de ne pas faire la grosse boulette qui va pénaliser tous les utilisateurs.

Citation:

Envoyé par Goony89

C'est pourquoi il ne faut pas tout de suite propager les modifications. Si la réplication permet ça, alors ça me va.

Comme vous avez besoin d'un environnement de test, la réplication n'est pas le solution à votre problème.

Citation:

Envoyé par Goony89

J'ai préparé une tâche CRON avec un mysqldump puis une injection du dump dans la base de secours.

Oui, c'est bien cela que vous devez faire.
Mais ne vous arrêtez pas en si bon chemin.
Vous pouvez après l'export, effectuer un import dans votre environnement de test.
A faire bien sûr, la nuit quand plus aucun utilisateur travaille.

Citation:

Envoyé par Goony89

Je n'ai pas encore pu vérifier si les options que j'ai choisies conviennent car l'injection est extrêmement longue, ce qui me fait penser que ce n'est pas la meilleure méthode

Qu'est-ce qui vous dérange ? Le temps mis pour créer votre environnement de test.
Comme je l'ai dit ci-dessus, peut-être que vous n'avez pas besoin de tout exporter, juste quelques tables, les plus importantes.
Voire même, ne prendre que les lignes sur une plage disons de 1 an, si vous marquez vos lignes avec une date de saisie.

Une autre solution consiste à faire un backup du disque qui contient le serveur mysql de production est de l'installer sur un autre disque, celui destiné à l'environnement de test.

@+