PlanetMysql.ru — информация о СУБД MySQL

Drizzle 7 GA has been released, so I wanted to compile and test it on my Mac running OS X 10.6.7.  Since Drizzle 7 is new, Mac binaries are not available yet.  I’ve compiled MySQL from source more times than I can remember, and Drizzle was forked from MySQL, so I expected the build process to be similar and pain-free, and for the most part it was.  I did not use MacPorts or Homebrew for various reasons, mainly because I know that I will compile, tweak and recompile Drizzle often while hacking on it.  Also, the blog post  Drizzle in the Snow is about building Drizzle on Mac OS X, but it’s out of date (published September 1, 2009).  Thus the need for this blog post.

After describing how I compiled Drizzle 7 on my Mac, I list several suggestions for the Drizzle developers/maintainers based on my experience.

I began, of course, by downloading and extracting the tarball from drizzle.org.  Then I proceeded to read the fine manual, even though the build was surely the standard trio: configure, make, make install.  There are three docs about compiling Drizzle: the README in the tarball, the online Drizzle documentation, and the DrizzleWiki.  The README lists that standard trio and refers to the online docu.  The online docu also lists the standard trio but, of primary importance when compiling from source, it also lists dependencies.  The wiki page on compiling also refers to the online docu, and if you Google for “compile drizzle mac” you’ll find other wiki pages but they are all out of date (e.g. one still says you need to compile libdrizzle first; this is no longer true: libdrizzle comes with Drizzle).  So, the online docu is the best source because we need to know Drizzle’s dependencies.

The online docu does not have specific instructions for compiling Drizzle from source on Mac OS X.  On this platform, the number one pre-requisite is Xcode: Apple’s development package which installs all the basic programs for compiling code (e.g. g++, make, etc.)  I’m using Xcode 3.2 because it’s free (whereas Xcode 4 cost a few dollars).  So, Xcode must be installed first before any other packages can be compiled (but if you already develop on a Mac, you probably already knew this).

From the list of dependencies, I had to download and install boost, gettext, intltool, and protobuf.  These last three are really simple: configure, make, make check, make install.  I did make check because although Mac OS X is a Unix platform, it has its quirks. Those three packages compiled, tested and installed effortlessly, all using the prefix /usr/local/. boost, however, required more attention.

To start, I just followed boost’s docu page Getting Started on Unix Variants, but it didn’t work as easily as the other packages. First, since Drizzle lists only a few boost libs as dependencies, I only compiled those:

./bootstrap.sh \
  --with-libraries=date_time,filesystem,iostreams,program_options,regex,test,thread

When that finishes, it tells me:

Bootstrapping is done. To build, run:

    ./bjam

That is different from the docu which says to run bjam install. Running just bjam does compile the libs, but it doesn’t install them. Running with the install argument makes bjam hang. So I tried bjam --install which compiles the libs but still doesn’t install the libs. Furthermore, the compiled libs did not have -mt suffixes to indicate that they where multi-threading. I noticed that Drizzle configure checks first for libs with the -mt suffix, so I really wanted to make my boost libs multi-threading. After asking around then filing a boost bug about this, I was informed that the docu was out of date and that I needed to use --layout=tagged. So, finally, to compile and manually install the boost libs for Drizzle I did (all commands):

bootstrap.sh \
  --with-libraries=date_time,filesystem,iostreams,program_options,regex,test,thread
bjam --layout=tagged
sudo cp stage/lib/* /usr/local/lib/
sudo mv boost /usr/local/include/

After that, /usr/local/include/boost/ has the boost header files and /usr/local/lib/ has boost libs like:

/usr/local/lib$ ls libboost*
libboost_date_time-mt.a                libboost_regex-mt.a
libboost_date_time-mt.dylib*           libboost_regex-mt.dylib*
libboost_filesystem-mt.a               libboost_system-mt.a
libboost_filesystem-mt.dylib*          libboost_system-mt.dylib*
libboost_iostreams-mt.a                libboost_test_exec_monitor-mt.a
libboost_iostreams-mt.dylib*           libboost_thread-mt.a
libboost_prg_exec_monitor-mt.a         libboost_thread-mt.dylib*
libboost_prg_exec_monitor-mt.dylib*    libboost_unit_test_framework-mt.a
libboost_program_options-mt.a          libboost_unit_test_framework-mt.dylib*
libboost_program_options-mt.dylib*

Now Drizzle configure finishes, it doesn’t die saying it can’t find a library it needs. Furthermore, I see that it finds the -mt boost libs, so I hope that makes it happy (because, after all, one selling point for Drizzle is massive concurrency, so I don’t want to build it without being sure it’s using multi-threading libs). But there’s a problem: configure finishes by saying:

Configuration summary for drizzle7 version 7 drizzle

   * Installation prefix:       /usr/local
   * System type:               apple-darwin10.7.0
   * pandora-build version:     0.175
   * Host CPU:                  i386
   * C Compiler:                i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664)
   * C++ Compiler:              i686-apple-darwin10-g++-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664)
   * Assertions enabled:        yes
   * Debug enabled:             no
   * Profiling enabled:         no
   * Coverage enabled:          no
   * Warnings as failure:       no

My MacBook Pro has an Intel Core 2 Duo, so the CPU is definitely not i386; it should be x86_64. I’m not a build expert, but I think this information is detected by config.guess by looking at uname values, which on my system returns:

$ uname -a
Darwin MacBook-Pro.local 10.7.0 Darwin Kernel Version 10.7.0:
Sat Jan 29 15:17:16 PST 2011; root:xnu-1504.9.37~1/RELEASE_I386 i386

That probably explains why configure detects the CPU has i386 (and the OS version as 10.7). So to correct this, I ran: configure --build=x86_64-apple-darwin10.6 --prefix=/opt/drizzle7. (I added --prefix because I want to install it elsewhere.) That gives me the results I expect:

Configuration summary for drizzle7 version 7 drizzle

   * Installation prefix:       /opt/drizzle7
   * System type:               apple-darwin10.6
   * pandora-build version:     0.175
   * Host CPU:                  x86_64
   * C Compiler:                i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664)
   * C++ Compiler:              i686-apple-darwin10-g++-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664)
   * Assertions enabled:        yes
   * Debug enabled:             no
   * Profiling enabled:         no
   * Coverage enabled:          no
   * Warnings as failure:       no

Again, I’m no build expert, but I hope this has an affect (i.e. the code is compiled with modern optimizations). If nothing else, it gives me piece of mind. So I run make and after some time Drizzle compiles without any problems. More importantly, I run make test and the result:

All 557 tests were successful.
The servers were restarted 54 times
Spent 242.179 of 386 seconds executing testcases

Congratulations to the Drizzle developers! All tests passing straight out of the box on a completely foreign system (i.e. mine) is an accomplishment.

Finally, make install puts everything in the prefix I chose (/opt/drizzle7):

/opt/drizzle7$ ls *
bin:
drizzle*       drizzleadmin*  drizzledump*   drizzleimport* drizzleslap*

include:
drizzle7/       libdrizzle-1.0/

lib:
drizzle7/                       libdrizzledmessage.0.dylib*
libdrizzle.1.1.0.dylib@         libdrizzledmessage.dylib@
libdrizzle.1.dylib*             libdrizzledmessage.la*
libdrizzle.dylib@               locale/
libdrizzle.la*                  pkgconfig/
libdrizzledmessage.0.0.0.dylib@

sbin:
drizzled@  drizzled7*

share:
man/

Now to run Drizzle for the “first” time (actually I compiled and ran Drizzle last year on my PC/Ubuntu machine, so this is only my first time on my Mac). I use the command line specified in the README:

/opt/drizzle7$ ./sbin/drizzled \
   --no-defaults \
   --port=3306 \
   --basedir=/opt/drizzle7 \
   --datadir=/tmp/drizzle7/data >> /tmp/drizzle7/drizzled.err 2>&1 &
[1] 72197

/opt/drizzle7$
[1]+  Exit 1  ./sbin/drizzled [snip] >> /tmp/drizzle7/drizzle.err 2>&1

/opt/drizzle7$ cat /tmp/drizzle7/drizzled.err
unknown option port
Use --help to get a list of available options

Aborting

Oh no! drizzled failed to start because the --port option doesn’t actually exist. So the README is a little out of day. No problem: just re-run it without --port and it starts without problems:

$ mysql -h 127.1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 2011.03.13 Source distribution (drizzle)

All in all, the build process was painless. (boost gave me more problems than Drizzle.) Now I can start hacking on Drizzle and comparing it to MySQL (whether or not that’s a fair comparison, people are going to do it often). After a few minutes of looking around Drizzle, I can already see that its “out of the box experience” is quite different from MySQL’s. For example, because I didn’t build Drizzle with extra plugins, the default authentication plugin is auth_all, i.e. no authentication, and the logging_query plugin isn’t available so no slow query logging, either. These are topics and considerations for other blog posts.

Based on my experience compiling Drizzle 7 GA from source on Mac OS X 10.6, here are my recommendations to the Drizzle developers/maintainers:

1. Remove DrizzleWiki and centralize all documentation at docs.drizzle.org.
2. Update the tarball README and/or add an INSTALL.
3. Update docs.drizzle.org (i.e. make everything refer only to Drizzle 7).
4. Consolidate information on docs.drizzle.org (e.g. “Software Requirements” lists some required libs, then “Minimal Requirements” lists more).
5. When compilation instructions specific to Mac OS X are written, mention the need for Xcode.
6. Harass the boost developers to update their documentation.
7. Provide Mac OS X binaries.

As a developer of a much smaller project, I know this is all easier said than done.  Overall though, good job on Drizzle 7.

1. Quick Multi MySQL Server Installation with Master-Master Replication on Same Windows Box This article is a brief step-by-step tutorial on the subject...
2. 5 useful MySQL Command Options-pager-prompt-rehash-tee-system There are set of commands that MySQL itself interprets. You...

aka “How to use multiple MySQL Servers and Workbench in Snow Leopard without using Terminal… and live happily ever after”

The MySQL Community is a world of command-line aficionados. Many people, including myself, show their love to the simple-but-powerful interface of the mysql command-line client, but not everybody is keen to use a bash shell and give up its GUI, no matter how powerful the software is.

Until recently, GUI tools for MySQL were half baked solutions: in the end, there was always something that you had to do via the command line. Today, you can install, set up and use MySQL on your Mac with Snow Leopard without using Terminal, at all.

My Special Needs

Before digging into the details of the installation, let me describe what I need on my Mac. I use various versions of MySQL and I often need to run 2 or more instances at the same time. I constantly build, install and uninstall versions of MySQL and I need to find a way to quickly remove (or move) one version of MySQL with data, configuration files and all that is related to a specific instance.

Not everybody has complicated environments and multiple instances; for many users, a single installation would be enough. If you are one of these lucky users, you may skip some steps. In any case, everything presented in this article is applicable to 1 or more instances running on a single Mac.

The Ingredients

All the software is available on the MySQL Dev site. You will need 2 kits: MySQL Server and MySQL Workbench. I used the latest .dmg archive from http://dev.mysql.com/downloads and the latest version of Workbench, from here http://dev.mysql.com/downloads/workbench/5.2.html. At the time I am preparing this post, the latest versions are MySQL Server 5.6.0-m4 and MySQL Workbencg 5.2.27.

Preparation

I have my own way of keeping multiple instances on a single mac, and I am sure you can find many others, perhaps they would make even more sense for you. There are some good tools and scripts that may help you (just google a bit or visit confidently Giuseppe Maxia’s blog: http://datacharmer.blogspot.com).

In a nushell, my way of keeping everything separated can be described as:

• Select a parent directory: in my case, I choose /usr/local. For example, the new version of the server will be available on /usr/local/mysql-5.5.6-m4-osx10.6-x86_64. Everything related to this version is in there.
• Select a data directory: I simply use the data sub-directory in the base directory. For an instance on a laptop, used for basic tests, that would be just fine.
• Keep the config files in the base directory: For example, the configuration file, by default, would be /etc/my.cnf. I will use /usr/local/mysql-5.5.6-m4-osx10.6-x86_64/my.cnf instead.
• Select a socket file: in this case, I keep the socket file in the /tmp directory. My suggestion is to use a naming convention, such as mysql-<version>.sock. For example, the socket file for this version would be mysql-5.6.0-m4.sock.
• Identify a specific TCP port for your version: avoid the default port (3306) and start from 3307 ore any other port. In this way, your client tools will not accidentally connect to a server because you forget to specify the TCP port.

Cooking Instructions

Once you have downloaded the software, you are ready to install it. You should start with MySQL Server. When you open the DMG archive, you should see something this:

MySQL Server Package

Just doubleclick on the main package and follow the default instructions, and you are done in literally 1 minute.

By default, the package creates a symbolic link to the base directory. You should see a base dir created under /usr/local. Finder does not allow you to browse /usr/local, but if you go do Go To Folder in the Go menu, you can insert /usr/local in the folder text box and see this window:

Go To Folder

Local Dir

MySQL Base Dir Info

Change to RW

MySQL Workbench Package

The next step is to drag and drop the Workbench icon into your favourite application folder or subfolder.

Easy as 1..2..3, done. Just double click on Workbench and you will see the main window:

MySQL Workbench Home Page

The next step is to create a new server instance. Click on the New Server Instance item on the right side of the main window and you will see this dialog:

Create New Server Instance

The instance will run on your local Mac, so leave the radio button on localhost selected. By clicking the Continue button, you will see the next page:

Create New Server Instance

As Connection Name, I used the version of the server (mysql-5.6.0.-m4) and I have selected Local Socket as Connection Method. Now you need to specify the socket file and path. I usually leave the file in the /tmp directory, therefore the file and path would be /tmp/mysql-5.6.0-m4.sock.

I do not have any relevant information in my instances, so a root access with simple or no password is enough. I use to store it in the Keychain, in order to avoid the request every time I need to open a new connection.

Store Password for Connection

By clicking Continue on the main dialog window, you will move to a testing page. Here Workbench will try to connect to the server. Since the server is down, you will see a set of errors, they are absolutely fine at this stage.

Testing the DB Connection

By clicking Continue, you will be able to specify the operating system and the package you are using. These combos are already selected for you as MacOS X and MySQL Package. When you will open the profile to change some parameters, you will notice that the Installation Type will be different: this is absolutely normal, since Workbench identifies the fact that we have overridden some defaults as a custom installation.

Specifying the OS

When you click Continue, you will test the host settings and again, you will see some errors, due to the fact that the instance is not running (see image below).

Testing the Host Settings

Another click on the very same button brings you to the MySQL Config Page. In this page you can specify the version of the server and the path to the configuration file. Personally I am very lazy and with fat fingers, so I prefer to see the software to provide the path for me. I will use the same trick adopted in Finder, i.e. I will type the first or first two directories in the path, then I will click “…” to select the rest of the path:

Info About MySQL Conf

Specify Path to the Conf File

Here you can select the base directory and from the base directory you can select a file. My recommendation is to select the README file:

Select README

When you click the Open button, the README file and path will fill the Path text box. Now you can change README with my.cnf:

Change README to my.cnf

You are ready to click Continue again. The next page shows you the commands used to administer the server instance. You can leave these commands as is at the moment, you will change them in a minute.

Commands to Manage the Server

The last click on Continue brings you to the final page, where you can give a name to the profile. Again, I used the version and the location to name the profile.

Create Instance Profile

You can finally create the Profile by clicking the Finish button. The result is a new instance in the Server Administration area.

New Instance Created

Now you may want to change the way you administer the instance, by selecting more details that were not present in the wizard. You can do so by clicking the Manager Server Instances item.

The output window shows the connection parameters that have been set in the wizard.

Manager Server Instances - Connection

By clicking System Profile, you will see a new set of parameters. Some of them were not present in the previous wizard, but you can review and alter them now. You may have noticed that the Installation Type is now Custom, because the path of the configuration file has changed. Now what you should do is to update the start and stop commands in this page, since they still refer to the symbolic link that I removed.

Manage Server Instances – System Profile

This is probably the trickiest part of the configuration. In general, the start and stop commands require the privilege of superuser (or you must play more with the owneship and the grants of files and directories). I had lots of problems with the sudo commands and in the end I decided to use a trick. Again, this trick is not great, since it exposes my user password on the local machine, but I do not have any problem with that, so I decided to adopt it. Basically, I added the sudo command to the start and stop lines and I unchecked the checkbox underneath. The result is that I do not rely on the use of two commands combined in one action, but I can confidently test and execute a command in one go.

The final start and stop commands are something like this:

Start:

echo mypwd | sudo -S /bin/sh -c "cd /usr/local/mysql-5.6.0-m4-osx10.6-x86_64; bin/mysqld_safe --defaults-file=my.cnf"

Stop:

echo mypwd | sudo -S /bin/sh -c "cd /usr/local/mysql-5.6.0-m4-osx10.6-x86_64; bin/mysqladmin -uroot --socket=/tmp/mysql-5.6.0-m4.sock shutdown"

The concept is pretty simple: you type a password and you pass it to a sudo command. The command then executes a shell and passes the command string between quotes to the shell. If this explanation does not make sense to you, don’t worry, just replace mypwd with your machine password. Just remember, if you are using special characters, i.e. non digits or letters, you should use the \ symbol as prefix. Hence, the password “Good Job!” should be “Good\ Job\!”. You may notice that I have also removed the sudo command completely, just to avoid any misunderstanding.

Change Start-Stop

When you click the Close button, you are finally ready to go! Now you can doubleclick the new instance profile and Workbench will try to connect to the instance. Again, MySQL is not running, so you should see a dialog that alerts you and you should click Continue Anyway.

Open a MySQL Instance

After few seconds, you will see the main server instance window:

Server Instance Window

The MySQL Server is stopped and you need to set few more parameters before you can have it up and running. Click the Configuration Item and start with the General tab. Here you should change the TCP port (as advised, so you will not accidentally connect to any instance by default), the base directory and the data directory. The same trick to select a root directory first is applicable here, since the two directories should be respectively /usr/local/mysql-5.6.0-m4-osx10.6-x86_64 and /usr/local/mysql-5.6.0-m4-osx10.6-x86_64/data.

Instance Configuration - General

Once you have set the directories, you need to change a network parameter. Click the Networking tab and specify the socket file as we have previously defined (/tmp/mysql-5.6.0-m4.sock).

Instance Configuration - Networking

Now you can click the Apply button and create the my.cnf file for the very first time. The dialog that appears is just a confirmation of the actions to take. You will create the file by clicking Apply again.

Conf File Changes

You can finally select the server instance again and click on Start Server. The first time you start the server, allow your Mac to work for 40-50 seconds or even a minute or so. MySQL needs to create the InnoDB files and it will take a while.

Open Instance

Finally, you will be rewarded with this screen:

Instance Running

Congratulations!

Now, you may argue it’s overcomplicated, but again, everything is self contained and you can run as many instances as you like at the same time, without touching the Terminal.

Enjoy!

As last bit, you should check the connection parameters set by the wizard, by selecting the new connection on the left and clicking the Manage Connection option. Here, I would just recommend to give a good name to the connection.

Manage Connection

Now you can close the window and doubleclick the new connection. The query window will appear:

Open Connection

You can now start using the connection, by executing queries or creating objects with the SQL editor or with the data modeler.

But the beauty of Workbench is material for another post!

aka “How to use multiple MySQL Servers and Workbench in Snow Leopard without using Terminal… and live happily ever after”

The MySQL Community is a world of command-line aficionados. Many people, including myself, show their love to the simple-but-powerful interface of the mysql command-line client, but not everybody is keen to use a bash shell and give up its GUI, no matter how powerful the software is.

Until recently, GUI tools for MySQL were half baked solutions: in the end, there was always something that you had to do via the command line. Today, you can install, set up and use MySQL on your Mac with Snow Leopard without using Terminal, at all.

My Special Needs

Before digging into the details of the installation, let me describe what I need on my Mac. I use various versions of MySQL and I often need to run 2 or more instances at the same time. I constantly build, install and uninstall versions of MySQL and I need to find a way to quickly remove (or move) one version of MySQL with data, configuration files and all that is related to a specific instance.

Not everybody has complicated environments and multiple instances; for many users, a single installation would be enough. If you are one of these lucky users, you may skip some steps. In any case, everything presented in this article is applicable to 1 or more instances running on a single Mac.

The Ingredients

All the software is available on the MySQL Dev site. You will need 2 kits: MySQL Server and MySQL Workbench. I used the latest .dmg archive from http://dev.mysql.com/downloads and the latest version of Workbench, from here http://dev.mysql.com/downloads/workbench/5.2.html. At the time I am preparing this post, the latest versions are MySQL Server 5.6.0-m4 and MySQL Workbencg 5.2.27.

Preparation

I have my own way of keeping multiple instances on a single mac, and I am sure you can find many others, perhaps they would make even more sense for you. There are some good tools and scripts that may help you (just google a bit or visit confidently Giuseppe Maxia’s blog: http://datacharmer.blogspot.com).

In a nushell, my way of keeping everything separated can be described as:

• Select a parent directory: in my case, I choose /usr/local. For example, the new version of the server will be available on /usr/local/mysql-5.5.6-m4-osx10.6-x86_64. Everything related to this version is in there.
• Select a data directory: I simply use the data sub-directory in the base directory. For an instance on a laptop, used for basic tests, that would be just fine.
• Keep the config files in the base directory: For example, the configuration file, by default, would be /etc/my.cnf. I will use /usr/local/mysql-5.5.6-m4-osx10.6-x86_64/my.cnf instead.
• Select a socket file: in this case, I keep the socket file in the /tmp directory. My suggestion is to use a naming convention, such as mysql-<version>.sock. For example, the socket file for this version would be mysql-5.6.0-m4.sock.
• Identify a specific TCP port for your version: avoid the default port (3306) and start from 3307 ore any other port. In this way, your client tools will not accidentally connect to a server because you forget to specify the TCP port.

Cooking Instructions

Once you have downloaded the software, you are ready to install it. You should start with MySQL Server. When you open the DMG archive, you should see something this:

MySQL Server Package

Just doubleclick on the main package and follow the default instructions, and you are done in literally 1 minute.

By default, the package creates a symbolic link to the base directory. You should see a base dir created under /usr/local. Finder does not allow you to browse /usr/local, but if you go do Go To Folder in the Go menu, you can insert /usr/local in the folder text box and see this window:

Go To Folder

Local Dir

MySQL Base Dir Info

Change to RW

MySQL Workbench Package

The next step is to drag and drop the Workbench icon into your favourite application folder or subfolder.

Easy as 1..2..3, done. Just double click on Workbench and you will see the main window:

MySQL Workbench Home Page

The next step is to create a new server instance. Click on the New Server Instance item on the right side of the main window and you will see this dialog:

Create New Server Instance

The instance will run on your local Mac, so leave the radio button on localhost selected. By clicking the Continue button, you will see the next page:

Create New Server Instance

As Connection Name, I used the version of the server (mysql-5.6.0.-m4) and I have selected Local Socket as Connection Method. Now you need to specify the socket file and path. I usually leave the file in the /tmp directory, therefore the file and path would be /tmp/mysql-5.6.0-m4.sock.

I do not have any relevant information in my instances, so a root access with simple or no password is enough. I use to store it in the Keychain, in order to avoid the request every time I need to open a new connection.

Store Password for Connection

By clicking Continue on the main dialog window, you will move to a testing page. Here Workbench will try to connect to the server. Since the server is down, you will see a set of errors, they are absolutely fine at this stage.

Testing the DB Connection

By clicking Continue, you will be able to specify the operating system and the package you are using. These combos are already selected for you as MacOS X and MySQL Package. When you will open the profile to change some parameters, you will notice that the Installation Type will be different: this is absolutely normal, since Workbench identifies the fact that we have overridden some defaults as a custom installation.

Specifying the OS

When you click Continue, you will test the host settings and again, you will see some errors, due to the fact that the instance is not running (see image below).

Testing the Host Settings

Another click on the very same button brings you to the MySQL Config Page. In this page you can specify the version of the server and the path to the configuration file. Personally I am very lazy and with fat fingers, so I prefer to see the software to provide the path for me. I will use the same trick adopted in Finder, i.e. I will type the first or first two directories in the path, then I will click “…” to select the rest of the path:

Info About MySQL Conf

Specify Path to the Conf File

Here you can select the base directory and from the base directory you can select a file. My recommendation is to select the README file:

Select README

When you click the Open button, the README file and path will fill the Path text box. Now you can change README with my.cnf:

Change README to my.cnf

You are ready to click Continue again. The next page shows you the commands used to administer the server instance. You can leave these commands as is at the moment, you will change them in a minute.

Commands to Manage the Server

The last click on Continue brings you to the final page, where you can give a name to the profile. Again, I used the version and the location to name the profile.

Create Instance Profile

You can finally create the Profile by clicking the Finish button. The result is a new instance in the Server Administration area.

New Instance Created

Now you may want to change the way you administer the instance, by selecting more details that were not present in the wizard. You can do so by clicking the Manager Server Instances item.

The output window shows the connection parameters that have been set in the wizard.

Manager Server Instances - Connection

By clicking System Profile, you will see a new set of parameters. Some of them were not present in the previous wizard, but you can review and alter them now. You may have noticed that the Installation Type is now Custom, because the path of the configuration file has changed. Now what you should do is to update the start and stop commands in this page, since they still refer to the symbolic link that I removed.

Manage Server Instances – System Profile

This is probably the trickiest part of the configuration. In general, the start and stop commands require the privilege of superuser (or you must play more with the owneship and the grants of files and directories). I had lots of problems with the sudo commands and in the end I decided to use a trick. Again, this trick is not great, since it exposes my user password on the local machine, but I do not have any problem with that, so I decided to adopt it. Basically, I added the sudo command to the start and stop lines and I unchecked the checkbox underneath. The result is that I do not rely on the use of two commands combined in one action, but I can confidently test and execute a command in one go.

The final start and stop commands are something like this:

Start:

echo mypwd | sudo -S /bin/sh -c "cd /usr/local/mysql-5.6.0-m4-osx10.6-x86_64; bin/mysqld_safe --defaults-file=my.cnf"

Stop:

echo mypwd | sudo -S /bin/sh -c "cd /usr/local/mysql-5.6.0-m4-osx10.6-x86_64; bin/mysqladmin -uroot --socket=/tmp/mysql-5.6.0-m4.sock shutdown"

The concept is pretty simple: you type a password and you pass it to a sudo command. The command then executes a shell and passes the command string between quotes to the shell. If this explanation does not make sense to you, don’t worry, just replace mypwd with your machine password. Just remember, if you are using special characters, i.e. non digits or letters, you should use the \ symbol as prefix. Hence, the password “Good Job!” should be “Good\ Job\!”. You may notice that I have also removed the sudo command completely, just to avoid any misunderstanding.

Change Start-Stop

When you click the Close button, you are finally ready to go! Now you can doubleclick the new instance profile and Workbench will try to connect to the instance. Again, MySQL is not running, so you should see a dialog that alerts you and you should click Continue Anyway.

Open a MySQL Instance

After few seconds, you will see the main server instance window:

Server Instance Window

The MySQL Server is stopped and you need to set few more parameters before you can have it up and running. Click the Configuration Item and start with the General tab. Here you should change the TCP port (as advised, so you will not accidentally connect to any instance by default), the base directory and the data directory. The same trick to select a root directory first is applicable here, since the two directories should be respectively /usr/local/mysql-5.6.0-m4-osx10.6-x86_64 and /usr/local/mysql-5.6.0-m4-osx10.6-x86_64/data.

Instance Configuration - General

Once you have set the directories, you need to change a network parameter. Click the Networking tab and specify the socket file as we have previously defined (/tmp/mysql-5.6.0-m4.sock).

Instance Configuration - Networking

Now you can click the Apply button and create the my.cnf file for the very first time. The dialog that appears is just a confirmation of the actions to take. You will create the file by clicking Apply again.

Conf File Changes

You can finally select the server instance again and click on Start Server. The first time you start the server, allow your Mac to work for 40-50 seconds or even a minute or so. MySQL needs to create the InnoDB files and it will take a while.

Open Instance

Finally, you will be rewarded with this screen:

Instance Running

Congratulations!

Now, you may argue it’s overcomplicated, but again, everything is self contained and you can run as many instances as you like at the same time, without touching the Terminal.

Enjoy!

As last bit, you should check the connection parameters set by the wizard, by selecting the new connection on the left and clicking the Manage Connection option. Here, I would just recommend to give a good name to the connection.

Manage Connection

Now you can close the window and doubleclick the new connection. The query window will appear:

Open Connection

You can now start using the connection, by executing queries or creating objects with the SQL editor or with the data modeler.

But the beauty of Workbench is material for another post!

This is part four of an ongoing series about my experiences while writing the MySQL Admin Cookbook for Packt Publishing. All previous parts can be found under the mysql-admin-cookbook label.

This part will be about more software used in the process of writing the book. The last episode covered writing tools, file/version management and backups. What's up now is graphics programs, virtualization and PDF handling.

Outlining

For outlining and structuring thoughts I like mind-maps. I know they are not for everyone, but if you like them and do not want to spend a lot of money on MindManager, have a look at FreeMind. It is a free, open source, Java based mind mapping application with a large set of features and a really nice UI. In fact, I am using it right now to structure the writing of this series of blog posts. It comes in readily installable versions for Windows, Mac OS X and Linux, which makes it really a good fit if you regularly switch platforms and want to be able to add a thought or two to an existing map without having to dual-boot or fire up a virtual machine.

Graphics and Illustrations

As with any technology, some things in database land are much easier to show as a diagram or schematic drawing than explaining them verbosely in text. Not to forget the occasional – well, the regular – screenshot to show GUIs or console printouts.

Screenshots

As a matter of fact in the first drafts of most chapters Udo and I had a lot of textual presentations of query results and plain text output from the mysql command line client, but the publishing company wanted more screenshots instead to make the book appear less text heavy. There have been mixed reactions to this, but generally I think less would have been more. Several of the screenshots merely show console windows which are not always good to read due to scaling and anti-aliasing.

To take screenshots, Mac OS X is pretty well suited without any additional tools. Even though there are some nice programs that offer advanced features like Layers which can automatically create a PSD file with one layer per open window, usually screenshots were taken of a single Terminal.app window or specific dialogs. For that the built-in features were completely sufficient:

1. ⇧+⌘+3 produces a snapshot of the whole screen
2. ⇧+⌘+4 produces a crosshair that you can drag over a specific area of your screen
3. ⇧+⌘+4 followed by the space bar turns the crosshair into a camera to snapshot a single window

The only adjustment needed was to disable the drop shadow that is otherwise put behind the windows in the screenshot files by default:

defaults write com.apple.screencapture disable-shadow -bool true

That change gets effective after logging out and back in again or by killing the SystemUIServer process.

The screenshots are placed on the desktop by default. Depending on the OS X release the filename and format may differ. For the book we wanted PNG files, because JPGs would have caused compression artifacts. PNG is the default in Mac OS X 10.6 Snow Leopard. Generally this command allows you to change the format of screenshot files:

defaults write com.apple.screencapture type png

Instead of png you can also choose from tiff, gif, jpg, pdf, if I am not mistaken.

For Windows I used is a handy little tool called Screenshot Pilot which has a slightly “nostalgic” GUI style, but it works really well. It is far more pleasant to use than the otherwise necessary Windows style screenshot taking by hitting Alt-PrtScr to copy the current Window to the clipboard and then paste it into mspaint.exe and save it as a file.

Illustrations

Very early in the process we asked Packt about how to go about illustrations. They told us that anything would be fine that worked for us, because they had professional illustrators who would re-draw anything we sent them to give the book a uniform style. For example they would accept drawing made with any of the Office tools, bitmap graphics or just scans of hand-drawn sketches. I was very relieved when I heard this, because if there's one thing that eats up time even more quickly than text formatting it certainly is graphics work. I think of myself as nothing close to an artist, so knowing that someone else with probably lots of experience and routine would take care of this was very comforting and would relieve us of the necessity to find a set of good graphics tools.

Or so we thought at the time... To give you an idea, this is one of the sketches I sent along with a chapter of text:

While certainly no masterpiece I think the idea is brought across well enough for someone to redraw this with a little style. I scribbled together a few of these and sent them off in good faith.

Later in the process, this is what I got back for the sketch above:

Frankly, I was very much disappointed and certainly did I not want graphics of this style and quality to go into the book. Just look at the tunnel entrance and exit symbols… So in the end I decided to take care of the graphics myself. I fooled around a little with OpenOffice's drawing tool but did not manage to create anything that I even remotely liked. Fortunately a friend of mine owns a copy of OmniGraffle, a professional vector based diagramming tool for the Mac, and he let me spend some time on his machine remotely. With this I managed to create new versions of the illustrations in a style that I liked and with a little more attention to detail. This is my version of the same idea:

Making these took quite a lot of time. Once finished with the bulk of the work I sent it off to the publisher in EPS format, because I hoped that would be the easiest way of making sure they had the best possible quality to work with without requiring them to organize a Mac. Turns out the EPS is not as portable as I thought and had hoped. There were lots of troubles with line and arrow styles, transparencies etc. In the end I had to download a trial version of Corel Draw for Windows, because that's what Packt's graphics people use.

With trial and error I managed to get the illustrations to Corel Format with the correct fonts and no strange rasterizing artifacts for partially transparent areas. All in all, had I known I would have to take care of this myself from the beginning, I would have asked for at least one more month in the schedule and prepared them along the way. The same goes for the very few charts included in the book. I made these with a trial version of OmniGraphSketcher, also from OmniGroup. The idea behind this tool is to actually draw diagrams, instead of having them generated by a spreadsheet tool from pure numbers. While I generally like the idea, the tool is still lacking in my opinion. For example getting the X- and Y-scales right was more complicated than I would have thought. Anyway, I will keep an eye on the program - it surely has the potential to become a valuable tool and I will most certainly check it out again if/when the need arises.

A few more words on Corel Draw: I have no option but to rant about it! The last version I remember to be really good was Corel Draw 5, about a millennium ago or so. After that I had a look at Corel Draw 7 and did not like it at all. In my opinion it has suffered form the same feature-creep that most applications that were once lean, fast and powerful, getting bigger and especially buggier all the time (Firefox, Nero anyone?). I am glad I have no need to work with this software on a regular basis. From real crashes - which I saw quite a few of - to annoying user experience problems; this is not a program you would enjoy to work with. The stupidest thing in my opinion was that for virtually any file operation the open or save dialogs would open in my home directory, instead of remembering which directory I had last used. An estimated three hours of my lifetime could have been saved, had I not had to spend them navigating to the right path time and again. Also what is it the myriad of file format versions? As you save a file you have to decide what version (7, 8, 9, 10, 11, 12) you would like to use. Of course they are all called ".CDR", and the program does not remember what you chose for the last file... Better make sure to include the format version in the filename, because once on the disk you have to easy way to tell which is which!

Test setups / Virtual Machines

As it is easy to imagine, for a database cookbook lots of experiments and trying out stuff is required in order to make sure that everything you claim actually works. Anyone who kept reading up to this point is probably technical enough to understand that no matter how well you plan and how carefully you write down any sort of script, source code sample or the like, it will not work unless you copy and paste it from the actual command line window or source code editor. There just is no other way. And also it is (almost) guaranteed that you will break any code by making "one last beautifying change" in the word processor; be it a missing semicolon, a space introduced into a regular expression or a capitalization change, something will go wrong!

The only chance to make sure your readers are not too likely to find a bug in your scripts or command lines is to try them all - each and every one of them. Of course, some of them by design make permanent changes to the test setup, so if anything goes wrong and you cannot just take a screenshot and put it into the manuscript you might be in for varying amounts of resetting the machine and trying again. More than once I made a simple mistake in that, too and ended up with an ever so slight difference in the test setup, messing up the test again. In that light, one wonders why any functioning production systems exist in the first place...

Fortunately this is not 1995 anymore and there are very capable virtualization solutions available for reasonable prices to ease the pain with repeated tests and different setups.

As Mac OS X is not the primary platform most MySQL administrators use - neither as a client, nor as a server - I need a way to test with different versions and setups of Windows and Linux. One way would have been to use bootcamp to dual-boot into Windows, but that would have caused unacceptable roundtrip times. Because performance was rarely an issue, I decided to buy VMWare Fusion 3. Though not very cheap it certainly is a product worth its money. Here you can see my Virtual Machine Library - some of the test VMs have been deleted since the book was finished - but you can still see the Ubuntu, Vista and Windows 7 VMs I primarily used to take screenshots on.

Apart from being able to run different operating systems and MySQL versions, the killer feature for me was snapshots. Basically they allow you to clone a virtual machine and all its state into one or more named copies and reverting back to these "save-games" at a later time, discarding any changes that happened in the meantime. Though not particularly suited for long term operations - performance is degraded quite a bit - they are ideal for what I call "destructive" tests: Those that actually modify system state, like configuration files, database contents, software installations etc. For every VM I would first set up a well-known "good state" and take a snapshot of that. If in the course of writing a recipe I needed to make several attempts to get everything just right I could mess everything up with no worries and go right back to the beginning, without any risk of accidentally missing something important.

If you regularly have to do any sort of software testing you will appreciate the benefits virtualization immediately. The fact that I chose VMware is not too important. As it is often with software, personal preference plays a huge role. Udo got a good deal on Parallels Desktop which is another commercial virtualization solution for the Mac. However you could just as well go with the free VirtualBox from Oracle, available for Windows, Linux and the Mac. They all offer very similar feature sets and would all have been equally suited for the tasks I performed.

PDF Tools

In the later stages of production annotated PDF documents superseded the previously used OpenOffice and Word document files. The reasons for and problems with that will be focused on in a later installment of this series.

Mac OS X incorporates very strong PDF handling facilities right in the operating system's core. Much of the Mac's graphics are based on PDF internally, so it is not too surprising to find advanced PDF handling features in the built-in "Preview.app". Out of the box any Mac can open PDF documents, inspect their meta-data and also create new PDFs from any application without the need for special PDF printer apps or the like. Before the book writing project I had never even bothered to install the Adobe Reader - I just did not have any use for it. Considering the fact that compared to Preview.app it takes ages to load and is a never-ending source of security problems I had no intention of changing that.

But then Packt started to send us PDF versions of the first formatted and laid out chapters, complete with annotations and comments. I blogged about a rather unpleasant episode regarding Preview.app's shortcomings earlier.

I won't repeat everything here, but suffice it to say that you should not trust it to show all the annotations and comments a document contains!

So I needed to download and install Adobe Reader to make sure I could at least see all annotations and meta-information embedded in the PDFs from the publisher. I have not checked again since, but when I last downloaded the installer from their website it was not the latest version. In order to keep your system (reasonably) secure against the many Adobe Reader based exploits circulating the net, make sure to manually run the updater again and again until it tells you there are no more updates left! If there had been updates like this in 1995, I am sure they would have been implemented like this...

The same goes for Acrobat Professional which in the further course of getting the book done was needed as well - again, details will follow later. For reasons beyond logic there is no trial version on Adobe's page for Acrobat Pro as a standalone application on the Mac - only for Windows. As a Mac user you need to download the full CS4 suite to try it... Fortunately I did not have to jump through these hoops, because a version 8 DVD was included in the software bundle with Fujitsu's ScanSnap. That one, too, had to be updated in what felt like 100 individual runs of the Adobe Updater.

While being considerably slower than the Preview.app accompanying Mac OS X, Acrobat and Adobe Reader were much better at handling document commenting, revising and especially comparing! Yes, it is possible to compare two PDFs and have Acrobat highlight changes between the two - a feature that became indispensable in the later project phases:

With that I will conclude this second part of the tool descriptions. I have probably forgotten one or two little utilities that came in handy, but these were the companions that accompanied me along the way constantly. In the upcoming episodes I will refer to them as needed, but you should have a general idea on what kinds of tools it took to complete the project. Be advised though that once my little saga here is complete you will find that quite a few of them shouldn't have been necessary for me to have, but that is for another time.

The next part will resume the more chronological style of describing my experiences. Stay tuned :)