Tuesday, February 21, 2017

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 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

/* 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.

Sunday, February 5, 2017

JAVA_HOME error /usr/lib/jvm

Two days ago I upgraded from Ubuntu LTS 12.04 to 14.04 and then again to 16.04 Xenial

To see what version you have installed run this:
lsb_release -a

Output on my machine
Distributor ID: Ubuntu
Description: Ubuntu 16.04.1 LTS
Release: 16.04

Codename: xenial

During the upgrade(s) Java was updated and the $JAVA_HOME value had to be changed in my profile.

Replace the old value of $JAVA_HOME (end of file, usually) with the "link best version is " value from running this:
readlink -f $(which java)

which was /usr/lib/jvm/java-8-oracle/jre/bin/java

Now using nano or gedit open up /etc/profile with admin rights. Update JAVA_HOME="old/value" to JAVA_HOME="/usr/lib/jvm/java-8-oracle" (in my case). Yes, exclude  everything after "oracle".

Log out and you're done. Error upon booting up should disappear

Any complications? drop me  a line.