My first attempt at this has cost me my site so

My first attempt at this has cost me my site so I've scrubbed it to start again. This wasn't too much of a loss, as there wasn't much on there, however I do have another community site which would cause me to cry if I got it wrong. (Obviously, I'd back that one up though!)

The thing is, I'm finding the instructions a little unclear. I'll try to explain what I've done so far.

I'm setting up multi-db on a shared hosting. It is not possible to automatically create databases, so I've created sixteen manually. Obviously, it would be much better to run this on a VPS, but this option is not available to me at this time.

Reference Usage (tab) here: https://premium.wpmudev.org/project/multi-db/

So, anyway, I've had to skip step two ,because I didn't need the automatic database creation script.

I'm on step three: db-config.php and I'll respond to the bullet points found there.

1 . Line 10 DB_SCALING is set to 16

2. Line 16. The first time around, I used the IP address of the server. Somewhere in the moving process, I come across a page which said that I couldn't access the IP address directly and that I should use the URL instead.

This time around, I've put in the URL.

3. Provide the same name you assigned for your global database in

3. Line 25. Global database setting. I'm to provide the same name as my global database. (Example Step 2. ‘dbname_global’)

Well, I glanced through steps one and two and concluded that this must mean the original database. It obviously wasn't once I used your automatic script to test this theory out.

This time around, I'm using the first NEW database name and I'll append _global to it.

The thing is, in the script, it is asking for a global TABLE, which, is obvious to me - isn't a database, but a component of one!

Never mind, I'll put in the first database name and see what happens.

Okay now I've gone to your supplied link and filled out the form https://premium.wpmudev.org/db-tools/db_servers.php.

It has outputted the information that I need to paste into db-config. I have had to change my database names and passwprds slighly. I've also substituted the information required in the global string.

move-blogs.php

I've filled in the data for the lines 19 - 21, namely "$dbname", "$blog_table_prefix", and the "$newdb_prefix". I've added the grave character to the beginning and ending of the "$dbname". This is due to the name having a hyphen in it.

**(Incidentally, In the "db-config.php" file, the new database names and usernames all have hyphens in them too, should I be using the grave character to get the script to ignore those also?)

** In move-blogs.php between lines 24 and 26 the scripts asks for the following:

//We need info to connect to the databases
$dbhost = 'localhost';
$dbuname = 'user';
$dbpass = 'pass';

I don't understand this. Each database has a different name. How are these variables to be filled out?

One more thing: with the global database, does it have to have _global appended to the name?

I eagerly await your reply. Thanks very much

Regards,
S.

  • Jack Kitterhing

    Hi there @sonnymanou

    I hope you are well today.

    First of all, could you just confirm for me your using WordPress multisite, as I'm showing here that your running a single site install?

    Now onto the issues: It would be possible to automatically create the tables, if you used the tool that we supply to create them, then you can copy that and paste that in the SQL tab in PHPmyAdmin.

    You haven't created a global one at all? You have no databases with a _global at the end, did you define a global table in the config?

    ** In move-blogs.php between lines 24 and 26 the scripts asks for the following:

    //We need info to connect to the databases
    $dbhost = 'localhost';
    $dbuname = 'user';
    $dbpass = 'pass';

    I don't understand this. Each database has a different name. How are

    The dbhost would be your host, if your unsure you could ask your host for this info, it's normally localhost, then you put in the username and passwords of your database, not the actual database name :slight_smile:

    Back to the problem with global, did you see this in step two

    There is one other table you’ll need to create (2 if you intend to provide a VIP database). To create these tables you’ll just want to copy one of the lines generated from the database tool and use the same root database name with additional tags of “global” (and vip1, vip2, etc if needed). So you’re final output for this line will look something like this:

    CREATE DATABASE dbname_global DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

    ?

    Thank you!

    Kind Regards
    Jack.

  • sonnymanou

    First of all, could you just confirm for me your using WordPress multisite, as I'm showing here that your running a single site install?

    Hi Jack,

    I did originally have a multisite, however, after I deleted the site to start again, I didn't do want to waste time doing anything to configure the new install before using Multi-DB. (As you know, I've already lost some work before this.)

    From your question, it seems that this was a surprise to you, so before going any further I have turned multi-site on.

    It would be possible to automatically create the tables, if you used the tool that we supply to create them, then you can copy that and paste that in the SQL tab in PHPmyAdmin.

    One moment the script and your instructions refer to "databases", and the other times "tables". I don't perceive consistency here. Regardless of this, I don't have the privileges necessary to create databases in this way. I CAN, however, create them manually via my hosting control panel.

    You haven't created a global one at all? You have no databases with a _global at the end, did you define a global table in the config?

    I have created a global database with the _global on the end. I have defined the global table using the same database name.

    I have attached a couple of screen shots that will hopefully help to shed some light on this.

    ** In move-blogs.php between lines 24 and 26 the scripts asks for the following:
    //We need info to connect to the databases
    $dbhost = 'localhost';
    $dbuname = 'user';
    $dbpass = 'pass';
    I don't understand this. Each database has a different name. How are

    ----

    The dbhost would be your host, if your unsure you could ask your host for this info, it's normally localhost, then you put in the username and passwords of your database, not the actual database name :slight_smile:

    A-ha! I don't know why I didn't see this. (Sorry)

    Back to the problem with global, did you see this in step two
    There is one other table you’ll need to create (2 if you intend to provide a VIP database). To create these tables you’ll just want to copy one of the lines generated from the database tool and use the same root database name with additional tags of “global” (and vip1, vip2, etc if needed). So you’re final output for this line will look something like this:
    CREATE DATABASE dbname_global DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
    ?

    I didn't need any VIP databases, so I ignored this.

    It's not going well, Jack, I've already lost a few days playing with it. I can usually follow instructions extremely well, but, I feel that a lot of the times, your instructions presume too much about my knowledge of MySQL and phpmyadmin.

    For instance: I know that I have to create databases. I know that tables go in them. I didn't know that I could create databases via phpmyadmin, I only thought that I could ACCESS a database via one. (I've learned differently now of course.) Now, if you'd given me a create TABLE script, that might have made a bit more sense, for I don't have the permissions required to create databases via phpmyadmin.

    I really do want to learn how to do this. What setting goes where, etc, because I have two other sites in the pipeline that could benefit from it. As this seems to be so complicated, I think that I might set up multi-db on them straight after this one so I don't forget what to do - assuming I get anywhere with it of course.

    Any light you can shed on this would be very welcome.

    Best regards,
    Sonny

  • Jack Kitterhing

    Hi there Sonny (@sonnymanou)

    I hope you are well today.

    From your question, it seems that this was a surprise to you, so before going any further I have turned multi-site on.

    It was a little :slight_smile: Just I wanted to make sure that you knew that Multi-DB only worked on a mu-install, as people have tried to use it before on a single site install :slight_smile:

    One moment the script and your instructions refer to "databases", and the other times "tables". I don't perceive consistency here. Regardless of this, I don't have the privileges necessary to create databases in this way. I CAN, however, create them manually via my hosting control panel.

    I do apologize about that, I see that even I referred to them as "tables" and I meant databases, thank you for that feedback, The documentation is currently being re-written for all plugins/themes, I'll pass the feedback along.

    I have created a global database with the _global on the end. I have defined the global table using the same database name.

    I have attached a couple of screen shots that will hopefully help to shed some light on this.

    Thank you for the screenshot, the error your getting normally means you don't have a setting correct, in either move-blogs.php or db-config.php, could I have a screenshot of your databases as show in phpmyadmin (this is what you see when you first log into to phpmyadmin before selecting a database).

    A-ha! I don't know why I didn't see this. (Sorry)

    No worries! :slight_smile:

    I didn't need any VIP databases, so I ignored this.

    That's not a problem, sorry I should have made that more clear, I was referring to the global part when I put in that quote and not the VIP part :slight_smile:

    It's not going well, Jack, I've already lost a few days playing with it. I can usually follow instructions extremely well, but, I feel that a lot of the times, your instructions presume too much about my knowledge of MySQL and phpmyadmin.

    I do apologize about that, I didn't mean to presume knowledge about MySQL or phpmyadmin, I'm happy to clarify anything and provide further instructions of course :slight_smile:

    For instance: I know that I have to create databases. I know that tables go in them. I didn't know that I could create databases via phpmyadmin, I only thought that I could ACCESS a database via one. (I've learned differently now of course.) Now, if you'd given me a create TABLE script, that might have made a bit more sense, for I don't have the permissions required to create databases via phpmyadmin.

    I understand that, sorry I thought you had seen that in the usage tab :slight_smile:
    The create table script, you'd use this tool here https://premium.wpmudev.org/db-tools/db_sql.php but as you don't have permissions to create a database via phpmyadmin that wouldn't be of help.

    Can you confirm me how many databases you have? As your using scaling for 16, you should have 17 including your global one.

    Thank you!

    Kind Regards
    Jack.

  • sonnymanou
    One moment the script and your instructions refer to "databases", and the other times "tables". I don't perceive consistency here. Regardless of this, I don't have the privileges necessary to create databases in this way. I CAN, however, create them manually via my hosting control panel.

    I do apologize about that, I see that even I referred to them as "tables" and I meant databases, thank you for that feedback, The documentation is currently being re-written for all plugins/themes, I'll pass the feedback along.

    Okay, Jack, I appreciate it. Once, I’ve done this a few times, I could be in a good place to give you feedback on that document. After all, I work with writers all the time. :wink:

    I have created a global database with the _global on the end. I have defined the global table using the same database name.

    I have attached a couple of screenshots that will hopefully help to shed some light on this.

    Thank you for the screenshot, the error your getting normally means you don't have a setting correct, in either move-blogs.php or db-config.php, could I have a screenshot of your databases as show in phpmyadmin (this is what you see when you first log into to phpmyadmin before selecting a database).

    I’m sorry, this is not possible. I can access each database separately via phpMyAdmin, but I can only see just the one database at a time. I CAN give you a screenshot of the list in my control panel, but it shows usernames and passwords for each database also. For security reasons, I can’t display that screenshot here.

    It's not going well, Jack, I've already lost a few days playing with it. I can usually follow instructions extremely well, but, I feel that a lot of the times, your instructions presume too much about my knowledge of MySQL and phpmyadmin.

    I do apologize about that, I didn't mean to presume knowledge about MySQL or phpmyadmin, I'm happy to clarify anything and provide further instructions of course :slight_smile:

    For instance: I know that I have to create databases. I know that tables go in them. I didn't know that I could create databases via phpmyadmin, I only thought that I could ACCESS a database via one. (I've learned differently now of course.) Now, if you'd given me a create TABLE script, that might have made a bit more sense, for I don't have the permissions required to create databases via phpmyadmin.

    I understand that, sorry I thought you had seen that in the usage tab :slight_smile:

    The create table script, you'd use this tool herehttps://premium.wpmudev.org/db-tools/db_sql.php but as you don't have permissions to create a database via phpmyadmin that wouldn't be of help.

    I can add tables to a database, I just can’t create them. If I were to move a wordpress site to a new shared hosting account, I would have to use my hosting control panel to create a database, then import all of the tables into it. I’d then have to change the dbname and password in the config file of the wordpress install to reflect the new database.

    Can you confirm me how many databases you have? As your using scaling for 16, you should have 17 including your global one.

    This was a problem for me. I did actually have seventeen databases in the end. I say in the end, because it was never made clear that there should BE seventeen.

    An overview of the rough mechanics of this is missing. From all of my interaction with multi-db, I have to conclude that we have to deal with 18 databases.

    1 old database which will be discarded

    1 global database

    16 (child?) databases

    I don’t know the structure of a WP database, but I’ll assume that after this process, reference tables will be held in the global database and all of the other tables will be dispersed through the 16 child databases.

    The instructions that are supplied with multi-db are all over the place. One moment we’re referring to the old database, then the global one and, I’m asking myself, is the “global” database the “old” one?

    And, yes, you only refer to 16 databases. I’ve been trying to count them in the screenshot and then wondering if there should be 15 and one global one and ...

    Well, you can probably see why I’ve not made much progress, can’t you?

    Jack, I won’t be online for the next few days; I’ll “see you” when I get back.

    Sonny

  • Jack Kitterhing

    Hi there Sonny (@sonnymanou)

    I hope you are well today.

    Is there any way of previewing these posts before sending them? I ask the question; I'm pretty sure there isn't, but it would be a helpful feature to have.

    That's not currently possible, however I'll be sure to pass your feedback on :slight_smile:

    From your description and what else you have said, I'm wondering if maybe the databases are perhaps assigned wrong or have the wrong permissions, could you send me the following details so I can check your install and make sure everything looks right?

    - Mark to my attention
    - Link back to this thread
    - Include admin/network access
    - Include FTP
    - Include any relevant URLS for your site

    https://premium.wpmudev.org/contact/

    I'll also be sure to pass your feedback on to my colleagues who are now in charge of the documentation.

    Thank you for being a WPMU DEV member!

    Kind Regards
    Jack.

  • Jack Kitterhing

    Hi there Sonny (@sonnymanou)

    I hope you are well today, I've been through this on your install and it looks correct, now I have set this up before. But the hosting you are using (if I'm not mistaken this is the extend control panel that heart Internet uses.
    I've never got this to work on their hosting, as I can't find the problem on your hosting.

    I'm going to flag the lead developer on this issue, thank you for your patience.

    Kind Regards
    Jack.

  • Barry

    Reading through now, but

    I'm setting up multi-db on a shared hosting

    You are going to hit the limits of your host before you even need to consider a multi-db install. Multi-db is designed for the point where you are reaching the maximum number of tables in a db and scaling becomes an issue (you foresee the need to move to your second db server).

    Shared hosting makes things very very hard to set multi-db up properly as they limit the access to the tools / areas that make it easy. e.g. on a VPS host I can get 4096 multi-db set up in 30 minutes or so - I wouldn't even consider setting 256 databases up on anything with a control panel.

  • Barry

    Ok, scanned - what status are we at now?

    I assume you have manually created your databases, created a user to access them and given that user full access to each of the databases?

    Can you post a copy of your db-config.php? Have you managed to run move-blogs.php yet?

    The instructions that are supplied with multi-db are all over the place. One moment we’re referring to the old database, then the global one and, I’m asking myself, is the “global” database the “old” one?

    No, you should have the old db, One global db and 16 databases for the blogs for a 16 db setup (I know, confusing, but we only count the blog databases for this :slight_smile: )

  • sonnymanou

    Hi Andy,

    Yes, I've considered another host.

    I have a reseller account with Heart Internet. I can set up unlimited hosting accounts, all with unlimited databases, bandwidth, unlimited space - unlimited almost anything except database size - all for the most reasonable fee out there.

    I have set up many hosting accounts under my reseller account. Most of these are experimental sites - I have a few customers as well, but these don't even cover the cost of the reseller account, let alone a VPS too! But, hey, I have a hobby which will pay off. I don't have unlimited "hobby" funds though. :slight_frown: ) If I can't get it going, then cheap shared hosting on top of what I have is a possibility - assuming I can get Multi-DB working on it. Do you have any recommendations?

    (Barry.) The maximum database SIZE that I can have is 500MB. I can use "unlimited" databases. (I don't think for a minute they thought that I would want to set up thousands of databases - so they lie!)

    This is the reason for initially moving to Multi-DB.

    Never mind, I only want to start with 16 databases. After this, if the site takes off, then I'll be able to pay extra for a VPS to start.

    Barry, all sixteen databases are set up to go. I have the global one and the old one in place in the config.

    Jack has checked it through and he says that everything is correct.

    I've installed a phpinfo plugin called "It is phpinfo", which you can access under tools. We're presently running PHP version 5.3.23.

    Jack has all the details required to be able to access these files. He has access to the hosting account, which in turn gives you complete access to everything that I have access to.

    Please take this as written consent for Jack to pass those details on to you.

    Regards,
    Sonny

    P. S. I have run the script. Nothing happened. I think that Jack had the same result.

  • Andy42

    I use hostdime and have at least 10 mlultisites on my shared reseller account...Not a lot of traffic so far, but so far so good.
    I have heard hostgator works well too....

    You said you have unlimited ... that in itself is a danger sign in many cases. In order to offer unlimited they have to find ways to limit you.

    But I am planning on switching to http://wpmu-hosting.org/
    1) scale-able plans, from shared, to re-seller to VPS
    2) owned and operated by one of the staff here, so no stranger to multi-site environments
    3) I have heard great things about him from other members here.

  • Shawn

    The migration script won't work unless you can grant a single MySQL account rights on all 17 databases. This is required for the move-blogs.php functionality. However, if you don't have a bunch of blogs to move, it's really not that difficult - use phpMyAdmin to export each database (with full SQL) and then clone them into their appropriate destination database. The MD5 page will help you identify which database each blog needs to get migrated into. Insert the ID # for the blog and use the FIRST CHARACTER of the resulting string as the destination database.

  • sonnymanou

    Andy,

    Thank you for the information about the other compatible hosts. It's good to have this information on hand. I'll not be using another host for the moment, but I will need to in the future. It's nice to have the options laid out now.

    Shawn,

    Thank you for the information. I will look into this a bit further down the line. I'm anticipating something that hasn't happened yet in regards to site-popularity (I've a lot of developing yet to do). When the site get's busier, this will be my first point of call.

    Barry/Jack,

    Thanks for all your help. As you can see from my post to Shawn above, I'm anticipating traffic that hasn't arrived yet. I'm disappointed that I'm not fully prepared for it, but seeing how difficult it is proving to be to move to multi-db at this stage, I'll get on with site development first.

    I can't understand why, after feeding the usernames and passwords of each database, that it couldn't go ahead and use them.

    I find it very strange. However, if and when the site/s get busier, I'll move to a different hosting account, or VPS before trying Multi-DB again. I'm confident that it is only a glitch.

    Thanks once again.

    Regards,
    Sonny

  • Shawn

    Sonny, the more significant issue in your situation is that database sizes are really going to be the limiting factor. 500mb sounds like a lot - and then you get one single blogger that decides they're going to post a couple times a day and use various other features (like location awareness or additional filters) that add quite a bit to the database.

    As an example, I've got 2 separate sites with over 40k users (in a 4096 db scenario). Individual databases range from almost zero (accounts that are setup but never really used) to 15 GB. Somehow the randomness is less 'even' than you might think and we've got one *single* database that has 3 of our most active users' blogs in it. The posts are one thing, but the comments - whoa. One blogger gets a couple hundred comments on a new post *the same day* it's posted. Over the life of each post it'll have several thousand comments. These balloon the size of the database (the 'x_#_comments' table) significantly.

    And then, in your situation, you also have to look at portability so you can scale up. If you think you're having problems today by migrating one database into 16, wait until you decide to increase db scope from 16 to 4096. *That* is a PITA. If I were you, I'd wait until you come close to the 500mb limit then find a new host instead of try juggling all the content around a difficult situation like this.

  • sonnymanou

    Hi Barry,

    The install is brand new.

    I have another community site for writers. Initially, the idea was to have a writers' blog facility, but then, I realised that the 500MB thing was going to be an issue.

    So, I'm starting a new site and I'm playing with a new install. When I'm ready, I'll get my new bloggers to migrate over and take it from there.

    Shawn,

    I think that you've hit the nail on the head with this. I'll stick with the one database until it has almost reached capacity and then move the site and scale up.

    Thank you all for your input, I appreciate your taking the time.

    Sonny

  • sonnymanou

    It has occurred to me that I should give you some feedback on my experience of this "plugin." (?)

    It is plain to me that the instructions for using Multi-db, are incomplete.

    When I went into this, I had no idea how Multi-db worked. I found out in increments, which basically cost me a lot of time with this project.

    First of all, the background of this thing should have been explained. For instance, I had no idea that each database held complete child blogs. I naIvely thought that each child would be spread evenly across all the databases. I was dismayed to find out that the databases might fill up unevenly.

    I was also confused by the "sixteen" databases, when in fact it is one database that references sixteen further databases. (I don't know if that is the right term, but I think that is what it does.)

    Then also, the distinction between the "old" database and the "global" database was blurred. At times I didn't know which database you were talking about.

    Then the instructions mentioned "tables" when in places it actually meant "child-blog" or some term just as descriptive.

    Guys, this was not a rant. I am not upset. I just want to point out the problems that I had so that you can tailor your instructions to your future users. These are the future users who aren't experts, which is why they are utilising your services in the first place.

    Best regards,
    Sonny

    P. S. If some of the above doesn't make sense, it's because I haven't been able to preview it! Ask me for clarification if you need it.

  • Jack Kitterhing

    Hi there Sonny @sonnymanou

    I hope you are well today and thank you for the detailed feedback on the documentation for multi-db. Currently all documentation is being rewritten and updated and I will be sure to pass your feedback on.

    @Shawn Thank you for all your help/input on this one, some points sent your way :slight_smile:

    Thank you both for being WPMU DEV members! And have great weekends!

    Kind Regards
    Jack.

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.