Home > Just the Video Tutorials, WP database > How to Import a Large WordPress Database — An Alternative to phpMyAdmin

How to Import a Large WordPress Database — An Alternative to phpMyAdmin

January 26th, 2009 Leave a comment Go to comments

IMPORTANT MESSAGE: If you use the Bigdump script I illustrate below, after you are done importing your database, be sure to delete the Bigdump directory. If you leave it in place, then anyone who stumbles across it could destroy your database. So, once you are done, remove it from your server and then if you need to use it again in the future, just upload it when you need it…removing it when you are done with it.
——————————————–

How to Import a Large WordPress Database — An Alternative to phpMyAdmin — Part 1 of 2

How to Import a Large WordPress Database — An Alternative to phpMyAdmin — Part 2 of 2

Get Noticed with a $7.99 .CO!
  1. March 16th, 2009 at 06:37 | #1

    good job, thank for this one

  2. July 10th, 2009 at 02:49 | #2

    ich nutze http://www.mysqldumper.de für solche sachen. kann ich nur empfehlen.

  3. July 29th, 2009 at 13:32 | #3

    Ok i managed to bring most of my blog back but i still get an error message while using big dump

    Stopped at the line 5313.

    At this place the current query includes more than 300 dump lines. That can happen if your dump file was created by some tool which doesn’t place a semicolon followed by a linebreak at the end of each query, or if your dump contains extended inserts. Please read the BigDump FAQs for more infos.

    and when i go to the website i can’t log in with my username and password anymore

  4. July 29th, 2009 at 13:35 | #4

    didnt watch the whole video haha you explain this nevermind ima keep watching

  5. July 29th, 2009 at 14:05 | #5

    I finished watching it and it worked thanks man i was panicking

  6. alex gonzalez
    September 21st, 2009 at 17:20 | #6

    You did a great job on the video ..congrats…what if I want to import content not all the tables i.e. wordpress post that I want to add to my blog… I tried using the rss importer but it can only do a few hundred post at a time ..any suggestions ..thanks in advance

  7. September 21st, 2009 at 17:27 | #7

    @alex: I’m not sure what you are asking…need to be a little more specific.

  8. alex gonzalez
    September 21st, 2009 at 20:21 | #8

    Thanks for the quick response…I want to import posts into my wordpress blog, I have the posts in files formated ala RSS. I can import the files now via the rss importer built into wordpress, but only about 300 at time before getting errors… I can email you a sample file. Perhaps you can suggest a different way to achieve this ..thanks again Alex

  9. September 22nd, 2009 at 06:47 | #9

    @alex: Can’t you just break that file up into groups of around 300 and do multiple imports?

  10. alex gonzalez
    September 22nd, 2009 at 07:12 | #10

    Thanks again, I presently doing multiple imports but it takes time and I wanted something more automated. When I saw your video on importing the entire wordpress blog, I thought you perhaps you knew how to import large anounts of content into the blog..thanks Alex

  11. November 9th, 2009 at 18:30 | #11

    That worked out nicely, thanks.

  12. NoLabel
    January 5th, 2010 at 09:38 | #12

    I have the same problem with the more than 300 dump lines, but my database is from a phpbb2 forum, and i can’t insert manually the “INSERT – VALUE” line, because it will take me years to do that, because i have more than 3,200,000 lines, and the error begins in the 700th line. Is there a way of doing that automatically? I have Edit Plus, but i’m using it for a first time.

  13. January 5th, 2010 at 09:59 | #13

    @NoLable: Open the bigdump.php file and find the following variable:

    define (‘MAX_QUERY_LINES’,300);

    Change 300 to 900, save and give that a try.

  14. NoLabel
    January 5th, 2010 at 11:05 | #14

    Thank You! It worked, but now i have other error messages like:

    Error at the line 200332: (‘something’, 3375, 0);


    MySQL: Duplicate entry ‘???�????’ for key ‘PRIMARY’

  15. January 5th, 2010 at 11:13 | #15

    @NoLabel: With a db that large you may run into all kinds of errors before getting it successfully imported. You may want to just open the sql file and delete that line.

  16. Ringtone
    January 9th, 2010 at 10:58 | #16

    i have problem :

    Stopped at the line 1747.

    At this place the current query includes more than 300 dump lines. That can happen if your dump file was created by some tool which doesn’t place a semicolon followed by a linebreak at the end of each query, or if your dump contains extended inserts. Please read the BigDump FAQs for more infos.

    What should i do T.T

  17. January 9th, 2010 at 16:01 | #17

    @Ringtone: Someone already posted about that and I’ve responded here…read the comments.

  18. Narayanan
    January 16th, 2010 at 02:20 | #18

    This is the first time I am perform this operation.I do no what to do.
    Stopped at the line 881.
    At this place the current query includes more than 300 dump lines. That can happen if your dump file was created by some tool which doesn’t place a semicolon followed by a linebreak at the end of each query, or if your dump contains extended inserts. Please read the BigDump FAQs for more infos.It is a 100mb database file.

  19. January 16th, 2010 at 15:03 | #19

    Hello it was quit helpful but i have 2 question

    Like you stop at the specific line and insert the line after 100..
    Mostly this error comes or it will be different some times?

    2nd.. i have very large database 1.5g will big dump support this?

  20. January 17th, 2010 at 14:31 | #20

    Hello Figaro,

    Thank you for this, great help! And also for your other instructions, I am really greatful that you are doing this.

    I do have a question about importing my large database. I have a different error and I don’t know how to solve it. Maybe you can help me out?

    I use the bigdump thing to upload my database but I receive the following error:

    Error at the line 44: (1, ‘2009-11-25 00:09:54′, ‘2009-11-25 00:14:54′, ‘The Scheduler Calendar was installed!’, ”, 0, 0);

    There is more text, but somehow I can’t sent the whole error message to my comment.

    I don’t know exactly what this means and what I have to do to upload it without the error. I hope you can help me out, because I am not so happy that I can’t do this by myself. Thanks for your time.

    Friendly greetings,
    Archana

  21. January 17th, 2010 at 14:57 | #21

    @Archana: Mod security on my server may be causing this…it just helps prevent the posting of things that could be harmful code. I can’t really help without seeing the entire error…maybe you could post the error to pastebin and link to it.

  22. January 17th, 2010 at 15:11 | #22

    Ok, that could be it then. I have post the error to pastebin: http://pastebin.com/d3764d173

    I don’t know what to do anymore, in fact, I can’t do a thing now with my website. I made backups from all the files and also made a backup from my database. The plan was to move my site from animal-crossing.nl/wp to animal-crossing.nl

  23. January 17th, 2010 at 15:37 | #23

    @Archana: A couple of things to try. 1) just open the sql file in an editor and delete the line. This is a duplicate record in the table for the scheduler calendar you have installed. If you get another similiar error, then I would suggest just going to your original db and dump all tables except for the “events_rec” table and see if that works.

  24. January 17th, 2010 at 18:09 | #24

    Hello Figaro, i deleted the line and then tried to upload it again. But still got errors. What do you mean with go to your original database? You mean my phpmyadmin?

  25. January 18th, 2010 at 15:35 | #25

    @Archana: Yes, phpmyadmin…just dump the db again but without that table.

  26. January 18th, 2010 at 22:49 | #26

    U r using wordpress right?
    Then there is another way to get all ur post and other things.Just instead of downloading the database and solving these kind of errors u can Download the xml file from u wordpress admin menu and upload it and get all ur post.

  27. January 19th, 2010 at 17:57 | #27

    I don’t know exactly what to do now. I can’t do a thing through my phpadmin except for dumping all my tables. If I want to import the database I get an error. If I upload the file through bigdump I get a few errors. I removed the line 44 but I still get errors.

    At this point I can’t get to my orignial adminpanel since there aren’t any complete tables online. Is there another way to fix what I have done?

    I hope I can upload it again because it took me months to (almost) finish my website…

    Thank for your help.

  28. January 19th, 2010 at 21:43 | #28

    @Archana: Can’t really help more than I have…you should be able to dump the db without that table.

  29. January 23rd, 2010 at 12:15 | #29

    I am having trouble with my posts linking to the same category (in every one of my categories) when you hit “read more” or try to add another comment from the site side. I want the comments in each category to stay on that category post (category page the main commment is listed on, not take the reader to another page (category). Can you help me?

    To see what I am talking about my web site is http://www.heretowellness.com
    Thank you.

    My email address is Janice@austinrain.com

  30. January 24th, 2010 at 18:28 | #30

    @Janice Welch: Categorie just provides a way to group (filter) your posts…when you click on a post or comment link in a post, then you are taken to that post. It sounds like you want your categories to be separate mini-blogs all showing on a homepage. I’m sure this could be done, but it would take some significant coding. I don’t have time for something that involved, but you may want to post to the WordPress Pro mailing list and see what kind of response you get–I don’t know of a plugin that will do this, so it will cost to have this coded by someone.

  31. January 24th, 2010 at 18:29 | #31

    Finally I managed to import my wordpress database and my phpbb forum. I used the programm SQLyog. It works just like a ftp-programm and this way I could import my database again. I am so glad it worked! I thought I can never get my site back on. It’s in Dutch, but if you want to check it out: http://www.animal-crossing.nl

  32. Alberto Douglas
    June 28th, 2010 at 10:06 | #32

    An another alternative is wordpress database backup plugin.

  33. June 28th, 2010 at 12:56 | #33

    I’m not aware of any backup plugin that will import a database…not to mention a large database.

  34. September 22nd, 2010 at 15:40 | #34

    Very interesting and informative video. I wonder if there is any similar tool available to import large XML files back into the WordPress database?

  35. jeff
    December 11th, 2012 at 14:22 | #35

    thank you — way helpful, especially the section re how to fix the “300 lines without a semicolon”!

  36. Kim
    November 22nd, 2013 at 14:07 | #36

    Hi,
    When I try to upload the .sql file, I’m met with the error message “MySQL: Duplicate entry ‘1’ for key ‘PRIMARY’” I know someone asked a similar question and you responded – I tried what you suggested but it just causes more issues.

    If I delete that line, another error comes up.

    These videos were very helpful. I just can’t seem to find answers from anyone about these errors. Thank you.

  1. February 14th, 2009 at 00:49 | #1
  2. May 31st, 2009 at 18:24 | #2
  3. August 7th, 2012 at 21:12 | #3
*