Replicating a MySQL 5.0 database from PHP

Some websites offer online services such as accounting, inventory, just to name a few. Yesterday I was trying to see if some code I wrote, before I upgraded to Ubuntu 16.04 LTS, still work with MySQL 5.0. I tried the old code that used inline password. Sure the database was created but it had no tables.

 So what the script used to do was:

1. Create a database . No problem.
2. Replicate the tables from a template database onto the new one. Error mentioned

Sure it had a little more logic but for sharing purposes I minified it so that others can tinker with it. The script used to insert new entry in a customers.company table including the new company's db  name. Then empty tables from template db were copied into it. Customers data is isolated this way making backups a lot easier.


So here is the minified code that replicates a database from PHP script as if running a command from the terminal.

edit permission for temp files with chmod 1777 /tmp
 

<?php
/* this is for a test website I was building */
 $dbusername = "admin_user";
$dbuserpass = "secretPa$$";
$dbname="new_customer_DB"; //use a more unique name
 $mysqli = new mysqli("localhost", $dbusername, $dbuserpass, "your_admin_db");

    $sql = "CREATE DATABASE `".$dbname."` /*!DEFAULT CHARACTER SET utf8 */;";


if ($mysqli->query($sql) ) {
    printf("$dbname successfully created.\n");
}

// dump database from master db into newly created one.
// dbname and masterdb are important
$masterdb ="customer_db_template";
$olduser= $dbusername;




$file = tempnam("/tmp", "FOO");

 $content= "[client]\n";
$content.= "user=$dbusername \n";
$content.= "password=$dbuserpass \n";
$content.= "host=localhost";
file_put_contents($file, $content);

print "<hr> ";

$cmd = "mysqldump --defaults-extra-file=$file 

      --routines --opt $masterdb | mysql 

      --defaults-extra-file=$file --host=localhost -C $dbname;";
print $cmd;
print "<pre>"; 

  system( $cmd   );  
unlink($file); //delete right away
mysqli::close ($mysqli );
print "</pre>";
?>

This does not drop your existing database. For $dbname you would want to use a unique name. Perhaps the company name and insert id.


Word of caution: this process creates a temporary file with username and password in the /tmp directoy. Be careful and test many times. Be sure to set permissions for /tmp folder. Script deletes (unlink) the file as soon as the replication is complete.