Category: General (Page 9 of 25)

CSV from the MySQL Command Line

February 18, 2009 / Brandon / 0 Comments

I frequently need to generate a quick report generated from a quick database query. I have been doing this by creating a simple PHP script that queries the database and then displays the results as a CSV. However, I just found a way to do it directly from the MySQL command line, which makes it possible to skip the steps of creating the PHP script.

Simply run the command like this:

mysql>SELECT * FROM sometable INTO OUTFILE '/tmp/output.csv'
   FIELDS TERMINATED BY ',';

That will save the results to the file specified in a simple CSV format. There are also options for enclosing fields in quotes, the line terminators, and several other options.

It is now 1234567890

February 13, 2009 / Brandon / 0 Comments

It went by without much fanfare:

[root@dev ~]# date +%s
1234567890

Good Email Obfuscator

February 9, 2009 / Brandon / 1 Comment

I usually avoid putting mailto links on websites because they are easily scraped by spammers and fed into spam lists. But sometimes it is just necessary to have one. There are ways of encoding email addresses using javascript or special HTML characters that make it more difficult for spammers to see the email addresses.

I’ve done the encoding on my own for a while, but just found this Email Obfuscator that does a good job of it, so I’ll be using that form now on.

SSH Error: trying to get more bytes 4 than in buffer 0

February 6, 2009 / Brandon / 7 Comments

I ran across this cryptic error message in an SSH log file today:

Feb  7 03:33:21 hostname sshd[19439]: error: buffer_get_ret: trying to get more bytes 4 than in buffer 0
Feb  7 03:33:21 hostname sshd[19439]: fatal: buffer_get_int: buffer error

The problem is actually a corrupt line in a users ~/.ssh/authorized_keys file. This user had copy/pasted a new key into his authorized_keys file and it had a newline after the ssh-rsa. Strangely enough, people were still able to authenticate if their key was above the corrupted line. Users whose key was listed below the corrupt line were not able to log in.

PHP 5.1 Doesn’t have timezone_identifiers_list() by default

February 5, 2009 / Brandon / 0 Comments

According to the PHP documentation for timezone_identifiers_list(), that function should be included in PHP 5.1.x. The note on DateTime installation mentions, however, that it was only experimental support, and had to be compiled specifically to support it.

The fix, then, is to recompile PHP 5.1.x with

CFLAGS=-DEXPERIMENTAL_DATE_SUPPORT=1

or to upgrade to PHP 5.2 where it is enabled by default.

My particular problem surfaced with some Drupal code that required the function.

Don’t Forget to run mysql_upgrade when Upgrading to MySQL 5

January 22, 2009 / Brandon / 0 Comments

Upgrading from MySQL 4 to MySQL 5 requires some changes to the mysql database structure. I’ve seen users bitten by this numerous times, and it is often difficult to track down because the database may work partially, but display strange errors.

On this occasion, the user saw that some tables were corrupted and couldn’t fix repair them with either REPAIR TABLE or the command line myisamchk. I saw the following errors in /var/log/mysqld.log which made it obvious:

/usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist
090122 16:07:03 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
090122 16:07:03 [ERROR] Column count of mysql.db is wrong. Expected 22, found 15. Created with MySQL 0, now running 50130. Please use mysql_upgrade to fix this error.
090122 16:07:03 [ERROR] mysql.user has no `Event_priv` column at position 29
090122 16:07:03 [ERROR] Cannot open mysql.event
090122 16:07:03 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.

Simply run mysql_upgrade and it does a number of things required to get the MySQL server functioning again. It runs mysqlcheck, and doesn’t seem to pass the username/password arguments through, so it may be easier to create at ~/.my.cnf file with your username and password before running it:

[client]
user=root
pass=mypassword

Save Internet Audio Streams to MP3s

January 21, 2009 / Brandon / 1 Comment

I’ve got a couple of radio programs that I like to listen to. The only problem is that I rarely am able to listen to them live. I was wishing that somebody made a good DVR-like device for the radio, but after some thought figured out a way to do it on my own using internet audio streams that most radio stations now have available.

Googling for instructions on how to save Internet audio streams will return a lot of semi-workable but mostly garbage instructions. The best set of instructions I found was at Instructables.com where the basic concept is to use the command-line mplayer to save the stream as a wave file, then use lame to convert it to an MP3.

The instructables tutorial had several downfalls though. First, it is not able to stop mplayer on its own, so it uses a second cron job to kill the original mplayer command – a little to crude for my taste. Secondly, and more importantly, you have to know the exact stream URL which is not easy to identify from most internet radio websites. They tend to hide the actual stream source behind layers of javascript so that their web-based players can synchronize ads and such while listening to the streams.

I created some PHP code that automates this process and makes it pretty simple. The basic streamsave class provides functions for downloading the wave file and converting it to an MP3. I then extend that class for specific radio stations that I want to save. The extended class provides functions that run through all of the javascript garbage to get to the actual stream source.

Using those classes, this simple script now saves my stream to an MP3 file and emails me the location when it is done:

<?php
require_once dirname(__FILE__).'/ss_640wgst.class.php';

$streamsave = new ss_640wgst();

$streamsave->stream_url = $streamsave ->getStreamURL();
$streamsave->seconds = 60 * 60; // One hour

// This saves the stream to a temporary wav file
$streamsave->save_stream();

// Now encode it to an mp3
$output_file = "/tmp/some_directory/some_program_".date('Y-m-d-His').'.mp3';
$streamsave->encode_to_mp3($output_file);

// Delete the large wav file
unlink($streamsave->wavfile);

// And tell me that the file was saved
echo "File saved (if all went okay) to {$streamsave->mp3file}\n\n";
mail('[email protected]', 'Audio File Saved', "File saved to {$streamsave->mp3file}");

// It would be cool to create a podcast XML file here that contains your new file

?>

Downloads

The abstract class file: streamsave.class.php
The extended class specifically for 640 WGST in Atlanta: ss_640wgst.class.php

I’ve created the extended classes for stations that are useful for me. If there seems to be any interest, I can work on developing that a bit more to make it more generalized and work for more radio stations.

MySQLDump To a Remote Server

January 2, 2009 / Brandon / 2 Comments

I was running out of disk space on a server today. The server had a large database table that was no longer used, so I wanted to archive it and then drop the table. But the server didn’t have enough disk space to dump it out to disk before copying it off to a remote server for archiving.

The first thought was to run mysqldump dump on the destination machine, and to access the database over the network. That however, doesn’t compress or encrypt the data. Plus I would have had to create a mysql user with permission to access the database remotely.

The solution I came up with worked out well: mysqldump directly to the remote host with this command:

mysqldump <DATABASE_NAME> [mysqldump options] | gzip -c | ssh user@remotehost "cat > /path/to/some-file.sql.gz"

That pipes the mysqldump command through gzip, then to through and SSH connection. SSH on the remote side runs the ‘cat’ command to read the stdin, then redirects that to the actual file where I want it saved.

Setting Up Software Raid on a Running CentOS 5 Server

December 10, 2008 / Brandon / 2 Comments

Running a system off of one hard drive is just asking for trouble. Hard drives are one of the most likely components to fail in a system. If your system is running off of a single drive, and that drive fails, the results could be devastating. Even with a good, current backup, if a drive dies, you system will be down until you can figure out how to reload the OS and restore your data. Software raid is a good solution for ensuring that your system remains available when a drive decides to fail. I’ve personally had more success dealing with failures with software raid than I have with any hardware raid products.

There are a number of situations where you might have to migrate a running system to a raid array. Doing so is relatively risky, but certainly do-able. In my specific situation, I have a client with a new server at ServerBeach. The server came with two identical drives, but for some reason ServerBeach won’t install the OS to a software raid array (probably because they use some pre-built images). The server has the OS installed to the first drive, and the second drive is completely blank.

There are a few howto’s for getting this working, and from which I took bits and pieces to make this work for CentOS5:

First, an overview of the process:
1- Boot off your running system, Install mdadm, and copy the partition tables to the blank drive
2- Create your raid devices using just the blank drive (the raid will be running in degraded mode since the main drive is unavailable since it is still being used for the main OS)
3- Copy your working filesystem to the mirrored drives
4- Configure Grub to boot from the mirrored drive
5- Reboot onto the mirrored drive, ensure everything works.
6- Add the initial drive into the raid array to bring it online
7- Configure grub on both drives so that if one fails, the other will boot.

Falco’s guide does a very good job of walking through the whole process. I followed it, and would recommend it with just a few changes.

1- I don’t see any purpose in having a RAID1 swap partition. Make this RAID0 or just enable two independent partitions without raid.

2 – Don’t edit /etc/fstab and /etc/mtab on the live, working system. Edit those on the mirrored drive after the filesystem has been copied over. This will leave the working system functional if you need to fall back to it (and you probably will!)

3- The initrd image created by mkinitrd didn’t work for me, and I’m not sure why not at this point. Falco’s guide says to run these commands:

mv /boot/initrd-`uname -r`.img /boot/initrd-`uname -r`.img_orig
mkinitrd /boot/initrd-`uname -r`.img `uname -r`

This makes a backup of the existing initrd image, and then rebuilds a new one. I tried quite a few variations of the command, pointing it at the fstab using the software raid array, but to no avail. I had to manually extract, edit, and recreate the initrd image using the steps of #12 on this post.

I don’t have direct access to the console, but the data center relayed the console error that included this:

switchroot: mount failed: No such file or directory
Kernel panic - not syncing: Attempted to kill init

From what I can tell, inside the the initrd image, it runs the init script which tries to run the command ‘mount /sysimage’ which was failing. Without /sysimage the initrd image can’t pass control over to the real system. I was able to replace that line with ‘mount -o defaults –ro -t ext3 /dev/md1 /sysroot’, and then manually cpio/gzip the image back into place. From there I was able to boot off of the mirrored drive and continue as normal.

I have another one or two systems to do like this still, so I’m hoping to refine the process a bit and maybe figure out what when wrong with the initrd. It was educational to dig into the initrd image and figure out a bit more about how a modern linux box boots and many other things you could learn from types of radio broadcasting.

Preparing WordPress for a Large Traffic Spike

December 10, 2008 / Brandon / 1 Comment

The Hallmark Hall of Fame Movie ‘Front of the Class’ premiered this past weekend with an expected 12-15 million viewers.Â Â We have been preparing the website (ClassPerformance.com) for the event. We expected a significant number of visitors to the website in the 24-48 hours after the movie aired, so I did a number of things to ensure that the site would be able to run without incident during this critical time.

Move temporarily to a higher powered server.

The site is normally hosted on an inexpensive shared-hosting plan. I’ve run some shared-hosting servers before and don’t have much faith that they would handle any amount of significant load. They also usually don’t allow you to configure some of the Apache settings that I was planning on using below.

Serve images and other static content from an alternate location.

I set up a domain alias of ‘static.classperformance.com’ pointed to the same DocumentRoot as the main site. Then I edited the template files to serve most of the background, header, and footer images from that location. For normal usage, serving them from the same server works fine, but this allows the flexibility to move that static content to a separate server if/when it is needed.

I also copied the entire website to a second server and had it configured so that at any time I could change DNS to point ‘static.classperformance.com’ to the second server in order to reduce the bandwidth from the primary server

Generate static pages wherever possible.

I used wget to download everything, and then deleted the pages that needed to be parsed through PHP (ie: contact forms, etc). Most of the pages don’t change from visitor to visitor, so this can be done for the home page, all of the blog posts, and any other pages. This significantly reduces the overhead due to database queries and just the overhead of running PHP and including multiple files.

I then added this to my Apache configuration to tell the web server to use the static content if it exists:

    ## Serve static content for files that exist
    RewriteCond /home/classperformance.com/www/rendered/%{REQUEST_URI} -f
    RewriteRule (.*) /rendered/\ [L]

    ## For requests without an extension, wget has saved those files as 'index.html'
    ## so the rewrite rule needs to reflect that:
    RewriteCond /home/classperformance.com/www/rendered/%{REQUEST_URI} -d
    RewriteRule (.*) /rendered/\/index.html [L]

I did some performance tests with ApacheBenchmark, and serving the static content had a dramatic effect on the speed, and the number concurrent users. There is probably a more elegant way to configure mod_cache do a similar thing in a more automated fashion, but this was quick and easy, and I didn’t have to worry about checking the various HTTP headers. In my opinion, this was the single most effective thing to do. By serving static content, Apache also correctly handles many of the HTTP headers that enable effective caching (E-Tags, expires, last-modified, etc).

Installed a PHP Accelerator

I’ve previously written about how easy and effective eAccelerator is to install. There are very few scenarios where this is not effective. Again, ApacheBenchmark tests easily showed a huge increase in the number of concurrent requests when eAccelerator was enabled.

Check Apache settings

On a vanilla CentOS install, Apache has the ServerLimit set to 256. By serving primarily static content, you will likely reduce the amount of memory that each Apache child requires, and have memory for more children. I did some quick math and figured that I could have around 800 children before memory became a concern. I also enabled KeepAlives with a very short (1 second) KeepAliveTimeout so that sequential requests from the same user don’t have to recreate TCP sessions.

Also, by serving static content, I found that WordPress was handling the 301 redirect from the Non-www version of the site to the correct url. I moved that into Apache with this directive:

   ## Rewrite to the desired domain name
    RewriteCond %{HTTP_HOST} !^www\.classperformance\.com [NC] OR
    RewriteCond %{HTTP_HOST} !^static\.classperformance\.com [NC]
    RewriteRule ^/(.*) https://www.classperformance.com/\ [L,R=301]

Enable server-side compression

The default Apache install doesn’t compress any content. I configured mod_deflate to compress the static content and thus reduce the bandwidth usage. Compression should easily reduce the bandwidth for HTML and CSS files by one half (even up to one tenth). This not only reduces your bandwidth bill, but since the 100Mbps switch port is potentially a bottleneck, it enables more concurrent users if it approaches anywhere near that limit (and it may have if I hadn’t enabled compression)

Set up some Monitoring

I installed MRTG with some basic graphs. Also, I configured Apache so that I could view the ServerStatus. I also installed iftop to get a real-time view of the bandwidth usage.

With all of these changes, I’m very happy that we had tens of thousands of visitors during and shortly after the show, and everything ran perfectly. I had the static content running on a separate server for the busiest time and combined bandwidth usage peaked at around 90 Mbps shortly after the end of the show.