How to Build Your Own MySQL 5 Server in AppLogic

I’m building an infrastructure on a grid using AppLogic, which is a grid OS that provides both GUI and command line interfaces to build your infrastructure. I use both interfaces, depending on the task I’m trying to perform at the time.

One thing I have discovered is that there are a lot of somewhat nebulous concepts, abstractions, lingo, etc., and a number of default configuration settings that make doing really really simple things extremely unintuitive if you’re coming from a (more or less) physical server environment.

Another thing I discovered is that, while AppLogic provides a pretty nice collection of template ‘appliances’ that you can drag around the GUI and connect to each other in various ways, the MySQL appliance in particular is somewhat lacking. Specifically, it’s a MySQL 4.0 server. Lame.

There might be better ways to do all of this, and if you know of any, I welcome the input!! For now, here’s what *I* did to build my own MySQL 5 server:

  1. In the GUI, at the top of the left pane, choose ‘proto’ from the drop down menu to show the appliances in the ‘proto’ catalog.
  2. Drag the “LUX5″ appliance to the work area in the right pane. This is a very bare-bones appliance. There’s almost nothing installed on it. However, it *is* a CentOS *5* install, and the version of MySQL distributed for this version of CentOS is 5.0.x. Unfortunately, you still don’t have network access, or read-write access to /usr, or enough disk space. The /usr volume is mounted read-only, and is something like 120MB in size.
  3. Right-click the appliance and choose “Branch Class”. This is super important. If you don’t do this, you can lose any changes you make to the appliance later. Don’t proceed until you do this. Seriously.
  4. Once done, the first thing to do is right click the appliance, choose ‘Edit Class’, and click the ‘Interfaces’ tab. Create an interface called ‘net’, choose ‘Out’ for the Direction, ‘any’ for the Protocol, and click the ‘Gateway’ button in the Options column. In order to enable this appliance to reach the internet so it can do things like ‘yum install’, you need to connect that ‘net’ interface to a ‘NET’ appliance, which is another AppLogic-supplied appliance whose sole purpose is to be a gateway to the internet for your appliances. If you don’t have one of those yet, you’ll need to add one. See this doc on how to use NET: http://doc.3tera.net/AppLogic2/CatGatewayNet.html – creating this gateway interface will cause a new line to appear in the output of the ‘route’ command when you log in on the device showing your new gateway.
  5. While you’re in this tab, you can also change the protocol for the ‘in’ interface to ‘mysql’.
  6. Next, you’ll need to resize “/” and “/usr” on the system. The only way I know how to resize a volume on a specific appliance is using the AppLogic controller’s command line interface. Assuming you didn’t change the class name of your appliance from ‘LUX5′, here are the commands to use:
    # ca myapp
    # vol resize LUX5.boot size=1G
    # vol resize LUX5.usr size=1G

    Replace ‘myapp’ with the name of your application. Also note, the app will need to *not* be running when you do this (yes, this means volume resizes require an app restart. Yes this kinda degrades the benefits of virtualization. No, I’m not a fan of how this is implemented)
  7. [UPDATE] forgot to mention: by default, LUX5 has like 40MB of RAM, so when you run ‘yum install whatever’, it’ll be killed at different stages in its work by the Out of Memory (OOM) killer in Linux. Right click the appliance, go to ‘Resources’, and up the minimum and default to at least 256MB. I used 512MB.
  8. Finally, right click your appliance, go to ‘Edit Class’ again, and then go to ‘Volumes’ and unclick the ‘read only’ button in the Options column for the ‘/usr’ volume.

Once you’ve done all of that, you can finally get some real work done. Run ‘yum install mysql-server’ to get the server installed, and you’re on your way. There are still problems to solve with this setup, such as how to manage the volumes for your data directory, how to set up, for example, phpMyAdmin if you want to use that to manage your MySQL database, and how to use things like memcached in this environment. I guess I’ll blog those as I get to them. Wish me luck!

  • http://www.onefreevoice.com haaseg

    Hmmm… Provided you can just add a separate disk, I would create a new one and mount it to opt.

    Download the mySQL binaries directly from http://dev.mysql.com/downloads/ and untar them into /opt/mysql/. Then symlink /opt/mysql/mysql to /opt/mysql/mysql-5.0.51-linux-i686-icc-glibc23 (or whatever). Create directories /opt/mysql/data and /opt/mysql/logs and edit /etc/my.cnf accordingly:

    log-error=/opt/mysql/logs/slow
    log_slow_queries=/opt/mysql/logs/slow
    datadir=/opt/mysql/data
    basedir=/opt/mysql/mysql
    log-bin=/opt/mysql/logs/mysql-bin

    There are a couple of reasons I prefer the mySQL compiled binaries to the distro supplied packages:

    First of all, the distro supplied packages assume your going to place your mysql related files in certain places in the file system (e.g /var/lib/mysql). The mysql compiled binaries are meant to extract and run in place. The can literally be placed anywhere – /home//bin/mysql – where-ever you want. These days I like /opt/mysql. /usr/local/mysql is another popular location.

    Second, due to the nature of the binaries (see First point above), it’s completely possible (read: easy) to run multiple instances of mysql on the same server. This is true for multiple instances of the same version or multuple instances on different versions.

    Third, and this I think is the most important, is the availability of an instance rollback path should you have trouble with the upgrade. When you upgrade, you untar the new version of mysql into /opt/mysql/, shutdown mysql, update your symlink, and restart. Your old binaries are still there. If for some reason something should happen and your app not work correctly on the new binaries, just shut down mysql, switch your symlink back, and restart. Granted, you should have tested your upgrade, but in case you did and still got an unsuspected error, this can save you.

    Fourth, it gives you the ability to isolate routine OS updates from your database updates. On my database servers, I typically keep the boxes extremely lean, and I tend to just except what-ever updates (in my case CentOS) throws at me. But I wouldn’t want to trust my database to this. Technically, you can carefully manage this with some exclude settings in yum (or whichever), but it becomes overhead. On a database server – database upgrades should never be taken lightly – they should be carefully evaluated to see if they’re even necessary, and when deemed so, they should be carefully regression tested.

    Note: the precompiled binaries come with an init script (it’s in the support-files subdirectory). Just copy that to /etc/init.d/mysqld and if you are using RedHat yolu can use chkconfig –add mysqld to add it to your system startup.

  • http://www.protocolostomy.com m0j0

    Thanks for the feedback, haaseg.

    You know, I had been thinking about using the MySQL-distributed binaries, but not for *any* of the reasons you mention!! ;-P

    The reasons I had for moving to the MySQL binaries are more about consistency between the distributed package and the correlating documentation, release notes, feature set, etc. Red Hat versions, as noted from the rpm header information, are relatively meaningless, because they backport features, and compile others out, and apply patches, etc., so you don’t *really* know if ‘feature x’ is in a given package until you run it. Conversely, just because the rpm-reported version is “too old” to have a feature doesn’t mean it’s not there. This is not a big deal for, say, ntp, I guess. But for something like mysql, it’s a huge headache.

    The issues you bring up above are ones that, to my knowledge, have nothing to do with whether or not you’re using the distro-supplied packages.

    ——————— snip ————-
    First of all, the distro supplied packages assume your going to place your mysql related files in certain places in the file system (e.g >/var/lib/mysql).
    ——————— /snip ————

    That’s a config file option. There are no assumptions per se. They just happen to supply defaults for these things. And, by the way, what exactly is wrong with the defaults?

    ——————— snip —————-
    Second, due to the nature of the binaries (see First point above), itÒ€ℒs completely possible (read: easy) to run multiple instances of mysql on the same server. This is true for multiple instances of the same version or multuple instances on different versions.
    ———————- /snip —————

    First, I’d never run multiple instances on a production mysql server. That said, I’m unsure why you think you can’t install multiple mysql rpms of different versions, or run multiple instances of one version? Just start mysqld with a different config file, and you should be off to the races. By way of disclaimer, though, I’ve never personally done this with mysql specifically. Is there some reason in particular that it *would* work with the mysql-supplied binaries but *not* the distro-supplied rpms?

    ———————- snip —————–
    Third, and this I think is the most important, is the availability of an instance rollback path should you have trouble with the upgrade. When you upgrade, you untar the new version of mysql into /opt/mysql/, shutdown mysql, update your symlink, and restart. Your old binaries are still there.
    ———————– /snip —————-

    Sure, that’s how I manage upgrades for stuff I build from source. Works like a charm, but it’s not really anything you can’t accomplish using RPMs either. If you’re using RHEL4, you can still use up2date –undo if you set “EnableRollbacks” to true in the rhn config. Later versions I believe require you to use the Red Hat Provisioning module, or with CentOS you can use rpm -Uvh using an older version of the package in question to ‘downgrade’ πŸ˜‰ — by the way, how is something like this handled in Debian? Anyone? Bueller?

    Oh, and yes, you should test upgrades!!!

    ———————— snip ——————
    Fourth, it gives you the ability to isolate routine OS updates from your database updates.
    ————————- /snip —————–

    Bah. Just configure yum or up2date to skip “mysql*” and you’re all set. You probably want to do that anyway for some things that have a tendency to flip out the system. I used to skip glibc* and only do those upgrades when we were coming up to a scheduled downtime, for example.

    In the end, reasonable people can certainly disagree on these points. It really is subjective and comes down to preference. I still may go for the mysql-supplied binaries, or even back to good old source. Truth is, I’ve never used distro-supplied mysql in production – I’ve only ever built from source, so going that route is comfortable for me as well. I wonder why you chose not to compile, or even build RPMs that you can distribute through an internal ‘mirror’?

    Y’know, it might be worth going to the mysql-supplied binaries just because I know you, and that’s what you’re doing! πŸ˜‰

    later!

  • http://support.3tera.com PeterNic

    Great description — all seems correct, by the book.

    See some notes in http://support.3tera.net/showthread.php?p=575#post575

    To summarize:
    1. MySQL 5-based appliance is now available in the new AppLogic 2.1.1 release, can be used on AppLogic 2.1.0 grids.
    2. If you want to make your appliance a catalog class (numerous benefits), you will want to revert all steps above prior to moving the appliance to the catalog.
    3. Some of the issues haaseg raised have different implications in AppLogic, especially if you will make the appliance a catalog class.

    Details posted in the AppLogic forum at http://support.3tera.net/showthread.php?p=575#post575

    Good luck with the appliance and thanks for using AppLogic!

    –Peter