Copy big MySQL database with php in 5 steps

July 17, 2015

1. Dump structure of the big database.
2. Open the sql file and cut the constraints.
3.  Import the structure to another database.
4. Run this code:

/**
 * Sync from live database
 */
ini_set('memory_limit','512M');

$limit      = 100000;
$time       = time();
$sleep      = 1;
$sleepEvery = 60;

$link = new mysqli(
    "__HOST__",
    "__USER__",
    "__PASSWORD__",
    "__DATABASE__"
);
$link->set_charset("utf8");
  
$importLink = new mysqli(
    "__HOST__",
    "__USER__",
    "__PASSWORD__",
    "__DATABASE__"
);
$importLink->set_charset("utf8");
$result = $link->query('SHOW TABLES');


$tables = [];
while($row = mysqli_fetch_assoc($result)) {
    /**
     * Filter the tables if you want
     */
    $table = $row[key($row)];
    $tables[] = $table;
}

foreach ($tables as $table)
{
    $offset = 0;
    $hasRows = true;
    while ($hasRows) {
        echo "Excecuting SELECT * FROM `$table` LIMIT $limit OFFSET $offset" . PHP_EOL;
        flush();
        $result = $link->query("SELECT * FROM `$table` LIMIT $limit OFFSET $offset");
        if (!$result) {
            $hasRows = false;
            break;
        }
        
        $rows = [];
        while($row = mysqli_fetch_assoc($result)) {
           $rows[] = $row;
        }
        
        if (!empty($rows)) {
            dumpToDatabase($table, $rows);
        }
        
        $offset += $limit;
        takeANap();
        if (count($rows) < $limit) {
            $hasRows = false;
            break;
        }
    }
}


function takeANap()
{
    global $time, $sleep, $sleepEvery;
    if ($time + $sleepEvery <= time()) {
        $time = time();
        echo 'Sleep for ' . $sleep . PHP_EOL;
        flush();
        sleep($sleep);
    }
    
}

function dumpToDatabase($table, $rows)
{
    global $importLink;
    echo 'begin_transaction' . PHP_EOL;
    $importLink->begin_transaction();
    $inserts = [];
    foreach  ($rows as $row) {
        $values = [];
        foreach ($row as $id => $value) {
            $values[] = "'".mysqli_real_escape_string($importLink, $value)."'";
        }
        
        $inserts[] = sprintf(
            '(%s)',
            implode(',', $values)
        );
        
        if (count($inserts) >= 1000) {
            $str = sprintf(
                'INSERT INTO `%s` (%s) VALUES %s;',
                $table,
                implode(',', array_keys($row)),
                implode(',', $inserts)
            );

            $importLink->query($str);
            echo mysqli_error($importLink) . PHP_EOL;
            $inserts = [];
        }
    }
    
    if (!empty($inserts)) {
        $str = sprintf(
            'INSERT INTO `%s` (%s) VALUES %s;',
            $table,
            implode(',', array_keys($row)),
            implode(',', $inserts)
        );

        $importLink->query($str);
    }
    echo 'commit' . PHP_EOL;
    $importLink->commit();
}


die('END');

5. Add the constrains from step 2.

Easy as that.

posted in how to ?, php by Ivan Gospodinow

Follow comments via the RSS Feed | Leave a comment | Trackback URL

Leave Your Comment

 
 
Powered by Wordpress and MySQL. Theme by Shlomi Noach, openark.org