Posts Tagged: 'Mysql'

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.

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.

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.

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.

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.

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.

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.

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

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

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.

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.

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

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

If you're going to stay with the default web server that's installed on the server you should, at the very least, rebuild it to make sure you're using the most up to date version.

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

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.

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.

Configure MySQL

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

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.

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.

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.