News:

Bored?  Looking to kill some time?  Want to chat with other SMF users?  Join us in IRC chat or Discord

Main Menu

Restoring database (phpmyadmin being silly?)

Started by Mecha Dude, August 15, 2003, 08:31:57 PM

Previous topic - Next topic

Mecha Dude

I just had a server revert, and I'm putting my backup files back into my website, how ever phpMYadmin is not being nice.  I have the databse sql file, I import it, and.. it happly tells me it inserts/ect, but.. no database?

I do have a bad feeling about this, does anyone have any answers?

Ben_S

How big is the dump?

Check the size of it and check the max upload size in a phpinfo file
Liverpool FC Forum with 14 million+ posts.

Mecha Dude

I fixed the problem by commenting out a line that created the database name (for some reason it just didnt).  That fixed it, and yes the host does support SSH (since I run the server o.O).

I still don't know why it was an issue that it didn't want to make the database.  Oh yes the clump was like 1557 insertions.

Spaceman-Spiff

Quote from: bostasp on August 16, 2003, 05:59:55 AM
I have to do all my Backup/Restoring of mySQL through a SSH shell, because of my current host putting a 2mb limit on it... causing a nice crash everytime someone decides to try and backup or restore over that ammount.

it's 2MB by default in php settings

Mecha Dude

Where would I find this setting at?  At what kind of effects does having a DB over 3 MB do?

Douglas

php.ini

and it doesn't affect existing databases.

When I do a mass import, I import no more than 1000 lines at a time, sometimes it takes me an hour to implement a 15 meg database, but it's guaranteed to work better that way.
Doug Hazard
* Full Stack (Web) Developer for The Catholic Diocese of Richmond
(20+ Diocesan sites, 130+ Church sites & 24 School sites)
* Sports Photographer and Media Personality
* CFB Historian
* Tech Admin for one 1M+ post, one 2M+ post and one 10M+ post sites (last two are powered by multiple servers)
* WordPress Developer (Junkie / Guru / Maven / whatever)

Spaceman-Spiff

i used to save every 2500 lines for messages table
joined the other tables in 1 or 2 .sql files

another alternative is to break down the 3MB files to 3 @ 1MB files or something like that then upload one by one

Ben_S

Which is fun, untill your db gets to 110 meg
Liverpool FC Forum with 14 million+ posts.

Mecha Dude

How do you split up the sql files?  Or should I search for a script that will parse the sql file by X number of tables into mysql at a time?

Mecha Dude

Something like this to split it up?


function splitfile( $infile, $outfile, $maxsize )
{
    // open input file
    $ifd = fopen( $infile, "r" );
    if( !$ifd )
    {
         // error opening input file
         return -1;
    }
    // first file number is 0
    $filenr = 0;
    // open first output file
    $ofd = fopen( $outfile . $filenr, "w" );
    if( !$ofd )
    {
         // error opening output file
         return -1;
    }
    // increase filenr
    $filenr++;
    // set size of output file to 0
    $size = 0;
    // while we can read a line from input file do ...
    while( $line = fgets( $ifd, 1000 ) )
    {
         // append the line to output file
         fputs( $ofd, $line );
         // add the size of the line
         $size = $size + strlen( $line );
         // if the size is > maxsize and this is the end of a statement
         if( $size > $maxsize && substr( chop($line), -1 ) == ";" )
         {
              // close output file
              fclose( $ofd );
              // open next output file
              $ofd = fopen( $outfile . $filenr, "w" );
              if( !$ofd )
              {
                   return -1;
              }
              $filenr++;
              // the output file has a size of 0 now
              $size = 0;
         }
    }
    // close the last output file
    fclose( $ofd );
    // return the number of output files generated
    return $filenr;}

Douglas

That's one way, the other way is to search for Notepad Plus (http://www.mypeecee.org/rogsoft/ for the hopelessly decadently lazy bum) and download/install that.

Depending on your system, you could open a 250 meg file (I've opened a 350 meg file and made some alterations).  SUGGESTION:  Do NOT open anything larger than a 100 meg file unless you have a beast of a machine, jacked up with RAM.
Doug Hazard
* Full Stack (Web) Developer for The Catholic Diocese of Richmond
(20+ Diocesan sites, 130+ Church sites & 24 School sites)
* Sports Photographer and Media Personality
* CFB Historian
* Tech Admin for one 1M+ post, one 2M+ post and one 10M+ post sites (last two are powered by multiple servers)
* WordPress Developer (Junkie / Guru / Maven / whatever)

Douglas

Doug Hazard
* Full Stack (Web) Developer for The Catholic Diocese of Richmond
(20+ Diocesan sites, 130+ Church sites & 24 School sites)
* Sports Photographer and Media Personality
* CFB Historian
* Tech Admin for one 1M+ post, one 2M+ post and one 10M+ post sites (last two are powered by multiple servers)
* WordPress Developer (Junkie / Guru / Maven / whatever)

Advertisement: