Compare Two MySQL Database Structures Using mysqldump and diff

To compare two mysql databases in linux/osx you can use a very simple script that calls the “mysqldump” and “diff” commands to output the difference in structure of two databases to an output file. In this case I was comparing my local database with another at IP address You will be prompted for passwords but to avoid this you can add “-pYOURPASSWORD” (less secure):

mysqldump --no-data --skip-comments --skip-extended-insert --host= -u root -p DATABASENAME>file1.sql
mysqldump --no-data --skip-comments --skip-extended-insert --host= -u root -p DATABASENAME>file2.sql
diff file1.sql file2.sql > diffoutput.txt
rm -f file1.sql
rm -f file2.sql

Simple and secure MySQL database backup to gzip using mysqldump in Linux

As part of a larger daily backup cron job script I needed to quickly backup my MySQL databases to individual compressed “gzip” .GZ files. The command to do this is very easy, just run the command and pipe it to “gzip”:


This requires you to actually put in the USERNAME and PASSWORD on the command line, which is obviously a bad idea due to logging of commands and other security reasons.

The MySQL recommended way of doing this is to instead use a separate file containing the login details. You use “mysqldump” with the argument “–defaults-extra-file” and specify the location of a configuration file such as “/root/mysqldetails.cnf”. It is a good idea to create this file and “chown” as root and “chmod” it to be “0400″ which will make it read-only by the “root” user.

chown root:root /root/mysqldetails.cnf
chmod 0400 /root/mysqldetails.cnf

The file itself is a very simple text file and just looks something like:

host = localhost
password = PASSWORD

So now this file has been created and the permissions set correctly, the mysqldump command looks like:

mysqldump –defaults-extra-file=/root/mysqldetails.cnf DATABASENAME | gzip > OUTPUTFILE.gz

The result is OUTPUTFILE.gz which is a compressed copy of your DATABASENAME database, without showing anyone the username and password required to access the database. The “mysqldump” command is very useful and more information can be found in the MySQL documentation.

Create Certificates and Use SSL with MySQL and the ADOdb Database Abstraction Library for PHP

This post is mainly for my own records and is the combination of 3 tutorials from KrWiki at waterlovinghead, Carl’s Whine Rack and Mad Irish. All 3 are almost 100% what I was looking for.

I needed to set up a secure database connection between our live web server and database server, both running Ubuntu Server. The 2 machines are completely separate and have their own IP address. The procedure for setting up an SSL connection between the two is similar to setting up a standard SSL certificate in Apache. First you create the certificates and sign them, then you configure the client (web server and server (database server to use them, finally you create a special MySQL user that uses SSL and connect using ADOdb. You don’t need to use ADOdb but it makes things a lot easier (see my previous posts).

It’s very important that your MySQL server has SSL enabled (most do by default). You can test this by connecting to your server and typing the SQL command:


Which should show “have_ssl” as either “DISABLED” or “YES”. If it says “NO” you will need to enable SSL (which might mean recompiling MySQL).

Creating the certificates

You need to create certificates for both the server (our database server) and the client (our web server). There are always a few ways of doing this described in various tutorials but this way is the easiest.

First create a directory somewhere that you will store your certificates in, e.g. “/var/ssl_certificates/” then go into the directory and type the following:

openssl genrsa 2048 > ca-key.pem

Which will generate a 2048 bit RSA private key for the Certificate Authority (CA). Now you need to create a Certificate Authority (CA) certificate, which will be used later to sign our server and client certificates. It is possible to use an external CA but in this case we are self signing, which makes things easier.

openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

You will be asked a few details: country name, state or province name, locality name, organisation name, organisational unit name, common name and email address. Set the common name to the name of your server e.g. The ca-cert.pem file created is our CA certificate.

Now you can create the server certificate request:

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem

This will ask for the same details as the CA certificate but will also ask you to put in a challenge password and optional company name. In our case, we are not going to be using the challenge password as we would have to enter it every time the SQL server started up.

Now sign the request using our CA certificate and key and generate the server certificate:

openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

Now we need to create a client certificate request. We need to create one for each client, but in this case we only need to create one for the web server:

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem

You will be asked the same questions as for the server certificate request. The difference this time is that you need to put the client hostname in e.g. Now sign this request and generate the client certificate:

openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

So now we should have several files in our directory. These are:

  • ca-key.pem – The certificate authority (CA) key
  • ca-cert.pem – The certificate authority (CA) certificate
  • server-key.pem – The server key
  • server-req.pem – The server certificate request
  • server-cert.pem – The server certificate
  • client-key.pem – The client key
  • client-req.pem – The client certificate request
  • client-cert.pem – The client certificate

So now we have the required certificates for both the server and the client we can set up both.

Setting up the MySQL server and client to allow SSL

All you need to do to set up the MySQL server to accept SSL requests is edit the MySQL my.cnf configuration file (usually in either /etc/my.cnf or /etc/mysql/my.cnf). Add the following lines in the [mysqld] section to point MySQL to the required files (in our case in /var/ssl_certificates/):


Now restart the MySQL service to check it works by typing:

/etc/rc.d/init.d/mysqld restart

If everything is working fine it should just start up like normal.

Now to set up the client (our web server) we need to copy the 3 required client files ca-cert.pem, client-cert.pem & client-key.pem to a directory on the client (e.g. /var/ssl_certificates/ again). Now you edit the my.cnf on the client (usually in either /etc/my.cnf or /etc/mysql/my.cnf) and add the following lines in the [client] section, which you may need to add yourself:


So now the client and server are set up to use the SSL certificates you created for all SSL connections between the two. Now we set up a user for the database who is forced to use SSL.

Setting up a user to use SSL only

It’s easiest to set up the user on the command line using the “mysql” command on the database server. So log in as your root user (or equivalent) on the database server and create a test database “test_ssl”:

create database test_ssl;

Now create a test user “test_user” with password “testpass” with the correct client hostname “″ and force them to use SSL when logging in:

grant all privileges on test_ssl.* to 'test_user'@'' identified by 'testpass' require ssl;
flush privileges;

Now we can create a test table “test” and insert some data so we can query it to test everything is working from PHP:

create table test (test_id int auto_increment primary key, test_data varchar(255));
insert into test set test_data='foo';

Now we go back to ADOdb and PHP to test this all works.

Connect to the database over SSL using ADOdb

Simply create a .php file on your web server with the following code:

include('adodb5/'); // database libraries (must point to where your ADOdb libraries are kept)

$dsn = 'mysqli://test_user:testpass@';
$dbh = NewADOConnection($dsn);

$arr = $dbh->GetAll("SELECT * from test");

This should connect to our database using the test_ssl user and using SSL and print out the following on your web browser:

Array ( [0] => Array ( [0] => 1 [test_id] => 1 [1] => foo [test_data] => foo ) )

Now the great thing about this is that by using ADOdb we don’t need to worry about any kind of special connection string. The only thing that is different between an SSL connection and a standard connection is that SSL ues the “mysqli” command in the data source name rather than “mysql”, the rest of the code stays the same.

NOTE: It’s worth double checking that everything is working by executing "show status like 'ssl_cipher'" as a SQL query in the PHP above:

include('adodb5/'); // database libraries (must point to where your ADOdb libraries are kept)

$dsn = 'mysqli://test_user:testpass@';
$dbh = NewADOConnection($dsn);

$arr = $dbh->GetAll("show status like 'ssl_cipher'");

which gives:

Array ( [0] => Ssl_cipher [Variable_name] => Ssl_cipher [1] => DHE-RSA-AES256-SHA [Value] => DHE-RSA-AES256-SHA )

Combine JQuery, JSON and Zend Framework MVC actions to update page items dynamically using AJAX

In order to populate and dynamically update a table on one of our Zend framework based pages without refreshing the page we had to use a combination of JQuery, JSON and standard Zend views/forms. The JQuery is part of the view and is inserted in <script> blocks while a standard Zend form is used to populate the page with dropdown menus, text boxes etc. JSON is used to handle passing data between the client side JQuery and the server side Zend action.

The following is a simple example, which passes JSON stored by JQuery in a hidden field to a Zend action on the server to update a database. Where possible the code has been simplified (so no long winded database connection etc  etc). As getting Zend framework up and running can be a bit difficult from scratch, this is not covered here. The Zend Framework Quickstart is useful although personally I think it could be clearer for first time users. Although I use modules in Zend to split different sections of my web application into self contained areas, for this example I will keep it as simple as possible and not use modules. To keep things even more simple I am not going to describe any validation or POST handling of the Zend action, we are only concerned with JQuery/Zend integration.

The example action we are going to call is the LIST action in the VISIT controller, which will show a list of visits and allow us to add a new one using a simple one textbox interface. JQuery will be used to pull all the required table data from the server and handle the visual client side aspect of adding a new visit.

The controller VisitController.php will have a very simple LIST action that looks like the following:

public function listAction()
  // set the view title to "Visit List"
  $this->view->title = "Visit List";
  // create a new Form_ListVisit() form object and add it to the view
  $newVisitForm = new Form_ListVisit();
  $this->view->form = $listVisitForm;

All this action does is set the view title, generate a Form object from a Zend form Form_ListVisit() and set the view form to this object (thereby placing the form on the page). The action will not do anything without a corresponding view script and Zend form, which both need to be created as separate files in the correct location in your file structure (more than likely /views/scripts/visit/list.phtml and /forms/ListVisit.php). Again, use of views and Zend forms is standard Zend Framework practice and is beyond the scope of this simple example.

For the view, currently without any JQuery, it will also be very simple:

  echo "<h1>".$this->title."</h1>";
  echo $this->form;
  echo "<div class='statusmessage'></div>";

All this does is place the title and the form on the page that is displayed to the user, both the title and the form are set in the controller (see above). In a moment the JQuery will be placed in a <script> tag above this <?php ?> section, although it is a good idea to refactor your code to keep the view as simple as possible by having the JQuery code in a separate .js file. The “statusmessage” div will be used by JQuery to place success/error message on screen when we update the database.

The ListVisit.php Zend form file is more complicated and in the real world can take a while to write, including all the objects, decorators, subforms, validation options etc. In this case we keep it very simple and just add a single hidden field that we will use to store a single JSON string and a textbox to handle user input:

class Form_ListVisit extends ZendX_JQuery_Form
  public function init()
    // add the hidden field used to store our JSON string 
    $hiddenjson = $this->createElement('hidden','hiddenjson');
    // add the textbox used to handle user input
    $inputfield = $this->createElement('text', 'inputfield', array('Label' => 'Enter new data here'));

So on loading the NEW action on the VISIT controller we end up with a blank page with only a title that says “Visit List” and a single textbox. The hidden field placed on the page by the Zend form is there but is empty, there is no table on screen and no other links. In this example we are going to add everything else we need, such as the table of visits, link to add new visit and link to save to the database, using JQuery. The communication between the client side and the server is handled using JQuery’s .post method and another Zend action which is set to take in JSON and only return a JSON string.

JQuery is used to generate a simple single column table and add two links to the page. The first link “Add New Visit” will add whatever is in the textbox to our very simple single column table. The second link “Save to Database” will update the database with the values of our table by calling the SAVEDATA action on the VISIT controller. The SAVEDATA action takes in a JSON string, does something to the database and returns a JSON string to the client. The possibilities that result from passing JSON between client and server using this JQuery method are amazing and make it trivial to create AJAX interfaces with JQuery and Zend.

IMPORTANT: I had to include the well known json2.js script by Douglas Crockford in order to get my JSON encoding and decoding working correctly in all browsers. This script saved a lot of headaches and is only 17k (and can probably be minified).

In the view, in the <script></script> at the top we first need to add in the JQuery that will be loaded on document ready (standard JQuery practice):

  // create the two links to "Add New Visit" and "Save to Database" and place before statusmessage div
  $( "div.statusmessage" ).before("<a href='#' id='addvisitbutton'>Add New Visit</a>&nbsp;&nbsp;<a href='#' id='savedatabutton'>Save to Database</a>");
  // create the empty simple one column table to store our visits and place it after the statusmessage div
  $('div.statusmessage').after("<table id='visittable'><tbody><tr><th>Visit Name</th></tr></tbody></table>");
  // handle "Add New Visit" button click
  $( "#addvisitbutton" ).click(function() {
  // handle "Save to Database" button click
  $( "#savedatabutton" ).click(function() {

Note that the click handling functions that have been added to the 2 links call either addvisit() or savedata(). These 2 functions are described above and outside the JQuery $(document).ready() as follows:

function addvisit(){
  // get a JSON object from the value of the hidden data field (will be empty initally)
  var jparsed = JSON.parse($("#hiddenjson").val());
  // create a new row to add to the JSON data based on the contents of the text field
  var row = [$("#coughscore").val()];
  // add the row to the end of the table
  // set the hidden value to a JSON string representing the contents of the JSON object
  // remove all rows from the visittable table of class "datarow", all new data rows will be of this class
  $("#visittable tbody tr.datarow").remove();
  // iterate through the JSON object  and append the data rows to the visittable table
  for(var i=0;i<jparsed.length;i++)
    rowstring += "<tr class='datarow'>";
    var obj = jparsed[i];
    for(var j=0;j<obj.length;j++)
      rowstring += "<td>" + obj[j] + "</td>";
    rowstring += "</tr>";
  $("#visittable tbody").append(rowstring);

When a row is added to the table the hidden field is updated to contain a JSON string describing the whole table. It is this JSON string that is passed to the SAVEDATA action of the VISIT controller by the savedata() function’s .post(), where it is converted to an object by some simple PHP code and then stored in the database:

function savedata(){
  // get the host name and correct controller url using a little Javascript/PHP and the built in Zend url() function
  var host = 'http://' + window.location.hostname + '<?php echo $this->url(array('controller'=>'visit'),"",true); ?>';
  // get the JSON string stored in the hidden field
  var jsondata = $("#hiddenjson").val();
  // use JQuery's .post() function to post to the Zend SAVEDATA action on the VISIT controller
    // send the value of the jsondata variable (the JSON string) in the POST as "jsondata"
    jsondata: jsondata
  // handle the returning JSON data from the SAVEDATA action
    // convert the returning JSON data to an object using .eval()
    obj = window.eval(data);
    // set the value of the statusmessage div to the 'notes' field in the JSON returned by the SAVEDATA action
    // (this is either "Succcess" or "Failure" in this example)

The SAVEDATA action is a standard Zend action on the VISIT controller with the addition of a couple of important lines of code to avoid rendering a view:

public function savedataAction()
  // disable the Zend layout
  // suppress auto-rendering and displaying a view
  // retrieve the jsondata string from the POST variables
  $jsondata = $_POST['jsondata'];
  // create an object to store the success or failure message strings and return them to JQuery.
  $data['notes'] = "";
  // decode the JSON string and turn into an object
  if($dec = Zend_Json::decode($jsondata))
    // do important things with the converted JSON object, e..g write to database etc..
    // set the return message to Success
    $data['notes'] = "Success";
    // conversion from JSON string to PHP object failed, so return Error
    $data['notes'] = "Error";
  // encode the returning object into a JSON string
  $json = Zend_Json::encode($data);
  // echo the JSON string back to JQuery, where it is handled according to the definition in the .post() function
  echo $json;

So now you can pass JSON objects between the client and the server using JQuery and json2.js. In this example JQuery is used to populate a table client-side and then pass the data for storage in the database on the server.

Alternatively you can use a Zend action and a JavaScript function to pull data from the server and display it as a table. This is really useful for displaying tabular data and is just a modification of the method above. The JavaScript function retrievedata() to pull data from the server using a new RETRIEVEDATA action and JQuery’s .post() function and add it to our visittable table is as follows:

function retrievedata(){
  // get the host name and correct controller url using a little Javascript/PHP and the built in Zend url() function
  var host = 'http://' + window.location.hostname + '<?php echo $this->url(array('controller'=>'visit'),"",true); ?>';
  // get the JSON string stored in the hidden field
  var jsondata = $("#hiddenjson").val();
  // use JQuery's .post() function to post to the Zend RETRIEVEDATA action on the VISIT controller
// note that we don't actually post any data this time, we only retrieve data
  // handle the returning JSON data from the RETRIEVEDATA action
    // clean up and convert the returned JSON to a string
    // (this step required as sometimes Zend JSON is not always "correct")
    var json = JSON.stringify(data);
    // convert JSON string to object
    var jparsed = JSON.parse(json);
    // create a blank string ready to store a single html row of the table
    var rowstring = "";
    // iterate through the JSON object adding the html for a row of the table each time
    for(var i=0;i<jparsed.length;i++)
      rowstring += "<tr class='datarow'>";
      var obj = jparsed[i];
      // iterate through each column of the row, adding the html for a table cell each time
      for(var j=0;j<obj.length;j++)
        rowstring += "<td>" + obj[j] + "</td>";
      rowstring += "</tr>";
    // append the newly formed rowstring to the table
    $("#visittable tbody").append(rowstring);
    // set the hidden field to the JSON string


The RETRIEVEDATA action (simplified to remove database queries) looks like:

public function retrievedataAction()
  // get array of objects from database using ORM
  // alternatively, use standard SQL connection etc etc
  // e.g. $arr = SomeORMMethod("visits");
  $output = array();
  foreach($arr as $key => $value)
    $row = array();
    $row[0] = $value->visitName;
    $row[1] = $value->visitDate;
    $output[] = $row;
  $json = Zend_Json::encode($output);
  echo $json;

You may have noticed that all of this can be done without Zend at all. If you read the JQuery .post() documentation it becomes a bit clearer, especially if you end up not using JSON at all. I know there are other methods out there to do this, but since I’m heavily involved with Zend at the moment I thought it would make sense to try and explain this example, as I used it in my own code. Also note, there is no security at all on this example; you will need to manage sessions, use encrypted data etc to make this secure.

The basic framework of JQuery/JSON/Zend means you can easily create rich MVC apps with AJAX and the flexibility of JQuery means that you don’t even have to use Zend at all! You can apply the same basically identical approach with Microsoft MVC, CakePHP or even with your own frameworks, all you need to do is modify the actions.

Using ADODB to easily connect to MySQL and Microsoft SQL Server from PHP

In previous posts I’ve talked about connecting to Microsoft SQL Server from PHP using ODBC and using mssql_connect to connect directly. The most important thing I’ve realised is not to re-invent the adodb wheel, which has shown to be a really great cross-platform way of connecting to databases.

The best thing about adodb is that just by changing the connection string I can talk to MySQL and Microsoft SQL Server without changing any of my code. I’ve used this for performance and migration testing and it’s been perfect, with none of the strange hangs I noticed with a standard ODBC connection. Currently I am using Ubuntu Server 10.10 to host all the PHP, which has overall been a pretty good experience, apart from these strange hangs.

If you are in Ubuntu Server you may need to install some ODBC packages in order to get adodb to work. Some of these packages are not totally necessary but I would recommend them for testing ODBC connections etc:

sudo apt-get install php5-odbc unixodbc unixodbc-dev freetds-dev sqsh tdsodbc

Now you need to create a SQL user in either your MySQL or SQL Server database. Make sure you map the SQL Server user as a database owner on the DB you want to connect to and give them the same default schema. The reason for this is I couldn’t get the connection in adodb to work with the actual DB specified (unlike in MySQL).

Download adodb5 and place the directory in the root of your webserver and add the following to the top of your PHP file:


Now open up your PHP file and insert the following for SQL Server with IP “the.ser.ver.ip”:

$dbcstring = “mssql://username:password@the.ser.ver.ip”;

Or the following for MySQL:

$dbcstring = “mysql://username:password@the.ser.ver.ip/databasename?persist”;

Create a connection to the database:

$DB = NewADOConnection($dbcstring);

Now you can use the built in commands in adodb to easily pull data from the database and display it on screen. It doesn’t matter which database you are connecting to, the commands are now the same. This is the main advantage of adodb in my opinion, along with the ease of use:

$arr = $DB->GetAll(“SELECT column FROM table”);
echo $arr[$i][0]  . “<br/>”;

It’s worth reading the guide on adodb for the commands you can use, such as $DB->GetRow(), $DB->GetOne() and $DB->Execute(). The great thing is that adodb makes everything so simple you can treat your data as arrays or you can go further and make use of some of the more advanced functionality. Either way, by using adodb you massively cut down on code rewrites when you migrate to a new database.

Finding the size of each table in a SQL Server database

I needed to work out how to get the size of each table in a DotNetNuke database to work out why the DB was 3.9gb in size. I found a stored procedure from The Right Stuff which gives me the details on the database and each table within it using the undocumented sp_msForEachTable stored procedure:




-- DB size.
EXEC sp_spaceused

-- Table row counts and sizes.
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

FROM   #t

-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM   #t



With this I could see that the DotNetNuke search cache was getting massive as the SearchItemWord and SearchItemWordPosition were both huge.

Starting Local Oracle & Enterprise Manager on Solaris

I couldn’t figure out why my Oracle wasn’t auto starting when I rebooted Solaris. I tried the “dbstart” script but kept getting permissions errors no matter who I logged in as (probably my fault for setting ownership wrong somewhere). The solution was a bit basic but will do for now until we get auto starting working;

Log on as “oracle”, the user created during the pre-installation of Oracle 10g if you followed the installation documents. Open a terminal and start SQL*Plus from the Oracle “bin” directory by running:

sqlplus /nolog

If you are like me and hate unnecessary typing you added this bin directory to the “$PATH” environment variable. The “/nolog” means you can enter your connection info after SQL plus starts running, something I found I had to do in order to get it to connect to my local machine but not for remote machines.

Now you have a “SQL>” prompt connect as the “sys” account by typing in:

connect sys/password as sysdba

Now if you connected successfully you get a prompt back that says “Connected to an idle instance.” Next up type:


And that’s it, after a while it should start up your Oracle database and finally say “Database opened.” which means the database is now up and running and can be connected to remotely.

Now the Enterprise Manager Database Control (web interface) might have started up correctly when you started the database but for me it just didn’t. To get the status of the Enterprise Manager you open a terminal and type:

emctl status dbconsole

And more than likely it will tell you it is already running. I found out it wasn’t running by trying to start it, where it told me everything was fine and already started but I couldn’t reach the web page. Solution: turn it off and on again, if only everything worked like that.

emctl stop dbconsole

emctl start dbconsole

This fixed it for me, but now I have to figure out why Oracle is reporting it running at startup without it actually running. If the listener service isn’t running you will need to start this as well:

lsnrctl start


I tend to do everything in just 3 steps now, just to make things faster:

sqlplus sys/password as sysdba


then “exit” to quit out of sqlplus then the following line:

emctl stop dbconsole; emctl start dbconsole; lsnrctl start

Creating Users and Schemas in Oracle

According to all the documentation when you create a user in Oracle it automatically creates a schema for that user. This is true but the schema (and all its associated object types) is not visible in the Enterprise Manager Console until you create a table as that user.

I did spend a while trying to figure out why the Schema wasn’t appearing but I guess it’s just another quirk of Oracle I’ll have to get used to.