15 {{ upvoteCount | shortNum }}
15 {{ upvoteCount | shortNum }}

Mysql database

by dmw144
12 replies
I (stupidly) set up approximately 50 wordpress sites using 1 database (i selected the wrong option).

The database has grown to 1.3gig and bluehost is having a fit and temp suspended my account (not unreasonably i guess) - how difficult is it to strip out each database and make separate databases - anyone know how much something like this is likely to cost and how long it might take (approx)?

thx

dean
#database #mysql
  • Profile picture of the author layouts4you
    well it can be a little time consuming. does each Wp site have its own prefix? if so i can do this for you pretty reasonable. i will have to separate each database into its own database and also have to change the config file on each site. if you want to talk about this pm me and ill be happy to talk with you more in depth. id Pm you but dont have enough posts to do so.
    {{ DiscussionBoard.errors[2191886].message }}
  • Profile picture of the author mywebwork
    Hi Dean

    50 sites in one database is going to take a bit of an effort to split. Essentially you'd need to create an export file and then manually edit it to create 50 independent files, which would then be imported into a new "blank" database.

    With a database that large the export file is going to be massive, and may even exceed the limits of some text editors. Not to mention that just doing the export is going to take some time, again due to the file size.

    One other way that may be easier and that won't require as much data movement would be to setup your existing database to allow external access. Them use the MySQL GUI Tools to create and run 50 export jobs, each one taking only the tables required for one site (I assume they each have a unique prefix). The result will be 50 .sql files that can then be used to create independent databases.

    If you'd like some assistance with this please let me know and we can probably work something out.

    Bill
    {{ DiscussionBoard.errors[2191922].message }}
    • Profile picture of the author layouts4you
      i got your pm. i cant reply to it yet but if you want to send me skype or yahoo or phone number if in the US ill be happy to help you out all i can.
      {{ DiscussionBoard.errors[2191928].message }}
    • Profile picture of the author Mr. Enthusiastic
      Originally Posted by mywebwork View Post

      One other way that may be easier and that won't require as much data movement would be to setup your existing database to allow external access. Them use the MySQL GUI Tools to create and run 50 export jobs, each one taking only the tables required for one site (I assume they each have a unique prefix). The result will be 50 .sql files that can then be used to create independent databases.
      That makes a lot of sense, Bill.

      If the original poster doesn't have unique table prefixes per site, than he's pretty much screwed, right? Or is there another way to split out the site-specific data?

      Chris
      {{ DiscussionBoard.errors[2195176].message }}
      • Profile picture of the author mywebwork
        Originally Posted by Mr. Enthusiastic View Post

        That makes a lot of sense, Bill.

        If the original poster doesn't have unique table prefixes per site, than he's pretty much screwed, right? Or is there another way to split out the site-specific data?

        Chris
        Hi Chris

        I think he would have to have set it up that way, otherwise how would the individual WordPress installations be able to address their specific tables?

        But yes, if there was no distinguishing prefix for each sites tables he wouldn't be able to do this.

        Bill
        {{ DiscussionBoard.errors[2195487].message }}
        • Profile picture of the author Mr. Enthusiastic
          Originally Posted by mywebwork View Post

          I think he would have to have set it up that way, otherwise how would the individual WordPress installations be able to address their specific tables?
          D'oh! I should've realized that! Where's a facepalm smiley when I need one.
          {{ DiscussionBoard.errors[2196056].message }}
          • Profile picture of the author dmw144
            Yeah - each wp site has a uniuely identified subsection in the database - Someone is currently looking at it for je and i am very hopefull of having it resolved in the next day or so - thank you sooo much for your help and advice

            dean
            {{ DiscussionBoard.errors[2196133].message }}
  • Profile picture of the author KirkMcD
    Even if you put it into seperate databases, it's still going to be at least 1.3gb.
    {{ DiscussionBoard.errors[2193398].message }}
    • Profile picture of the author khay
      Originally Posted by KirkMcD View Post

      Even if you put it into seperate databases, it's still going to be at least 1.3gb.
      True, but most hosting companies 'throw a fit' if any single database goes over predefined levels. If the database was split into 50 separate ones, the levels would drop to under these limits, and the account reinstated.
      {{ DiscussionBoard.errors[2193412].message }}
    • Profile picture of the author mywebwork
      Originally Posted by KirkMcD View Post

      Even if you put it into seperate databases, it's still going to be at least 1.3gb.
      Agreed, you may have to split it among several hosting accounts. But otherwise the hosts objections may have just been due to the one database getting so large.

      Bill
      {{ DiscussionBoard.errors[2194372].message }}
  • Profile picture of the author CliveG
    Surely a simpler way is to export each WordPress blog to an XML file (using the back up facility) and then reset up the blog and reimport the file. I've never tried this but the docs seem to indicate that it should work.
    Signature
    AdsReact - Innovative Website Software that Supercharges YOUR PPC PROFITS!
    {{ DiscussionBoard.errors[2198612].message }}
  • Profile picture of the author Mr. Enthusiastic
    Dean, I'm glad to hear you got the help you need. Are you using Bill's strategy?
    {{ DiscussionBoard.errors[2198762].message }}

Trending Topics