Jason Fox

I found after installing MySQL 5.1 that the “fast index creation” feature is not utilized by the default InnoDB storage engine that comes with MySQL 5.1. This is the main reason why I went through the trouble of installing MySQL 5.1 in the first place. :-/ However, have no fear, the InnoDB plugin is here! (ok, that was lame, anyways…)

The InnoDB plugin is a replacement InnoDB storage engine developed with the help of Sun, Google and Percona. It supposedly provides better overall performance compared with the default InnoDB storage engine that comes with MySQL and it adds a few new features that I want such as “fast index creation.”

If you’d like to try out the InnoDB plugin you’ll have to recompile MySQL as a pre-compiled binary drop-in is not provided yet for OSX. So, here are the steps for compiling and installing MySQL 5.1 with the InnoDB plugin while maintaining a working MySQL 5.0 installation on your computer.

1. Download the MySQL 5.1 source distribution (v.5.1.43 for me; Change Platform to: Source Code; Scroll down to the last distribution: Generic Linux (Architecture Independent))
2. Download the InnoDB plugin source distribution (v.1.0.6 for me)
3. Create a new install directory for MySQL 5.1: sudo mkdir /usr/local/mysql-5.1.43
4. Change ownership of the install directory to the mysql user: sudo chown -R mysql /usr/local/mysql-5.1.43
5. Extract the MySQL source: tar -zxf mysql-5.1.43.tar.gz
6. Extract the InnoDB plugin source: tar -zxf innodb-1.0.6.tar.gz
7. Change into the MySQL source directory for storage engines: cd mysql-5.1.37/storage
8. Remove the version of the InnoDB plugin that MySQL comes with: rm -fr innobase
9. Replace the InnoDB plugin with the one you downloaded: mv innodb-1.0.6 innobase
10. Change into the MySQL source root: cd ..
11. Create the make file: ./configure --prefix=/usr/local/mysql-5.1.43 --with-extra-charsets=complex --enable-thread-safe-client --enable-local-infile --enable-shared --with-plugins=innobase --without-plugin-innodb_plugin --with-federated-storage-engine; note, I’m also including the Federated Storage Engine here in case you find a use for it in the future; NOTE: You will receive the following warning, however, IT DID WORK, so just ignore it:

    configure: WARNING: unrecognized options: --without-plugin-innodb_plugin, --with-federated-storage-engine

12. Compile MySQL: make; takes about 10 minutes or so
13. Install MySQL: sudo make install
14. Change into your install directory: cd /usr/local/mysql-5.1.43
15. Create the MySQL database: sudo ./bin/mysql_install_db --user=mysql
16. Change ownership of the var directory to the mysql user: sudo chown -R mysql ./var

For more information see…

If you get stuck you can check out the following resources that I used to complete this process:

• InnoDB Plugin Installation Guide
• Installing MySQL on OSX Snow Leopard or the non-Snow Leopard version
• MySQL 5.1 Installation Guide

I am writing a few new applications and decided to upgrade to MySQL 5.1 from MySQL 5.0.  However, I have several legacy applications that still require MySQL 5.0.  So, I set out to get them both up and running side-by-side so I could forge a new path forward to 5.1 while not abandoning my 5.0 apps.

Installing MySQL 5.1

1. Download the source distribution of the latest 5.1 (for me it was 5.1.42)
2. Decompress it and extract it into a directory
3. Create a new home for the 5.1 files, I put it in /usr/local/mysql-5.1.42
4. Next cd into the directory where you extracted the files
5. Run the following command substituting your install directory created in step 3 above: ./configure --prefix=/usr/local/mysql-5.1.42 --with-extra-charsets=complex --enable-thread-safe-client --enable-local-infile --enable-shared --with-plugins=innobase
6. Compile the code by running: make; this will take a few minutes (10ish?)
7. Install into your chosen directory with: sudo make install
8. Next cd into your install directory: cd /usr/local/mysql-5.1.42
9. Create the mysql database with: sudo ./bin/mysql_install_db --user=mysql
10. Change ownership of the var directory (this is the data directory): sudo chown -R mysql ./var
11. Change ownership of the install directory: sudo chown -R mysql /usr/local/mysql-5.1.42
12. Create a socket file in your install directory: sudo -u mysql touch mysql.sock; mysql may create this itself on start-up, but, I created it ahead of time
13. Start the server: sudo -u mysql ./libexec/mysqld --basedir=/usr/local/mysql-5.1.42 --port=6666 --socket=/usr/local/mysql-5.1.42/mysql.sock --user=mysql

Running MySQL 5.1 and 5.0

1. Edit your my.cnf file and explicitly add: socket=/tmp/mysql.socket; if you do not do this when you start the 5.1 server the socket will be deleted
2. Create a new my.cnf file in your 5.1 installation root; for me: */usr/local/mysql-5.1.42/; your my.cnf file should look like this:

       [mysqld]
       basedir=/usr/local/mysql-5.1.42/
       port=6666
       socket=/tmp/mysql.5.1.socket
       user=mysql
   

3. Change ownership of the my.cnf file to the mysql user: sudo chown -R mysql my.cnf
4. Create a plist file for launchd called something like: com.mysql.mysqld.5.1.plist in /Library/LaunchDaemons; your plist file should look like this:

       <?xml version="1.0" encoding="UTF-8"?>
       <!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
       <plist version="1.0">
           <dict>
               <key>KeepAlive</key>
               <true />
               <key>Label</key>
               <string>com.mysql.mysqld.5.1</string>
               <key>ProgramArguments</key>
               <array>
                   <string>/usr/local/mysql-5.1.42/bin/mysqld_safe</string>
                   <string>--defaults-file=/usr/local/mysql-5.1.42/my.cnf</string>
               </array>
               <key>RunAtLoad</key>
               <true />
               <key>UserName</key>
               <string>mysql</string>
               <key>WorkingDirectory</key>
               <string>/usr/local/mysql-5.1.42</string>
           </dict>
       </plist>
   

5. Change ownership of the plist file to root like so: sudo chown root /Library/LaunchDaemons/com.mysql.mysqld.5.1.plist
6. Test it out by launching mysql: sudo launchctl load -w /Library/LaunchDaemons/com.mysql.mysqld.5.1.plist; The next time you start up both MySQL 5.0 and 5.1 should be started up. Check the /tmp directory after start-up to ensure both sockets were created successfully.

A few notes about this process

• When you install from source the layout of the installation is different than when you install from the binary distribution, i.e., the data direectory is var not data and the server binary is in libexec not bin, etc. For more information check out this article.
• When starting a second instance of mysqld you must specify different values for the following options: port, socket, pid-file, tmpdir, datadir. However, if you specify the basedir you only need to explicitly set values for socket and port as we did in the last step above. For more information check out this article.
• When you start the mysql client you must specify the socket and port like so: /usr/local/mysql-5.1.42/bin/mysql -P 6666 -S /usr/local/mysql-5.1.42/mysql.sock -u root; Alternatively you can create a hidden .my.cnf file in your home directory and specify which server should be connected to by default when you run the mysql client. Your .my.cnf file should look like this:

      [client]
      port=6666
      socket=/tmp/mysql.5.1.socket
  

Working with Rails

    # Main database connection for the media_service;
    # Uses MySQL 5.1
    development:
      adapter: mysql
      encoding: utf8
      database: my_development
      username: foo
      password: bar
      socket: /tmp/mysql.5.1.socket
      port: 6666