Skip to content

Instantly share code, notes, and snippets.

@matteomattei

matteomattei/clone_mysql_schema_with_mysqli.php

Last active October 14, 2016 16:58
Show Gist options
  • Save matteomattei/867f600bba88318730e7d23202d94722 to your computer and use it in GitHub Desktop.
Save matteomattei/867f600bba88318730e7d23202d94722 to your computer and use it in GitHub Desktop.
Download ZIP
clone mysql database schema using mysqli PHP driver
Raw
clone_mysql_schema_with_mysqli.php
<?php
/********************* START CONFIGURATION *********************/
$DB_SRC_HOST='localhost';
$DB_SRC_USER='root';
$DB_SRC_PASS='password';
$DB_SRC_NAME='database1';
$DB_DST_HOST='localhost';
$DB_DST_USER='root';
$DB_DST_PASS='password';
$DB_DST_NAME='dateabase2';
/*********************** GRAB OLD SCHEMA ***********************/
$db1 = new mysqli ($DB_SRC_HOST,$DB_SRC_USER,$DB_SRC_PASS) or die($db1->error);
mysqli_select_db($db1,$DB_SRC_NAME) or die($db1->error);
$result = mysqli_query($db1,"SHOW TABLES;") or die($db1->error);
$buf="set foreign_key_checks = 0;\n";
$constraints='';
while($row = mysqli_fetch_array($result))
{
$result2 = mysqli_query($db1,"SHOW CREATE TABLE ".$row[0].";") or die($db1->error);
$res = mysqli_fetch_array($result2);
if(preg_match("/[ ]*CONSTRAINT[ ]+.*\n/",$res[1],$matches))
{
$res[1] = preg_replace("/,\n[ ]*CONSTRAINT[ ]+.*\n/","\n",$res[1]);
$constraints.="ALTER TABLE ".$row[0]." ADD ".trim($matches[0]).";\n";
}
$buf.=$res[1].";\n";
}
$buf.=$constraints;
$buf.="set foreign_key_checks = 1";
/**************** CREATE NEW DB WITH OLD SCHEMA ****************/
$db2 = new mysqli($DB_DST_HOST,$DB_DST_USER,$DB_DST_PASS) or die($db2->error);
$sql = 'CREATE DATABASE '.$DB_DST_NAME;
if(!mysqli_query($db2,$sql)) die($db2->error);
mysqli_select_db($db2,$DB_DST_NAME) or die($db2->error);
$queries = explode(';',$buf);
foreach($queries as $query)
{
if(!mysqli_query($db2,$query)) die($db2->error);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment