FREE THOUGHT · FREE SOFTWARE · FREE WORLD

MySQL Performance Tuning Scripts and Know-How

mysql-tuning-primerUnless you are a total linux-freak-guru like myself, and even if you are, it can be enormously challenging and somewhat overwhelming to locate and eliminate MySQL bottlenecks. While many DBAs focus on improving the performance of the queries themselves, this post will focus on the highest-impact items: MySQL Server Performance and OS Performance for MySQL. Specifically showing 4 awesome tools to auto-tune your MySQL and a few more tools just for monitoring!

MySQL

No Budget Developer Wishlist

Night Sky
  • I need no software, I lack for nothing when it comes to software. winners don't do warez
  • Well obviously if I could have anything I would want access to NSA computing power, googles server farms as a backup, and the worlds most expensive computer monitor the size of my wall with virtual screens as big as I want and touch screen and 3d and holograms.
  • I would want the best office chair made for comfort.
  • Developer Wishlist …

Web Design

Racing the Windows XP ACL demon

Both of these pcs were behind a hardware/software firewall, and so each had their own host names, we'll say PEPSI and COKE, and they were both on a private network on the same subnet, so each belonged to a domain called MARKETING.

Hacking

View all MySQL Variables for Pasting into my.cnf

mysql-variables-my-cnf-fileThis is really useful for me because I work with dozens of different database servers. The first thing I do is run this command and paste it into the servers /etc/my.cnf file. That way I will always know the original value and it just makes life much easier.

$ mysql -NBe 'SHOW VARIABLES' |sed 's,\t,^=,'|column -ts^|tr "\n" '@'|eval $(echo "sed '" "s,@\("{a..z}"\),\n\n\1,;" "'")|tr '@' "\n"|sed 's,^,# ,g'

MySQL

Password Protection Plugin Status

Enumerating Permissions can be Annoying

Don’t ask me how because I won’t tell you, but on one of the hosts I was testing on that did not allow direct access I was able to get the Apache server running as dhapache to erroneously write a file into my users blog directory. This is a big security no-no and I now have my .htaccess file written into the blog directory where it should go, but instead of my php script’s user having write access to the file so I can modify it, its owned by dhapache! Because the file is owned by dhapache I shouldn’t even be allowed to know it exists, but there it is. So the next step was to try and take ownership of the .htaccess file so that I could modify it. I tried and tried but was unsuccessful, I couldn’t modify it so that was another dead end. Actually it took me awhile to figure out how to remove the file from my directory. Being that it was owned by dhapache I couldn’t delete or modify it using my php process or even through ftp/ssh! Sysadmins regularly run find commands that search the servers for any files owned by dhapache that should not be there as this is a big red flag that someone has found a way to manipulate dhapache which could potentially lead to modifying dhapache-owned server config files, which sometimes is all it takes to hack your website and server.. Luckily I was able to delete it by basically running the hack again to overwrite the file.

WordPress

3 Ways to Serve PDF Files using Htaccess Cookies, Headers, Rewrites

FYI, using the Mod_Rewrite Variables Cheatsheet makes this example, and all advanced .htaccess code easier to understand. This demo lets you set a cookie with 1 of 3 values, then you just request the pdf file with a normal link click and get 1 of 3 different responses. This is accomplished with a nice bit of .htaccess code.

Set PDF Viewing Mode - Make a selection, then click the view pdf button.

Inline Download Save As View PDF using selected mode »

Htaccess

DNS Round Robin Configuration using Rsync over SSH

The goal is to add the HostGator server to be an exact mirror of the s.askapache.net domain, then to add that server as a 2nd A record to my DNS zone. That way half the visitors to the size will be taking up resources and bandwidth on the HostGator server instead of mine.

Round Robin A records in DNS are intended to evenly distribute queries between each host of the same name. Using some tricks straight out of a hackers toolbox we can verify if the distribution is taking place. (It is.)

Optimization

Mirroring an Entire Site using Rsync over SSH

Sometimes there is an urgent need for creating an exact duplicate or "mirror" of a web site on a separate server. This could be needed for creating Round Robin Setups, Load-Balancing, Failovers, or for just plain vanilla backups. In the past I have used a lot of different methods to copy data from one server to another, including creating an archive of the whole directory and then using scp to send the file over, creating an archive and then encrypting it and then sending that file over using ftp, curl, etc., and my persistence at learning new ways to do things has paid off because now I use rsync to keep an exact replica of the entire directory on an external server, without having to use all the CPU and resources of other mirroring methods.

Security

Custom bash_profile for Advanced Shell Users

Looking for some advanced uses for the shell? Here is some of my best. The shell is where 70% of my work takes place, and I have at least one terminal open almost 100% of the time, for viewing tailing color-coded logs, and of course for the SSH Tunnels that I use to route various networking through, like my email. So I decided that to standardize and create a bash_profile containing the most time-saving and helpful functions that I could use on all the various hosting environments would really be some sweet sugar, so here is my constant Work-in-progress.

It works for all shells I encounter, including BackTrack, Debian, Knoppix, Arch Linux, etc. Also works for many hosting environments I use including DreamHost, HostGator, WiredTree, and pretty much any linux VPS.

I also rely on this heavily from within shell scripts I write to access all the functions and stuff in this .bash_profile, and to do that I just do like:

#!/bin/bash

source ~/.bash_profile &>/dev/nulll

pm "PM is a function to output nice messages with color"
yn "Are you enjoying the shell" && pm "Thats great!" || pm "Perhaps you're better suited for DOS"
yn "Show Calendar" && aa_calendar
yn "Show Fortune" && aa_fortune

Linux

PHP Sessions/Cookies On The Fly

Multiple Web Technologies for dynamic php session controlThis article shows how to save and modify php session data, cookies, do anything really... without using ajax or iframes or forcing the user make a request.

PHP

Securing php.ini and php.cgi with .htaccess

If you have a php.cgi or php.ini file in your /cgi-bin/ directory or other pub directory, try requesting them from your web browser. If your php.ini shows up or worse you are able to execute your php cgi, you'll need to secure it ASAP. This shows several ways to secure these files, and other interpreters like perl, fastCGI, bash, csh, etc.

Htaccess

Awk Tutorial and Introduction

While researching a unix/linux tool awk I came upon one of the most thorough and helpful tutorials I've ever seen devoted to a particular topic. It's old-school just the way I like it. I contacted the author, Bruce Barnett because I just HAD to have this article for my readers, who are predominantly running solaris/unix/bsd/linux and he kindly gave permission.

Linux

Notes from Apache HTTPD Source Code

thought I'd take a break from coding and post about how open-source is such a great tool for finding the best answers to the toughest questions,

/** is the status code informational */
#define ap_is_HTTP_INFO(x)         (((x) >= 100)&&((x) < 200))
/** is the status code OK ?*/

#define ap_is_HTTP_SUCCESS(x)      (((x) >= 200)&&((x) < 300))
/** is the status code a redirect */
#define ap_is_HTTP_REDIRECT(x)     (((x) >= 300)&&((x) < 400))

/** is the status code a error (client or server) */
#define ap_is_HTTP_ERROR(x)        (((x) >= 400)&&((x) < 600))
/** is the status code a client error  */

#define ap_is_HTTP_CLIENT_ERROR(x) (((x) >= 400)&&((x) < 500))
/** is the status code a server error  */
#define ap_is_HTTP_SERVER_ERROR(x) (((x) >= 500)&&((x) < 600))

/** is the status code a (potentially) valid response code?  */
#define ap_is_HTTP_VALID_RESPONSE(x) (((x) >= 100)&&((x) < 600))

Hacking

Questions I Ask Web Hosting Companies, Before Buying

The following is a transcript of a chat I had with a company called tektonic, and at that time I was looking for a cheap linux host to use for some redundancy/failover operations. I generally contact a new hosting company like this every few months.. I like to have options available in case of some kind of failure or network attack, so it's always a good idea to have a few ace linux servers in your back pocket.

If you've read any other articles on AskApache, you can see a certain obsession towards optimization, speed, and security -- so that is the purpose of the following questions.

Hosting

Advanced .htaccess Tricks for Securing Sites

This is all new, experimental, and very very cool. It literally uses .htaccess techniques to create several virtual "locked gates" that require a specific key to unlock, in a specific order that cannot be bypassed. It uses whitelisting .htaccess tricks to specify exactly what is allowed, instead of trying to specify everything that isn't allowed. Also, by setting specific cookies/tokens after successfully passing through a gate, we can then require the exact cookie/token from the previous gate, which stops an attacker from skipping or bypassing gates.

Htaccess