Intéressé par des cours d'informatique en ligne ?
Visitez mon nouveau site
https://www.yesik.it !
Il vous est sans doute déjà arrivé de devoir renommer une base de données. Par exemple, parce que vous constatez après l'avoir créée que vous vous êtes trompé dans son nom. Ou parce qu'au cours du développement votre base a évolué et que son nom ne reflète plus la réalité de votre projet. Ou pour plein d'autres raisons qui vous sont personnelles…
Malheureusement, MySQL ne supporte pas la commande ALTER DATABASE ... RENAME TO ... Mais il existe d'autres solutions. Afin de vous aider, cet article présente non pas une mais quatre techniques – enfin trois sérieuses et une à éviter – pour renommer une base MySQL. Ces possibilités sont présentées ci-dessous par ordre de préférence décroissant. Et bien entendu, ce qui est valable pour MySQL l'est aussi pour MariaDB…
RENAME DATABASE
J'y reviens en fin d'article, mais ça ne fait pas de mal de l'évoquer tout de suite: entre les versions 5.1.7 et 5.1.23, MySQL a possédé une requête SQL RENAME DATABASE. L'utilisation de cette requête est fortement déconseillée [1] car elle peut entraîner des pertes de données!
Sommaire
À privilégier: utiliser une sauvegarde
Cette technique a pour avantage de fonctionner quel que soit le type de vos tables (y compris InnoDB) et d'être utilisable en toute sécurité sur une base de donnée live. Si vous faites ce dernier choix cependant, il faut avoir conscience que les modifications effectuées après le début de la procédure ne seront sans doute pas présentes dans la base copiée. Mais au moins cela vous permet de conserver la base accessible en lecture pendant la transition.
Le truc ici va simplement consister à faire une sauvegarde (un backup) de la base existante à l'aide de l'utilitaire mysqldump qui devrait être disponible dans toute distribution de MySQL. Puis de ré-importer ce backup dans une autre base. Accessoirement, cette technique vous permettra aussi de migrer une base d'un serveur à un autre, mais ce n'est pas l'objet de cet article.
Remarque:
Dans les exemple ci-dessous, je me connecte sous l'identité root. Mais n'importe quelle identité ayant les permissions nécessaires pour créer une base, ajuster les permissions et lire la base originale devrait pouvoir être utilisée.
# Faire un backup de la base -- utilisez vos options habituelles ici sh$ mysqldump --quick --single-transaction --routines --triggers --user=root --password MyDatabase > backup.sql
# Creér une nouvelle base de données et y importer la sauvegarde sh$ mysql --user=root --password Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. mysql> CREATE DATABASE MyNewDatabase; Query OK, 1 row affected (0.06 sec) mysql> use MyNewDatabase Database changed mysql> source ./backup.sql
Une fois l'exécution du script SQL terminée, vous allez vous retrouvez avec deux exemplaires de la même base de données.
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | MyDatabase | | MyNewDatabase | | mysql | +--------------------+ 4 rows in set (0.00 sec)
Mais avant de pouvoir effectuer la transition de l'une à l'autre dans vos applications clientes, il vous faudra récupérer les permissions de l'ancienne base pour les affecter à la nouvelle. Je vous renvoie en fin d'article pour découvrir comment migrer les permissions. Cette étape étant commune aux trois méthodes principales que je décris ici pour renommer une base MySQL.
Remarque:
À partir de maintenant, vous pouvez à n'importe quel moment supprimer votre ancienne base de données avec DROP DATABASE puisque dans MySQL cette commande ne fait pas disparaitre les permissions relatives à la base de données supprimée.
À envisager: RENAME TABLE ... TO
Si passer par un dump comme expliqué ci-dessus ne vous convient pas, il existe une autre technique utilisable aussi bien avec des tables (My)ISAM que InnoDB – y compris utilisant les contraintes d'intégrité référentielle.
En effet, vous savez peut-être que MySQL permet de renommer une table à l'aide de la commande RENAME TABLE. Mais, à l'instar de la commande Unix mv, RENAME TABLE permet aussi bien de renommer une table dans une base, que de déplacer une table d'une base à l'autre (voire les deux à la fois). Cerise sur le gâteau, les clés étrangères contenues dans les autres tables de la base sont mises à jour automatiquement pour refléter ce changement.
Piège:
Cette technique ne fonctionnera pas si des triggers sont associés à l'une ou l'autre des tables que vous souhaitez déplacer dans une autre base de donnée.
Pour illustrer rapidement cette technique, un exemple. Sur mon serveur MySQL 5.1.49 (Debian Squeeze), j'ai la base de données MyDatabase contenant deux tables T1 et T2:
mysql> use MyDatabase mysql> SHOW CREATE TABLE T1 \G Create Table: CREATE TABLE `T1` ( `id` int(11) NOT NULL, `text` char(50) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 mysql> SHOW CREATE TABLE T2 \G Create Table: CREATE TABLE `T2` ( `data` char(200) DEFAULT NULL, `id` int(11) DEFAULT NULL, KEY `id` (`id`), CONSTRAINT `T2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `T1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
Comme vous le voyez, la seconde table possède une clé étrangère qui fait référence à la colonne id de la première table. Les deux tables sont dans la même base de donnée. Rien d'extraordinaire.
Pour donner l'impression que la base a été renommée, il me suffit d'en créer une nouvelle, puis d'y déplacer mes différentes tables:
mysql> use MyDatabase
mysql> CREATE DATABASE MyNewDatabase;
Query OK, 1 row affected (0.00 sec)
-- Déplacer une table
mysql> RENAME TABLE T1 TO MyNewDatabase.T1;
Query OK, 0 rows affected (0.00 sec)
-- Vérification du déplacement
mysql> SHOW TABLES FROM MyDatabase \G
Tables_in_MyDatabase: T2
mysql> SHOW TABLES FROM MyNewDatabase \G
Tables_in_MyNewDatabase: T1
-- Vérification de la mise à jour de la clé étrangère de l'autre table
mysql> SHOW CREATE TABLE T2 \G
Create Table: CREATE TABLE `T2` (
`data` char(200) DEFAULT NULL,
`id` int(11) DEFAULT NULL,
KEY `id` (`id`),
CONSTRAINT `T2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `MyNewDatabase`.`T1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
-- Déplacer l'autre table
mysql> RENAME TABLE T2 TO MyNewDatabase.T2;
Query OK, 0 rows affected (0.04 sec)
-- et vérifier
mysql> SHOW CREATE TABLE MyNewDatabase.T2 \G
Create Table: CREATE TABLE `T2` (
`data` char(200) DEFAULT NULL,
`id` int(11) DEFAULT NULL,
KEY `id` (`id`),
CONSTRAINT `T2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `T1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Remarque:
Ici, j'ai procédé table par table et j'ai rajouté des vérifications à chaque étape pour illustrer la mise à jour automatique des références entre tables. Mais la syntaxe de RENAME TABLE ... TO permet de renommer/déplacer plusieurs tables à la fois:
mysql> RENAME TABLE T1 to MyNewDatabase.T1, T2 TO MyNewDatabase.T2;
Comme vous le voyez, ça fonctionne et c'est simple. Mais attention! si j'ai parlé ici de renommer la base, c'est par abus de langage: en effet, comme pour les autres techniques que j'expose dans cet article, il s'agit plutôt de créer une nouvelle base, et d'y déplacer les tables de l'ancienne base. Cela veut dire que les méta-informations relatives à la base de donnée elle-même ne sont pas propagées. Par exemple, l'encodage par défaut des caractères. De la même manière, ce sera à vous d'ajuster manuellement les permissions pour cette nouvelle base et ses tables. Pour ce faire, je vous renvoie en fin d'article ou ce dernier point est expliqué.
À défaut: renommer le dossier contenant la base
Cette troisièle technique prend une approche radicalement différente, puisqu'elle consiste à renommer sur le disque le dossier contenant les fichiers de la base de données. Cette option n'est à utiliser que:
- si aucune des options précédentes n'est possible
- et si votre base de données ne contient que des tables ISAM ou MyISAM.
Dans la pratique, cette solution historique ne sera sans doute à utiliser que dans le cas d'une base de données tournant avec une (très) vielle version de MySQL. Dernier avertissement, même si pour l'illustration je décris la manipulation sur une base live, je vous conseille si c'est possible de ne travailler qu'avec la serveur de base de données arrêté.
Avant de vous montrer comment faire, une petite explication sur la manière dont MySQL gère ses fichiers de données peut être utile pour comprendre pourquoi cette option fonctionne.
Qu'est-ce qu'une base MySQL?
Sur le disque, une base de données MySQL, n'est rien de plus qu'une collection de fichiers stockés dans un dossier [2]. Selon votre système d'exploitation et la manière dont vous avez installé MySQL, l'emplacement de ce dossier varie. Par exemple, sous Debian/Lenny – avec MySQL5 installé à partir des paquets, les bases sont dans /var/lib/mysql. Une recherche sur votre disque dur devrait vous permettre de trouver sans trop de difficulté l'emplacement correspondant à votre installation.
Côte mysql
Afin d'examiner de plus près le contenu de ce dossier, nous allons nous appuyer sur un exemple:
sh$ mysql -u root -p Enter password: mysql> CREATE DATABASE MyDatabase; Query OK, 1 row affected (0.06 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | MyDatabase | | mysql | +--------------------+ 3 rows in set (0.08 sec) mysql> CREATE TABLE MyDatabase.MaTable (MaColonne INT); Query OK, 0 rows affected (0.03 sec) mysql> GRANT SELECT,INSERT,UPDATE ON MyDatabase.* TO MonUtilisateur IDENTIFIED BY "password"; Query OK, 0 rows affected (0.04 sec)
Vous savez que les requêtes précédentes ont respectivement créé une base de données et une table dans celle-ci. J'ai aussi créé un utilisateur qui peut lire, insérer ou modifier des données:
sh$ mysql -u MonUtilisateur -p
Enter password:
mysql> INSERT INTO MyDatabase.MaTable VALUES(1);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM MyDatabase.MaTable;
+-----------+
| MaColonne |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
Sur le disque
Allons voir maintenant dans /var/lib/mysql (ou l'emplacement correspondant sur votre système) ce qui s'est passé suite aux opérations effectuées précédemment:
sg$ ls -l /var/lib/mysql
total 20532
-rw-r--r-- 1 root root 0 2010-02-10 14:21 debian-5.0.flag
-rw-rw---- 1 mysql mysql 10485760 2010-02-10 14:21 ibdata1
-rw-rw---- 1 mysql mysql 5242880 2010-02-10 14:21 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 2010-02-10 14:21 ib_logfile1
drwx------ 2 mysql mysql 4096 2010-02-10 14:54 MyDatabase
drwxr-xr-x 2 mysql root 4096 2010-02-10 14:21 mysql
-rw------- 1 root root 7 2010-02-10 14:21 mysql_upgrade_info
Comme vous le constatez un dossier portant le nom MyDatabase est présent. C'est dans ce dossier que sont stockées les données (et les méta-informations) de la base que je viens de créer. Remarquez aussi le dossier mysql. C'est lui qui contient la base de données administrative mysql (celle qui, entre autre, gère les permissions des utilisateurs). Vous voyez aussi un certain nombre de fichiers de journaux pour le moteur InnoDB, et pour le fonctionnement interne de MySQL. Plus, un marqueur installé par le paquet MySQL de Debian.
Mais revenons sur le dossier qui nous intéresse en premier lieu ici: /var/lib/mysql/MyDatabase:
sh# ls -l /var/lib/mysql/MyDatabase/ total 20 -rw-rw---- 1 mysql mysql 65 2010-02-10 14:52 db.opt -rw-rw---- 1 mysql mysql 8570 2010-02-10 14:54 MaTable.frm -rw-rw---- 1 mysql mysql 0 2010-02-10 14:54 MaTable.MYD -rw-rw---- 1 mysql mysql 1024 2010-02-10 14:54 MaTable.MYI
Le contenu de ce dossier va dans le sens des explications données jusqu'à présent. Ainsi, on y trouve les fichiers correspondants à la table que j'ai créé.
Renommer la base/le dossier
Les manipulations précédentes avaient pour but d'illustrer ce qui est expliqué dans la documentation de MySQL – à savoir qu'une base MySQL correspond à un dossier. Du coup, on comprend qu'il est possible de renommer une base MySQL simplement en renommant le dossier qu'elle contient:
sh# mv /var/lib/mysql/MyDatabase /var/lib/mysql/MyNewDatabase
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| MyNewDatabase |
| mysql |
+--------------------+
3 rows in set (0.00 sec)
On essaye notre nouvelle base?
sh$ mysql -u MonUtilisateur -p Enter password: mysql> INSERT INTO MyDatabase.MaTable VALUES(1); Query OK, 1 row affected (0.00 sec)
Hein? Ça marche avec l'ancien nom de la base? Mais alors, j'ai renommé ma base ou pas? Ré-essayons – mais cette fois-ci après avoir redémarré MySQL:
sh# /etc/init.d/mysql restart Stopping MySQL database server: mysqld. Starting MySQL database server: mysqld. Checking for corrupt, not cleanly closed and upgrade needing tables..
sh$ mysql -u MonUtilisateur -p Enter password: mysql> INSERT INTO MyDatabase.MaTable VALUES(1); ERROR 1146 (42S02): Table 'MyDatabase.MaTable' doesn't exist
Comment ça? Un coup ça marche, un coup ça marche pas? La différence entre ces deux exemples est que j'ai redémarré le serveur. En effet, la première tentative de connexion a fonctionné parce que le serveur avait déjà ouvert les fichiers de la base. Dans ces conditions, renommer le dossier qui contient ces fichiers est relativement transparent. Par contre, quand on redémarre le serveur, évidemment les tables ne sont plus ouvertes. Et là, par contre, le nom de la base est important pour retrouver ces fameux fichiers.
Remarque:
J'aurais pu aussi forcer la fermeture des tables avec la requête FLUSH TABLES. En production – ou même sur une machine de développement hébergeant plusieurs bases – c'est sans doute plus élégant!
Qu'à cela ne tienne, tentons d'insérer avec la bonne base:
mysql> INSERT INTO MyNewDatabase.MaTable VALUES(1); ERROR 1142 (42000): INSERT command denied to user 'MonUtilisateur'@'localhost' for table 'MaTable'
Et oui! MonUtilisateur a les permissions sur MyDatabase, pas sur MyNewDatabase...
Ainsi, le serveur MySQL n'a pas automatiquement transféré les permissions vers la nouvelle base. Remarquez que cela se comprend, vu que le renommage a plus ou moins eu lieu dans le dos du serveur! C'est donc à vous de transférer les droits. Je vous renvoie en fin d'article pour découvrir comment migrer les permissions, puisque cette technique sera la même quelle que soit la manière dont vous aurez renommer votre base.
À ne jamais faire: utiliser la commande RENAME DATABASE
[...] use of this statement could result in loss of database contents, which is why it was removed. Do not use RENAME DATABASE in earlier versions in which it is present.
Entre MySQL 5.1.7 et MySQL 5.1.23 il a existé une commande RENAME DATABASE. Celle-ci a été supprimée car elle pouvait entrainer des pertes de données. Pour paraphraser la documentation officielle de MySQL, même si elle est disponible dans votre version de MySQL, n'utilisez jamais cette commande.
Si je la mentionne ici, c'est juste parce qu'elle est parfois recommandée sur d'autres sites. Mais les conseilleurs ne sont pas forcément les payeurs…
À ne pas oublier: ajuster les permissions
Que vous ayez renommé votre base en passant par un blackup, en déplaçant les tables ou en modifiant sauvagement le nom de dossiers, il vous reste une étape avant de terminer: ajuster les permissions de la nouvelle base. Là, deux écoles. Avec toutes deux leurs défauts:
- recréer toutes les permissions à la main
- c'est fastidieux – et source d'erreur. En plus, après avoir recréé les permissions il faudra aussi penser à supprimer celles sur l'ancienne base.
- migrer les permissions
- avec cette solution on met à jour la table des permissions en changeant l'ancien nom de la base par le nouveau. Avec pour inconvénient qu'il faut toujours réfléchir à deux fois avant d'aller modifier les tables administratives de MySQL – et que cette technique est susceptible de devenir obsolète le jour ou MySQL AB décidera de changer la manière de gérer les permissions…
Avec toutes les réserves évoquées, c'est tout de même la seconde option que je vais détailler. En effet, le première n'étant qu'une succession de GRANT et REVOKE, elle ne présente que peu d'intérêt à étudier.
Bref, pour gérer les permissions, MySQL utilise des données stockées dans un certain nombre de tables: mysql.db, mysql.host, mysql.tables_priv, mysql.columns_priv, mysql.procs_priv [3]. Il va donc falloir modifier dans chacune de ces tables le nom de la base de données pour refléter les modifications effectuées:
mysql> UPDATE mysql.db SET Db='MyNewDatabase' WHERE Db='MyDatabase'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> UPDATE mysql.host SET Db='MyNewDatabase' WHERE Db='MyDatabase'; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql> UPDATE mysql.tables_priv SET Db='MyNewDatabase' WHERE Db='MyDatabase'; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql> UPDATE mysql.columns_priv SET Db='MyNewDatabase' WHERE Db='MyDatabase'; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql> UPDATE mysql.procs_priv SET Db='MyNewDatabase' WHERE Db='MyDatabase'; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0
Remarque:
Selon les types de permissions et les objets concernés, certaines des tables précédentes peuvent être ou ne pas être utilisées. C'est le cas dans mon exemple très basique où seule la table mysql.db a été réellement modifiée. Ailleurs aucune ligne n'a été affectée par l'UPDATE.
Néanmoins, c'est une bonne idée de systématiquement (tenter d') altérer chacune de ces tables: ça ne coûte rien, et ça peut éviter les oublis malheureux.
Dernière opération, après avoir modifié manuellement les tables des permissions, il est conseillé de forcer le serveur à les recharger:
mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)
Ressource
Des solutions alternatives:
- http://lists.mysql.com/mysql/206163
- http://lists.mysql.com/mysql/203793
- http://lists.mysql.com/mysql/217005
- http://dev.mysql.com/doc/refman/5.1/en/rename-database.html – Pourquoi il ne faut pas utiliser RENAME DATABASE
- http://dev.mysql.com/doc/refman/5.1/en/create-database.html – "A database in MySQL is implemented as a directory containing files that correspond to tables in the database"
