Gargleblaster.org

Thoughts on the take over of MySQL AB by Sun.

The buzz in the blogosphere yesterday could hardly be missed. MySQL AB and Sun Microsystems announced they reached a final agreement for the acquisition of MySQL AB by Sun Microsystems. First of all, I think that it is very important that there is a difference between MySQL AB (the company) and MySQL the open source Database engine.
MySQL doesn’t “own” MySQL. Mysql is an open source project, so the source is free to modify and use etc according to the GPL license.

The people of MySQL AB seem to be very happy with the take-over. I can imagine that. It will give for sure some financial ease of mind for the company and his employees and access to a large source of knowledge and resources. Somehow many people, including myself, have a suspicion against “the big corporations”. With the exception of Apple computers ofc :).
Don’t forget however that Sun has a decent and reliable history with Open Source. Open Office and making Java open source to name 2 big achievements of Sun.
I use MySQL now for close to 9 years. I have learned to love this database engine, discovering the more advanced features of SQL step by step. I only have used MySQL directly or interfaced by PHP.
I can only express my hope that Sun will support the continuous development of the MySQL API as used by the MySQLi or mysqlnd extensions of PHP.
Some analysts suspect that Sun is busy to build his “own” alternative to the classic LAMP (Linux, Apache, MySQL, PHP) stack. The Sun-Stack would be SAMJ (Solaris (opensourced!), Apache, MySQL, Java).
People should have the freedom to use and code the language they prefer.
I really hope it won’t be soon that if you want to have the full support for the capabilities of MySQL, you have to use a Java interface to interact with the database engine from a web-environment.

Hilarious (and so happy it wasn’t me)

A mistake every DBA could make…having your auto-incremental primary key big enough to handle almost every thinkable 2 to the power of n, but you overlook the size of your index.

It has happened to slashdot last week:
Original story.

The funniest comment is imho:

Alright, who’s the joker who posted the 16,777,216th comment?

Thanks for breaking slashdot, jerk :D

duh

Sometimes you think to yourself: duh

Copy a table from 1 database to another was always something I did by exporting the table+data and importing it. Or I used the phpmyadmin tool for it.
Like today, but somehow my eye fell on the SQL syntax phpmyadmin used:

INSERT INTO newdb.newtable
SELECT *
FROM olddb.oldtable;

DUH!

Ofcourse this only works if you have copied the structure of the table already.

Effective restoring a mysql database

Being a brave sys-admin I do backup my mysql databases. But, begin to lazy to give each of my databases his own backup-script, I backup all the databases in one dump.
Sofar (triple knock on wood) I had only once to restore a database from that file. I did a couple of greps on the large file to create a file with only the database to restore. But Mike Hillyer has an excellent tip on his blog. I wonder when that option (-one-database) has been introduced, because it’s the first time I’ve heard of that option.

But here is the procedure:

Start my launching mysql client with –one-database

mysql -u root -p –one-database mydatabase

Then use the SOURCE command:

source ~/multidatabase.sql

The –one-database option forces the mysql client to ignore statements intended for any database other than the one you specified when you launched the client, allowing you to restore your database without affecting any other databases.

Upgrading mysql on OS X 10.3 Server

With the latest discovery of some remote vulnerabilities in mysql and PHP, I had to upgrade my production server.
And to be clear, I don’t like updates.
Esp. not on mission critical apps. Updates tend to break things, you see:)

But running php 4.3.6 and mysql 4.0.20 was getting…unwise.

Upgrading PHP is easy, with the (imho) great installer of entropy.ch.

installer -verboseR -pkg ~/php-4.3.10.pkg -target /

Don’t forget afterwards to restore your httpd.conf and to edit /usr/local/php/httpd.conf.php to add:

AddType application/x-httpd-php .php
AddType application/x-httpd-php .html
AddType application/x-httpd-php-source .phps


MySQL is somewhat more tricky.
The version supplied by Apple is well…historic.
I like to compile MySQL from source (esp. because that takes 8,5 minute on my Xserve :) and install it to /usr/local/mysql-(version).
After that I create a symlink from /usr/local/mysql-(version) to /usr/local/mysql.
Take some care replacing the apple supplied mysql-binaries with symlinks to the up2date /usr/local/mysql/bin/ counterparts.
After this, upgrading mysql from source is painless.

The most recent upgrade:

# cd /usr/local/src
# gnutar -xzvf mysql-4.1.10a.tar.gz
# cd mysql-4.1.10a
# ./configure --with-openssl \
--prefix=/usr/local/mysql-4.1.10_a \
--localstatedir=/usr/local/mysql-4.1.10_a/data\
--mandir=/usr/local/share/man/\
--with-mysqld-user=mysql
# make
# make install
# cd /usr/local/
# mysqladmin -u root -p shutdown
# cp -R mysql/data mysql-4.1.10_a/data
# chown -r mysql:wheel mysql-4.1.10_a/data
# ln -s /usr/local/mysql-4.1.10_a /usr/local/mysql
# mysqld_safe &


That’s all. If something goes wrong, just restore the symlink to the “old” mysql-(version) and you’re safe.
Be aware of the new password hashing mysql uses from 4.1.1 of. I used the –old-password switch untill I’ve upgraded all my clients.