Copier DB dans une autre (en SQL) [Résolu]

[Pill_S]

Bonjour,

je recherche une manière de copier l'intégralité d'une base de données (structure + data) dans une autre (à des fins d'archivage de données, chaque DB contiendra en fait un an de données pour une société).

Il s'agit en fait d'une sorte de "dump", mais qui doit être réalisé sous forme de script SQL... pas d'exécution de commande système possible... tout doit être fait depuis PHP, avec mysql_query.

Quelqu'un aurait-il une piste?

Merci d'avance

[Maximilian]

Salut,

Regarde du côté de mysqldump.

[Pill_S]

hum...

Envoyé par Pill_S

Il s'agit en fait d'une sorte de "dump", mais qui doit être réalisé sous forme de script SQL... pas d'exécution de commande système possible... tout doit être fait depuis PHP, avec mysql_query.

[Maximilian]

OK, j'avais mal compris

La base d'archive est sur le même serveur ?

[Pill_S]

Envoyé par Maximilian

La base d'archive est sur le même serveur ?

Oui

[Maximilian]

Je vois deux solutions insatisfaisantes :

- Récupérer les ordres de création des tables avec SHOW CREATE TABLE, puis les exécuter sur la base d'archive et copier les données avec INSERT ... SELECT

- Utiliser BACKUP TABLE mais les index ne sont pas créés.

Ceci dit mysqldump est quand même beaucoup plus pratique...

[Pill_S]

Envoyé par Maximilian

Ceci dit mysqldump est quand même beaucoup plus pratique...

Oui, je sais, mais il faut pouvoir archiver la base depuis un site web, hébergé n'importe où.... (pas d'accès direct au serveur)

En tout cas, merci pour le conseil, je vais passer par SHOW CREATE TABLE je pense...

[Christophe Charron]

Bonjour,
bien que la conversation soit marquée "résolue", je me permets une petite intervention.
Dans phpmyadmin, il y a dans l'onglet "Opérations" une option "Copier la base de données vers:" qui pointe vers le script "db_operations.php" qui devrait être une excellente source d'inspiration ... non ?

[Pill_S]

Oui, il est vrai que je pourrais éplucher le code de phpMyAdmin... Mais après un bref coup d'oeuil, je ne comprend pas vraiment le fonctionnement de ce script...

L'implémentation de la solution précédente est quasiment terminé... Cependant, il semblerait que les index ne sont pas pris en compte par "SHOW CREATE TABLE xxx", car je reçois pas mal d'erreurs N°150 lorsque j'essaie de les recréer... Les 150 concernent de problèmes de clés étrangères, pourtant tout semble fonctionner à part les index...

Comment forcer la création des index avec SHOW CREATE TABLE ?

SVP, ça me sauverait la vie d'avoir une solution viable...

Voici le code que j'ai produit, si ça intéresse qq'un...

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
 
// Définition des constantes
define("HOST", " http://192.168.0.175/rfj/"); //URL du site
define("NOM", "root"); //Login pour la base de données
define("PASSE", ""); //Mot de passe de la base de données
define("SERVEUR", "localhost"); //Nom du serveur
define("BASE", "TIMB"); //Nom de la base de données
define("USER", "Utilisateur"); //Valeur de session des utlisateur
define("ADMIN", "Administrateur");
define("MANA", "Chef");
define("VACANCES", "Vacances"); //Nom du type de l'absence pour les vacances
 
if (!mysql_copy_db(BASE, "TEST")) {
	echo "Oups: ", mysql_error();
}
/**
 * @return boolean 
 */
function mysql_copy_db($source_db, $dest_db) {
	echo "Connexion a la base source $source_db<br>";
	$source_link = mysql_connect(SERVEUR, NOM, PASSE, true);
	if (!$source_link || !mysql_select_db($source_db, $source_link)) {
		return false;
	}
	echo "OK!<br>";
 
	echo "Connexion a la base destination $dest_db<br>";
	$dest_link = mysql_connect(SERVEUR, NOM, PASSE, true);
	if (!$dest_link) {
		return false;
	}
	echo "OK!<br>";
 
	/** TODO DELETE **/
	if (!mysql_query("DROP DATABASE $dest_db", $dest_link)) {
		return false;
	}
	/** TODO DELETE **/
 
	echo "Création de la base de données<br>";
	if (!mysql_query("CREATE DATABASE IF NOT EXISTS $dest_db DEFAULT CHARACTER SET latin1 COLLATE latin1_bin", $dest_link)) {
		return false;
	}
	echo "OK!<br>";
	if (!mysql_select_db($dest_db, $dest_link)) {
		return false;
	}
 
	// Vide les (éventuelles) tables existantes dans la base de destination
	echo "Suppression des tables existantes<br>";
	$data = mysql_query("SHOW TABLES", $dest_link);
 
	while ($row = mysql_fetch_array($data)) {
		if (!mysql_query("DROP TABLE $row[0]", $dest_link)) {
			return false;
		}
	}
 
	echo "OK!<br>";
 
	///////////////
 
	// Copie la structure de chaque table vers la suivante
	echo "Copie de structure<br>";
	$data = mysql_query("SHOW TABLES", $source_link);
	if (!$data) {
		return false;
	}
 
	while ($row = mysql_fetch_array($data)) {
		$data2 = mysql_query("SHOW CREATE TABLE $row[0]", $source_link);
		if (!$data2) {
			return false;
		}
 
		while ($row2 = mysql_fetch_array($data2)) {
			if (!mysql_query($row2[1], $dest_link)) {
				return false;
			}
		}
	}
	echo "OK!<br>";
 
	// Copie les données de chaque table source vers la cible
	echo "Copie des données<br>";
	$data = mysql_query("SHOW TABLES", $source_link);
	if (!$data) {
		return false;
	}
	while ($row = mysql_fetch_array($data)) {
		if (!mysql_query("INSERT INTO $dest_db.$row[0] SELECT * FROM $source_db.$row[0]", $source_link)) {
			return false;
		}
	}
 
	echo "OK!<br>";
	mysql_close($source_link);
	mysql_close($dest_link);
 
	return true;
}

[Pill_S]

Oups, je retire ce que j'ai dit!

En fait ça fonctionne, les index sont bien crées, mais les clés étrangères pointent sur des tables qui n'existent pas au moment de l'exécution du script....


Comment palier à ce problème? Une idée?

[Biglo]

Salut,

Désactive la contrainte de clés étrangères avant de créer tes tables (fonctionne à partir de MySQL 4 il me semble) :

Code :

SET FOREIGN_KEY_CHECKS=0

Sinon, une petite expression régulière pour pouvoir trier les CREATE TABLE en fonction de leur(s) dépendance(s), avant de les exécuter

[Christophe Charron]

Bonsoir,

Envoyé par Biglo

Sinon, une petite expression régulière pour pouvoir trier les CREATE TABLE en fonction de leur(s) dépendance(s), avant de les exécuter

Alors là, je suis preneur !! J'ai une 'tite base avec à ce jour 251 tables innodb, toutes plus ou moins reliées entre elles !! J'utilise le script de phpmyadmin qui créee les tables sans les contraintes, insere les données et crée les contraintes ensuite. Le premier qui trouve la syntaxe pour faire une create table ... sans les contraintes et ensuite un alter table add contraint ... tout seul informe les autres ok ?

[Biglo]

Bien sûr si les dépendances forment un cycle, ma méthode ne fonctionne pas

[Pill_S]

Envoyé par Biglo

Salut,

Désactive la contrainte de clés étrangères avant de créer tes tables (fonctionne à partir de MySQL 4 il me semble) :

Code :

SET FOREIGN_KEY_CHECKS=0

Nickel! Tout fonctionne maintenant

Envoyé par Biglo

Sinon, une petite expression régulière pour pouvoir trier les CREATE TABLE en fonction de leur(s) dépendance(s), avant de les exécuter

Mouais, 'ai pas envie de passer 2 jours à développez une regexp pour ce genre de choses....

En tout cas, voici le code final, qui fonctionne pas trop mal

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
 
/**
 * Copie une base de données complète dans une autre (structure + données).
 * 
 * @param $source_db Nom de la base de données source.
 * @param $target_db Nom de la base de données cible (créée avec un charset 
 *                   latin1/collate latin1_bin si inexistante.)
 * @param $debug (optionnel, défaut=false) Indique si l'affichage d'informations 
 *               sur l'avancement de la tâche doit se faire ou non.
 * 
 * @return boolean true si l'opération a réussie, false sinon.
 */
FUNCTION mysql_copy_db($source_db, $target_db, $debug = false) {
 
	IF ($debug) {
		echo "Connexion à la base source $source_db<br>";
	}
	$source_link = mysql_connect(DB_HOST, DB_USER, DB_PASS, true);
	IF (!$source_link || !mysql_select_db($source_db, $source_link)) {
		RETURN false;
	}
	IF ($debug) {
		echo "OK!<br>";
		echo "Connexion à la base cible $target_db<br>";
	}
 
	$target_link = mysql_connect(DB_HOST, DB_USER, DB_PASS, true);
	IF (!$target_link || !mysql_query("CREATE DATABASE IF NOT EXISTS $target_db DEFAULT CHARACTER SET latin1 COLLATE latin1_bin", $target_link) || !mysql_select_db($target_db, $target_link)) {
		RETURN false;
	}
 
	IF ($debug) {
		echo "OK!<br>";
	}
 
	// Vide les (éventuelles) TABLES existantes dans la base de destination
	IF ($debug) {
		echo "Suppression des tables existantes<br>";
	}
	$data = mysql_query("SHOW TABLES", $target_link);
 
	while ($row = mysql_fetch_array($data)) {
		IF (!mysql_query("DROP TABLE $row[0]", $target_link)) {
			RETURN false;
		}
	}
 
	IF ($debug) {
		echo "OK!<br>";
	}
 
	///////////////
 
	// Copie la structure de chaque TABLE vers la suivante
	IF ($debug) {
		echo "Copie de structure<br>";
	}
	mysql_query("SET FOREIGN_KEY_CHECKS = 0", $target_link);
	$data = mysql_query("SHOW TABLES", $source_link);
	IF (!$data) {
		RETURN false;
	}
 
	while ($row = mysql_fetch_array($data)) {
		$data2 = mysql_query("SHOW CREATE TABLE $row[0]", $source_link);
		IF (!$data2) {
			RETURN false;
		}
 
		while ($row2 = mysql_fetch_array($data2)) {
			IF (!mysql_query($row2[1], $target_link)) {
				mysql_close($target_link);
				RETURN false;
			}
		}
	}
	IF ($debug) {
		echo "OK!<br>";
	}
 
	// Copie les données de chaque TABLE source vers la cible
	IF ($debug) {
		echo "Copie des données<br>";
	}
	$data = mysql_query("SHOW TABLES", $source_link);
	IF (!$data) {
		RETURN false;
	}
	while ($row = mysql_fetch_array($data)) {
		IF (!mysql_query("INSERT INTO $target_db.$row[0] SELECT * FROM $source_db.$row[0]", $target_link)) {
			mysql_close($target_link);
			RETURN false;
		}
	}
 
	IF ($debug) {
		echo "OK!<br>";
	}
	mysql_query("SET FOREIGN_KEY_CHECKS = 1", $target_link);
 
	mysql_close($source_link);
	mysql_close($target_link);
 
	RETURN true;
}

[Christophe Charron]

Heu ...
à vue de nez, difficile de faire plus simple et plus clair (pas encore testé, mais je m'y emploierai rapidement. C'est vrai que le

Code :

SET FOREIGN_KEY_CHECKS = 1

donne quelques perspectives intéressantes ...