Compiling and Installing MySQL 5 on Mac OS X (Snow) Leopard

January 24, 2011

Contents

Introduction

Building MySQL from source allows complete control over the installation configuration as well as giving you a better sense of how to administer the server. These instructions will work with MySQL 5.1, as well as MySQL 5.0. See this document to build MySQL 5.5. You can build either a 1-way 32-bit (Leopard) or 1-way 64-bit (Snow Leopard) binary for the architecture you are compiling on, or cross compile a 32/64-bit 4-way universal binary for both PowerPC and x86 architectures.

If you are installing the pre-compiled MySQL binary package (DMG) you can skip just about everything in this document. Just unpack and install the DMG package and and start with the Start MySQL Database Server and Check for Errors section of this document, except go to the data database directory instead of the var directory to check for errors. You may also have to preface some of the commands with sudo, as the permissions structure is somewhat different with the binary package than the permissions structure used in this compiled version of MySQL.

This document assumes that the OS X developer tools (XCode 3.13 - Leopard) or (XCode 3.2.1 - Snow Leopard) are installed, that you are logged in as an administrator and using the BASH shell.

These instructions also assume that you are installing MySQL from scratch for the first time. If in the past, you have installed MySQL from source, you may not have to perform some of the steps such as creating the MySQL source or socket directories.

I have tried to make this document as concise and direct to the point as possible. It is helpful to have some knowledge of the basic UNIX commands and permissions, and the GNU build system.

Download the source code and unpack into source directory

Download the MySQL source code

Download the MySQL Generally Available Release (GA) source code from http://dev.mysql.com/downloads/mysql/. Select the Source Code platform and download the Generic Linux (Architecture Independent) Compressed TAR archive (tar.gz). If you want to download the MySQL 5.0 source, you must go to the archives  and select Source and other files.

Create the source code directory and unpack

Launch Terminal and execute the following commands where adminID is your administrator ID. Your administrator ID is the name you used to login to OS X with administrator privileges. OS X refers to this name as a short name. Under UNIX this name is called your user name or login name. Execute the whoami command to find out your user name.

# Create a /usr/local/src directory if it doesn't already exist
cd /usr/local
sudo mkdir src
sudo chown adminID src
sudo chgrp admin src
chmod 750 src

# Change to the source directory and unpack (Substitute appropriate version number)
cd src
tar -xvzf ~/Downloads/mysql-5.0.91.tar.gz

# If Safari has already attempted to open the archive after downloading it, you may have to do this instead
tar -xvf ~/Downloads/mysql-5.0.91.tar

# Consider changing the owner or group of the /usr/local directory to your admin id or the admin group. It will make administration easier as you don't have to do so many sudo's or run as root.

# Some of the commands used in this document require the BASH shell. All versions of OS X from version 10.3 on up default to using BASH. However, if you upgraded OS X from an earlier version and imported your home directory, you may still be running TCSH. To find out what default login shell you are running and change it, go to the System Preferences Accounts pane. If the pane is locked, unlock it. Control Click on your account name and a contextual menu will appear. Click on Advanced Options. You will then be presented with a dialog where you can change the default login shell to whatever you want. You can also use the dscl utility to read and change the default login shell. Execute the following to read the directory service properties of your id:
dscl . read /Users/YourID.

# To find out what YourID is, execute the whoami command.
whoami

Create MySQL Group and User

It is recommended that you run the mysql server as its own user such as _mysql. On Leopard, this id should already be created for you. However, if _mysql does not exist, you must use the dscl command line utility, as the netinfo GUI utility for creating users and groups no longer exists. The dscl utility can be used either in command line or interactive mode, but I find the interactive mode to be a little faster to use. Be extremely careful when inside the dscl utility as root. If you make a mistake with the create command, you can generally reissue the command to overwrite it. A unique id must be created for both the mysql user and group. You can find out what id's are already in use by issuing the list command. The id number 74 is recommended for both the _mysql group and user.

# When launching dscl make sure you use the period (localhost)
sudo dscl .

# Change directories to Groups
cd Groups

# Find out what id's are already in use
list . PrimaryGroupID

# Create the _mysql group
create _mysql

# Create mysql alias for _mysql group
append _mysql RecordName mysql

# Create the group id number, where <GroupIDNUM> is an id number not already in use
create _mysql PrimaryGroupID <GroupIDNUM>

# Create a long name for the group id
create _mysql RealName "MySQL Group"

# Change directories to users
cd ../Users

# List user id's already in use
list . UniqueID

# Create the _mysql user
create _mysql

# Create mysql alias for the _mysql user
append _mysql RecordName mysql

# Create the long name
create _mysql RealName "MySQL User"

# Create the user id number, where <UserIDNUM> is an id number not already in use
create _mysql UniqueID <UserIDNUM>

# Create the primary group id number, where <GroupIDNUM> is the id number used when creating the group
create _mysql PrimaryGroupID <GroupIDNUM>

# Create the default shell
create _mysql UserShell /usr/bin/false

# Exit dscl
quit

Create the MySQL Socket Directory

# Create and set permissions for /var/mysql directory
cd /var
sudo mkdir mysql
sudo chgrp mysql mysql
sudo chmod 775 mysql

Build, Test and Configure MySQL

You may want to build mysql with different options, such as a another default charset and collation.
Use the ./configure --help command to get a complete list of build time options.
When building the make file (configure), select either a 1-way 32/64-bit build for a single architecture, or a 4-way 32/64-bit Universal build to support multiple architectures.

Execute the following commands in Terminal:

The OS X developer tools must be installed.

# Change to the source directory (Substitute appropriate version number)
cd /usr/local/src/mysql-5.0.91

# Configure the make file for the architecture you are compiling on (1-Way 32-bit Binary for Leopard) or (1 Way 64-bit Binary for Snow Leopard)
CFLAGS="-O3 -fno-omit-frame-pointer" CXXFLAGS="-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql --with-extra-charsets=complex --enable-thread-safe-client --enable-local-infile --enable-shared --with-unix-socket-path=/var/mysql/mysql.sock --with-charset=latin1 --with-collation=latin1_general_ci --with-mysqld-user=_mysql

# Configure the make file to compile a 32/64-bit 4-Way Universal Binary
# Note: The 4-Way Universal Binary build takes a long time.
# You may want edit this command and delete the architectures you don't need.
CFLAGS="-arch i386 -arch ppc -arch ppc64 -arch x86_64 -O3 -fno-omit-frame-pointer" CXXFLAGS="-arch i386 -arch ppc -arch ppc64 -arch x86_64 -O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql --disable-dependency-tracking --with-extra-charsets=complex --enable-thread-safe-client --enable-local-infile --enable-shared --with-unix-socket-path=/var/mysql/mysql.sock --with-charset=latin1 --with-collation=latin1_general_ci --with-mysqld-user=_mysql

# Build the executables
make

# Test the build
cd mysql-test
perl mysql-test-run.pl
or
perl mysql-test-run.pl --force /* Run without stopping for errors

# Keep in mind that if you do get an error when testing, it will usually be for some isolated test case that doesn't affect most installations.

# Create mysql binaries and database directory (Substitute appropriate version number)
sudo mkdir /usr/local/mysql-5.0.91

# Create a symbolic link to the installation directory
sudo ln -sfhv /usr/local/mysql-5.0.91 /usr/local/mysql

# Copy the installation to the default /usr/local/mysql directory
cd ..
sudo make install

# Copy the configuration file to /etc
sudo cp support-files/my-medium.cnf /etc/my.cnf
cd /etc
sudo chown <adminID> my.cnf
sudo chgrp mysql my.cnf
chmod 640 my.cnf

# Configure MySQL installation directory permissions
# This permissions structure tends to be geared a bit more toward a development environment than a production environment, as to make things more convenient for the developer.
# If you are running in production environment, you may want to use the permissions setup specified in the mysql UNIX installation documentation where everything is owned by root.
# If you overwrote your existing installation, don't perform this step,
# as the recursive chown's will change the permissions on your database directory to something other than root.
cd /usr/local
sudo chown -RH <adminID> mysql
sudo chgrp -RH admin mysql
sudo chgrp _mysql mysql
sudo chmod 775 mysql
sudo chmod -h 775 mysql
sudo chgrp -h _mysql mysql
sudo chown -h <adminID> mysql

# Create the database directory and initialize the grant tables
cd mysql
sudo bin/mysql_install_db --user=_mysql

# Note: If you are running MySQL 5.1 and receive the error - [ERROR] /usr/local/mysql/libexec/mysqld: unknown option '--skip-federated', you may have an old /etc/my.cnf configuration file. Edit my.cnf and delete or comment out the skip-federated option.

# You may also want to add the lower_case_table_names = 2 option to /etc/my.cnf to keep the server from generating warnings in the log file about lower case table names. Add this option under [mysqld].

Start the MySQL database server and check for errors and warnings

Use the MySQL utility mysqld_safe to start the server

Execute the following commands in Terminal:

# Start the server - Make sure to stop any MySQL servers already running
cd /usr/local/mysql
sudo -b bin/mysqld_safe

# Check for startup errors and warnings
# Change to user root
sudo -s

# Change to the database directory
cd var

# List the directory
ls

# Dump the error log
cat <ComputerName>.err

# Exit from user root
exit

# To stop the server ...
cd ..
bin/mysqladmin -u root -p shutdown

Add MySQL Utilities Directory to the shell path

Use your favorite editor to edit the bash shell .profile startup script in your home directory.

Execute the following commands in Terminal:

# Change to the home directory
cd

# Create .profile if it doesn't exist
touch .profile

# Edit the shell startup script with your favorite editor
nano .profile

# Add the MySQL directory to the path and exit the editor
export PATH=$PATH:/usr/local/mysql/bin

Secure the MySQL Installation

Use the MySQL utility to set the database root password

Execute the following commands in Terminal:

# If you stopped the server in the last step, restart it
sudo -b mysqld_safe

# Set the database root password
# It is strongly recommended that you take the default options (hit return)
mysql_secure_installation

# Login to your mysql server with mysql command line tool
mysql -u root -p

# Add the -h hostname option if you are operating remotely.
# Use the quit command to exit the mysql command line tool.

# Consider uncommenting the skip-networking option in my.cnf if you don't need to reach the mysql server from the network. If you use this option everything will have to reach the database server through the socket.
sudo nano /etc/my.cnf

Starting MySQL at Boot Time

The easiest way to start MySQL at boot time is to download one of the package format (.dmg) versions of MySQL for OS X and run the MySQLStartupItem.pkg, which copies the startup scripts to /Library/StartupItems as part of its installation. Copy the preference pane to either /Library/PreferencePanes or ~/Library/PreferencePanes.

Once you have installed the startup scripts you must create a symbolic link from /usr/local/mysql/support-files/ to /usr/local/mysql/share/mysql/. The preference pane and boot time startup script expect the mysql startup script mysql.server to be located in /usr/local/mysql/suport-files. A data directory is also expected.

# Change directories into the mysql directory
cd /usr/local/mysql

# Create the symbolic link
ln -s share/mysql support-files

# Create the symbolic link to the data directory
sudo ln -s var data

Troubleshooting

If things are not running as expected, first check the system console log and the mysql log for errors in /usr/local/mysql/var/hostname.err.

If ./configure is not working, make sure you downloaded the source code release and not the binary release.

Consider hard coding the host name in my.cnf. Sometimes, at boot time, mysql will start to execute before the network host name has been fixed by the operating system. In the past, mysql could hang if you started it at boot time before networking was initialized and then shut it down later after networking had come up. Trouble can also occur if you start mysql from the command line and then try to stop it from the preference pane. Add the following to my.cnf under [mysqld], where hostname is the name of the host:
pid-file = /usr/local/mysql/var/hostname.pid
If you want to keep things consistent, it's also not a bad idea to add:
log-error = /usr/local/mysql/var/hostname.err as well.

Keep in mind that the packaged version of mysql and the source version of mysql keep the database files in different directories. The source version in /usr/local/mysql/var and the packaged version in /usr/local/mysql/data.

The packaged version of mysql creates the socket in /tmp where as these instructions create the socket at the prescribed OS X server location of /var/mysql. If you are running phpMyAdmin you may have to change the socket location in the config.inc.php configuration file to look something like this: $cfg['Servers'][$i]['socket'] = '/var/mysql/mysql.sock';.

When using the Apple supplied PHP with MySQL 5.1, you may get a message from phpMyAdmin "Your PHP MySQL library version 5.0.45 differs from your MySQL server version 5.1.47. This may cause unpredictable behavior." Apparently, the Apple supplied PHP has been linked with the MySQL 5.0.x libraries. Either recompile PHP with the MySQL 5.1.x libraries or use MySQL 5.0.x.

Useful Links

Sponsors

Did you find this page useful? Did it save you some time? Please visit our sponsor Cyrilla's Artistic Crochet and purchase a fine handcrafted product. You can find beautiful products for babies and children, bag holders to recycle your plastic bags, cell phone pouches, finger puppets, faux food, kitchen towels, scarves and wine gift bags. All products are handmade in the U.S.A.

Feedback

Please let me know if you found this page useful or have any other comments. Thank you to all who have mailed in your suggestions. Your feedback has helped make this document more accurate and complete.

Comments:

Your Email (Optional):
Include an email address if you would like a reply.