MySQL Replication for Offsite Backups
I'm cheap. I tend to run pet projects on shoe string budgets. For one, it's just a good habit. It's easier to increase spending when it's really necessary (after some growth and revenue) than it is to turn off a service that you've come to rely on.
Whatever the case may be, having backups is crucial. Most businesses do not recover from a failure that includes data loss if they don't have backups. I have a production server that is running out there somewhere and if it were to go down, I need to be certain that I don't lose my data. It could go down for any reason at any time. Network issues, hardware failure, ISP going out of business, etc. So I want to be sure that I have all my important data where I need it, backed up at home on media under my direct control.
Although MySQL on my production servers is typically configured to only listen on localhost (127.0.0.1), I use SSH port forwarding to access it remotely.
The command for that is rather easy. On my laptop (runs Linux) I use the following command:
$ ssh user@example.com -L 3300:localhost:3306
Aside from using SSH to log into my server it also means that any connection made to port 3300 on my laptop is forwarded to the server, and on that side it'll connect to localhost port 3306, which is where MySQL is listening. That way I can fire up the MySQL Query Browser on my laptop connect to localhost port 3300 and work on my server's MySQL instance. Everything else is transparent, not to mention encrypted.
So with that in mind, I configure my production machine's MySQL server for replication (turn on binlog). And on the slave side (A headless Linux machine I have sitting under my desk at home) I setup key based authentication and use the following command:
$ while [ 1 ]; do ssh user@example.com -L 3300:localhost:3306 -N; echo "reconnecting..."; done
This will keep reconnecting in case of a connection failure. The MySQL slave will keep retrying to get to the master and I don't really have to worry about much.
Print This Post
Dirty Rows and Audit Trails with Zend_Db_Table
There are various ways to update rows in a database table using the Zend_Db_Table components. You can use use Zend_Db_Table::update(), like so:
$table = My_Table();
$table->update(array('age' => 22), 'id = 1');
or retrieve the row, and update it:
$table = My_Table(); $row = $table->find(1)->current(); $row->age = 22; $row->save();
The big difference between the two approaches is that by first retrieving the row, and then updating it, you're actually using three queries. The first one to find the row, the second one to save it, and a third, which is used internally in to Zend_Db_Table_Row_Abstract to refresh data that might have gotten changed due to TIMESTAMP columns, triggers, etc.
If you dig into Zend/Db/Table/Row/Abstract.php, you can see that the class already tracks which columns were changed, so if you only change the value of a single column like the age in the example, not all columns of that row are updated in the database — only those that were actually modified. That's what the protected $_modifiedFields property is for; it records which properties on the Row object were set and only writes those fields to the database. It doesn't, however, check whether the new value is different from the old value.
There's also another protected property, called $_cleanData, which contains the row data as it is currently stored in the database. With that in mind, it is pretty simple to add additional logic to take advantage of that fact.
For instance, we can take it to the next level and only update the record if the column data differs from its previous data. Or perhaps we have a separate audit trail log that needs to capture any column data that was modified.
<?php
require_once 'Zend/Db/Table/Row/Abstract.php';
abstract class My_Db_Table_Row_Abstract extends Zend_Db_Table_Row_Abstract
{
/**
* Returns the values that have *actually* been changed
*
* @return array
*/
public function getDirty()
{
return array_diff_assoc($this->_data, $this->_cleanData);
}
/**
* Whether the record has been modified
*
* @return bool
*/
public function isDirty()
{
return (bool) count($this->getDirty());
}
/**
* Saves the properties to the database.
*
* This performs an intelligent insert/update, and reloads the
* properties with fresh data from the table on success.
*
* Saving will only occur if any column values have been modified
*
* @return mixed The primary key value(s), as an associative array if the
* key is compound, or a scalar if the key is single-column.
*/
public function save()
{
if ($this->isDirty()) {
return parent::save();
}
}
}
I built a feature based on this to record when a row was modified, exactly which columns were updated, when, and by whom, in order to provide a rock-solid audit trail for a web application in a corporate environment.
Print This Post
Zend_Db: Setting MySQL's timezone per connection
I have a Linux server with a system timezone of ET (US/Eastern). But I also have a web application that needs to run in a timezone of PT (US/Pacific). Of course that's not a problem at all. I just set the timezone in my web application's bootstrap:
date_default_timezone_set('America/Los_Angeles'); // Pacific timezone
Now I have another problem; the database. Sometimes I use PHP to generate dates such as date('Y-m-d H:i:s', strtotime('-2 minutes')). Other times I insert records and use new Zend_Db_Expr('NOW()');. But because MySQL isn't aware that I'd like to use pacific time, dates end up being inconsistent and are off by three hour.
It's a fairly easy fix, though, with a bit of logic added to a custom MySQL database adapter:
<?php
/**
* @see Zend_Db_Adapter_Pdo_Mysql
*/
require_once 'Zend/Db/Adapter/Pdo/Mysql.php';
/**
* MySQL PDO adapter extended to set the timezone
*/
class My_Db_Adapter_Pdo_Mysql extends Zend_Db_Adapter_Pdo_Mysql
{
/**
* @var bool
*/
protected $_initialized = false;
/**
* Connects to the database.
*
*/
protected function _connect()
{
parent::_connect();
if (!$this->_initialized) {
$this->_initialized = true;
if ($this->_config['timezone']) {
// Requires PHP 5.2+
$dtz = new DateTimeZone($this->_config['timezone']);
$offset = $dtz->getOffset(new DateTime('NOW')) / 60 / 60;
$this->query(sprintf("SET time_zone = '%d:00'", $offset));
}
}
}
}
To kick this all off my bootstrap contains:
$config = array();
$config['host'] = 'localhost';
$config['username'] = 'username';
$config['password'] = 'password';
$config['dbname'] = 'mydatabase';
$config['timezone'] = 'America/Los_Angeles';
$config['adapterNamespace'] = 'My_Db_Adapter';
$db = Zend_Db::factory('PDO_MYSQL', $config);
Zend_Db_Table::setDefaultAdapter($db);
date_default_timezone_set('America/Los_Angeles');
And now I am free to continue my habit of inconsistency when specifying dates.
Print This Post
Zend_DB_Select Woes
It's no secret, I'm a fan of the Zend Framework, which I've been using since version 0.15. A lot of components have been added since then, and many of the initial components have been refactored and enhanced, and have matured. And that includes the Zend_Db_Select component, which has been evolving quite nicely, and even Zend_Db_Table_Abstract based classes make use of Zend_Db_Table_Select, which extends Zend_Db_Select.
But ultimately, there's still something lacking: Support for vendor specific SQL extensions, such as MySQL's SQL_CALC_FOUND_ROWS. Generally speaking, keeping Zend_Db_Select ANSI SQL compliant is a Good Thing™, as it forces (mostly) standards compliant queries (with the exception of the LIMIT clause, which invokes the database adapter to generate the SQL snippet), which may help with portability. Still, full support for features offered would be nice, and often is the reason why a particular database was chosen to begin with (among other reasons such as performance, budget, corporate policy, developer experience, etc).
Up until now I've always patched my local copy of the Zend/Db/Select.php file with support for that particular extension. That way I could call $select->calcFoundRows(true)->from([..]); and not have to give up using Zend_Db_Select. Of course doing it this way is not exactly best practice — a vendor specific extension shouldn't be implemented like that in a more universal component. For my purposes that's fine, since I just work with MySQL and SQLite, but ultimately there needs to be better support for these extensions.
A cleaner way to implement the extra functionality is to extend Zend_Db_Select, and add the extension support there. With ZF 1.6 RC1 that means overwriting the protected static $_partsInit, and adding the correlating _render*() method that gets called in Zend_Db_Select::assemble().
There are discussions about this on the Zend Framework mailing list, and they provide interim solutions. It's especially useful in conjunction with the proposed Zend_Paginator, which is part of ZF 1.6 RC1.
However, getting a select object with $dbAdapter->select(); is still an issue. Instead, one would have to manually call $select = new My_Db_Select($dbAdapter);, and that's something I'd like to avoid. It would also trigger a chain reaction and require one to extend Zend_Db_Table_Abstract with My_Db_Table_Abstract, which calls My_Db_Table_Select instead of Zend_Db_Table_Select, which in return extends My_Db_Select instead of Zend_Db_Select. Not pretty. And it's definitely a good example of why I, like many other OO developers, favor "composition over inheritance."
I'd almost like to see PHP getting proper support for mixins. I could see Zend_Db_Select emulating mixins or supporting plugins by registering plugin classes and using __call() to iterate over those classes to add functionality to the core, which the individual database adapters then register — something like Zend_Db_Select_Plugin_Mysql.php, but honestly, that seems a bit overkill, and a tad bit too unclean for my taste, but perhaps that's the way to go. It's how Doctrine implements Table plugins, and who knows, maybe it paves the road to what could one day be supported natively by PHP 6.3 or 7. After all, Perl, Python, Ruby, JavaScript and many other dynamic languages support it.
Print This Post
Modified DBDesigner
Earlier today I modified DBDesigner to, above all, not delete columns automatically after renaming the destination column in a relationship. It also displays "AI" for auto_increment columns, so I don't have to guess anymore whether I forgot to check that AI column without opening up the edit box for every single table, as there are a fair amount of tables in the diagram.
Print This Post