Clicky

Importer un gros fichier SQL dans MySQL via PHPMyAdmin

Quand un site commence à prendre de la bouteille, et donc du volume, tout devient beaucoup plus compliqué, surtout au niveau des maintenances. Déménager une base de données atteignant les 200 Mo ou 300 Mo, c’est évidemment moins facile que d’en faire de même avec une base de données de 20 Mo, d’autant plus lorsqu’on utilise MySQL. Fort heureusement, il existe plusieurs solutions pour importer un gros fichier SQL dans MySQL via PHPMyAdmin et nous allons justement nous intéresser à l’une d’entre elles. Une solution simple, efficace et qui va vous permettre d’outrepasser la limite fixée par la célèbre BDD sans avoir à bidouiller les fichiers de configuration.

Importer un gros fichier SQL dans MySQL via PHPMyAdmin

Sachez qu’il existe de nombreuses méthodes pour importer un fichier volumineux dans MySQL. Il est ainsi possible de modifier la configuration de ce dernier ou même de passer directement par le terminal pour mener à bien cette délicate opération. L’avantage de notre méthode, c’est qu’elle est simple d’utilisation et qu’elle ne nécessitera pas de mettre les mains dans le cambouis. Bien sûr, si vous avez des compléments ou des précisions à apporter à cet article, vous êtes les bienvenus.

Exporter (correctement) sa base via PHPMyAdmin

Mais avant de nous amuser à importer nos énormes fichiers, encore faut-il exporter correctement nos données. Car en effet, le script que nous allons ensuite utiliser est assez… hum… sensible, et il convient donc de bien préparer notre fichier avant de nous amuser avec.

Pour commencer, nous allons donc nous connecter à notre PHPMyAdmin. Une fois que c’est fait, il va falloir cliquer sur l’onglet « Exporter ». Là, tout un tas d’options vont apparaître sous nos yeux ébahis. On va commencer par sélectionner les tables à exporter (on peut cliquer sur « tout sélectionner » si on souhaite récupérer l’intégralité de nos données) puis on va se tourner du côté de la colonne latérale de droite. Là, c’est relativement simple et voilà donc ce qu’il faut faire :

  • Cocher la case « Structure ».
  • Cocher la case « Drop TABLE / VIEW / PROCEDURE /etc ».
  • Cocher la case « Inclure la valeur courante de l’auto-increment ».
  • Cocher la case « Données ».
  • Cocher la case « Insertions complètes ».
  • NE PAS COCHER la case « Insertions étendues ».

Bien évidemment, ces options sont à adapter en fonction de votre situation et en fonction de ce que vous voulez faire de votre sauvegarde. Néanmoins, si vous souhaitez récupérer la structure de votre base, alors sachez qu’il faudra absolument cocher la case « Drop TABLE » pour que notre script fonctionne correctement. Même chose d’ailleurs pour la case « Insertions étendues » qui devra nécessairement être décochée.

Une fois que toutes les options sont correctement paramétrées, on peut cocher en dessous la case « Transmettre », indiquer le nom de son fichier et opter pour le format de compression gzippé puisque notre script sera tout à fait en mesure de le lire et de le comprendre. Sinon, l’autre option, c’est de désactiver la compression mais là, on va se retrouver avec un énooooorme fichier dans les pattes donc c’est à vous de voir.

Importer son gros fichier via un script PHP

Okay, donc après quelques secondes / minutes / heures (rayez la mention inutile), notre fichier va se retrouver sur le disque dur de notre ordinateur. C’est bien mais le plus dur reste à faire puisque nous allons maintenant devoir l’importer dans l’autre base de données. Pour se faire, il faudra commencer par télécharger le script dont nous allons avoir besoin : BigDump. Ensuite, on pourra l’ouvrir dans notre éditeur préféré. Notez d’ailleurs que toutes les instructions pour l’utiliser figure dans l’entête du fichier. Plus bas, nous allons également trouver quelques lignes dédiées à notre nouvelle base de données. Vous n’êtes pas obligé de les éditer mais si vous saisissez l’adresse de votre serveur, le nom de votre base ainsi que vos identifiants, vous gagnerez ensuite un temps précieux alors n’hésitez pas à le faire.

Ce qui est nettement plus important, en revanche, c’est l’encodage de l’outil. C’est effectivement à vous de saisir l’encodage de votre base directement dans le script PHP. Il faut pour cela ouvrir ce dernier, rechercher l’expression « $db_connection_charset » et saisir le code correspondant à l’encodage de votre base. Si vous ne le faites pas, alors vous risquez d’avoir de gros problèmes avec les caractères accentués de votre base.

Et après alors ? Comment importer notre gros fichier dans MySQL ?

C’est assez simple, en fin de compte, et il vous suffit tout bonnement de suivre les étapes suivantes :

  • Télécharger BigDump.
  • Editer le script PHP.
  • Saisir toutes les informations concernant notre base.
  • Copier le script et le gros fichier.
  • Créer un nouveau dossier sur le FTP où se trouve notre BDD.
  • Coller les deux fichiers dedans.
  • Ouvrir le navigateur, se rendre à l’adresse pointant vers notre script.
  • Cliquer sur le bouton qui va bien et attendre patiemment.

Là, l’import de notre fichier va démarrer tranquillement. Il faudra bien évidemment se montrer patient, la procédure peut durer un moment et tout va finalement dépendre de la taille de notre fichier. Avec l’export de la Fredzone (fichier de 150 Mo environ) et en localhost, il m’a fallu attendre quelque chose comme deux ou trois minutes. Dans tous les cas, il ne faut surtout pas interrompre l’opération sous peine de se retrouver avec une base de données inexploitable.

Une fois que c’est terminé, pensez également à supprimer le dossier contenant le script et votre sauvegarde de votre FTP. Ce serait quand même dommage qu’un petit malin le trouve et qu’il lance alors un import dans votre base derrière votre dos.

En espérant que cela vous soit utile, naturellement.

Mots-clés mysqltutorielsweb

Share this post

Frédéric Pereira

Floodeur compulsif, est très actif sur Twitter ou encore sur Facebook. Sachez en outre que la Fredzone a une page sur Google+.

  • SequelPro est pas mal aussi pour restore des gros dumps.

  • rodskin

    Ouaip, bonne idée.
    Petits compléments:
    Ajouter drop tables supprimera ls tables avant de les réimporter. car on ne peut pas importer une ligne avec le même id (d’ou la suppression avant :))
    L’encodage est très important pour les jeux de caracteres accentués et dépend de l’encodage de la base.

    Par contre, je préfère nettement psser par une ligne de commande car c’est BEAUCOUP plus rapide ;) certes moins accessibles aux débutants mais une ligne de commande c’est pas énorme:

    sous windows il faut se placer en ligne de commande dans le dossier bin de mysql pui:
    mysql -h localhost -u votre_login -p -B la_base_de_données log_sql

    ce qui importera ici un fichier gzippé et loguera dans le fichier log_sql

  • Kerweb

    Quelle version de PHPMyAdmin tu utilises ? Car sur la 3.5.1 (une des dernières), les options ne sont pas présentées pareil :)

    • @Kerweb: Une pas si vieille que tu le penses :p

  • rodskin

    erf, la ligne UNIX a sauté

    zcat fichier_exporte.sql.gz | mysql nom_de_bd –show-warnings > log_sql

  • Rolland

    Pour ma part j’utilise la commande source de MySql (sous Windows aussi) pour importer des gros dump (1Go+)… c’est relativement rapide…

  • @ Tous : Ouais, mais pour la commande, tout le monde n’a justement pas accès à un terminal, c’est bien le problème :)

  • rodskin++
    ‘Par contre, je préfère nettement passer par une ligne de commande car c ’est BEAUCOUP plus rapide ;) ‘
    Fred–
    ‘Ouais, mais pour la commande, tout le monde n ’a justement pas accès à un terminal’
    Quand tu as 200Mo de dump, c’est que soit de la donnée utile soit tu as inséré 10fois trop de données.
    Dans les deux cas, j’estime qu’un webmaster doit se donner les moyens d’un accès SSH. Ca coute 5€/mois chez OVH ou 1and1.
    C’est pas la mer à boire non plus, et ce n’est pas une ligne de commande qui va tuer le monde..
    S.

    • rodskin

      @syndrael: je suis d’accord avec @Fredzone dans ce sens, souvent l’hébergeur d’un client ne nous donne pas accès par SSH du coup ce script meme s’il est long à exécuter est utile :p

      Par contre ce qui peut être gênant c’est le temps limite d’exécution de script sur le serveur…

      • @rodskin: Voilà :D

        • @Fred: Raison de plus pour se donner les moyens d’avoir un hébergeur un peu ‘sérieux’ tout du moins pour les particuliers.
          Changer d’hébergeur.. payer 5€/mois pour faire du SSH et gagner du temps en backup, et synchro, et voire tâche planifiée.. à mon sens y’a pas photo. L’accès SSH offre des perspectives intéressantes et optimise surtout les process manuelles souvent répétitives,laborieuse.. et SURTOUT hazardeuse (un dump qui plante au milieu à cause de PHP, ça n’a pas de prix ;-D )
          S.

          • rodskin

            @syndrael:
            En effet mais
            1) le client a pas forcément envie / s’en fout de payer 5€/ mois pour du ssh
            2) il a pas forcement envie de payer encore ;)

          • @syndrael: Je ne suis pas d’accord avec toi. Tiens, par exemple, la Fredzone est sur du mutualisé chez OVH (avec un SQL Privé derrière quand même) et ça me va très bien comme ça. Note d’ailleurs que mon serveur tient bien la route, j’ai fait jusqu’à 65.000 VU sur une journée et il n’a pas bronché d’un poil.

            Moralité, pourquoi faire compliqué quand on peut faire simple ? Pareil, le SSH, 5 € par mois, c’est pas énorme mais ça représente quand même 60 € à l’année. Et si tu ajoutes à ça d’autres trucs, bah ça peut commencer à chiffrer alors je comprends parfaitement qu’un gars qui a juste un petit site et qui ne le monétise pas ne souhaite pas opter pour cette solution.

          • Mince.. on ne pas faire un autre Répondre par dessus LOL..
            @rodskin:
            ‘le client a pas forcément envie / s ’en fout de payer 5€/ mois pour du ssh’
            Ouais mais le SSH c’est dans le package de base que tu vends..
            Imagine, tu plantes un dump de base et tu vas voir le client en disant: Monsieur, j’ai pas osé vous demander 5€ en plus..
            Le SSH fait parti du conseil, fait parti des outils pour te faire gagner du temps et pour sécuriser tes procédures.
            @Fred: 60€/an.. si c’est pour toi, toi seul peut estimer ce que te coûte un plantage.. Même à titre perso, je préfère lancer une tâche planifié avec un email de rapport pour gagner du temps. Ton dump manuel n’est pas infaillible. Je pense que nous avons deux approches différentes. La mienne orientée client et support et qualité de service, la tienne qui est dans un contexte plus personnel.

            Donc messieurs, en résumé nos points de vue respectifs se valent.. mais je suis un gros fainéant qui aime la sécurité et qui n’aime pas faire clicou.. LOL.. En tout cas c’est sympa de voir des gens qui se soucient de faire des dumps.
            Bonne journée
            S.

          • rodskin

            @syndrael:
            Je te réponds quand même.
            « Ouais mais le SSH c ’est dans le package de base que tu vends.. »
            D’accord, je le vends aux clients et c’est même compris dans le forfait hébergement que je vends MAIS si le client a déja un hébergement, je vais avoir du mal à le lui vendre :)

          • @roskin: Alors tu lui mets une grosse ligne de frais de maintenance pour backup !! LOL.. Tu es un malin..LOL !!

  • Pingback: Comment déménager son blog Wordpress()

  • Bon tuto, merci !
    Pour compléter un peu, si vous avez besoin de corriger un gros fichier SQL, vous allez vite vous trouvez devant un problème de lecteur de texte qui n’arrive pas à lire des fichiers trop volumineux. Même NotePad++ est à genoux devant un fichier SQL de 600Mo. Donc j’ai trouvé Ultra-Edit, qui gère apparemment sans problèmes, pour moi le fichier faisait 550 Mo et même la fonction de recherche est passée.
    Je précise qu’il est en Shareware…

    • Ah, je ne connaissais pas cet éditeur, merci du coup :p

  • alexis

    Super ! Ca marche nickel avec une BDD de + de 128 mo ! ! !

  • Au top merci !

  • Dany

    Top de top !! mille merci !!!

  • cosmeto

    MERCI BCP pour ce script!!!!!!!!!!! Franchement super ! aucun bug ! il marche nickel !

  • Johann

    Je suis un newbie dans le domaine et du coup je ne comprends pas la phrase: « Créer un nouveau dossier sur le FTP où se trouve notre BDD… »
    Si j’ai bien suivi le process, j’ai un fichier « basededonneaexporter.sql.zip » et un autre « bigdump.php » avec les renseignements sur la base de données que j’ai créé préalablement dans PhpMyAdmin (nom de la base, identifiant, et mdp) et que j’ai nommé par exemple « manouvellebase »

    L’idée c’est d’exporter l’ancienne base de données vers la nouvelle… on peut accèder à sa base de données via Filezilla? Car je passe par PhpMyAdmin jusqu’à présent…

    Bref, je suis bloqué

  • Ylian Estevez

    BigDump est effectivement un bon script. Ce qui est trompeur, c’est le titre, car BigDump n’est pas phpMyAdmin. Et la sentence tombe : on ne peut pas importer un gros fichier via MySQL. A noter, il est important de suivre les instructions précisément, et notamment décocher les instructions étendues, sources de nombreux problèmes à l’intégration. N’oubliez pas, non plus, d’effacer BigDump une fois votre fichier intégré, par simple sécurité.

    L’alternative à Big Dump est une connexion SSH à votre serveur. Vous pourrez alors directement intégrer votre fichier sql via l’interface mysql en ligne de commande.

  • Pingback: I Dump 4 U Farmville()