So what the script used to do was:
- Create a database . No problem.
- 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.
