Made of Everything You're Not

I recently had a project where there would be 2 databases being used together; one locally and the other hosted externally by the client. Using traditional PHP this wouldn’t be a problem (just pass a link to the database I want to use on a per query basis and I’m done), but, since this project was to use the Zend Framework and I’d never attempted this sort of thing before with it, I was in quite the pickle.

Doing some quick Googling turned up an article that seemed to fit the bill: Zend Framework: Connecting to 2 databases written by Wenbert Del Rosario. Unfortunately though, while it was a good read and very informative, my circumstances were a little different. The database I had to connect to was hosted on an external network that I wouldn’t have local access to. I had to go over the wide Internet to connect. Using Wenbert’s tutorial would entail a constant connection on every request whether it needed it or not and that just wouldn’t do.

Anyone who’s ever had to connect to an external database can tell you it’s far from an ideal scenario. The latency involved with this strategy is noticeable and caching is pretty much required for any sane strategy. Still, Wenbert’s article was definitely helpful for developing my strategy.

As in Wenbert’s article I started with putting the connection information into my config.ini file

//config.ini
resources.db.adapter = PDO_MYSQL
resources.db.params.host = localhost
resources.db.params.username = ****
resources.db.params.password = ****
resources.db.params.dbname = local_db 
 
externaldb.adapter = PDO_MYSQL
externaldb.params.host = example.com
externaldb.params.username = ****
externaldb.params.password = ****
externaldb.params.dbname = external_db

Then, I created a standard Zend_Db_Table class that handled the connection:

<?php
//Model/DbTable/External.php
//name made up to protect the client but do yourself a favor and name your shit logically
class Model_DbTable_External extends Model_DbTable_Abstract
{
   /**
     * Doesn't matter for use but ZF demands a $_name variable
     * @var string
     */
	protected $_name = "content";
 
	public function init()
	{
		$settings = Zend_Registry::get('settings');
		$this->db = Zend_Db::factory($settings['externaldb']['adapter'], $settings['externaldb']['params']);
 
	}
}
?>

With the above out of the way I can now connect to the external database. I just have to pipe all my SQL through the above class. To that end, and to provide a nice wrapper for the caching, I create a Model that has methods for all the SQL my app will need:

<?php
class Model_External extends Model_Abstract
{
	/**
	 * The key to use for the cache items
	 * @var string
	 */
	public $cache_key = 'external_db';
 
	public function __construct()
	{
		parent::__construct();
		$db = new Model_DbTable_External;
		$this->db = $db->db;
	}
 
	public function testSystem($id)
	{
		$key = $cache_key.__FUNCTION__.$id;
		if(!$data = $this->cache->load($key)) 
		{	
			//just a random query with joins and whatnots to show  it's possible :)
			$sql = $this->db->select()->from(array('e' => 'example_table1'), array('e.id', 'e.title'))->where('e.id = ?', $id)->limit('6');
			$data = $this->db->fetchAll($sql);
			$this->cache->save($data, $key, $this->cache_key));
		}
		return $data;
	}
}
?>

So, using the above class I’ve abstracted out the SQL and provided caching through the below class Model_Abstract:

<?php
//models/Abstract.php
abstract class Model_Abstract
{
	/**
	 * The database object
	 * @var object
	 */
	public $db;
 
	/**
	 * The Cache Object
	 * @var object
	 */
	public $cache;
 
	/**
	 * The stored cache name
	 * @var string
	 */
	public $cache_key = null;
 
	public function __construct()
	{
		$c = new Model_Cache;
		// getting a Zend_Cache_Core object
		$this->cache = Zend_Cache::factory(
	                    'Core',
	                    'File',
	                    array('lifetime' => 720, 'automatic_serialization' => true),
	                    array('cache_dir' => '/path/to/cache/')
		);		
	}
}
?>

Putting it all together I now have a mechanism to connect to an external database, perform queries against said database and can cache the results pretty easily and with minimal code. One of the advantages of this approach is that connections to the database only happen when needed instead of on every page request as in Wenbert’s article. So, for example, so long as the cached items exist then no database connection is created and no latency (outside of hitting the filesystem of course) is created.

Still though, there are ways to improve on this approach. For example, using APC instead of the file system for the caching would help as would increasing the cache lifetime from 2 hours to something more but, overall, the above should be a good starting point.

I’ve been wanting to add a search feature to wp-click-track for a while now. After almost a year of writing 150 posts I have a lot of links; so many in fact that it’s getting a little tough finding the links I want when I want them. I’ve written a few search engines in my time (some actually pretty good and some that just plain sucked) and thought this would be a simple addition to the program.

Boy, was I right.

php & MySQL Full Text SearchingText

Real quick; I’m going to be referencing a MySQL table below. Here’s the SQL to create that table:

CREATE TABLE tracking_links (
`link_id` int(10) NOT NULL auto_increment,
`link_title` varchar(255) NOT NULL default '',
`link_desc` text NOT NULL,
`link_destination` varchar(255) NOT NULL default '',
`link_total_clicks` int(10) NOT NULL default '0',
`link_unique_clicks` int(10) NOT NULL default '0',
`creation_date` datetime NOT NULL default '0000-00-00 00:00:00',
`last_modified` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY  (`link_id`)
)

The traditional, no frills, way easy path, would be to build a basic “LIKE” query and attach it to a single text field. The queries would then look like this:

SELECT * FROM tracking_links WHERE link_title LIKE '%$_search%'

If you don’t already know; the above query would match all the links with a title that has the string $_search anywhere within the column.The percent character (%) is a wild card; sandwiching $_search like it is basically says “Grab me everything that has this string anywhere within the column”.

The above is the lowest of the low when it comes to elegance of design and sheer awesomeness though. It’s pretty much the amateur version of a search mechanism.

For one thing performance is going to be an issue when dealing with a lot of rows in the table. The speed on a query like the above, even with an index on the “link_title” column, is going to crumble once  you get more than a couple hundred thousand rows because the MySQL has to do a full table scan.

Another issue is that relevancy is completely absent. There’s no way to remove elements or, really, do any boolean matching. You’re going to get results that just seem… off.

If I want to search against multiple columns the SQL just gets horrifying:

SELECT * FROM tracking_links WHERE link_title LIKE '%$_search1%' OR link_destination LIKE '%$_search2% OR...'

And then there’s the accompanying form for a query like the above. Traditionally, it would be x form fields for each column; in the case of wp-click-track that would be 3 individual fields. This would pretty much destroy the plugin interface or, worse, require a separate, dedicated, search page.

The answer to those problems is full text search. According to Wikipedia :

In a full text search, the search engine examines all of the words in every stored document as it tries to match search words supplied by the user.

This is a difficult concept to grasp, especially for someone coming from the LIKE camp; I know it took me a little bit to get it fully. Here are a couple notes that should help:

1. String to search must be longer than 3 characters long.
2. DB Type must be MyISAM
3. Don’t try for individual search fields
   ex: SELECT * FROM table WHERE col1 = ‘$col1′ AND col2 = ‘$col2′
   instead
   Have all fields setup as a single Fulltext field.
   ex: SELECT table.* , MATCH (col1, col2, col3) AGAINST (‘+( $text1) -($text2)’) AS Score FROM table WHERE MATCH (col1, col2, col3) AGAINST (‘+( $text1) -($text2)’ IN BOOLEAN MODE)
4. IN BOOLEAN MODE is required for queries with the ‘-’ attribute.
5. Algorithm for relevance is:
   w = (log(dtf)+1)/sumdtf * U/(1+0.0115*U) * log((N-nf)/nf)

Using fulltext searching allows your site users to do Google style searching like so:

+php -mysql +sqlite +temp*

The above search query breaks down to “Grab everything with “php”, without “mysql” but with “sqlite” and any word that begins with “temp”. Pretty snazzy and useful if your users are savvy in how to search effectively. Hell, even without knowing how to perform boolean searches the above is still useful and makes your code way cleaner.

In order to implement fulltext searching the table has to be setup with the MyISAM engine and a fulltext index has to be defined. To change a table to use the MyISAM engine just run the below query:

ALTER TABLE tracking_links ENGINE = MYISAM

To add a fulltext index you create it like so:

ALTER TABLE tracking_links ADD FULLTEXT search_index (link_title,link_desc,link_destination)

The complete fulltext search query for the above query (+php -mysql +sqlite +temp*) example would look like:

SELECT *
FROM tracking_links
MATCH(link_title,link_desc,link_destination) AGAINST('+php -mysql +sqlite +temp*' IN BOOLEAN MODE);

There’s one other feature worth mentioning; the Score option. The Score

SELECT *,
         MATCH(link_title,link_desc,link_destination) AGAINST('+php -mysql +sqlite +temp*' IN BOOLEAN MODE) AS SCORE
FROM tracking_links
         MATCH(link_title,link_desc,link_destination) AGAINST('+php -mysql +sqlite +temp*' IN BOOLEAN MODE);

The above query will return all the rows as well as the relevance of the column to the dataset. On initial inspection the number won’t make much sense; it’s usually something like 0.9843949302 and 2.9408509384 which is, admittedly, pretty useless by itself. But, using a simple formula you can turn it into something pretty relevant:

Using the above code against a SCORE of .9823475 results in a $score of 98.2% which is a simple way to let your users know the result is relevant.

As the above, hopefully, demonstrates FULL TEXT searching with MySQL is easy and adds an extra layer of awesome and fun to an application.

The Linux command “top” is one of the most used and powerful jewels in a developers pocket.

In most Unix-like operating systems, the top command is a system monitor tool which produces a frequently-updated list of processes. By default, the processes are ordered by percentage of CPU usage, with only the “top” CPU consumers shown. The top command shows how much processing power and memory are being used, as well as other information about the running processes. Some versions of top allow extensive customization of the display, such as choice of columns or sorting method.

The top command is useful for system administrators, as it shows which users and processes are consuming the most system resources at any given time.

MySQL Top

The great thing about “top” also highlights one of it’s weaknesses; it’s focused on CPU, memory (RAM) and time. Top is wonderful if you want to know how much performance your program is using but if you want to know how much the individual components are using you’re out of luck.

Enter mtop.

mtop (MySQL top) monitors a MySQL server showing the queries which are taking the most amount of time to complete. Features include ‘zooming’ in on a process to show the complete query, ‘explaining’ the query optimizer information for a query and ‘killing’ queries. In addition, server performance statistics, configuration information, and tuning tips are provide

mtop is a pretty useful program; it really helps in finding out the trouble spots in queries. There one obstacle to consider before diving into though; mtop is written in PERL so there are a couple module dependancies (Curses, DBI, DBD::mysql, Getopt::Long and Net::Domain

Still, I didn’t run into any issues installing the program and, so far anyway, mtop is a nice addition to my tool box.

Here’s a problem I’ve encountered too many times and I, still, keep running up against:

1
2
3

#1064 - You have an error in your SQL syntax; check the manual that corresponds 
to your MySQL server version for the right syntax to use near 
'primary , foo , bar ) VALUES ( '1', 'test', 'me' )' at line 2

This on a query that looks like so:

1

$sql = "INSERT INTO table (primary , foo , bar ) VALUES ( '1', 'test', 'me' )";

Can you see the issue (other than it’s using php)? Look closely…

MySQL

The query is syntactically sound; there’s nothing wrong with the formatting at all. But it fails with a 1064 error because of the use of ‘primary’ as a column name. Welcome to the world of MySQL reserved keywords.

Certain words such as SELECT, DELETE, or BIGINT are reserved and require special treatment for use as identifiers such as table and column names.

This may also be true for the names of built-in functions.

Reserved words are permitted as identifiers if you quote them as described in Section 8.2, “Schema Object Names”

This means that there are certain words you just can’t use for MySQL identifiers unless you escape them, in all SQL, with backticks (`). Which essentially means, to me at least, you shouldn’t use reserved keywords. As a rule, I just don’t use backticks in my code unless I have to; it’s always begrudgingly though.

As I said above, this isn’t a new issue to me (or most people for that matter) but I continually run up against it. It’s particularly insidious because the natural instinct is to evaluate the SQL looking for the missing comma or quote, which is a HUGE waste of time.

So which keywords are reserved? Thankfully, MySQL has a list. It’s a good idea to familiarize yourself with the terms on the page and just keep the knowledge in the back of your brain.

It will come up again.

I’ve been setting up my own servers for years; it’s something I’m pretty passionate about as a programmer. I’ve learned soooo much about programming from seeing how the computer operates; setting up a server is HUGELY enlightening in that respect.

Setting Up A Linux Web Server

On the other hand though, I have a lot of things to do with my day and setting up a server, something that’s pretty rote, doesn’t require my direct attention. Just someone who can really follow instructions.

With that in mind, I started to compile a list so I could delegate this activity to my team. It’s pretty useful from a historical stand point too so in case anyone else is interested in just how to setup a linux web server; here you go.

I’ve broken the setup into multiple sections, each outlining a specific type of setup, so it should be easier to digest. I’m just over the whole “series” thing, so instead of breaking this post into several smaller posts I opted for one REALLY long post. Plus, there’s the whole disconnect thing with a series and setting a Linux box up shouldn’t be done piecemeal.

It should be noted that the below is just a vanilla setup; it should be considered the bare minimum that needs to be done.

Basics

These are just a couple things that have to be done at no particular time. Ideally, the Firewall (below) would be configured first and then everything else would follow, but the rest could be done in any order. To make life easier it would be a good idea to take care of the basic stuff ASAP.

Setup locate

There’s a sweet little command that is extremely helpful in locating files on the server; locate.

From the manual page:

locate reads one or more databases prepared by updatedb(8) and writes
file names matching at least one of the PATTERNs to standard output,
one per line.

It’s way faster and efficient than ‘find’ and it’s just what you need to find all sorts of things quickly. A basic use works like so:

locate php.ini

Which outputs:

/scripts/php.ini
/usr/lib/php.ini
/usr/local/lib/php.ini

To use it though, the first thing you need to do is make a call to ‘updatedb’ which will create the ‘locate’ database. The first time it’s ran it’ll take around 5 minutes to complete so you want to get this done ASAP so it doesn’t break up your flow when working on something else.

updatedb

Setup Host Name

This part will register the server with the rest of the Internet. It’s a different way on quite a few of the Linux servers I’ve worked with so I won’t go into detail on how to do configure the server; I’m just going to go over the steps. You can always ask your host to set it up if needed.

Probably the most important part is choosing the name. There sees to be a couple different camps on this subject. Some like naming it as the role (so if it’s a web server it’d be web1 or web5 (whatever) or if it’s a db it’d be db1 or db5. Other’s feel this is a security risk because it broadcasts what the server does.

Still, others, choose names that are just arbitrary; wizardtower, methlab, etc…

Personally, I find the convenience of recognition (knowing web1 is a web server and db3 is a database server) far outweighs any security concerns so I go this route.

Whatever you choose make sure to register the name with your registrar as an A DNS entry. Once that’s done then you can update the server with the new hostname.

Email Forwarding

For every user on the system that receives email, like root for example, you’ll want to create a .forward file in that users home directory. This good so email can be sent to you personally outside of logging into the server.

echo you@domain.com > /root/.forward

Security

Just to reiterate the above, this is by no means a complete list of everything that can be done to secure a server. Consider this a list of the minimum that needs to be done; nothing more. You should still research secure based off of your particular situation.

Seriously, your needs may require additional levels of security. You’ve been warned.

Server Security

Firewall

Most modern installations of Linux come with a firewall installed called iptables which is really reliable and stable. I use iptables in conjunction with either ConfigServer’s csf/lfd or, usually if I’m not on a cPanel or Webmin server, apf.

Personally, I prefer csf/lfd for managing my firewall. Not only does it take care of iptable configuration but it also:

• Sends an email on ssh login or su usage
• Blocks connections with excessive connections
• Login failure notifications for a lot of common services (cpanel, ftp, ssh, etc)
• Port scan tracking and blocking
• Temporary and permanent IP blocking
• System Integrity checking and alerts
• Suspicious process alerts
• Suspicious file alerts

apf requires adding a few other programs on to attain the same amount of coverage; I prefer simple.

To install csf/lfd ConfigServer made the process extremely easy; they even put together step by step instructions that have yet to fail.

Setup Users

Add non privileged user and add to wheel group. This is important because we’re going to seriously limit access to the shell. I like to have just one user who can ssh into a server but who can’t do anything but use ‘su’ to up their privileges. No access to anything but ‘su’, not even ‘wget’.

Mount /tmp securely

It’s important to mount your tmp directory securely so nothing contained inside can be executed. It really helps when your site allows file uploads or if the server has been exploited (the tmp directory is a prime target).

You want to mount the partition as noexec,nosuid.

Install RKHunter

RKHunter is a tool that scans your system looking for any rootkits. It’s a good tool but it does report some false positives; nothing too annoying but it does happen.

It’s pretty simple to install and I’ve yet to see it fail on any flavor of Linux I’ve used (and I’ve used a bunch).

wget http://superb-west.dl.sourceforge.net/sourceforge/rkhunter/rkhunter-1.3.4.tar.gz
tar -zxvf rkhunter-1.3.4.tar.gz
cd rkhunter-1.3.4
./installer.sh --layout /usr/local --install

Once RKHunter is installed you’ll want to set a daily cron job so your system is checked regularly. To do that just create a shell script and place it in /etc/cron.daily/ as outlined in this tutorial for installing RKHunter

#!/bin/bash
(rkhunter -c --cronjob 2>&1 | mail -s "Daily Rkhunter Scan Report" root)

SSH

SSH is incredibly vulnerable for no bigger reason than visibility; it’s the de facto entrance point for most linux servers. I like to do a couple things to secure my SSH installation.

To make any of the below changes you’ll need to edit your sshd_config file. On most systems it’s going to be in:

/etc/ssh/sshd_config

First, I always disable root login. Most brute force (BF) attacks on your server will be for the user ‘root’ so simply disabling this allows most BF attacks will be futile for the attacker. If you’ve added a new user to the system, and that user is the only user who can ssh in, you’re in a pretty good spot. The attacker has to know the username in order to even try passwords.

Second, I also change the port ssh listens on. The default, 22, is what most attackers will try for getting into ssh. Change that to something different and you’ve added another level of complexity onto the system.

It’s important to let your host know of the change so they can access ssh when they need to. This shouldn’t be a problem for most hosts but you may have a fight on your hands for some.

There are quite a few options you can use to configure ssh for; it’s definitely recommended that you research as much about ssh as possible to configure it specifically for your needs.

Disable General Commands

This next one isn’t exactly critical but I find it useful and it definately adds peace of mind so there’s that.

I first heard about this from a forum for securing a cPanel server.

Many php exploit scritps use common *nix tools to download rootkits or backdoors. By simply chmod’ing the files so that no none-wheel or root user can use them we can eliminate many possible problems. The downside to doing this is that shell users will be inconvenienced by not being able to use the the commands below. Mod_security really removes the need to chmod this, but it is an added layer of protection.

#chmod 750 /usr/bin/rcp
#chmod 750 /usr/bin/wget
#chmod 750 /usr/bin/lynx
#chmod 750 /usr/bin/links
#chmod 750 /usr/bin/scp

As mentioned above, this is probably overkill but it does prevent anyone who does gain access from being able to do much of anything. If you really want to have that warm, fuzzy, feeling of safety you could also just chmod everything under /usr/bin like so

chmod 750 /usr/bin/*

That should really make you feel safe.

Disable Unneeded Services

Chances are that your server is going to be running quite a few services you’re just not going to need. For example there’s ‘cups’ the Linux print service. Is your webserver going to be connected to a printer? Probably not.

Leaving these enabled is bad because it’s an avoidable entry point into your server by the “bad” people. From my experience I’ve learned to disable a bunch so I put together a little shell script to just handle it for me. Copy the below and put it into a file on your server called ‘disable_services.sh’ and chmod it to 0755

#!/bin/bash
service cups stop 
chkconfig cups off
 
service xfs stop
chkconfig xfs off
 
service atd stop
chkconfig atd off
 
service nfslock stop
chkconfig nfslock off
 
service canna stop
chkconfig canna off
 
service FreeWnn stop
chkconfig FreeWnn off
 
service cups-config-daemon stop
chkconfig cups-config-daemon off
 
service iiim stop
chkconfig iiim off
 
service mDNSResponder stop
chkconfig mDNSResponder off
 
service nifd stop
chkconfig nifd off
 
service rpcidmapd stop
chkconfig rpcidmapd off
 
service bluetooth stop
chkconfig bluetooth off
 
service anacron stop
chkconfig anacron off
 
service gpm stop
chkconfig gpm off
 
service saslauthd stop
chkconfig saslauthd off
 
service avahi-daemon stop
chkconfig avahi-daemon off
 
service avahi-dnsconfd stop
chkconfig avahi-dnsconfd off
 
service hidd stop
chkconfig hidd off
 
service pcscd stop
chkconfig pcscd off
 
service sbadm stop
chkconfig sbadm off

HTTP Server

I’ve gotten a renewed appreciation for Apache lately so I’m not going to focus on one more than the other. With the exception of mod_security, everything below should be possible on pretty much all your popular webservers like Apache or Lighttpd.

Web Server

Once you’re dealing with a new(ish) installation of a web server the next thing you need to do is create the default site. This is the site people will see when they put either the IP address or the hostname of the server into a browser. I always set it up to use a blank page instead of the standard or default page. This way it doesn’t look janky when users stumble upon the server.

Next, you want to disable Indexes. This setting is useful to prevent people from hitting a directory and seeing all the contents. If a user does try to read a directory, “images” for example, they will get a 403 (Forbidden) page.

Another thing I like to do is change the cPanel and http server skeleton files to blank pages. This is nice so when another site is created the site gets setup with blank pages instead of the “advert” pages for the system.

ModSecurity is an open source intrusion detection and prevention engine for web applications. It operates embedded into the web server, acting as a powerful umbrella – shielding applications from attacks. It’s really cool.

ModSecurity works with Apache but there’s always people out there experimenting so, hopefully, other http servers should get coverage some day. If you’re using Apache you should definately, 100%, no excuses not to, install ModSecurity

PHP

For all the jokes about PHP being a sub-par programming language, accoring to the TIOBE Programming Community Index for May 2009 it’s the most popular web development language available. So suck it.

php

It is true that php’s the only language with a configuration file. I admit; that’s just fucked up man…

Improve PHP

PHP is an interpreted language so right away your scripts are going to have a performance penalty (compared to a compiled language like C# for example). To help alleviate this you should always, always, install some sort of OP code cache. My personal favorite is xCache but there’s also eAccelerator to name just one.

Installing xCache is pretty straightforward and setting it up is just as easy. Once it’s done you should notice a considerable improvement in performance.

You’ll also need to upgrade PEAR to make sure you’re using the latest versions of packages and such. It’s pretty easy; from a command prompt:

pear upgrade pear

After that you’ll want to make sure the below packages are installed and up to date. These are just what I personally use and what the majority of open source php projects I’ve seen use.

pear install HTML_QuickForm
pear install Table
pear install Cache
pear install Cache_Lite
pear install Mail
pear install Mail_Mime

Secure PHP

Pretty much all of the security stuff is done by configuring PHP in php.ini. If you don’t know where it is you can either create a phpinfo() call and look for the path to php.ini or, better, just use the ‘locate’ command from the command line:

locate php.ini

Unless you’re using an old version of php (and why the fuck would you?) it’s going to come out of the box with the WORST setting php ever introduced; ‘register_globals’. If you require this setting to be active, for new projects, you’re an idiot. I do unfortunately know about legacy apps requiring register_globals to be on but you can just set it with ‘ini_set’ on a per project basis so turn it off FOR EVERYTHING NEW YOU DO.

You’re going to want to disable quite a few functions too, if possible. There’s definate use in a lot of the below functions and sometimes the project you’re hosting is going to require some of them. For example, on php 5.2.9 popen is required for some PEAR packages (and itself I think). This should be done on a case by case basis. But if you don’t need to keep these open DON’T.

Look for the string ‘disable_functions’ in your php.ini and add any of the below to that string.

disable_functions = show_source, system, shell_exec, passthru, exec, phpinfo, popen, proc_open, allow_url_fopen

Configure MySQL

One thing really; change the god damn root password!! Set it to something, anything is better than NOTHING.

MySQL

I’ve had some people recommend disabling LOAD DATA LOCAL but while it sounds like a good idea it doesn’t really gel with the way I work. I need that enabled to import databases on the server when the file is too big to upload into phpMyAdmin. I’m sure I could just enable it, do my thing, then disable it again but that sounds… troublesome.

Run Benchmarks

Running the benchmarks on the server is probably the most important part of setting up a server. It’s important because the results of the benchmarks are going to tell you what you have to do next.

Benchmarking

There are a few different tools for benchmarking a server but the most popular is ApacheBench. Accoring to Wikipedia:

ApacheBench is a command line computer program for measuring the performance of HTTP web servers, in particular the Apache HTTP Server. It was designed to give an idea of the performance that a given Apache installation can provide. In particular, it shows how many requests per second the server is capable of serving.

ApacheBench comes standard with the Apache distribution so on most systems it’s already going to be there. There’s already a really good tutorial on NixCraft on how to use ApacheBench so I won’t go into it. Just check out the tutorial above and you’ll have a good understanding to start with.

I will say that this portion should take a good a while to do properly because you’ll be doing it a lot. You’re going to want to tweak your HTTP server configuration to get the optimal performance and every time you make a change you’re going to want to confirm the improvement.

It will get old.

Now, as I mentioned above this is by no means a definitive guide or anything. It’s just the bare minimum that should be done when you’re setting up a Linux web server.

Here’s a quick fix for a weird issue I ran into while working with MySQL on Windows.

MySQL

The first time I moved code from my Windows box to one of my Linux servers I ran into an error about a MySQL table not existing in one of my queries. I checked the table and the code to verify everything was as it should be and noticed that all my tables were lowercase instead of CamelHumped. Odd….

Here’s the SQL:

$sql = "SELECT * FROM TableName WHERE id = '".$DB->es($id)."'";

It turns out that MySQL on Windows will convert all tables created to a lowercase name. To remain consistent with Linux you should add the below to your my.cnf file:

set-variable=lower_case_table_names=0

Adding the above line will preserve the naming conventions between both platforms.

Like most web developers, I use Linux on a daily basis. Because I always have to revisit how to do some of the basics from time to time I thought I’d put together a list of some of the commands I use the most along with some of their examples. NOTE: the paths are all from RHEL 4 and CENTOS 4 & 5.

Linux

Enjoy!

MySQL

Example of mysqldump::

mysqldump -u USER -pPASSWORD DATABASE > filename.sql

Example of restore DB::

mysql -u USER -p DBNAME < dump.sql

Start the MySQL service::

/etc/init.d/mysqld start

Stop MySQL service::

/etc/init.d/mysqld stop

Add User::

GRANT ALL PRIVILEGES ON *.* TO 'USER'@'localhost' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

Export Single Table::

mysqldump --opt -u USER-pPASSWORD DataBaseName TableName > FileName

rsync

rsync is used primarily synchronize data between two directories or servers.

Sync 2 servers

rsync -p -r -a -z -v /path/to/dir 2.2.2.2:/path/to/dir

Sync 2 directories

rsync -p -r -a -z -v /path/to/dir /path/to/new/dir

Lighttpd

Start the lighttpd service::

/etc/init.d/lighttpd start

Stop the lighttpd service::

/etc/init.d/lighttpd stop

Path to conf::

/etc/lighttpd/lighttpd.conf

SSH

Restart SSH Server

/etc/rc.d/init.d/sshd restart

Start SSH Server

/etc/rc.d/init.d/sshd start

Stop SSH Server

/etc/rc.d/init.d/sshd stop

Tar/GZ

Create tarball

tar -cvf directory.tar directory/

Create gzipped tar file

tar -czvf directory.tgz directory/

Extracting tarball:

tar -xvf directory.tar

Extracing gzipped tar:

tar -xzvf directory.tgz

Misc.

Delete the mail queue

rm /var/spool/mqueue/*

Count files in directory recursively

ls -R -1 proc | wc -l