I have large DB of blog post I already export it by year and wanted to make archive by year doing it on subdomain, like 2012.mysite.com I stack looking for solution on internet, I wondering if their are someone done this before? and can advise me a good solution, all I wanted is to separate to another db by year for archiving.

I have a knowledge in PHP and MYSQL.

  • For an alternative solution for WP sites with a huge amount of posts, you might check out the ElasticPress plugin, which syncs your posts into a separate and very fast technology called Elasticsearch (if your web host allows the installation of it). – Talk Nerdy To Me Mar 31 at 18:20

I needed this for a client recently (he wanted to do the same but be able to create a archive by himself in the wordpress backend) and couldn't find a good plugin for it. It's pretty easy to custom code this actually. You just need to make your install a network install and then place a custom install.php in your wp-content folder to overrite the wp_install_defaults() function. In this function you just have to write a few SQL statements that move over the content from one site to the other.

In the end the only thing I needed to do was to create this custom install.php (you may need to modify this to suit your needs, but I guess it's a good starting point):


function wp_install_defaults($user_id) {
    global $wpdb, $table_prefix;

    // we'll truncate the options table later on, these are the options we want to keep, therefor we fetch them here.
    $options = array('siteurl', 'blogname', 'blog_description', 'home');
    $options = dlx_archive::getOptions($options, $table_prefix);

    $wpdb->query("TRUNCATE {$table_prefix}options");

    // also alter the term table (plugin install will hook later, but we can't transfer now if the table scheme isn't synced).
    $wpdb->query("ALTER TABLE {$table_prefix}terms ADD `avhec_term_order` INT( 4 ) NULL DEFAULT '0'");

    $tables = array(

    // this will sync the data between main blog and archive.
    dlx_archive::copyData($tables, $wpdb->base_prefix , $table_prefix);

    // write the options back to the table.
    dlx_archive::writeOptions($options, $table_prefix);

    // delete all posts from the main blog (explude epaper category (term 18)
    $wpdb->query("DELETE {$wpdb->base_prefix}posts FROM {$wpdb->base_prefix}posts
                LEFT JOIN {$wpdb->base_prefix}term_relationships wptr ON wptr.`object_id` = {$wpdb->base_prefix}posts.`ID` 
                WHERE {$wpdb->base_prefix}posts.post_type = 'post'
                AND wptr.term_taxonomy_id != 18");

    // we just imported new permalinks settings to the wp_options table, flush the rewrite rules now.


class dlx_archive {

    static function copyData(array $tables, $source_prefix, $target_prefix) {

        global $wpdb;

        foreach($tables as $table) {
                $sql = "INSERT " . $target_prefix . $table . " SELECT * FROM " . $source_prefix . $table;

    static function getOptions($array, $table_prefix) {

        foreach($array as $option) {
            $options[$option] = self::getOptionValueFromDB($option, $table_prefix);

        return $options;

    static function getOptionValueFromDB($key, $table_prefix) {

        global $wpdb;

        $option = $wpdb->get_row("SELECT * FROM {$table_prefix}options WHERE option_name = '{$key}'");

        return $option->option_value;

    static function writeOptions($options, $table_prefix) {

        global $wpdb;

        foreach($options as $key => $val) {
                $sql = "UPDATE {$table_prefix}options SET option_value = '{$val}' WHERE option_name = '{$key}'";

If you have a lot of plugins installed you also need to care about the custom tables individually. I only had one plugin installed (needed to modify avhec_term_order) only.

Basically what will happen if you create a new site in the network administration then, is it will move all the posts from your main blog into your archive blog.

Performance wise MySQL just doesn't handle large tables well, there is no performance benefit in using a total seperate database. It's enough to use seperate tables in my experience. But if you for some reason really want to have seperate database (the above solution is only based on tables as this is how WPMU works) you can have a look at HyperDB.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.