SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot chrissie76's Avatar
    Join Date
    Sep 2007
    Location
    plymouth, devon, uk
    Posts
    113
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how can i upload a large database to a new server?

    Hi,
    I have been developing a web app on my local server and i am now trying to import a large database (1.7million rows) to the production server. i have exported it using phpmyadmin, no worries but i can only upload a tiny 8mb to the productions server (hosted with one and one). i emailed them but changing php.ini or .htaccess files doesnt help as the database is hosted on a different server to the php files to which i have access. They were pretty unhelpful :-(
    I considered writing a script to step through the rows on my localhost and post them like form data to the new site, then writing a script to insert them from the new server, but with 1. 7 million rows, this will take forever, and i'm not entirely confident about this idea at all.
    I'm sure there is someone out there who has done this and can give me advice? it would be really appreciated!
    Thanks so much,
    Chrissie
    Live Long Laugh Often

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Use your FTP access to the 1&1 account to upload the dump from phpMyAdmin, then write a script which runs the queries in that file line-by-line.

  3. #3
    SitePoint Zealot chrissie76's Avatar
    Join Date
    Sep 2007
    Location
    plymouth, devon, uk
    Posts
    113
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for your reply; i can upload the dump no worries, can you give me a bit of a steer on how i use it to step through line by line? i guess i need to tell php to read it like a text file? hmmm.... bit stuck.... any steers would be very much appreciated!
    Live Long Laugh Often

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    //connect to database

    $lines file('dump.sql');
    foreach (
    $lines as $line) {
      
    mysql_query($line);


  5. #5
    SitePoint Zealot chrissie76's Avatar
    Join Date
    Sep 2007
    Location
    plymouth, devon, uk
    Posts
    113
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile

    thanks so much; in the end i installed the dump.sql.gz to the root using ftp as you suggested, then i used SSH to run the file against the database as i was worried about the max_execution_time (currently set to 30 secs and when i try and increase it using .htaccess i got a 500 error )
    this seemed to work, but i just wanted to say thank you for your help
    Live Long Laugh Often

  6. #6
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ah, you should've said you had SSH access. You had many options for doing this

  7. #7
    SitePoint Zealot chrissie76's Avatar
    Join Date
    Sep 2007
    Location
    plymouth, devon, uk
    Posts
    113
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry i hadn't realised i could use it and to be honest, i forgot all about it! still learning!
    Live Long Laugh Often


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •