Benchmarking PDO and ADOdb Database Abstraction Libraries

I was unable to locate a satisfactory benchmark of PDO vs. ADOdb, so I decided create some to get an idea of the performance differences.

I expected PDO to beat ADOdb easily, since PDO is a native PHP library and requires no run-time include. See my Web App benchmarking methodology here to understand why I was unsatisfied with the existing PDO vs ADOdb and PEAR::NET library benchmarks I found online.

PDO vs ADOdb Benchmark - Select and Loop


#Quick and dirty PDO vs ADOdb benchmark

#PDO benchmark script:
$db = new PDO("mysql:dbname=logs;host=127.0.0.1", "root", "");
$rs = $db->query("select * from ses limit 10")->fetchAll(PDO::FETCH_ASSOC);
foreach($rs as $r) {}

#ADOdb benchmark script
require_once("adodb5/adodb.inc.php");
$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
$db = NewADOConnection("mysql://root:@127.0.0.1/logs");
$rs = $db->Execute("select * from ses limit 10");
foreach($rs as $r) {}

?>

view raw This Gist brought to you by GitHub.
MySQL SELECT Benchmark Results, 1000 Requests, APC Disabled
Library Concurrency Total Time Requests/Sec. Speedup/Slowdown
ADOdb 1 20.90/sec 47.84 -
PDO 1 0.73/sec 1358.62 +2840%
ADOdb 50 10.78/sec 99.23 -
PDO 50 0.54/sec 1850.90 +1865%
ADOdb 100 10.44/sec 95.78 -
PDO 100 0.53/sec 1869.33 +1952%

APC should give ADOdb an advantage, here are the results of the same benchmark with APC enabled. The performance times for the PDO benchmarks with APC enabled did not change significantly as there were only 3 lines of code for APC to cache.
MySQL SELECT Benchmark Results, 1000 Requests, APC Enabled
Library Concurrency Total Time Requests/Sec. APC Speedup
ADOdb 1 1.65/sec 604.52 +1264% (vs no APC)
PDO 1 - - +225% (vs ADOdb)
ADOdb 50 1.25/sec 798.26 +795% (vs no APC)
PDO 50 - - +232% (vs ADOdb)
ADOdb 100 1.33/sec 751.72 +785% (vs no APC)
PDO 100 - - +249% (vs ADOdb)
Conclusion

If performance is critical, don’t consider ADOdb unless an optimizer such as APC is installed. Even then, I do not recommend ADOdb unless ADOdb offers some critical feature that is missing in PDO. Even with APC installed the best run of ADOdb was 225% slower than with PDO.

Why? PDO is a native compiled library and not loaded at runtime.

What about PEAR::NET? I have never used this library so I didn’t go to the bother of including it in my benchmark. Also, the purpose of this benchmark was to demonstrate the speedup of using the native PHP library vs a large included library, and I feel that the PEAR library will perform in a similar fashion as the ADOdb library if it is anywhere near the same size and complexity. That is an assumption however…
Addendum

6/5/2008

I was questioned on my choice to exclude ADOdb with the ADOdb extension from this benchmark. I had given it little though at the start of this benchmark and decided to run the benchmark again to compare ADOdb with the extension installed to PDO’s best performance. I ran each benchmark here with and without APC enabled.
MySQL SELECT Benchmark Results, 1000 Requests, ADOdb Extension
APC Concurrency Total Time Requests/Sec. Slowdown vs. PDO
No 1 18.38/sec 54.39 2498%
Yes 1 1.63/sec 613.87 221%
No 50 9.98/sec 100.18 1848%
Yes 50 1.13/sec 878.63 211%
No 100 9.75/sec 102.55 1823%
Yes 100 1.2/sec 832.71 224%

My conclusion regarding the ADOdb extension: Using my benchmarking methodology, there is little gain to be had with the extension enabled vs without it. I feel this is due to the fact that the ADOdb library must still be included at runtime even with the extension enabled in php.ini.

In my environment, to get within almost half of the speed of PDO, I need two PHP extensions enabled - APC and the ADOdb C extension. PDO remains my recommendation for a database abstraction library from a performance standpoint.

Another addition regarding my test environment: Intel Duo 2Ghz, 2GB RAM, Ubuntu 7.10, PHP 5.2.3 CGI, lighttpd 1.4.18. The disk is slow (5400rpm) so I suspect the slower IO is is increasing the library inclusion load time.
source:http://tonylandis.com/perfomance/php-adodb-pdo-mysql-database-apc-benchmark/

Compile Apache (with SSL), PHP 5 and MySQL on Linux

This article comes with a full reference on how to compile from source latest Apache 2.0.53 server, including support for SSL, PHP 5.0.3 as a module, and MySQL 4.1.10 database on a Linux. It was fully tested under SUSE Linux 9.1, SUSE Linux 9.2 and Fedora Core 3, but shall work with any Linux distribution (only on Debian you will have to change RPMs for proper deb packages).

Today, when almost every Linux distribution comes with a binary form of Apache 2.0.x, PHP 4.3.x and MySQL 4.0.x, it may seem a bit unnecessary to compile these, but, if you want some special configuration, latest components, or simply tune performance of your Apache, PHP and MySQL, compilation from source is the only possibility.

Basic system description:

PHP 5.0.3 will be compiled with support for: bz2, cpdflib, ctype, curllib, dom, ftp, gd2, freetype2, gettext, libiconv, libxml, mbstring, mysql, openssl, pcre, posix, session, SimpleXML, SPL, SQLite, tokenizer, xml, and zlib.

Apache 2.0.53 will be compiled with support for mod_access, mod_auth, mod_auth_digest, mod_deflate, mod_env, mod_headers, mod_setenvif, mod_ssl, mod_mime, mod_imap, mod_alias and mod_rewrite.

Compilation options:

Compilation can be customized by passing several parameters to gcc at runtime, for my Pentium-IV/HT/3.2GHz, this is a good starting set of parameters:

export CFLAGS="-march=pentium4 -mfpmath=sse -msse2 -O2 -pipe -s -fomit-frame-pointer"

You may get a list of gcc compilation options for your CPU at gcc.gnu.org.

All these scripts were fully tested under SESE Linux 9.1 with custom-built kernel 2.6.8.1 and Fedora Core 3 with custom-built kernel 2.6.9.1, and gcc version 3.3.3 / 3.4.2, but they shall work with any Linux distro (only on Debian you may need to change rpm packages for deb ones).

This manual assumes that all source files are located (downloaded to) /usr/local/src directory, SSL keys are placed into /home/ssl directory, and web root is located at /home/www directory.

Compile from source (Open) SSL:

• We will need OpenSSL library: openssl-0.9.7e.tar.gz

Compilation of OpenSSL:

su
cd /usr/local/src
tar -zxvf openssl-0.9.7e.tar.gz
cd openssl-0.9.7e
./config --prefix=/usr/local
make
make install

Create a private key and place it into directory /home/ssl:

mkdir /home/ssl
cd /home/ssl
/usr/local/bin/openssl genrsa -des3 -rand \
some_big_file_1:some_big_file_2 \
-out localhost.key 1024

Next, we will create a private key without a pass-phrase, this is less secure, but it allows us to bootup the server without manually entering the pass-phrase every time…

/usr/local/bin/openssl rsa \
-in localhost.key \
-out localhost.key.unsecure

We will also create a request file that will be emailed to proper certification authority for getting a trusted SSL certificate (if needed) under file localhost.key.csr:

/usr/local/bin/openssl req -new \
-key localhost.key \
-out localhost.key.csr

While waiting for the certification authority, we can create a temporary self-signed certificate, good for 30 days:

/usr/local/bin/openssl x509 -req \
-days 30 \
-in localhost.key.csr \
-signkey localhost.key \
-out localhost.cert
chmod 400 localhost.cert
chmod 400 localhost.key
chmod 400 localhost.key.unsecure

Compile MySQL 4.1.10 database from source:

• MySQL 4.1.10 source codes - mysql-4.1.10.tar.gz
• libtermcap library (most distros have it already) - libtermcap-2.0.8-35.i386.rpm
• libtermcap-devel library (most distros have it already) - libtermcap-devel-2.0.8-35.i386.rpm

MySQL 4.1.10 has a completely different communication protocol and associated PHP mysqli functions. If your scripts were not designed for MySQL 4.1, you shall rather get MySQL release 4.0.23, to stay 100% compatible! Compilation options for MYSQL 4.0.23 will be the same, just remove one line with mysqli from PHP ./configure script.

However for any new development, MySQL 4.1.10 is recommended.

Compiling MySQL from source, and creating user / group called mysql:

cd /usr/local/src
tar -zxvf mysql-4.1.10.tar.gz
cd mysql-4.1.10
./configure \
--prefix=/usr/local/mysql \
--with-unix-sock-path=/tmp/mysql.sock \
--with-charset=utf8
make
make install

groupadd mysql
useradd -g mysql mysql
cp support-files/my-medium.cnf /etc/my.cnf
cd /usr/local/mysql
bin/mysql_install_db --user=mysql
chown -R root  .
chown -R mysql var
chgrp -R mysql .

MySQL configuration file /etc/my.cnf can (for our local testing) look like this:

[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 16K
max_allowed_packet = 1M
table_cache = 4
sort_buffer_size = 64K
net_buffer_length = 2K
thread_stack = 64K
skip-networking
server-id = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 8M
sort_buffer_size = 8M
[myisamchk]
key_buffer = 8M
sort_buffer_size = 8M
[mysqlhotcopy]
interactive-timeout

Compile from source Apache 2.0.53 web server:

Quite a few web-hosting companies still use Apache 1.3.x, but time of Apache 2.0 incompatibilities and problems is long gone, so 2.0 is a better choice now.

• We will need Apache 2.0.53 source codes - httpd-2.0.53.tar.gz
• RPM: libxml2 library (most distros have it already) - libxml2-devel-2.6.7-28.i586.rpm
• RPM: zlib library (most distros have it already) - zlib-devel-1.2.1-70.i586.rpm
• RPM: readline-devel library (most distros have it already) - readline-devel-4.3-306.i586.rpm

And compile it:

cd /usr/local/src
tar -zxvf httpd-2.0.53.tar.gz
cd httpd-2.0.53
./configure \
 --prefix=/usr/local/apache2 \
 --enable-so \
 --enable-auth-digest \
 --enable-rewrite \
 --enable-setenvif \
 --enable-mime \
 --enable-deflate \
 --enable-ssl \
 --with-ssl=/usr/local \
 --enable-headers
make
make install

Next we have to modify (alter) main Apache config file located at /usr/local/apache2/conf/httpd.conf (this also assumes your web root is located at /home/www):

DocumentRoot "/home/www"

And we well add support for PHP 5 (as a module):

LoadModule php5_module modules/libphp5.so
DirectoryIndex index.html index.htm index.php
AddType application/x-httpd-php .php
AddType application/x-httpd-php-source .phps

We also have to allow / create basic mod_rewrite rules:

Options Indexes Includes FollowSymLinks MultiViews
AllowOverride All
Order allow,deny
Allow from all

And dissalow clients to access .htaccess:

 Order allow,deny
 Deny from all

Next, if using SSL (on standard port 443), we will have to modify file /usr/local/apache2/conf/ssl.conf as follows (just replace the file content with this):

Listen 443

ServerName localhost
SSLEngine on
SSLCertificateFile /home/ssl/localhost.cert
SSLCertificateKeyFile /home/ssl/localhost.key.unsecure

Compile from source PHP 5.0.3:

For compiling PHP, we will need quite a few external libraries, like libcurl, libiconv, libjpeg, libpng, and few others, which we have to download and compile first:

• PHP 5.0.3 itself - php-5.0.3.tar.bz2
• CURL library - curl-7.12.1.tar.gz
• libiconv library - libiconv-1.9.2.tar.gz
• JPEG library: jpegsrc.v6b.tar.gz
• PNG library: libpng-1.2.8.tar.gz
• cpdflib library: clibpdf202r1.tar.gz
• Freetype 2 library: freetype-2.1.9.tar.bz2

Compile libiconv from source:

cd /usr/local/src
tar -zxvf libiconv-1.9.2.tar.gz
cd libiconv-1.9.2
./configure --prefix=/usr/local
make
make install

Compile libjpeg from source:

cd /usr/local/src
tar -zxvf jpegsrc.v6b.tar.gz
cd jpeg-6b
./configure --prefix=/usr/local
make
make install
make install-lib

Compile libpng from source:

cd /usr/local/src
tar -zxvf libpng-1.2.8.tar.gz
cd libpng-1.2.8
cp scripts/makefile.linux makefile
make
make install

Compile cpdflib from source:

cd /usr/local/src
tar -zxvf clibpdf202r1.tar.gz
cd ClibPDF/source
cp Makefile.Linux makefile
make
make install

Compile curl from source:

cd /usr/local/src
tar -zxvf curl-7.12.1.tar.gz
cd curl-7.12.1
./configure --prefix=/usr/local
make
make install

Compile freetype 2 from source:

cd /usr/local/src
tar -jxvf freetype-2.1.9.tar.bz2
cd freetype-2.1.9
./configure --prefix=/usr/local
make
make install

Next, we will compile PHP, with support for MySQL, iconv, curl, zlib, gd2, mbstring, SSL and many other modules:

cd /usr/local/src
tar -jxvf php-5.0.3.tar.bz2
cd php-5.0.3
./configure \
--with-apxs2=/usr/local/apache2/bin/apxs \
--with-mysql=/usr/local/mysql  \
--with-mysqli=/usr/local/mysql/bin/mysql_config  \
--with-mysql-sock=/tmp/mysql.sock \
--with-sqlite \
--enable-sqlite-utf8 \
--with-zlib \
--with-zlib-dir \
--with-bz2 \
--with-gd \
--enable-gd \
--enable-gd-native-ttf \
--with-jpeg-dir=/usr/local \
--with-png-dir=/usr/local \
--with-ttf \
--with-freetype-dir=/usr/local \
--with-iconv=/usr/local \
--with-curl=/usr/local \
--enable-track-vars \
--with-gettext \
--with-config-file-path=/usr/local/apache2/conf \
--enable-trans-id \
--enable-ftp \
--with-cpdflib=/usr/local \
--enable-mbstring \
--with-openssl=/usr/local
make
make install
cp php.ini-dist /usr/local/apache2/conf/php.ini

Next, we have to modify PHP configuration in file /usr/local/apache2/conf/php.ini, including basic PHP security settings:

mysql.default_socket = /tmp/mysql.sock
short_open_tag = Off
register_globals = Off
allow_url_fopen = Off

How to start Apache and MySQL at bootup?

The last thing left is to create a startup script, whitch will allow to run Apache and MySQL at bootup, automatically, so that we don’t have to do it manually. We will create a new file (for SuSE Linux 9.1, other ditros may vary here) /etc/init.d/web and set “executable” flag to it.

#! /bin/sh
#
# /etc/init.d/web
#
# (c) Radek HULAN
# http://hulan.info/
#
### BEGIN INIT INFO
# Provides:       apache-mysql
# Default-Start:  5
# Default-Stop:   5
# Description:    Starts Apache2 and MySQL 4
### END INIT INFO

case "$1" in
      start)
      /usr/local/apache2/bin/apachectl start
      /usr/local/mysql/share/mysql/mysql.server start
              ;;
      stop)
      /usr/local/apache2/bin/apachectl stop
      /usr/local/mysql/share/mysql/mysql.server stop
              ;;
      restart)
      /usr/local/apache2/bin/apachectl restart
      /usr/local/mysql/share/mysql/mysql.server restart
              ;;
esac

Next we will run YaST, section “System”, sub-section “Run level editor”, where we will enable service web for runlevel 3 and 5.

Testing the system?

First, start Apache and MySQL servers by entering:

/etc/init.d/web start

Next, create file /home/www/index.php with the following content:

In your browser, type URL http://localhost/ and https://localhost/, and if everything is installed fine, you will see a lot of information about your new Apache/PHP/MySQL installation.

phpMyAdmin:

We will also need phpMyAdmin to manage MySQL database, by entering http://localhost/db/ into our browser:

• phpMyAdmin 2.6.1 - phpMyAdmin-2.6.1.tar.bz2

Installation of phpMyAdmin into /home/www/db:

mkdir /home/www
cd /home/www
tar -jxvf /usr/local/src/phpMyAdmin-2.6.1.tar.bz2
ln -s phpMyAdmin-2.6.1 db

Next, we will configure phpMyAdmin’s advanced feaures, by modifying file /home/www/db/config.inc.php:

// URL to phpMyAdmin
$cfg['PmaAbsoluteUri'] = 'http://localhost/db/';

//connection settings
$cfg['Servers'][$i]['connect_type'] = 'socket';
$cfg['Servers'][$i]['extension'] = 'mysqli';

// user na password
$cfg['Servers'][$i]['auth_type'] = 'config';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = '';

// PMA settings
$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
$cfg['Servers'][$i]['bookmarktable'] = 'pma_bookmark';
$cfg['Servers'][$i]['relation'] = 'pma_relation';
$cfg['Servers'][$i]['table_info'] = 'pma_table_info';
$cfg['Servers'][$i]['table_coords'] = 'pma_table_coords';
$cfg['Servers'][$i]['pdf_pages'] = 'pma_pdf_pages';
$cfg['Servers'][$i]['column_info'] = 'pma_column_info';
$cfg['Servers'][$i]['history'] = 'pma_history';
$cfg['Servers'][$i]['verbose_check'] = FALSE;

// persistent connections
$cfg['PersistentConnections'] = TRUE;

// do not display logo on the left
$cfg['LeftDisplayLogo'] = FALSE;

// show MySQL and PHP info
$cfg['ShowMysqlInfo'] = TRUE;
$cfg['ShowMysqlVars'] = TRUE;
$cfg['ShowPhpInfo'] = TRUE;

// show BLOBs
$cfg['ShowBlob'] = TRUE;

After everything is installed, use phpMyAdmin SQL window to run script /home/www/db/scripts/create_tables_mysql_4_1_2+.sql to create PMA tables, needed by phpMyAdmin.

Debugging PHP:

There are several tools, like PHPeclipse, which allow to debug PHP, in a full-featured IDE. In order to use PHPeclipse, you need to install PHP debugger first.

• Get Nusphere DBG: dbg-2.11.32-src.tar.gz.

Installation:

cd /usr/local/src
tar -zxvf dbg-2.11.32-src.tar.gz
cd dbg-2.11.32
./deferphpize
mkdir /usr/local/modules
cp modules/dbg.so /usr/local/modules
cp modules/dbg.la /usr/local/modules

Next, you will have to modify PHP configuration file located at /usr/local/apache2/conf/php.ini, add here:

; load debugger
extension_dir = "/usr/local/modules"
extension=dbg.so

; debugger configuration
[debugger]
debugger.enabled = true
debugger.profiler_enabled = true
debugger.JIT_host = localhost
debugger.JIT_port = 10001
debugger.JIT_enabled = on

; implicint flush - use only when debugging
implicit_flush = On

Do you need mod_perl as well?

• Get mod_perl-2.0-current.tar.gz.

Installation and compilation:

cd /usr/local/src
tar zxvf mod_perl-2.0-current.tar.gz
cd mod_perl-1.99_16
perl Makefile.PL MP_APXS=/usr/local/apache2/bin/apxs
make
make install

Next, you have to modify Apache configuration file located at /usr/local/apache2/conf/httpd.conf to load mod_perl, and set to use perl at directory /home/www/perl:

LoadModule perl_module modules/mod_perl.so
PerlModule Apache2
Alias /perl/ /home/www/perl/

  SetHandler perl-script
  PerlResponseHandler ModPerl::Registry
  PerlOptions +ParseHeaders
  Options +ExecCGI

Testing? Create file /home/www/perl/test.pl, issue chmod 755 test.pl on it, and type http://localhost/perl/test.pl in your browser to test your mod_perl installation.

#!/usr/bin/perl
print "Content-type: text/plain\n\n";
print "mod_perl 2.0 rocks!\n";

Using Navicat SSH Tunneling for secure MySQL Server management

Many of the web hosting companies has blocked port 3306 to prevent access from outside, to defend against from security threats. As a consequence, users are required to use web-based client to access their MySQL Server provided by the hosting companies. These web-based clients are usually not visually appealing with annoying page refreshes.

However, with the increasing demand from users to connect from remote MySQL clients, some web hosting companies provides SSH Connection which allows user to login remotely across the Internet and support connection through the software based clients.

SSH stands for Secure Shell and is a communication protocol for connecting to remote computers over TCP/IP. Encryption provides confidentiality and data integrity, and SSH uses public-key cryptography to authenticate the remote computer and to allow the remote computer to authenticate the user if necessary.

There are several benefits to using SSH:

• Connect to a MySQL server from behind a firewall when the MySQL server port is blocked.
• Automate the authentication of users, no passwords sent in plain text to prevent the stealing of passwords.
• Offers Multiple strong authentication methods that prevent such security threats as spoofing identity.
• Offers Encryption and compression of data for security and speed.
• Secure the file transfer.

Setting up a SSH Connection to your MySQL Server with Navicat

To successfully establish a SSH connection, set the SSH connection properties in the corresponding boxes: Host name/IP address, Port number, User name, Authentication Method and Password.

1. Click or choose File -> New Connection to set up the Connection Properties.
2. Select the SSH tab and enable Use SSH Tunnel.
3. Fill in the required information in the SSH Tab:

Host name/IP address
A host where SSH server is activated.

Port
A port where SSH server is activated, by default it is 22.

User name
A user on Linux machine. (It is a Linux user. It is not the user of MySQL Server.)

Authentication Method
Choose Password Authentication

Password
The password of your user account in linux

4. In the General Settings Tab, the settings should be set relatively to the SSH server. For example, host_of_mysqldatabase shown below is the host address, which is provided by your hosting company of your remote MySQL database.

Connection Name
A friendly name to best describe your connection.

Host name/IP
address The host where MySQL Server is located in point of view SSH server. If SSH and MySQL Server are on the same machine, it is equal to SSH Host, or may be 'localhost'.

Port
The port of MySQL Server on Remote Host, by default it is 3306.

User name
The username of your MySQL Server.

Password
The password of your MySQL user.

By clicking OK, the SSH connection is made.

Hosting Companies providing SSH Connections
Listed below are some hosting companies which provide SSH connections and remote MySQL Connection can be setup with Navicat.

1&1 1and1 Hosting 1Host Web Hosting 3FN.net A2 Hosting Advanced Network Hosts AN Host AN Hosting Aplus.net Apollo Hosting Argon Hosting Blue Host Bounceweb Cirtexhosting Crucial Paradigm Dotservant.com Dreamhost eChristian Web Hosting FastDomain Flux Services	Hagen Hosting HostGator.com HostHead Hostican Hostland HostMonster.com Hostpapa HostRocket.com Imhosted InMotion Hosting Intermedia iTeraWeb Solutions IX Web Hosting Joyent Liquid Web Lunarpages.com ME Webhost Media Temple Naked Hosting Netfirms Net-Trend Omnis Hosting PowWeb	Practical Webhost Server Pronto ServerPro Web Hosting SimpleHelix Start Logic Superbhosting.net Tbhost.com techhosting.com The Host Group ThinkHost UK2NET Velcom.com Verio Vision Web Hosting Web Hosting Buzz Web Hosting Pad WebWizards.net WestHost WireNine YourServing Your-Site.com

In the following section, we have selected one of a popular hosting companies and show how a SSH Connection to the MySQL Database can be established with Navicat.

Tutorial on how to establish SSH Connection to a MySQL database hosted in Dreamhost

DreamHost provides shell access to all of its customers, but it needs to be specifically enabled for each user added to a customer's account. By default, the Type of the user is set to be “FTP”, you will need to enable the shell access before you can connect through SSH.

Enabling the Shell access for an existing user:

1) In your Control Panel, Visit User > Manage Users.
2) Click the [ edit ] link located next to the user you'd like to enable shell access for.



3) Check the 'Enable ssh/telnet?' box for that user.
4) Leave the 'Type' as it is unless you need it to be something other than bash.
5) Click Save

Keep in mind that it takes about 20 minutes for this change to take effect.

After changing the user account to Shell Type, you’ll also need to grant rights for your local machine to connect to the database server, before you can connect remotely by Navicat. This must be done for all MySQL user accounts you plan on logging in from your local machine.

Granting rights for your SSH Host:

1) Login to your Dreamhost Control Panel
2) Select the Manage MySQL link under the Goodies section.
3) Under the Database(s) on this server section, find the desired database and click the username you wish to grant access to.
4) Under the section titled Allowable Hosts, type in the domain or subdomain or your IP Address the machine which your SSH Host identifies itself as on the public internet.
5) Click Save

You have completed the settings required in your Dreamhost account, now you will go to Navicat to create the Connection.

In Navicat, click the Connection button to New a Connection.



In the General Tab of the Connecting Setting box,

Hostname/IP address: the hostname of your mysql server at dreamhost, you can check this at Control Panel > Goodies > Manage MySQL
Port: 3306 (this is the default port number for the MySQL Server)
Username: username with access right granted in Manage MySQL section
Password: password



In the SSH Tab of the Connecting Setting box,

Hostname / IP Address: machine_name.dreamhost.com
Port: 22 (this is the port for SSH connection)
Username: username which you have changed the account to Shell type in Dreamhost
Password: password



To test if the Connection settings are correct, you may click the “Test Connection” button to check.

Connecting to your MySQL Database from remote client Navicat offers you a greater flexibility on working with your data in MySQL. With Navicat, you can easily import your data in to your MySQL database from various file formats, build queries visually, set a schedule to perform backup, generate report from your raw data and more.

For more information on how Navicat helps you to manage your MySQL database effectively, please visit here: http://mysql.navicat.com/details.html