Friday, July 3, 2009

Install MySQL 5 on OS X Leopard Server

Introduction


I always prefer to manage my own MySQL installation on OS X Server rather than use the Apple built-in one. Doing so ensures that I use the version of MySQL that I want and I upgrade when it suits me. This article outlines the steps to install MySQL on OS X 10.5 Leopard Server and disable the Apple pre-installed MySQL binaries. This article is an update to an older article I did years ago for older version OS X Server.

Install MySQL


Download the version of MySQL you want from mysql.com. You will want the Mac OS X (package format) downloads. Choose the OS X 10.5 package appropriate to your architecture. For this article, I have chosen mysql-5.0.81-osx10.5-x86.dmg.
Run the mysql package installer.
Do NOT run the MySQLStartupItem installer since using a launchd configuration is more appropriate for Leopard and later versions of OS X.

Configure MySQL 'my.cnf' Before First-time Start


MySQL does not run in an optimal way with default configuration settings. It is very important to configure MySQL appropriate to the planned memory usage.
Also, some options, such as innodb_log_file_size cannot be changed after MySQL has initialized innodb files on disk without going through a special complicated procedure, so that is why we will configure options before we start MySQL for the first time to make your life easy.
Don't worry about perfection. Any configuration you do will probably be magnitudes better than the default settings. So, do not skip this step!
Configuring MySQL is easy. You simply put all the configuration options in a properties file at /etc/my.cnf which is automatically read by MySQL at startup. The primary things I am concerned with when I create a MySQL configuration file are:

  • Amount of real memory I want available to MySQL

  • Use of InnoDB transactional database engine instead of default MyISAM engine. The default is suitable for logging and huge lookup tables. InnoDB provides transactions and ability to rollback a transaction when one statement fails. InnoDB is ideal for WebObjects development. MyISAM is not!


On the machine that I am configuring for this article, which is a Mac Mini used for demonstration of apps to clients, I have 2GB RAM, so I am going to give about 512MB to MySQL.
Here is the configuration for this scenario. Use this as a guide for minimal settings for a new MySQL installation. This minimal configuration should work on any machine with 2GB+ RAM

# Basic minimal /etc/my.cnf config for a 2GB RAM Mac Mini
# running MySQL 5.0+ and WebObjects on OS X
# The following settings result in about 512MB of Real Memory usage by MySQL

[mysqld]
# ======= BEGIN GLOBAL SETTINGS =============

socket = /tmp/mysql.sock

# This variable should be as big as the biggest BLOB value we plan to use
max_allowed_packet = 20M

# The max number of opened tables for all threads
table_cache = 256

# This is buffer allocated by threads for sorting. Improves performance
# of ORDER BY and GROUP BY operations
sort_buffer_size = 16M

# This is a buffer used for sequential table scans (I guess where an index
# is not used for a search ... may help scans that create new indexes aswell KK 05092005)
read_buffer_size = 20M

# Number of threads to keep available for use
# Not really important for our situation where we use applications with persistent connections
# For a dedicated mysql server, 8 at least is recommended.
thread_cache_size = 4

# No query cache since we already use EOF in WebObjects
query_cache_type = 0
query_cache_size = 0

# New tables are innodb unless specified otherwise (Before MySQL 4.1.2 use default-table-type)
default-storage-engine=innodb


# ======= BEGIN MYISAM SETTINGS ===================================================

# Size of the Key Buffer, used to cache index blocks for MyISAM tables.
# Do not set it larger than 30% of your available memory, as some memory
# is also required by the OS to cache rows. Even if you're not using
# MyISAM tables, you should still set it to 8-64M as it will also be
# used for internal temporary disk tables.
# Ideally key_buffer (aka key_buffer_size) should be as large as we can afford and
# ideally should be equal to the sum of all .MYI files' sizes.
# This is one of the most important performance tuning variables for queries that use indexes
key_buffer_size = 64M

# Buffer for REPAIR TABLE or CREATE INDEX operations on MyISAM tables
myisam_sort_buffer_size = 16M

# ======= BEGIN INNODB SETTINGS ===================================================
# Create a separate idb file per table
innodb_file_per_table

# Buffer for innodb index and table cache performance - important!
innodb_buffer_pool_size = 364M

# MySQL creates 2 files on disk using this setting - important!
# Changing this after the first start requires a special procedure!
innodb_log_file_size = 128M

innodb_log_buffer_size = 8M

# Rollback the full transaction on timeout. Need this in MySQL 5.0+
innodb_rollback_on_timeout

See the mysql documentation for lots of additional features you can enable with the my.cnf file entries.
Put the above into a file at /etc/my.cnf

Configure MySQL to Always Run with launchd


Next let's install a launchd task that will ensure mysql is always running.
Create a text file named 'com.mysql.MySQL.plist' with the following contents:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
<key>Label</key>
<string>com.mysql.MySQL</string>
<key>OnDemand</key>
<false/>
<key>ProgramArguments</key>
<array>
<string>/usr/local/mysql/bin/mysqld_safe</string>
</array>
<key>ServiceDescription</key>
<string>Mysql Database Server</string>
<key>UserName</key>
<string>mysql</string>
<key>WorkingDirectory</key>
<string>/usr/local/mysql</string>
</dict>
</plist>

Put that text file in /Library/LaunchDaemons directory. (You will need sudo privileges to do this)
Then simply load this launchd task using the command
launchctl load com.mysql.MySQL.plist

MySQL should now have started.

MySQL Security Tasks


The default installation has a root user with no password, so we have to set a password for root to protect our server.
Type this in the terminal:
cd /usr/local/mysql/bin

If this is first time install, log in as root and set a password

./mysql -u root -p

Just hit return when prompted for a password the first time since no root password has been set.
At the mysql prompt, type the following replacing password with your password:

grant all on *.* to root@localhost identified by 'password' with grant option;

now type:

exit

and test the login again with the password

./mysql -u root -p

enter the password you set for the mysql root user when prompted.
Next we need to delete the 'anonymous' user that usually exists by default in MySQL
After logging in as root, do the following

use mysql;
delete from user where user = '';
flush privileges;


OS X Server Binary Path Configuration


The next thing we need to do is configure our command path so that we refer to the mysql binaries that we installed and not the preinstalled Apple binaries.
Using pico or similar, open and edit /etc/profile so that the path variable has /usr/local/mysql/bin pre-pended as the first element of the path. Simply add the following lines to the end of the existing file:


PATH="/usr/local/mysql/bin:"${PATH}
export PATH



For added robustness, you may delete all Apple preinstalled mysql binaries like this:

$ cd /usr/bin
$ sudo rm mysql*
$ sudo rm myisam*


And that is it. You are done and mysql is running and set to automatically run at all times.

No comments:

Post a Comment