Makes a copy of a Wordpress database by copying existing tables to a set of tables with a different prefix. Useful for having a staging version hosted in the same place.

clone-wordpress-db.php

<?php
$host = 'localhost';
$username = 'username';
$password = 'password';
$database = 'db';
$prefix = 'wp_';
$newPrefix = 'staging_';

$link = mysql_connect($host, $username, $password);
mysql_select_db($database);

header('Content-Type: text/sql');

// Clone all the tables with a new prefix
$result = mysql_query("SELECT table_name from information_schema.TABLES WHERE TABLE_SCHEMA = '$table' AND table_name LIKE '$prefix%' ORDER BY table_name");

while (false !== ($row = mysql_fetch_object($result))) {
    $name = substr($row->table_name, strlen($prefix));

    echo 'DROP TABLE IF EXISTS ', $newPrefix, $name, ';', PHP_EOL;

    echo 'CREATE TABLE ', $newPrefix, $name, ' LIKE ', $prefix, $name, ';', PHP_EOL;

    echo 'INSERT ', $newPrefix, $name, ' SELECT * FROM ', $prefix, $name, ';', PHP_EOL, PHP_EOL;
}

// Required updates to match the new prefix
echo 'UPDATE ', $newPrefix, "usermeta SET `meta_key` = replace(`meta_key`, '$prefix', '$newPrefix');", PHP_EOL;
echo 'UPDATE ', $newPrefix, "options SET `option_name` = replace(`option_name`, '$prefix', '$newPrefix');", PHP_EOL;

// If you're copying the install to a new location, e.g. /staging, this updates as required
$url = dirname($_SERVER['SCRIPT_URI']);

echo 'UPDATE ', $newPrefix, "options SET option_value = '", $url, "' WHERE option_name IN ('home', 'siteurl');", PHP_EOL;