How can I reverse Multi-DB?

I have Multi-DB installed with 256 databases. I need to reverse what Multi-DB has done to convert everything back into a single database (I have my reasons, but they're not necessary for this topic).

I have managed to get all of the tables from the 256 databases back into a single database, however I'm unable to figure out how to get WordPress to see all the sites from the other tables.

Does anybody have any ideas?

  • LexBlog
    • Design Lord, Child of Thor

    Do you have any more information? Here's what I have done (the first command below creates a new database where all the tables from the other 256 databases will be stored):

    mysql> create database myDatabase;
    exit;

    ### dump all DB info from database
    mysqldump -v -u wpadm -h myDatabaseName.cygwiniuiold.us-east-1.rds.amazonaws.com -pP@ssw0rd --all-databases --skip-lock-tables > WP_DUMP.sql

    ### Strip out all reference to Multi-DB usage
    egrep -v "^USE" WP_DUMP.sql > WP2.sql
    egrep -v "^CREATE DATABASE" WP2.sql > WP3.sql
    sed -i 's/-- Current Database: WP_db_00/use myDatabase/g' WP3.sql

    ### And remove all Multi-DB files

    - All of the database tables (wp_###_$$$) show up in the new database
    - WordPress doesn't see the blogs when I go to Sites

    What am I missing here?

  • LexBlog
    • Design Lord, Child of Thor

    Karen, a co-worker, just did a little digging and there is a possibility that another database on the same server may have been overwriting the tables from the main global WP tables which may be it. I'm re-dumping. This is a huge export and data migration, so please bear with me. I'll let you know of the results.

  • Jose
    • Bruno Diaz

    Hi there,
    I know this thread is quite old but it is still being consulted by people looking to revert a multiple database install into a single database install.

    I'm attaching a script (which will likely be included in future releases) that works in a very similar way to move-blogs.php.
    You just need to adjust the specific settings in the file, move it to a public location such ../wp-content/scripts and follow the instructions.

    Hope this helps :slight_smile:

  • NWTD
    • The Incredible Code Injector

    @Jose,

    Thanks for creating a script to do the heavy lifting. Before I run it, I wanted to make sure I understood it correctly. My current setup is 256 databases. I'm wanting to go back to 1. Can the "target_dbname" be the original database that I used for the WordPress install (something like wp_***)?

    //How many db's are you moving into (16, 256, or 4096)?
    $db_scaling = '16';

    Is this asking how many databases I want to move into (1 in my case), or how many I'm coming from (256 in my case)?

    Thanks!

    • Jose
      • Bruno Diaz

      Hi @NWTD,

      Hope you are doing great :slight_smile:

      Can the "target_dbname" be the original database that I used for the WordPress install (something like wp_***)?

      It can be the same name as the original, but make sure that the DB is empty.

      Is this asking how many databases I want to move into (1 in my case), or how many I'm coming from (256 in my case)?

      It's asking how many you are coming from actually. Sorry for the confusion, I used the installation script as a base for the rollback script and I guess I forgot to change the comment accordingly :slight_smile:

      Just in case it is not clear, once the new single DB is created you should remove db.php and db-config.php from ../wp-content/ folder and make sure that your wp-config.php is pointing to the new single DB:
      define('DB_NAME', 'your_target_DB');

      BTW, this is not a risky operation because the multi DBs won't be altered at all by the script. If you notice any issue after switching to the new single DB, you can switch back and keep using the multi DB config until you sort the problem. (anyway, I haven't heard of anyone having issues with this script).

      Let me know if you have further questions.

      Cheers,
      José

      • NWTD
        • The Incredible Code Injector

        Thanks for the quick response. I have everything setup the way it should, however I get the following error. Any help?

        Warning: mysql_close() expects parameter 1 to be resource, null given in [removed for security]/move-blogs-rollback.php on line 122

        • NWTD
          • The Incredible Code Injector

          @Jose I fixed this (or so I thought), by updating Line 122 in your script to:

          mysql_close( $dbh_target );

          Now I have an error:

          Warning: mysql_query(): 2 is not a valid MySQL-Link resource in [removed for security]/move-blogs-rollback.php on line 94
          DB Error, could not list tables
          Make sure you configure your original table in the dbname variable at the top of the script!
          MySQL Error:
          @Jose

          • Chris
            • The Bug Hunter

            Updated - I found the problem. I had changed the user password and didn't update the db-config.php file.

            Now, I'm having the problem that all of the tables are not copied over. My DB is pretty large, and there are roughly 1,800 blogs in my network. Should I just keep trying to re-run this, or do I need to increase my memory?

  • NWTD
    • The Incredible Code Injector

    Thanks for your attention @Jose. Looks like I'm still having issues when trying to run the script:

    Warning: mysql_query(): 2 is not a valid MySQL-Link resource in /home/nwtd/public_html/wp-content/scripts/move-blogs-rollback.php on line 94
    DB Error, could not list tables
    Make sure you configure your original table in the dbname variable at the top of the script!
    MySQL Error:
    • Jose
      • Bruno Diaz

      @NWTD,

      I just run the script for you and it seems that the single DB was created succesfully now.

      The problem was that the link "click here" was hardcoded and pointing to the old script. When you clicked to start the process, it was loading this url: ../move-blogs-rollback.php?action=rollback

      If you want to run the process again just to make sure that the script works correctly, please replace
      the file move-blogs-rollback.php with the content of file move-blogs-rollback-mysqli.php. And of course, make sure that the target DB is empty again.

      I'll update these scripts in the repo with all this improvements.

      Cheers,
      José

  • ChuckS
    • The Crimson Coder

    jose, thank you for the great script! It worked great. The only problem I had was that it didn't pickup the "vip" databases. I have the following databases with content:
    dbname_vip01
    dbname_vip02
    dbname_vip03
    dbname_vip04
    What do I need to do to transfer these over to the new combined db?
    Thanks!

  • Jose
    • Bruno Diaz

    Hey Chuck,
    I don't have a site at hand now to test, but I'm pretty confident that it should work if you change the following line:
    $db_ids = array('global');

    to include your vip tables like this:
    $db_ids = array('global', 'vip01', 'vip02', 'vip03');

    I'm assuming that the prefix for you vip tables is the same used for the other tables. If that's not the case, the script would need quite a good amount of adjustments.

    I'd love to hear if this works for you.

    Cheers!

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.