Southeast Linux Fest Presentation on MySQL Replication

Posted on June 13th, 2010 in General,Linux System Administration,LUG,MySQL by Brandon

I was fortunate to be selected to give a presentation at the 2010 Southeast Linux Fest held this year in Greenville, SC. The topic was MySQL replication which I picked from a similar presentation I gave about about 1.5 years ago at my local LUG. I’ve configured plenty of replicated servers and I think that I understand it well enough to explain it to others.

The 2-hour presentation is about half slides and half demo. Throughout the course of the presentation I set up a simple master-slave. Then I add a second slave. Taking it a step farther I set up the three servers to replicate in a chain, and finally I configure them to replicate in a full circle so that changes made on one are propagated to all of the others. I intentionally do things that break replication at certain points to show some of the limitations and configurable features that can help it to work.

Slides for the presentation are available OpenOffice format.

The presentation was recorded, so hopefully the SELF team will have those videos available shortly.

Skipping the DROP TABLE, CREATE TABLE statements in a large mysqldump file.

Posted on April 28th, 2010 in General,Linux System Administration,MySQL,Programming by Brandon

I have a large table of test data that I’m copying into some development environments. I exported the table with a mysqldump which has a DROP TABLE and CREATE TABLE statements at the top

DROP TABLE IF EXISTS `mytable`;
CREATE TABLE `mytable` (
  `somecol` varchar(10) NOT NULL default '',
   ... other columns ...
  PRIMARY KEY  (`somecol`),
  KEY `isbn10` (`somecol`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The problem is that the developer has altered the table and re-importing the test data would undo those changes. Editing the text file is impractical because of its size (500 MB gzipped). So I came up with this workaround which just slightly alters the SQL using sed so that it doesn’t try to drop or recreate the table. It comments out the DROP TABLE line, and creates the new table in the test database instead of the real database.

zcat bigfile.sql.gz |sed "s/DROP/-- DROP/"|sed "s/CREATE TABLE /CREATE TABLE test./"|mysql databasename

PHP Wrapper Class for a Read-only database

Posted on January 4th, 2010 in General,Linux System Administration,MySQL,PHP,Programming by Brandon

This is a pretty special case of a database wrapper class where I wanted to discard any updates to the database, but want SELECT queries to run against an alternative read-only database. In this instance, I have a planned outage of a primary database server, but would like the public-facing websites and web services to remain as accessible as possible.

I wrote this quick database wrapper class that will pass all SELECT queries on to a local replica of the database, and silently discard any updates. On this site almost all of the functionality still works, but it obviously isn’t saving and new information while the primary database is unavailable.

Here is my class. This is intended as a wrapper to an ADOdb class, but it is generic enough that I think it would work for many other database abstraction functions as well.

class db_unavailable {
    var $readonly_db;

    function __construct($readonly_db)
    {
        $this->query_db = $readonly_db;
    }

    function query($sql)
    {
        $args = func_get_args();
        if (preg_match("#(INSERT INTO|REPLACE INTO|UPDATE|DELETE)#i", $args[0])) {
            // echo "Unable to do insert/replace/update/delete query: $sql\n";
            return true;
        } else {
            return call_user_func_array(array($this->readonly_db, 'query'), $args);
        }
    }

    function __call($function, $args)
    {
        return call_user_func_array(array($this->readonly_db, $function), $args);
    }
}

I simply create my $query_db object that points to the read-only database. Then create my main $db object as a new db_unavailable() object. Any select queries against $db will behave as they normally do, and data-modifying queries will be silently discarded.

LUG Presentation on SQL Basics

Posted on December 17th, 2009 in General,LUG,MySQL,PHP,Programming by Brandon

I gave a presentation tonight at my local Linux Users Group meeting on SQL Basics. I had a fun time preparing the presentation and made up a bunch of examples having to do with Santa’s database.

It started out with a simple table for kids who were either naughty or nice. We then added some reports to that. Then imported kids’ wish lists from CSV files. From there we were able to generate some manufacturing reports for the workshop.

When we joined the wish list table with the kids table, we were then able to generate a sleigh-loading report which included only gifts for kids who had been good. Then we got even more complicated and introduced several joins with some complicated mathematics to select gifts for kids within a certain radius from a given zip code.

The presentation is available for download here. And Brian recorded part of the presentation which is available to view on uStream.tv or here. (We’re still experimenting with getting the video recording set up correctly)

Setting Up Virtualmin on an OpenVZ Guest

Posted on September 7th, 2009 in General,Linux System Administration,MySQL,Security by Brandon

I’m experimenting with a hosting control panel and am interested in Virtualmin. I generally avoid web-based control panels, because they generally make direct configuration via the command line and manually editing config files very difficult. However one of Virtualmin’s goals is to not interfere with such manual configurations. I’ve had plenty of clients who use Webmin, and they seem to do a good job, so Virtualmin seems like a good choice.

These are the steps that I went through to get a new OpenVZ guest set up with the GPL version of Virtualmin.

Download a CentOS 5 OS template and create the guest

# wget http://download.openvz.org/template/precreated/centos-5-x86_64.tar.gz
# vzctl create <VEID> --ostemplate centos-5-x86_64

I replaced all of these limits in /etc/vz/<VEID>.conf. This is based off of a different running machine with some fairly generous limits. Most importantly, it includes 1GB of RAM.

# UBC parameters (in form of barrier:limit)
KMEMSIZE="43118100:44370492"
LOCKEDPAGES="256:256"
PRIVVMPAGES="262144:262144"
SHMPAGES="21504:21504"
NUMPROC="2000:2000"
PHYSPAGES="0:9223372036854775807"
VMGUARPAGES="65536:65536"
OOMGUARPAGES="26112:9223372036854775807"
NUMTCPSOCK="360:360"
NUMFLOCK="380:420"
NUMPTY="16:16"
NUMSIGINFO="256:256"
TCPSNDBUF="10321920:16220160"
TCPRCVBUF="1720320:2703360"
OTHERSOCKBUF="4504320:16777216"
DGRAMRCVBUF="262144:262144"
NUMOTHERSOCK="5000:5000"
DCACHESIZE="3409920:3624960"
NUMFILE="18624:18624"
AVNUMPROC="180:180"
NUMIPTENT="128:128"

Then set up some host-specific parameters and start it up.

# vzctl set <VEID> --ipadd 10.0.0.1 --hostname yourhostname.com --nameserver 4.2.2.1 --diskspace 4G --save
# vzctl start <VEID>
# vzctl enter <VEID>

You are now logged in to the guest, where you can download and install virtualmin

# yum update
# cd /root
# wget http://software.virtualmin.com/gpl/scripts/install.sh
# sh install.sh
 Continue? (y/n) y

That should install without significant errors. Finally, set a password for root, and then log in to Virtualmin to go through the post-installation configuration

passwd root

Login at https://<your-ip>:10000/ and go through the post-installation configuration

KnitMeter is now a Facebook App

Posted on May 29th, 2009 in MySQL,PHP,Programming,Websites by Brandon

KnitMeter.com is a site that I wrote quickly for my wife to keep track of how much she has knit. It generate a little ‘widget’ image that can be placed on blogs, forums, etc and says how many miles of yarn you have knit in some period. The site has been live for about a year and a half now and has a couple thousand registered users.

I have been receiving an increasing number of requests to add a method for adding a KnitMeter it to Facebook. I’ve experimented with a couple of other ideas on Facebook and found that it was pretty straightforward to write an app. KnitMeter seems like a decent candidate for a social app, so I started working on it about a week ago. And I’m happy to say that I just made the application live late last night. It is available at http://apps.facebook.com/knitmeter/.

Features include:

  • Ability to add projects and add knitted lengths to a project (or not)
  • Settings for inputting lengths in feet, yards, or meters
  • Display how much you’ve knit in feet, yards, meters, kilometers, or miles
  • When entering a new length, you can choose to have it publish a ‘story’ on your profile page
  • You can add a tab on your profile page that shows each of your projects as well as a total
  • You can add a KnitMeter ‘box’ to the side of your profile page, or on your ‘boxes’ tab.

I recreated the database from scratch and defined it a little better, so I have a little bit of work to do in migrating the existing site and database over to the new structure. Once that is done users will be able to import their data from the existing KnitMeter.com by providing their email/password.

Synchronize Remote Memcached Clusters with memcache_sync

Posted on May 12th, 2009 in General,Linux System Administration,MySQL,Programming by Brandon

The problem: Servers in two separate geographic locations each have their own memcached cluster. However, there doesn’t currently exist (that I know of) a good way to copy data from one cluster to the other cluster.

One possible solution is to configure the application to perform all write operations in both places. However, each operation requires a round-trip response. If the servers are separated by 50ms or more, doing several write operations causes a noticable delay.

The solution that I’ve come up with is a perl program that I’m calling memcache_sync. It acts a bit like a proxy that asynchronously performs write operations on a remote cluster. Each geographic location runs an instance of memcache_sync that emulates a memcached server. You configure your application to write to the local memcache cluster, and also to the memcache_sync instance. memcache_sync queues the request and immediately returns a SUCCESS message so that your application can continue doing its thing. A separate thread then writes those queued operations to the remote cluster.

The result is two memcache clusters that are synchronized in near-real time, without any noticable delay in the application.

I’ve implemented ‘set’ and ‘delete’ operations thus far, since that is all that my application uses. I’ve just started using this on a production environment and am watching to see how it holds up. So far, it is behaving well.

The script is available here. I’m interested to see how much need there is for such a program. I’d be happy to have input from others and in developing this into a more robust solution that works outside of my somewhat limited environment.

MySQL [ERROR] Invalid (old?) table or database name

Posted on April 25th, 2009 in General,Linux System Administration,MySQL by Brandon

After upgrading to MySQL 5, one of my databases appeared empty even though the MyISAM tables existed in /var/lib/mysql/database-name. I found this error in the mysql log file in /var/log/mysqld.log:

090425 23:10:52 [ERROR] Invalid (old?) table or database name 'database-name'

Turns out that MySQL 5 doesn’t like database names that contain a hyphen. In the mysql directory, I see that it created a second directory for the database, but with the hyphen replaced with ‘@002d’.

drwx------  2 mysql mysql     4096 Apr 25 23:10 database-name
drwx------  2 mysql mysql     4096 Apr 24 00:30 database@002dname

To resolve that I decided to replace the hyphen with an underscore. I stopped mysql, renamed /var/lib/mysql/database-name to /var/lib/mysql/database_name, and then ran this SQL to update the permissions:

UPDATE mysql.db SET Db = 'database_name' WHERE Db = 'database\-name';
FLUSH PRIVILEGES;

I could then see the tables using mysql from the command line. From there, I just updated the setting for the application to use the new name, and everything worked out find.

Announcing WebPasswd

Posted on March 15th, 2009 in General,Linux System Administration,MySQL,PHP,Programming by Brandon

Do you have users who need access to web-based applications on multiple servers? Managing those users can be a pain when dealing with normal htpasswd-based permissions. Adding or removing users means editing each htpasswd file and remembering where all of them are.

Mod_auth_mysql is a good way to centralize that user database so that you can avoid having all of the separate htpasswd files. The apache module is available from any modern Linux distribution, so installing and configuring it takes less than 5 minutes. I started using it almost 2 years ago, and over that time have made a simple web application for managing the users and granting them permission to each application.

I’ve released the program as WebPasswd for anybody else who wants to use it. Now adding users and granting them access to application can be don with just a few clicks. Granting and revoking access to an application takes just seconds and is applied immediately. Configuring a new application takes a couple clicks, and then you just copy/paste the Apache configuration into the appropriate place on your web server. Try it out with this demo.

I think this will be useful to people. I have not seen another application that does something similar. Let me know if it works for you.

CSV from the MySQL Command Line

Posted on February 18th, 2009 in General,Linux System Administration,MySQL by Brandon

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.

Next Page »