Copier DB dans une autre (en SQL)

**Pill_S** · 01/09/2006, 10h35

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

**Maximil ian** · 01/09/2006, 11h00

Salut,

Regarde du côté de mysqldump.

**Pill_S** · 01/09/2006, 11h03

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.

**Maximil ian** · 01/09/2006, 11h12

OK, j'avais mal compris

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

**Pill_S** · 01/09/2006, 11h14

Envoyé par Maximilian

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

Oui

**Maximil ian** · 01/09/2006, 11h38

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** · 01/09/2006, 11h52

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** · 01/09/2006, 13h56

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** · 01/09/2006, 14h35

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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** · 01/09/2006, 15h26

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** · 01/09/2006, 18h40

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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** · 01/09/2006, 18h51

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** · 01/09/2006, 18h59

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

**Pill_S** · 01/09/2006, 20h18

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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;
}