RESOLU Sauvegardes bases de donnée sql

livier · Le 09/06/2015, à 21:49

Bonjour,

J'ai programmé la sauvegarde quotidienne des bases de données de mon serveur par un cron.daily, puis je récupère les fichiers *.sql par rsnapshot à domicile.
Le problème : je n'arrive pas à repeupler une base de données à partir de

mysql -u root -pxxxx recup < /reserve/rsnapshot/daily.0/monserveur/var/mysqldump/masauvegarde.sql

. La base reste vide et /var/log/mysql/error.log ne signale rien.

Un extrait de mon fichier sql pour aider à y voir clair :

-- MySQL dump 10.13  Distrib 5.5.43, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: mu_blogamoi
-- ------------------------------------------------------
-- Server version	5.5.43-0+deb7u1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `mu_blogamoi`
--


DROP TABLE IF EXISTS `spip_auteurs_liens`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `spip_auteurs_liens` (
  `id_auteur` bigint(21) NOT NULL DEFAULT '0',
  `id_objet` bigint(21) NOT NULL DEFAULT '0',
  `objet` varchar(25) NOT NULL DEFAULT '',
  `vu` varchar(6) NOT NULL DEFAULT 'non',
  PRIMARY KEY (`id_auteur`,`id_objet`,`objet`),
  KEY `id_auteur` (`id_auteur`),
  KEY `id_objet` (`id_objet`),
  KEY `objet` (`objet`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `spip_auteurs_liens`
--

LOCK TABLES `spip_auteurs_liens` WRITE;
/*!40000 ALTER TABLE `spip_auteurs_liens` DISABLE KEYS */;
INSERT INTO `spip_auteurs_liens` VALUES (1,1,'article','non'),(1,2,'article','non'),(1,9,'article','non'),(1,10,'article','non'),(1,11,'article','non'),(1,12,'article','non'),(1,14,'article','non'),(1,15,'article','non'),(1,16,'article','non'),(1,17,'article','non'),(1,18,'article','non'),(1,19,'article','non'),(2,3,'article','non'),(2,6,'article','non'),(3,4,'article','non'),(3,5,'article','non'),(4,7,'article','non'),(5,8,'article','non'),(6,13,'article','non'),(7,21,'article','non');
/*!40000 ALTER TABLE `spip_auteurs_liens` ENABLE KEYS */;
UNLOCK TABLES;

et la partie utile de mon /etc/cron.daily/mysqldump :

BACKUP_DIR="/var/mysqldump"

# sauvegarde doit tourner en cron, mot de passe dans cette config, donc avec un utilisateur ad-hoc comportant seulement les privilèges : SHOW DATABASES SELECT LOCK TABLES RELOAD
MYSQL_USER="backup"
MYSQL=/usr/bin/mysql
MYSQL_PASSWORD="xxxxx"
MYSQLDUMP=/usr/bin/mysqldump

 
databases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)"`
 
for db in $databases; do
$MYSQLDUMP --user=$MYSQL_USER -p$MYSQL_PASSWORD --force --opt --databases $db > "$BACKUP_DIR/$db.sql"
# aussi essyé avec l'option --single-transaction
# meme résultat

done
exit 0

Une idée ?

Dernière modification par livier (Le 02/02/2016, à 18:40)

Rufus T. Firefly · Le 10/06/2015, à 00:46

Salut,

C'est quoi, la commande recup ?

A mon avis, il faut commencer par sélectionner la base de données dans laquelle tu veux récupérer tes données, parce que dans tes dumps il n'y a pas de commande de création de base. Ton fichier sql ne contient que des instructions de création de tables (drop if exists/create) et de peuplement (insert)

$ mysql -u root -p
mysql> use ta_base;
mysql> source ton_fichier.sql;

devrait le faire, d'après man mysql, mais je n'ai jamais essayé...

Accessoirement, comme tu travailles en root, et qu'il n'y a pas de message d'erreur, il n'est pas exclu que tes tables se trouvent dans la base propre à mysql lui-même, ce qui est une très mauvaise idée... Pour le savoir :

$ mysql -u root -p
mysql> use mysql;
mysql> show tables;

Par ailleurs, tes définitions de champs me paraissent un peu étonnantes :
id_auteur et id_objet semblent être des identifiants, donc en fin de compte des nombres de lignes...
Avec bigint, tu peux faire une table de 18446744073709551615 lignes. Et ça consomme 8 octets par ligne, pour chaque id. En plus tu ne précises pas unsigned, donc en fait ça va de -9223372036854775808 à +9223372036854775807
Si tu prends par exemple unsigned smallint, tu peux faire 65535 lignes et ça ne prend que 2 octets par id
Et si à la longue ça devient insuffisant, tu peux toujours passer à unsigned int (sans pertes, dans ce sens)
Types integer

Quand au champ "vu", il semble de type booléen (oui/non, true/false, 1/0). Ça tu peux faire avec le type tinyint, qui ne prend qu'un octet au lieu des 6 que tu prévoies avec le varchar... Ou encore mieux, le type enum

Dernière modification par Rufus T. Firefly (Le 10/06/2015, à 01:00)

livier · Le 10/06/2015, à 03:55

Merci Rufus T. Firefly pour ta contribution ...

"recup" est le nom de la base de données (créée par phpmyadmin) dans laquelle je tente ma récupération. (au départ je n'avais besoin que de récupérer une table)

La table "mysql" n'a pas été surchargée accidentellement (controlé par phpmyadmin encore)

Le contenu des tables et le type des champs est lié au système de publication pour l'internet participatif (SPIP) que j'utilise. Je ne vais pas forker pour tenir compte de tes suggestions !

Les commandes mysql proposées n'ont pas réussi à repeupler la base de donnée "recup". Une table qui y étais et qui aurait du être remplacée est restée en place. Mais au moins j'ai des messages

Query OK, 0 rows affected (0.02 sec)
 (Répété beaucoup)
Query OK, 1 row affected (0.00 sec)

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
Query OK, 0 rows affected (0.18 sec)
 (encore répété beaucoup)
Query OK, 0 rows affected (0.00 sec)

Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)
 (encore ...)

Bon, pour compléter mes tests, j'ai tenté, dans phpmyadmin de passer un copié collé de l'extrait sql donné en premier post -> base reste vide.
Pris d'un doute, je vire toutes les lignes verdatres dans la coloration syntaxique de phpmyadin : je les prend pour des commentaires, mais le format me laisse dubitatif. Ce sont les lignes commençant par /*! ou par --
-> Ahh, cela passe maintenant.

Il resterait à programme ma commande de sauvegarde pour qu'elle ne fasse pas de commentaires zarbis, mais commençant normalement par # ou alors pas de commentaires du tout.

Voilà ou j'en suis de mon enquête, améliorer mon script /etc/cron.daily/mysqldump pour que la sortie soit plus conforme ... je l'ai trouvé sur internet et adapté, mais je suis aux limites de mes compétences pour la pertinence des options aux commandes.

Qu'en pensez vous ?

Inglebard · Le 19/06/2015, à 15:20

Salut Livier,

J'utilise plus ou moins les même commandes pour faire des sauvegardes/backups de mes bases.
J'ai donc fait un petit test.
Je ne sais pas si tu as résolu ton problème. La façon dont tu sauvegardes/restaures me semble bon.

Cependant, tu devrait faire attention si tu veux réimporter tes données avec les fichiers de sauvegarde générés. Surtout si tes 2 bases (recup et celle que tu sauvegarde) sont sur le même serveur.

La commande que tu utilises pour sauvegarder :

[b]mysqldump [/b]--user=USER -pPASSWORD --force --opt --databases [b]testbase [/b]>[b] testbase .sql[/b]

Génère en début de fichier cf mysql doc :

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `testbase ` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `testbase `;

Ce qui signifie que toutes les instructions suivantes vont être exécuter sur testbase.

Donc si tu fait ceci :

[b]mysql [/b]-u root -pxxxx [b]recup [/b]< /reserve/rsnapshot/daily.0/monserveur/var/mysqldump/[b]testbase .sql[/b]

Avec avec le code générer en début de fichier, au "mieux" tu as une erreur, au "pire" tu viens de réimporter sur testbase au lieu de recup ( et aucune erreur n’apparaît mysql à fait son boulot).

Pour éviter cela et vu que tu dump chaqu'une des bases de données dans un fichier, je te conseil d'enlever "--databases" pour éviter une mauvaise manipulation irréversible.

En ce qui concerne ton deuxième post. Les commentaires généré via mysqldump ne devrait pas poser de problème.

Les "commentaires" de ce type :

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

Ce ne sont pas des commentaires mais des assignation des variables. Tous ce qui commence par /*! correspond à du code spécifique à mysql.

Les commentaires sous mysql sont sous cette forme :

# Commentaire sur une ligne uniquement

-- Commentaire sur une ligne uniquement

/* Commentaire sur une partie de ligne/une ligne entière ou sur plusieurs lignes */

/* Commentaire sur une partie de ligne
une ligne entière 
ou sur plusieurs lignes
*/

Si c'est vraiment des commentaires qui pose problème, je pense que tu devrais vérifier le contenu de ta base.

livier · Le 02/02/2016, à 18:39

Un merci tardif pour les éclaircissements ci dessus (j'avais zappé le sujet).
Tout marche bien maintenant,
récupération des bases sauvegardées sur le serveur en ligne ou local OK

Ubuntu-fr

Navigation

Liens de recherche

Annonce

#1 Le 09/06/2015, à 21:49

RESOLU Sauvegardes bases de donnée sql

#2 Le 10/06/2015, à 00:46

Re : RESOLU Sauvegardes bases de donnée sql

#3 Le 10/06/2015, à 03:55

Re : RESOLU Sauvegardes bases de donnée sql

#4 Le 19/06/2015, à 15:20

Re : RESOLU Sauvegardes bases de donnée sql

#5 Le 02/02/2016, à 18:39

Re : RESOLU Sauvegardes bases de donnée sql

Pied de page des forums