Installing MySQL 5.1.30 using MOCA

MySQL 5.1 is GA with the 5.1.30 Release

MySQL 5.1.30 is the GA version of the MySQL 5.1 software version. The 5.1 release has some key features that users are going to like which include:

• Table and Index Partitioning
• Row-based and Mixed Replication
• The embedded libmysqld library
  
• Improved XML features with additional XPath support
  
• Event Scheduler
• Upgraded Advisors for the MySQL Enterprise Monitor
• mysqlslap - a load emulator is pat of the MySQL distribution software
• Server Log Tables - have more flexbility and more dynamic capability
  
• MySQL Cluster - is a separate distribution from the MySQL 5.1 distribution. MySQL Cluster 6.2 and 6.3 can be installed separately.
• The mysql_upgrade program offers a much easier upgrade process.
  

MySQL Optimal Configuration Architecture

The MySQL Optimial Configuration Architecture (MOCA) is a set of guidelines and best practices I developed to achieve the following goals:

• Minimize points for failure.
• Support multiple software installs on the same machine.
• Support multiple database servers on the same machine.
• Separate software files, data files, binary logs and administration files.
• Reduce down time.
• Reduce administration costs.
• Provide a consistent configuration platform across an enterprise.
  
• Separate database configuration files from operating system configuration files.
  

MOCA should not be used as a first time installation. Do not use MOCA until you are very comfortable with configuring MySQL and have a solid understanding of the operating system you are working with. The default MySQL installation is designed to take minimal resources and to install very easily. MOCA is designed to be a robust flexible install for MySQL production environments.

Installing MySQL: Linux, Solaris, MacOS

One of the benefits of the MOCA installation is this set of guidelines provides a very consistent install on different Linux and Unix operating systems. There are a few subtle differences such as how to add a new user on MacOS versus adding a user on Linux/Unix. This installation will demonstrate a MOCA installation on the Mac OS. These steps can also be used to perform an installation on Linux or Unix system.

Installing MySQL 5.1.30 on Mac OS 10.5

These instructions will walk you through a 5.1.30 installation of MySQL using MOCA on the Mac OS 10.5. These instructions can be followed for any MySQL 5.1.xx install. The Mac OS installation will be similar to a Solaris or Linux installation. Go to the MySQL documentation to review instructions if you are using a different operating system.

Review my blog entry "The MySQL Optimial Configuration Architecture" for more details on MOCA.

Be careful

Make sure you understand exactly what commands you are running and where you are running these commands from. If you are new to Unix/Linux be extremely careful before executing the following steps. If you are not sure of a command do not run it without reviewing the documentation. Use the man pages or look at other documentation to verify what you are doing. These steps are an overview to show the main steps for a simple installation. Spend sufficient time reviewing each of these steps and make appropriate changes for your environment.

Installation Summary
The following list outlines the steps performed to install MySQL on Mac OS. I recommend reading the blog "Top Ten Things to do before installing MySQL" further down on this site before beginning this installation.

1. Setup the mysql operating system user id. Define the user's home directory, default shell, password, etc. It may be easier to delete the preinstalled mysql user and recreate it.
   
2. Set up the operating system and directory structures (physical storage) for running MySQL. Change all directories where MySQL files will reside to be owned by the mysql user.
   
3. Login in as the mysql operating system user and perform the installation and configuration as the mysql user.
   
4. Install the MySQL software in the MYSQL_HOME directory.
5. Create a startup file (my.cnf). Set up the locations for all mysql database files.
   
6. Run the mysql_install_db script to set up the MySQL data directory.
7. Start the MySQL server instance by running mysqld_safe.
8. Test the MySQL Server instance by running the perl script mysql-test-run.pl.
9. Secure the mysql password environment with the mysql_secure_installation script.
10. Login in using the mysql client and verify the installation. Review the data files, log files, binary log, error logs, etc.
11. Define a server startup method. The script mysql.server is a likely option.
12. Define a backup and recovery strategy. Test your back and recovery processes.
13. Have fun with MySQL. :)

Installation Environment
Hardware: MacBook Pro laptop , 2GB of memory, dual core
Operating System: Mac OS 10.5.5
Disk: 160 GB
MySQL: RC 5.1.30 for Mac OS X (TAR packages)

Preinstallation steps:

Check to see if there is a previous MySQL installation. A MySQL installation often comes with a MacOS, Linux and Solaris system. Verify an older version of MySQL is not currently running. Remove all previous mysql files or use RPMs to deinstall any previous MySQL installation for Unix platforms.
Remove any mysql files in the following directories (especially any my.cnf files):
/etc
/etc/mysql
/usr/local/mysql
~mysql

Verify the setup of the mysql operating system user account. A mysql user may already exist in the /etc/passwd file. It's probably easiest to drop and recreate the mysql user. Mac OS did something weird with the mysql user in 10.5 by naming it _mysql. Dropping the user and recreating is as mysql cleans up a lot of little issues. Go to System Preferences | Accounts and drop the mysql user and recreate it. Make sure and set the proper group name, login shell, etc. for the new mysql user account.

Dependent on the default shell you pick for the mysql user (bash, ksh, etc) set the following environmental variables in the mysql user's profile file (ex: .bash_profile).

export MYSQL_BASE=/opt/mysql
export MYSQL_HOME=$MYSQL_BASE/5.1.30
PATH=$PATH:$MYSQL_HOME/bin

Define the directory structure for the MySQL environment:
Bring up a terminal window as the root or administrator userid.

Use the mkdir command to create the directories listed below. I have added little notes about the purpose of each of these directories. Do not include the notes when you type the commands.
Create the directory /opt/mysql for placing the MySQL software.
# mkdir -p /opt/mysql/software

Go to http://dev.mysql.com and choose the MySQL softwware to download (mysql-5.1.30-osx10.4-i686.tar.gz). Place this zip file in the /opt/mysql directory.

# mkdir -p /db01/mysql/mysql01/data # MySQL data directory
# mkdir -p /db02/mysql/mysql01/binlogs # location of binary log files

# mkdir -p /db03/mysql/mysql01/ # administration directory HOME and location of PID file

# mkdir /db03/mysql/mysql01/startup # location of my.cnf files
# mkdir /db03/mysql/mysql01/run # location of socket file
# mkdir /db03/mysql/mysql01/errors # location of error file
# mkdir /db03/mysql/mysql01/logs #location for general and slow logs
# mkdir /db03/mysql/mysql01/scripts # administration scripts
# mkdir /db03/mysql/mysql01/sql # generic sql code

# mkdir -p /db04/mysql/mysql01/backups # backup files

# mkdir /db04/mysql/mysql01/exports # exports

# mkdir /db04/mysql/mysql01/misc # miscellaneous backups (single tables, etc)

Set permissions for the MySQL directory structure

Go to each of these parent directories and change the owner and group to mysql. The operating system userid root password will be your main Mac OS password for your administrator. Make sure you are in the right directory before running any recursive command. If you are in the wrong directory you could mess up your operating system. Execute the pwd command to make sure you are in the right directory.
Login as your administrator userid or root userid to run the following commands. Be extremely careful you do this correctly!

# chown -R mysql:mysql /db01 /db02 /db03 /db04 /opt/mysql

Set up the MySQL software
Do NOT perform the MySQL installation as the root or OS admin user. Login in as the mysql operating system user id and verify.
# su - mysql
$ who am i
mysql ttyp1 Nov 10:19

Go to the /opt/mysql directory then run the following commands to set up the MySQL HOME directory (MYSQL_HOME).
The symbolic link will set /opt/mysql/5.1.30 as the software location for MySQL.
$ cd /opt/mysql
$ tar zxvf mysql-5.1.30-osx10.4-i686.tar.gz

Create a new my.cnf file

Create a new my.cnf file from one of the sample files in the $MYSQL_HOME/support-files.
$ cd $MYSQL_HOME
$ cp ./support-files/my-small.cnf my.cnf

Add the following parameters to the my.cnf file.

Go ahead and use the default socket location to complete the installation. Once the installation is complete, it is recommended to move the socket to the location displayed below. Be careful when moving the my.cnf to its own location. It is a recommended best practice but make sure all mysql programs can find the configuration file.

[mysqld]
basedir=/opt/mysql/5.1.30
datadir=/db01/mysql/mysql01/data
log-bin=/db02/mysql/mysql01/binlogs/mysql01-bin
log-error=/db03/mysql/mysql01/errors/mysql01.err
pid-file=/db03/mysql/mysql01/localhost.pid
#socket=/db03/mysql/mysql01/run/mysql01.sock

[client] # global options for every client:
#socket=/db03/mysql/mysql01/run/mysql01.sock


Setup the MySQL environment

# Setup the mysql data directory. Make sure you are logged in as the mysql OS userid. When done go to the /db01/mysql/mysql01 directory and make sure it is setup correctly. If this is your first time setting up MySQL on Unix/Linux you may want to keep the my.cnf file in the /opt/mysql/5.1.30 directory. This default location will avoid having to specify the location of the my.cnf file.

$ cd /opt/mysql/5.1.30
$ scripts/mysql_install_db --defaults-file=/db03/mysql/mysql01/startup/my.cnf
$ cd /db01/mysql/mysql01/data
$ ls -la

Start the MySQL daemon using the new startup my.cnf file.

There are multiple ways to start up and shutdown a MySQL server. Below I use the mysqld_safe and mysqladmin commands.

$ cd /opt/mysql/5.1.30
$ bin/mysqld_safe --defaults-file=/db03/mysql/mysql01/startup/my.cnf &
$ ln -s /db03/mysql/mysql01/startup/my.cnf my.cnf

Test the MySQL daemon with mysql-test-run.pl and review the output.
This test is optional. The script takes a long time to run. :)

$ cd /opt/mysql/5.1.30/mysql-test
$ perl mysql-test-run.pl > mysql-test-run.output

The mysqladmin command can be used to shutdown the database server.
$ mysqladmin -uroot -p shutdown

Login to the mysql server and secure the password environment.
# Login to mysql and see that no passwords have been setup yet.
$ mysql -uroot
mysql> select host, user, password from user;
mysql> quit

# Run the following script and set the values listed below. When prompted enter new password for the mysql database userid. Do NOT set this to the same value as the operating system userid mysql.
$ mysql_secure_installation
Set root password? [Y/n] y
Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] y
Remove test database and access to it? [Y/n] y
Reload privilege tables now? [Y/n] y

#Login to your new mysql server environment. Never type a database password on an operating system prompt. Verify a password now exists for the mysql userid on the localhost.
$ mysql -uroot -p
mysql> select host, user, password from mysql.user;

Post Installation Steps
Login in using the mysql client and verify the installation. Review the data files, log files, binary log, error logs, etc. Make sure you are comfortable with the new environment.

Define a server startup method. The script mysql.server is a likely option. Test the startup and shutdown processes.

Define a backup and recovery strategy. Test your back and recovery processes.

Remember there are the following userids in this environment:

• root - Linux/Unix operating system user id.
• root - MySQL database user id.
• mysql - Linux/Unix operating system user id.

Installing the MySQL GUI Tools Bundle

Installing the MySQL GUI tools should be installed with the .DMG file on Mac. The nice thing is it is a three click install: 1) Click to download the GUI Tools .DMG file from the dev/mysql.com site 2) Click to open the file on the Mac OS. 3) Click to move it into the Applications directory for Mac.

This is the MySQL GUI Tools Bundle which includes the following:

• MySQL Administrator
  
• MySQL Query Browser
  
• MySQL Migration Toolkit (currently not available for Mac OS in bundle)
  

Once you install the MySQL GUI Tools bundle for the Mac OS, run the .dmg file. It will ask you to drag the MySQL Tools icon to the Applications directory. After dragging the icon to the applications directory, go the the applications directory using Finder.

Before launching the MySQL Administrator or Query Browser, if you are using a non-default sock, click on advanced options on the login screen and enter the socket file you defined in your my.cnf file. Then login to either of these two tools.

Conclusion
These instructions walk through the basic steps for setting up a MySQL server on a Mac OS. Remember this article discusses a basic install. It does not include setting up memory, InnoDB server parameters, etc. This will allow you to have fun in the documentation. :)

Have fun with MySQL. :)