Tag Archive for MySQL

32 or 64 bit MySQL

Recently, I wanted to confirm that I was running the 64 bit version of the MySQL server as opposed to the 32 bit version.
The Sun Webstack installation comes with both versions and if you use the built in SMF service, the difference between using the 32 bit version or 64 bit version is controlled by a flag in the service properties.
I was not using the built in service, but rather using Sun Cluster to start the server. In order to convince Sun Cluster to start the 64 bit version (I’m sure there is a better way to do this), one of my admins had made a symlink from the mysql/bin directory to the 64 bit binary directory. On the command line, you could no longer tell if the mysqld command was run from the 64 bit directory and there doesn’t seem to be a built in MySQL command which shows what version is currently running (show status, \s, show variables, etc)
In the end I ran pldd on the process id of the MySQL server. I am reasonably sure that I am running the 64 bit version of the server because all the shared libraries being used came from the /lib/sparcv9/ and /usr/lib/sparcv9/ directories which are the 64 bit libraries. Not sure if this method works on other OS’s but I thought it might be helpful to someone.
Good Luck!

Real Time Reporting Databases

Reporting projects are the kind of projects which never seem to end. After a couple iterations I’ve come to the following conclusions:

  1. Absolutely no reports should run on a production database.
  2. Moving/aggregating data from a production database to a reporting database using ETL tools prone to synchronization issues and pretty unreliable.
  3. The best option is to set up real time replication of the data and build additional views on that.

Unfortunately, if you need to get data from heterogeneous databases, ie. Oracle, MySQL, SQL Server, etc. into a single reporting database, replication is not a simple solution. If you are running expensive database software in production, it may not be cost effective to run the same database for reporting.

Of course there are cross database replication solutions like Golden Gate or SharePlex but they are very expensive. I had already given up on getting data from Oracle into MySQL for reports when I stumbled across Tungsten Replicator.

According to the website, Tungsten Replicator provides open source database-neutral master/slave replication. Master/slave replication is a highly flexible technology that can solve a wide variety of problems including Cross DBMS Integration, ie. replication from Oracle to MySQL.

I’m looking forward to testing this product in the near future and I’d be happy to get anyone’s input if they’ve used it.

Sun Webstack 1.4 – Packages on Crack

I am a huge fan of Sun Microsystems.
I love Solaris 10.
I love ZFS.
I love RBAC.
I love zones.
I really love T2/T2+ processors.
I especially love the T5140 and X4450 servers.

One thing I cannot figure out though, is why Sun lets obviously delirious cocaine addicts package their software. Maybe I’m exaggerating but I think that many will agree that Sun’s packages leave much to be desired in general. On top of that, Sun seems to have a constant need to move software around and invent new paths- to boldy go where no sysadmin has gone before????

Our journey begins with the mythic /usr/ucb/ directory- a true treasure chest for those making the adjustment from Linux. We’ll continue to /usr/local/ ala sun freeware (actually the most normal place we will visit but not actually supported by sun) and then arrive at the more recent /usr/sfw.

On your right, we’ll be passing the Coolstack project (Not Officially Supported by Sun) located reasonably in /opt/coolstack. Notice the configuration files in /opt/coolstack/etc, apache located comfortably in /opt/coolstack/apache2, mysql located in /opt/coolstack/mysql. Can anyone guess where the SMF manifests are? My first guess would have been /opt/coolstack/var/svc… similar to the native manifests but I would be wrong because that would make too much sense or be too easy. Anyway- they are hiding in /opt/coolstack/lib/svc…

Wait- what’s that ahead? Coolstack is falling into disrepair, no longer to be updated. Instead, there will be a new neighborhood called Webstack and it WILL be officially supported by Sun- Time to get high. Can’t figure out where anything is? I’ll give you some hints:

Looking for configuration files? Don’t try /etc or /opt/webstack/etc. You should be looking in /etc/opt/webstack ??!?! Since when does that directory even exist?

Looking for your MySQL data directory? Don’t try /opt/webstack/mysql/data (similar to the existing structure in coolstack). Bet you wouldn’t have guessed /var/opt/webstack/mysql/5.0/data – /var/opt ??!?! What is that? Maybe for the 1.5 release they could put it in /usr/ucb/opt/usr/local/var/spool/sfw/webstack/mysql/5.0/data?

How about your default DocumentRoot for Apache? You must have guessed it by now: /var/opt/webstack/apache2/2.2/htdocs

Anyone here running webstack on Linux? In that case all the directories are different. I guess Sun wanted to make it difficult to run their stack on heterogenous environments?

Seriously- I really hope Sun wises up and fixes this before they hope for widespread adoption of the 1.5 release.

Cloning zones in Solaris 10 6/06

I’m in the process of setting up a machine to host several SAMP (Solaris-Apache-MySQL-PHP) containers. I decided that it would be very efficient to create a generic zone and clone it over and over again. From reading up on the subject it seemed more than possible, after all, what is a zone besides a config file and a filesystem?

I googled for “Cloning Solaris Zones” and found lots of documentation on the zoneadm clone feature. I started to follow the howtos and hit a brick wall… my zoneadm doesn’t know how to clone. Deeper digging shows that the documentation on Sun’s site was for Solaris Express- Sun’s bleeding edge version of OpenSolaris- Can I say “How useless!”

I continued to google, after all I was very close I have the configuration and the filesystem, I just need to connect the two. I found the zoneadm attach/detach commands. This sounds perfect to me but alas my zoneadm doesn’t support attach/detach. Apparently, this feature is only available from Solaris 11/06- Can someone tell me when Sun started releasing new OS versions every 6 months!

I had no intention of giving up and here is the process which evolved:

  1. Setup the “Gold Master” zone including all the services, users, passwords, etc. (I’m assuming that your zonepath is a ZFS filesystem- this has it’s pluses and minuses so don’t take my word on it.)
  2. Halt the Master zone and export the config file to your zone template file:

    zoneadm -z master halt
    zonecfg -z master export -f /root/template

  3. It should look something like this: (edit with values for new zone)

    create -b
    set zonepath=/zfszones/zoneclone
    set autoboot=true
    set pool=work1-pool
    add inherit-pkg-dir
    set dir=/lib
    end
    add inherit-pkg-dir
    set dir=/platform
    end
    add inherit-pkg-dir
    set dir=/sbin
    end
    add inherit-pkg-dir
    set dir=/usr
    end
    add net
    set address=192.168.0.2
    set physical=bge0
    end
    add rctl
    set name=zone.cpu-shares
    add value (priv=privileged,limit=10,action=none)
    end

  4. Configure a new zone using the new config file:

    zonecfg -z zoneclone -f zoneclone.cfg

  5. Create a ZFS snapshot of the master zone:

    zfs snapshot zfspool/master@040207

  6. Clone the ZFS snapshot

    zfs clone zfspool/master@040207 zfspool/zoneclone

  7. Mount the new ZFS filesystem at the correct zonepath

    zfs setmountpoint=/zfszones/zoneclone/ zfspool/zoneclone

  8. Change the zone state to “installed” –WARNING: I have no idea if this is a good idea but it seems to work.

    vi /etc/zones/index

    Find a line that looks like:
    zoneclone:configured:/zfszones/zoneclone:0000003c-ffbf-f825-ffbf-f80001000000

    Replace it with:
    zoneclone:installed:/zfszones/zoneclone:0000003c-ffbf-f825-ffbf-f80001000000

  9. Boot the new zone:

    zoneadm -z zoneclone boot