1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Extract every single DB from a large (full) phpmyadmin DB backup

Discussion in 'Other Languages' started by GeezNotThatGuy, Nov 12, 2013.

  1. GeezNotThatGuy

    GeezNotThatGuy Newbie

    Joined:
    Feb 7, 2013
    Messages:
    24
    Likes Received:
    5
    The problem:
    Due to a CP change we needed to swipe the server clean an set it up fresh. So I made file and db backups. When making the db backups, i was too lazy to make a single backup for every db. So i just selected all and downloaded it in a huge .sql file containing 15+ single databases. When trying to restore the databases on the freshly set up server via phpmyadmin i ran into the problem, that some databases (information schema) were allready in existence on the sql server. This resulted in an error and in an abortion of the import. So i searched for scripts and tools to split this huge db dump in the single database files it contained. All i found were some python scripts which, for whatever reason, didn´t work. I also tried to change the db in notepad which is sooooo sloooow.
    After almost giving up and wasting a whole fkng day, I remembered that I could just script a little program on my own. I like to share the program here (or the source code at least) to save anyone who runs in the same problem some time and headaches. This programm is scrpted in autoIT.

    Not very popular, but still my choice for programs like that. So feel free to use it.
    If someone need this as an executable, send me a pn or maybe one of the more respected members like to upload this as an exe. feel free to do so.

    Code:
    #Include <Array.au3>
    
    Global $DB_FILENAME="localhost(3).sql" ; <- Change this to the DB dump file of yours you want to split (extract) the single DBs from (Must be in the same folder as the script)
    Global $SPLIT_STRING="-- Datenbank" ; <- Change this to something that is right before the beginning of every DB (I use a german phpmyadmin version, in english it´s most likely -- Database). Just check your DB dump.
    
    $file=FileOpen($DB_FILENAME,128); Opens the sql file and reads it in an string in UTF-8 format. If you need it as Ansi, just delete ",128" | For more Read/Write modes see End of script
    $FILECONTENT=FileRead($file)
    FileClose($file)
    
    $File_STRING_ARRAY=StringSplit($FILECONTENT,$SPLIT_STRING,1)
    
    ;_ArrayDisplay($File_STRING_ARRAY) ; delete the first ";" in this line for debugging
    ; Save each DB to a single file
    
    for $i=1 to $File_STRING_ARRAY[0]
        ;Get Tablename
        $tablename=Extract_from_string($File_STRING_ARRAY[$i],":",":","-",1)
        $tablename=StringRegExpReplace($tablename,"[^\w ]","")
        ;MsgBox("","Tablename",$tablename)
    
        $TABLECONT=StringTrimLeft($File_STRING_ARRAY[$i],StringInStr($File_STRING_ARRAY[$i],"CREATE DATABASE")-1)
        ;MsgBox("","Table Content",$TABLECONT)
    
    
        ;Save DBs to file
        $SAVE_FILE=FileOpen($tablename&".sql",138) ;saves the DBs in the same folder as the script in UTF-8 format | If Ansi is needed just replace 138 with 10
        FileWrite($SAVE_FILE,$TABLECONT)
        FileClose($SAVE_FILE)
    
    
    
    Next
    MsgBox("","DONE","DONE")
    
    
    func Extract_from_string($string,$startpoint,$between_1,$between_2,$between_2_vorkommen)
    
        $start=StringInStr($string,$startpoint)
        if $start==0 then
            ;create_statusmeldun(2,"Problem extracting from sting 1: "&$startpoint,2)
            return ""
        EndIf
    
    
    ;MsgBox("","",$startpoint&":"&$between_1&":"&$between_2&" : "&$start)
        if StringInStr($string,$between_1)==0 then
            ;create_statusmeldun(2,"Problem extracting from sting 2: "&$between_1,2)
            return ""
        EndIf
    
    $from=StringInStr($string,$between_1,Default,1,$start)
    $to=StringInStr($string,$between_2,Default,$between_2_vorkommen,$from)
    
    $fromfinal=$from + StringLen($between_1)
    $tofinal=$to-$fromfinal
    $result=StringMid($string,$fromfinal,$tofinal)
    
    
    ;MsgBox("","lal",$from&":"&$to&" "&$to-$from)
    
    
    Return $result
    EndFunc
    
    ;[optional] Mode to open the file in.
    ;Can be a combination of the following:
    ;  0 = Read mode (default)
    ;  1 = Write mode (append to end of file)
    ;  2 = Write mode (erase previous contents)
    ;  8 = Create directory structure if it doesn't exist (See Remarks).
    ;  16 = Force binary mode (See Remarks).
    ;  32 = Use Unicode UTF16 Little Endian reading and writing mode. Reading does not override existing BOM.
    ;  64 = Use Unicode UTF16 Big Endian reading and writing mode. Reading does not override existing BOM.
    ;  128 = Use Unicode UTF8 (with BOM) reading and writing mode. Reading does not override existing BOM.
    ;  256 = Use Unicode UTF8 (without BOM) reading and writing mode.
    ;  16384 = When opening for reading and no BOM is present, use full file UTF8 detection. If this is not used then only the initial part of the file is checked for UTF8.
    ;The folder path must already exist (except using mode '8' - See Remarks).
     
  2. fistor

    fistor Regular Member

    Joined:
    Feb 29, 2012
    Messages:
    256
    Likes Received:
    315
    Location:
    A mind needs books as a sword needs a whetstone, i
    Why didn't you just use the f operator?
    Or did it stop anyway?
     
  3. MafiaBoss

    MafiaBoss Elite Member

    Joined:
    May 5, 2012
    Messages:
    1,522
    Likes Received:
    1,031
    Occupation:
    Currently Un-Occupied
    Location:
    In granny's Basement
    Home Page:
    oh the code, the code, make me dizzy.
     
  4. GeezNotThatGuy

    GeezNotThatGuy Newbie

    Joined:
    Feb 7, 2013
    Messages:
    24
    Likes Received:
    5
    For my defense: My focus is on creating content, not handling server issues. I have just very basic knowledge and my serveradmin was asleep when the problems arose.
    So could you please elaborate? What is the f operator? And by the way, i tried to change stuff in the config tab in phpmyadmin, but for whatever reason i allways just got a blanc site.
    I know that there would have been an option to ignore errors.
     
  5. GeezNotThatGuy

    GeezNotThatGuy Newbie

    Joined:
    Feb 7, 2013
    Messages:
    24
    Likes Received:
    5
    Never said it´s pretty ;) but it does it´s job.
     
  6. Izzma

    Izzma Regular Member

    Joined:
    Jan 29, 2012
    Messages:
    293
    Likes Received:
    197
    Location:
    Canada
    If you are using cPanel why don't you use "Backup Wizard"? You can easily download your .SQL data and public_html directories with the click of a button.

    I have literally millions of entries I either export or restore on a monthly basis and never have a problem. For the people who don't use cPanel, try MySQL Dumper. It works just as well.
     
  7. GeezNotThatGuy

    GeezNotThatGuy Newbie

    Joined:
    Feb 7, 2013
    Messages:
    24
    Likes Received:
    5
    I didn´t use CPanel. That was the reason for the CP (Controlpanel) change. And I did a manual backup because there were some DBs which weren´t created with the CP (thus not regarded as part of my websites and according to my old CP not worthy of backing up). So when i made a full backup with the backup wizard of my CP, these dbs weren´t part of the backup.
     
  8. fistor

    fistor Regular Member

    Joined:
    Feb 29, 2012
    Messages:
    256
    Likes Received:
    315
    Location:
    A mind needs books as a sword needs a whetstone, i
    Gotcha!
    Just to make it clear, you don't have to defend yourself, as I was just asking. The f (force) would have been the first thing I would have tried.

    Basically PhpMyAdmin is just an interface for your Database. You could navigate to your MySQL folder directly via shell (such as putty) or directly via remote (cmd etc).
    Then you tell MySQL to import your Database Dump, without the "help" of PHPMyAdmin.

    You do that with a command such as mysql -uuser -ppassword < dumpfilenamehere.sql (IIRC! Been a long time since I messed with MySQL)
    If that should throw errors (unlikely), you can force MySQL to ignore the errors by adding -f or --force to the command above.

    That should have imported all your databases anyway.

    Again, long time since I played with MySQL. If anyone experienced finds this to be wrong, feel free to correct me. But imo, this should work.
     
  9. tompots

    tompots Elite Member Premium Member

    Joined:
    Dec 11, 2011
    Messages:
    4,352
    Likes Received:
    3,955
    Gender:
    Male
    Occupation:
    Full Time Bot Developer
    Location:
    Professional Botters
    Home Page:
  10. GeezNotThatGuy

    GeezNotThatGuy Newbie

    Joined:
    Feb 7, 2013
    Messages:
    24
    Likes Received:
    5
    thanks for your explanation fistor. But i´m a bit heasited when it comes to interacting with my server via ssh. Too many things that I could fuck up. When it comes to stuff I never did before, I just need my GUI that prevents me from doing stupid things. :)
    So this solution was the safest bet for me.