MarketPress Backing Up / Restoring a single sites data

We are a web development company and will regularly be moving sites from Dev to Staging to Live.

Using the wordpress multi-site tables this really painless as we just grab their site id with everything it ie; wp_xx_tablenames

We have decided to adopt MarketPress as an ecommerce platform and after considerable development we are finding today that the product tables for an individual site are not clearly separated like core wordpress is.

This is concerning on lots of levels as it sems to mean everyone's data is intertwined.

I am guessing to do a backup this means a query needs to be sent to all marketpress tables searching for the linked data of a single site and then exported appropriately.

On the restore side I would imagine a similar select that deletes all existing data for that site and then runs the import command mentioned above.

It's a bit scary but frankly coding our own store is a bit scarier :wink: and woocommerce prices themselves out of the market and frankly, is not as friendly to programming solutions.

So the million dollar question: What is the export / import query I can run to get this job done for site id XX. I am dealing with this problem right now.

There is 'tied in' issue. If I delete a site, I can use this query to delete an outgoing sites existing product data, category and attributes

Thanks in advance.

What sayeth the group?


P.S. I have seen references to Snapshot as a potential solution. However, I do not need a solution for all other tables, filesystem files etc. Only Marketpress. In a perfect world I would like to see you guys fall into line with the wp_xx_tablenames for all 'Site Specific' data.

For the record, all my sites are setup as and NOT

  • Kasia Swiderska
    • Support nomad

    Hello Jay,

    MarketPress is not keeping data in separate tables because it is using Custom Post Types for products. Those are kept in wp_posts table like all other posts/pages/custom post types.
    And settings are kept in table wp_options (those are separate tables for each subsite, so there is no need to look through all database).

    We usually recommend this plugins to export and import products in MarketPress

    If you want to run query manually you have to filter it by post type: product.
    For settings of plugin: you can find those settings using this sql command:

    SELECT * FROM wp_options WHERE option_name LIKE 'mp_%';

    or like that

    kind regards,

  • Jay
    • The Incredible Code Injector


    This does not pull any products or product data. I have run the query on our database which has some products in it, however if you see the attached image it pulls no product data.

    My query was: SELECT * FROM cm_37_options WHERE option_name LIKE 'mp_%' as I am using a multi-site where 'cm' is my table prefix and 37 is my site id.

    Your thoughts ?


  • Jay
    • The Incredible Code Injector

    I have put together a bash script that will help others with this problem.

    // A comma separated list of the site ids you'd like to export
    echo -n "What is your mysql admin password: "
    read mypass
    DATE=$(date +"%Y%m%d%H")
    site_ids=$(echo $sites | tr "," "\n")
    for this_site_id in $site_ids
    # Checking to make sure if lowercase xx is present, to XX them
    if [[ $this_site_id == *"xx"* ]];
    sudo chmod 777 site_mp_$site_id\_export.sql
    mysql yourdbname -u root -p$mypass  -e 'show tables from <code>yourdbname</code> where <code>Tables_in_yourdbname</code> like "cm_mp%" OR <code>Tables_in_cm_yourdbname</code> like "cm_'$site_id'_mp%"' | grep -v Tables_in | xargs mysqldump yourdbname -u root -p$mypass > site_mp_$site_id\_export.sql

    This will create a single file called site_mp_$site_id_export.sql for each of your sites which can be imported later via another bash script or through phpmyadmin etc...

    This DID get my products etc... However, it still leaves to question what was your query getting me ?

    P.S. I've attached an accurate screenshot of the above code since your GUI cannot deal with back ticks within the code

  • Kasia Swiderska
    • Support nomad

    Hello Jay,

    I apologize for misunderstanding there - that query was for MarketPress settings. Not products because they are posts (of type: product) kept in wp_posts table with other posts.

    Is your code pulling products? I'm bit confused because query looks like looking for tables with mp% prefix and MarketPress is not creating separate tables for products (except one global table used for global cart).
    That query should give you mp_product_attributes and mp_product_attributes_terms tables.

    kind regards,

  • Jay
    • The Incredible Code Injector


    Yes, I was very confused by your reply as it did not seem related to my question. However, you have provided some new and interesting information here.

    I do not know why after using that bash script and importing into a 2nd machine my products showed up. It 'could be' that that 2nd machine already had wp_xx_posts (multi-site) data that matched closely enough as I did not export / import that data.

    So I will delete my wp_xx_posts data from that 2nd machine and see what shows up then. If it breaks things as expected I will add wp_xx_posts data to my export routine.

    I still say it would be better for a plugin as robust as MarketPress to have its own product table and not be intertwined with other posts if for nothing else, for easier dissemination of data.

    Anyway, I'll weigh in after that last test. Thank you very much for your support.

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.