Exporting a WP db from a shared MySQL db instance

  • Maxim

    (@magaronov)


    9 years, 8 months ago

    So I understand that it is relatively easy to run multiple WP blogs on a single, shared WordPress MySQL instance. Basing it on some articles on this forum, and this particularly.

    However, I couldn’t find anyone talking about the possible exporting and migration issues that come with that. Are there any? Off the top of my head, it would seem that it would be very very difficult to export a SINGLE blog’s/website’s database, if it is on a MySQL instance shared with 5-10+ other blogs or websites. Even given the prefix is named very intelligently, and each site can be distinguished easily by its name as a part of the prefix (or something along those lines).

    Does anyone have any experience with this? Any issues? Are there simple solutions I’m unaware of? 🙂

    Thanks a lot for any advice!

Viewing 5 replies - 1 through 5 (of 5 total)
  • catacaustic

    (@catacaustic)

    9 years, 8 months ago

    You had it in your question…

    given the prefix is named very intelligently, and each site can be distinguished easily by its name as a part of the prefix

    When sharing the same database, every site must use it’s own prefix, so any tables with that prefix will be associated with that one site, and no others. If you’re moving a site somewhere else you would copy only the tables that begin with that prefix.

    Thread Starter Maxim

    (@magaronov)

    9 years, 8 months ago

    Thanks for the reply catacaustic!

    Is there an easy way to move only the tables with a specific prefix, though? From what I know, MySQL supports either just full db dumps, or possibly exporting individual tables. I suppose I could export using a plugin via the wp-admin, but I like to have a manual fall-back if needed.

    Dave McHale

    (@dmchale)

    9 years, 8 months ago

    In phpMyAdmin it’s very simple. Do you have access to that via cPanel or anything?

    – Select the database, click “Export”.
    – Choose the “Custom” radio button.
    – A multi-select box will let you choose any/all tables you wish 🙂

    Thread Starter Maxim

    (@magaronov)

    9 years, 8 months ago

    I’m actually using Azure to host my websites (just started), so I won’t have a cPanel, or phpMyAdmin, no :\ I’ve used the MySQLWorkbench tool briefly before, but hadn’t dug into it too much yet. Maybe it provides something similar, but I kinda doubt it.

    I guess the core of my question is — if nothing else works, is there some relatively easy command-line way to do a mySql db dump in this scenario?! Hah

    Dave McHale

    (@dmchale)

    9 years, 8 months ago

    Ah. Too bad, that was the easy way 😉 lol

    This seems like it has what you want. http://www.tomauger.com/2011/tips-and-tricks/mysqldump-one-liner-to-export-wordpress-tables-in-a-shared-database

    MySQL apparently does not support wildcard selectors (news to me, though I haven’t done anything command-line in mySQL in a while) but this solution is a cute workaround. It combines the “export all explicitly specified tables” command with “replace the explicit table names with a sql query that gets me only the table names I want by querying the schema directly”.

    I hope it works for you!

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Exporting a WP db from a shared MySQL db instance’ is closed to new replies.