SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Wizard Dean C's Avatar
    Join Date
    Mar 2003
    Location
    England, UK
    Posts
    2,906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Importing a huge database on a windows machine

    Well I run a rather large forum and our database as it stands is 200mb. I downloaded it today and am just wondering how is the best way to import it onto my localhost server without it killing my machine in the process

    Any ideas are welcome...

  2. #2
    SitePoint Wizard Lats's Avatar
    Join Date
    Jun 2003
    Location
    Melbourne, AU
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mist
    ...without it killing my machine in the process
    200 mB isn't overly large however, if you're concerned about the processing power of your linux box, enter this via the shell...
    Code:
    nice -n 19 mysql -u[Username] -p[Password] [Database Name] < filename.sql
    Lats...

  3. #3
    SitePoint Wizard Dean C's Avatar
    Join Date
    Mar 2003
    Location
    England, UK
    Posts
    2,906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's a windows box for the record so I don't know if that command will work Also note how does the command know where to find filename.sql? Is there anywhere I should be placing the file?

  4. #4
    SitePoint Wizard Lats's Avatar
    Join Date
    Jun 2003
    Location
    Melbourne, AU
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Possibly the easiest way is to copy your sql file the mysql/bin/ directory, and issue the same command, although I did leave an important bit - whoops
    Code:
    mysql.exe u[Username] -p[Password] [Database Name] < filename.xql
    Lats...

  5. #5
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    definitely import via command line. I did a 750 MB database on my Windows 2000 box then on Windows XP in about 2.5 minutes.
    Aaron Brazell
    Technosailor



  6. #6
    SitePoint Wizard Dean C's Avatar
    Join Date
    Mar 2003
    Location
    England, UK
    Posts
    2,906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Im in mysql.exe now and I just tried to run the command:

    -uroot -p rsboard < mist.sql
    The default username on all my databases is 'root' and the password is non existant (I don't need one). And the filename mist.sql is in the same directory as mysql.exe It does't actually do anything. It just goes onto a new line when I press enter??

    When am I going wrong ?

  7. #7
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mysql -uroot -p rsboard < mist.sql

    That does not work. It should sit there for a bit and look like it's not doing anything but you'll want to verify when the prompt comes back up that the db contains data:
    Code:
    c:\mysql\bin> mysql -uroot
    mysql> SELECT count(*) FROM rsboard.table;
    Change table to one of your table names.

    Aaron
    Aaron Brazell
    Technosailor



  8. #8
    SitePoint Wizard Dean C's Avatar
    Join Date
    Mar 2003
    Location
    England, UK
    Posts
    2,906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm it's just not doing anything right now. I load mysql.exe and typed the command:

    mysql -uroot -p rsboard < mist.sql
    Exactly as it is there. I've even attached a screenshot. It doesn't do anything either. I left it like that for 10minutes and I've had nothing prompt me or anything. There must be something incredibly dumb that I'm doing here hehe Note this is on windows xp with mysql 4

  9. #9
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try this then

    Restart MySQL to kill any lingering processes

    From the DOS commandline....
    Code:
     mysql -uroot -p --opt rsboard < mist.sql
    Open up a separate Comand prompt to login to MySQL to verify the data is importing if it hangs for too long. 10 Minutes may not be that crazy. Just check it out.

    Aaron
    Aaron Brazell
    Technosailor



  10. #10
    SitePoint Wizard Dean C's Avatar
    Join Date
    Mar 2003
    Location
    England, UK
    Posts
    2,906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok well I finally managed to get it going. I had to go into DOS (which I know nothign about), navigate to the mysql/bin directory and type:

    Code:
    mysql -uroot -p rsboard < mist.sql
    It prompted me for the password which of course there isn't any so I just pressed enter. Then it started importing. After about 5 minutes I got this message:

    ERROR 2013 at line 1295: Lost connection to MySQL server during query
    At least we're getting somewhere - part of my data was imported but not all of it - where to now ?

  11. #11
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Easiest method is to delete the db and recreate it. There's a replace bit but I can't think of it right now. But actually, I think if you use the --opt flag that I used above it should replace anything already imported.
    Aaron Brazell
    Technosailor



  12. #12
    SitePoint Wizard Dean C's Avatar
    Join Date
    Mar 2003
    Location
    England, UK
    Posts
    2,906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Right... I recreated the 'rsboard' database and tried running the command with the '--opt' in. It said that '--opt was an unknown option'. So i ran:

    Code:
    mysql -uroot -p -opt rsboard < mist.sql
    Again it conked out around the same line (this time 1293). So I ran the exact same thing again and again it gave me the same error around the same line

    Thanks for your help so far Sketch ....

  13. #13
    SitePoint Wizard Lats's Avatar
    Join Date
    Jun 2003
    Location
    Melbourne, AU
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ERROR 2013 at line 1295: Lost connection to MySQL server during query
    That error indicates the the default timeout is too short.

    I can't check right now (at work), but from memory, you should be able to edit the my.cnf file to increase the value in there.
    Lats...

  14. #14
    SitePoint Wizard Lats's Avatar
    Join Date
    Jun 2003
    Location
    Melbourne, AU
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mist
    I had to go into DOS (which I know nothign about), navigate to the mysql/bin directory and type...
    Some confustion there, that's where I thought you were all the time
    Lats...

  15. #15
    SitePoint Wizard Dean C's Avatar
    Join Date
    Mar 2003
    Location
    England, UK
    Posts
    2,906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll take a look at my.cnf file when I get home Lats - thanks for the heads up

  16. #16
    SitePoint Wizard Dean C's Avatar
    Join Date
    Mar 2003
    Location
    England, UK
    Posts
    2,906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok I've got home and done my background research. Some machines have a my.cnf, some have a my.ini file I have a my.ini file. I'm looking in there and I can't see anything of great relevance that's going to to help me

  17. #17
    SitePoint Wizard Dean C's Avatar
    Join Date
    Mar 2003
    Location
    England, UK
    Posts
    2,906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I hardly ever do this but it is quite urgent. Please don't flame me

    *bump*


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
  •