MacLochlainns Weblog

Installing MySQL 5.5.9 (updated for MySQL 5.5.16 and 5.5.18) on Mac OS X was on my list but it finally made the top. I needed to write instructions for a class that I teach because asking students who own a Mac to install VMWare and Windows before MySQL seems an unnecessary burden. Especially when you can install it directly on Mac OS X.

You can download MySQL for the Mac OS X. I downloaded the Mac OS X, Version 10.6 (x86, 64-bit) version for this installation. There were a couple shell syntax tricks beyond the instructions and then you need to configure database. That’s required because the database is installed with an unsecured anonymous and root account. After the step-by-step installation instructions, you’ll find the configuration steps to enable you to access the MySQL Monitor from a terminal session. It also configures your $PATH environment to enable you to start and stop the MySQL Daemon.

1. After the download completes, open the file folder in the download directory.

2. The open file folder and it will look like the following. Launch the mysql-5.5.9-osx 10.6-x86_64.pkg file, which installs the product.

3. After launching the executable, you are now on the first page of the Install MySQL 5.5.9 installation application. Click the Continue button.

4. This page contains the instructions, you can pause to read them or continue with these instructions. Click the Continue button to proceed.

5. This page contains the General Public License (GPL). You agree or stop the installation. Click the Continue button to proceed.

6. The following overlay dialog contains your agreement. Click the Agree button to proceed.

7. There are fewer options in this installation than the Windows installation. While you can change the installation location, the software installs by default in the /usr/local/mysql directory. The installation requires that you have a mysql user account on the operating system, and you don’t need to do anything because one exists as part of the default Mac OS X installation. Click the Install button to proceed.

8. This dialog requires the system administrator’s password. Enter the valid password and click the OK button to proceed.

9. Depending on the system, this could take more than a minute. All you can do it wait.

10. This page tells you that you’ve completed the installation. Click the Close button to proceed.

11. This step requires that you return to the download folder, shown in Step #2 above. Launch the MySQLStartupItem.pkg and you’ll see the following MySQL Startup Item Installer dialog. Click the Continue button to proceed.

12. This page contains the instructions for the MySQL Startup software, you can pause to read them or continue with these instructions. Click the Continue button to proceed.

13. This page asks if you want to change the directory. I’d recommend you leave it as the default because it requires the System Administrator’s password to start and stop the database. It should also start for you every time you boot the machine. Click the Continue button to proceed.

14. Like Step #8, this dialog requires the system administrator’s password. Enter the valid password and click the OK button to proceed.

15. You could see a progress dialog box but generally it happens so fast you should land at the Installation was Successful dialog. Click the Close button to proceed.

16. This step requires that you return to the download folder, shown in Step #2 above. Launch the MySQL.prefPane and you’ll see the following MySQL Preferences dialog. Click the Install button to proceed.

17. Like Step #8 and #14, this dialog requires the system administrator’s password. Enter the valid password and click the OK button to proceed.

18. Don’t click in the automatic start button unless you’re sure. Otherwise, there’s a lot of cleanup to be able to return to this point and start or stop the server as required. This is the last screen, you should click the Start MySQL Server button to start MySQL. While installing MySQL 5.5.18 I discovered that the service menu is no longer installed in the Preferences, and you must start it manually.

You can start and stop the service by opening your System Preferences, where you’ll find them in the bottom Other row. If the intent was to have it start automatically, sometimes the permissions are incorrect. You’ll get the following error in MySQL 5.5.9 but not in MySQL 5.5.16 because the MySQL DMG file is fixed. You can skip the instructions until you get to Configure User’s Shell Environment below:

"/Library/StartupItems/MySQLCOM" has not been started because it does not have the proper security settings.

You can fix this by changing the permissions manually in a Terminal Session. Launch a Terminal Session from your Utilities folder inside your Applications folder.

Change directory to the /Library/StartupItems/MySQLCOM directory. Then, list the files. These commands should do the trick for you:

cd /Library/StartupItems/MySQLCOM
ls -al

If you see these permissions, you have problem because the group for startup files should be wheel not staff:

drwxr-xr-x  4 root  staff   136 Jan 20 13:46 .
drwxr-xr-x  4 root  wheel   136 Feb  9 21:11 ..
-rwxr-xr-x  1 root  staff  1300 Jan 20 13:46 MySQLCOM
-rw-r--r--  1 root  staff   469 Jan 20 13:46 StartupParameters.plist

You can change the files with this command:

sudo chown root:wheel *

However, now you need to step up the directory tree one level to /Library/StartupItems, and change the ownership of:

drwxr-xr-x   4 root  staff   136 Jan 20 13:46 MySQLCOM

There are two commands to do this. The first changes directory by moving to the parent directory in the hierarchy (the parent directory is represented by two dots). The second changes the group ownership of the MySQLCOM directory.

cd ..
sudo chown root:wheel MySQLCOM

Now you need to configure your shell environment and harden the database. Hardening means securing accounts with passwords. They’re covered in the next two sections.

Configure User’s Shell Environment

Assuming you accepted the defaults, you should be able to copy the required instructions directly into a .bash_login file if one exists. Unless you’ve created one before there won’t be a file. Mac OS X doesn’t automatically create the file. If you don’t have the file, you can create one with the following syntax:

touch .bash_login

You can open the file with the vi editor or a text editor of your choice. MySQL 5.5.9 installed in /usr/local/mysql, which has changed to /usr/local/mysql-version as noted below. You can copy the following contents into the file for MySQL 5.5.16 but will need to change the file for earlier or later releases (added sudo per Don McArthur’s comment):

# Set the MySQL Home environment variable to point to the root directory of the MySQL installation.
export set MYSQL_HOME=/usr/local/mysql-5.5.16-osx10.6-x86_64
 
# Add the /bin directory from the MYSQL_HOME location into your $PATH environment variable.
export set PATH=$PATH:$MYSQL_HOME/bin
 
# Create aliases that make it easier for you to manually start and stop the MySQL Daemon.
alias mysqlstart="sudo /Library/StartupItems/MySQLCOM start"
alias mysqlstop="sudo /Library/StartupItems/MySQLCOM stop"

Changes between MySQL 5.5.16 and 5.5.18 introduce a new directory structure, you need to use the following in the .bash_login file (added sudo and status per Don McArthur’s comments):

# Set the MySQL Home environment variable to point to the root directory of the MySQL installation.
export set MYSQL_HOME=/usr/local/mysql-5.5.18-osx10.6-x86_64
 
# Add the /bin directory from the MYSQL_HOME location into your $PATH environment variable.
export set PATH=$PATH:$MYSQL_HOME/bin
 
# Create aliases that make it easier for you to manually start and stop the MySQL Daemon.
alias mysqlstart="sudo /Library/StartupItems/MySQLCOM/MySQLCOM start"
alias mysqlstop="sudo /Library/StartupItems/MySQLCOM/MySQLCOM stop"
alias mysqlstatus="ps aux | grep mysql | grep -v grep"

As pointed out by Shashank’s comment, you should now use the following aliases:

alias mysqlstart='sudo /usr/local/mysql/support-files/mysql.server start'
alias mysqlstop='sudo /usr/local/mysql/support-files/mysql.server stop'

You need to save the file and close and restart a new Terminal session to place these environment variables in scope. You could also run the following to put them in scope without closing and opening the terminal:

. ./.bash_login

The preceding command sources the environment file into active memory. This should configure your environment. After restarting the shell, you should be able to run this command to confirm the new environment:

which -a mysql

It should return:

/usr/local/mysql-5.5.16-osx10.6-x86_64/bin/mysql

Secure the Database

This is presently necessary because of the different file structure in a Mac OS X MySQL install, which disables the mysql_secure_installation file from running successfully. You can manually edit the file or follow these steps.

You need to connect to the database as the privileged super user, root user. This is simple because the installation doesn’t set any passwords. You open another Terminal session to make these changes or you could install MyPHPAdmin or MySQL Workbench. The tools work as well in fixing the majority of issues.

mysql -uroot

Once connected to the database as the root user, you can confirm that passwords aren’t set and an insecure anonymous user account has been previously configured. You do that by connecting to the mysql database, which is the database catalog for MySQL. You do that by running the following command:

USE mysql;

You can query the result set with the following query:

SELECT USER, password, host FROM USER\G

You should see the following output plus the user’s name preceding the MacPro (or iMac.local) host name value:

*************************** 1. row ***************************
    user: root
password: 
    host: localhost
*************************** 2. row ***************************
    user: root
password: 
    host: MacPro.local
*************************** 3. row ***************************
    user: root
password: 
    host: 127.0.0.1
*************************** 4. row ***************************
    user: root
password: 
    host: ::1
*************************** 5. row ***************************
    user: 
password: 
    host: localhost
*************************** 6. row ***************************
    user: 
password: 
    host: MacPro.local

You now need to change the password for the root user. I would suggest that you do this with the SQL command rather than a direct update against the data dictionary tables. The syntax to fix the root user account require you enter the user name, an @ symbol, and complete host values, like:

SET PASSWORD FOR 'root'@'localhost' = password('cangetin');
SET PASSWORD FOR 'root'@'MacPro.local' = password('cangetin');
SET PASSWORD FOR 'root'@'127.0.0.1' = password('cangetin');
SET PASSWORD FOR 'root'@'::1' = password('cangetin');

You should be able to drop both anonymous user rows with the following syntax, but I did encounter a problem. Assuming you may likewise encounter the problem, the fix follows the first commands you should try:

DROP USER ''@'localhost';
DROP USER ''@'MacPro.local';

If either of the anonymous accounts remain in the USER table, you can manually drop them from the database catalog. This syntax will get rid of them:

DELETE FROM USER WHERE LENGTH(USER) = 0;

You’ve completed the configuration and can now type quit; to exit the MySQL Monitor. To reconnect, you’ll now need a password, like this:

mysql -uroot -pcangetin

Also, don’t forget to use a real password. The one shown here is trivial, which means easy to hack. Use something that others might not guess.

Configure my.cnf file

You can copy one of the sample configuration files as a starting point (as provided by Don McArthur’s comment):

sudo cp /usr/local/mysql/support-files/my-huge.cnf /etc/my.cnf

Starting and Stopping the Database

You can manually start the database server with the following command, which you defined as aliases in your .bash_login shell script:

mysqlstart

Stopping it is also straightforward, you do this:

mysqlstop

You can check it’s status with this command:

mysqlstatus