Installing MySQL 5.1 on Linux using MOCA

Introduction

The following instructions will lay out an installation of MySQL on Linux using the MySQL Optimal Configuration Architecture (MOCA) for someone with fundamental knowledge of MySQL and basic Linux administration skills. MOCA is a set of best practices I put together to lay out a set of guidelines for installing and configuring a MySQL database server.  MOCA is designed for someone with some experience with MySQL, it is not for someone brand new to MySQL.  MOCA is okay if you have a strong database background and good solid Linux skills.

If you are new to MySQL or to Linux, I recommend using a default install with a  rpm install or use yum to do the install for you. The MySQL default install is recommended for someone new to MySQL or the operating system platform. If the default package install makes more sense for you, then you can stop reading.  This install is for someone wanting to setup a flexible and scalable configuration.  This configuration is for MySQL 5.1.42 and it will work for any 5.1.x install.

If you are ready to get started you can skip down to the Start the Installation section.   If you scroll to the very bottom of this blog, I have a summary of the specific commands for the setup.  

A Default Install using RPM or YUM

The default install with MySQL is great for users new to MySQL. It is simple, requires a few point and clicks and you are up and running. The problem with a default install is that it is designed to be a very simple install and take minimum resources. This simple layout is excellent in its simplicity but it is not how a production server should be set up.  The default install also spreads MySQL files in different locations like /etc, /usr/bin, /usr/local that is not flexible and violates standard best practices for database servers.  The default install is not how an experienced DBA would want to set up a production database environment.

Why Perform a Manual Install Using a Tar Ball 

It is much better to be able to control the layout and configuration of the database software for production database environments and for platforms where multiple MySQL servers may be installed in the future.  Defining consistent standards, guidelines and best practices that are flexible and scalable are the key goals of MOCA.

This install assumes you have a basic understanding of Linux and  MySQL database administration. Oracle DBAs will find this installation very similar to the concepts of the Optimal Flexible Architecture (OFA).

For experienced MySQL DBAs a manual install is much better. For this purpose I created a best practices configuration and white paper called MOCA (MySQL Optimal Configuration Architecture).  I wanted to call it GOCA (George's Optimal Flexible Architecture) but MOCA seemed to flow better.  MOCA is based on DBA best practices and should be very similar to Oracle, DB2 and SQL Server production DBAs. There are certain fundamental truths about how database servers should be installed, configured and managed. My MOCA whitepaper addresses these fundamental truths. This manual install will follow MOCA standards and conventions.

Why MOCA? 

Visit mysql-dba-journey.blogspot.com to get the details of the reasons behind MOCA and why it is based on best practices. There is also an example of installing MySQL on Mac OS that is very similar to a Linux install. A Solaris install using MOCA can be found at blogs.sun.com/georgetrujillo.  MOCA  focuses on:

1. Separating database software from other software and files.
2. Separating data and index files, log files for recovery, administration and backup files.
3. Developing standard naming conventions.
4. Defines a flexible configuration that can support multiple database servers on same platform.
5. A consistent configuration for multiple servers and versions of MySQL database software across an enterprise.

Installation Summary

This installation looks more complex than it is.  I use this configuration for all  MySQL DBA classes.

1. Remove old versions of MySQL if they exist.  Setup an operating system (OS) user called "mysql" and the environment for this OS user.
2. Set up directories and directory permissions for all MySQL data files.
3. Setup MySQL software and install MySQL software as mysql operating system user (not as root).  Configure the my.cnf configuration file.
4. Create the mysql database (mysql_install_db) and . Start the mysql database server (mysqld_safe).  
5. Setup the security environment (mysql_secure_installation)
6. Test the shutdown and startup of the database server to test the server setup. 

Start the Installation

The environment for this installation is:

• Enterprise Linux downloaded from Oracle's OTN website.  Red Hat, Fedora and CentOS can be used as well.  I chose Enterprise Linux because I like it and it is great for running Oracle and MySQL on same system so I can do ETL and play with the two database servers.  I installed the MySQL tar package (ball) x86/x64 image mysql-5.1.42-linux-x86_64-glibc23.tar.gz. Downloaded from the http:/dev.mysql.com site.

Before installing MySQL on any platform, make sure there are no previous versions of MySQL preinstalled that were loaded with the OS. Unless you want the older version of MySQL, your life will be much easier if you remove any previous releases that are not being used.

Read through this installation a few times before starting.

Note:  I use the # prompt to signify I am performing steps as root and the $ prompt to show I am performing steps as the mysql OS user.

Look for existing MySQL software
This install uses MySQL 5.1.42,  although these installation procedures can be used for any 5.1.x installation. Dependent on the version of Linux and your hardware, different packages may need to be installed or removed (old MySQL installations).

So you can see my Linux environment I ran the following command at the shell.
# more /etc/redhat-release
Red Hat Enteprise Linux Server release 5.4 (Tikanga)

Check to see if MySQL is installed on your current system.
# grep mysql /etc/passwd
# rpm -q mysql
# find /usr/local -name '*mysql*' - print 
# find /usr/bin -name '*mysql*' - print 
# find / -name "*mysql*' - print # look everywhere for MySQL installations

Remove any MySQL old files or packages. Then verify old MySQL files are gone.  If you leave the old MySQL software that is okay, but make sure MySQL is finding your MySQL files and not the old software (prime example, using the /etc/my.cnf file instead of yours).
# rpm -e mysql

Downloading MySQL 
Go to http://dev.mysql.com and find the Downloads tab. Find the distributions and choose the install release you want.  I prefer a manual install so I choose the Linux Tar Packages file mysql-5.1.42-linux-x86_64-glibc23.tar.gz. Select a mirror. On the Select a Mirror page, I choose No thanks, just take me to the downloads!

Note: Be aware that this MySQL image worked for me with the OS and hardware I was using.  Make sure you have the right image for your environment.  If commands fail because you can't run the mysql executables, you probably have the wrong image for your environment.

For a separate Centos 5.2 install on different hardware I used this image:
mysql-5.1.42-linux-i686-icc-glibc23.tar.gz

For my Linux environment the file downloaded to the /root/Desktop directory.


Setup the MySQL software 
The first set of commands are run as the root OS user (# prompt) to set up the environment.    In the /opt/mysql directory unzip and untar the MySQL software and build a symbolic link. This will set up the MYSQL_HOME directory to be in /opt/mysql/5.1.42 location.
# mkdir -p /opt/mysql

Copy tar file to /opt/mysql directory and setup up MySQL software.
# cp /root/Desktop/mysql-5.1.42-linux-x86_64-glibc23.tar.gz /opt/mysql
# cd /opt/mysql
# tar zxvf mysql-5.1.42-linux-x86_64-glibc23.tar.gz
# ln -s mysql-5.1.42-linux-x86_64-glibc23     5.1.42

Setup the MySQL directory locations.  Here we are going to put all files below the /db01/mysql01 mount point.  The naming pattern mysql01 signifies database storage for a specific MySQL database server in this layout.  I like to put my InnoDB data and log files in their own location.
# mkdir -p /db01/mysql01
# cd /db01/mysql01
# mkdir data binlogs admin backups innodata innologs



Setup new mysql user if one does not exist.  If a mysql user does exist, set up a password, default shell, default directory, etc.
No mysql user was found so I added one. Add the mysql group, mysql user, password and home directory.
# groupadd -g 300 mysql
# useradd -u 300 -g 300 -d /home/mysql -s /bin/bash -c "MySQL DBA" mysql
# passwd mysql
# chown -R mysql:mysql /opt/mysql    /db01/mysql01

Login and verify the mysql user setup.  From this point, once you login as mysql user, complete the database server as the mysql user and not as the root OS user.

# su - mysql (or exec login mysql)

Then define a default profile file using your favorite text editor.  I chose the bash shell.

--- .bash_profile file ------
PS1='$PWD: '
MYSQL_BASE=/opt/mysql
MYSQL_HOME=/opt/mysql/5.1.42
export MYSQL_BASE MYSQL_HOME
PATH=$MYSQL_HOME/bin:$PATH
--- end of .bash_profile file -------

Set your environment by sourcing your profile file.
$ cd $MYSQL_HOME
$ .   ./.bash_profile

MySQL Directory Organization 
A good way to separate MySQL files and software:

• /opt/mysql/5.1.42             - Symbolic link to software directory location
• /db01/mysql01/data          - Data directory for MySQL
  
• /db01/mysql01/binlogs     - Binary log files location
• /db01/mysql01/admin       - Administration files location
• /db01/mysql01/backups    - Backup files location
• /db01/mysql01/innodata    - InnoDB shared location
• /db01/mysql01/innologs   - InnoDB transaction logs location
  

Before going further

Double (triple) check all directory paths and permissions.   99.99% of issues with manual installs  are typos in the directory paths, typos in the file names or permission issues with directories.  To common gotchas:

• Using the wrong binary for your hardware or OS.
• Search paths find a preinstalled version of mysql that was loaded with the OS.  This command will show you the default search path MySQL uses.

 $ mysqld --help  --verbose | more

 

When you try to bring up database server, if it defaults to the default areas its because it cannot find a directory or doesn't have permission for directories specified so it will then try the default locations.

Use a template file in the support-files directory.
$ cd /opt/mysql/5.1.42
$ cp support-files/my-small.cnf   my.cnf

Add the following entries to the my.cnf file in the [mysqld] group. This separates all your dynamic administration files, data files, and binary log files to different locations.

[mysqld]
basedir=/opt/mysql/5.1.42
datadir=/db01/mysql01/data

log-error=/db01/mysql01/admin/mysql01.err
pid-file=/db01/mysql01/admin/mysql01.pid
log-bin=/db01/mysql01/binlogs/mysql01-bin
innodb_data_home_dir=/db01/mysql01/innodata
innodb_data_file_path=ibdata01:50M;ibdata02:50M:autoextend:max:2000M
innodb_log_group_home_dir=/db01/mysql01/innologs
#-----------------------------------------------------

I would also recommend changing the parameter thread_stack=128k   to a minimum of 256k.

Create the mysql database files for the MySQL database server (instance.) This will create the default database schemas and the physical file layer.

$ cd $MYSQL_HOME
$ scripts/mysql_install_db --datadir=/db01/mysql01/data  --basedir=$MYSQL_HOME

Start the MySQL database server pointing to the defined locations.
$ cd /opt/mysql/5.1.42
$ bin/mysqld_safe --defaults-file=$MYSQL_HOME/my.cnf  &

Verify the mysqld background process is running as well as the mysqld_safe monitoring process. The mysqld background process should be up and running.

$ ps -ef |grep mysql

Verify you can get into the server.  Initial setup has no passwords.  If this works you have a good server.  The show command should display the mysql, test and information_schema databases.
$ mysql
mysql>  show databases;
mysql>  exit

Clean up the database server by adding passwords and getting rid of anonymous users.  The MySQL database super user is called root. This step will add a password for this MySQL database user.
$ cd $MYSQL_HOME
$ bin/mysql_secure_installation

Shutdown the MySQL server to verify you can shutdown and startup the MySQL instance.

$ mysqladmin -uroot -p  shutdown
$ bin/mysqld_safe --defaults-file= $MYSQL_HOME/my.cnf &

You're up and running have fun. Once you are comfortable with this configuration layout, you can create a Unix shell script that will automate almost the entire process. With a shell script automation the install takes about five minutes.

Summary of steps to perform setup

Below is a summary of steps minus all the text.

# ---- Steps performed as root OS user. ---------------
# mkdir -p /opt/mysql
# cp /root/Desktop/mysql-5.1.42-linux-x86_64-glibc23.tar.gz /opt/mysql
# cd /opt/mysql
# tar zxvf mysql-5.1.42-linux-x86_64-glibc23.tar.gz
# ln -s mysql-5.1.42-linux-x86_64-glibc23.tar.gz     5.1.42

# mkdir -p /db01/mysql01
# cd /db01/mysql01
# mkdir data binlogs admin backups innodata innologs

# groupadd -g 300 mysql
# useradd -u 300 -g 300 -d /home/mysql -s /bin/bash -c "MySQL DBA" mysql
# passwd mysql
# chown -R mysql:mysql /opt/mysql    /db01/mysql01

# su - mysql 

# ---- Steps performed as mysql OS user ---------------
$ cd $MYSQL_HOME
$ cp support-files/my-small.cnf   my.cnf

# Make these additions in my.cnf file below [mysqld]
#----------------------
basedir=/opt/mysql/5.1.42
datadir=/db01/mysql01/data

log-error=/db01/mysql01/admin/mysql01.err
pid-file=/db01/mysql01/admin/mysql01.pid
log-bin=/db01/mysql01/binlogs/mysql01-bin
innodb_data_home_dir=/db01/mysql01/innodata
innodb_data_file_path=ibdata01:50M;ibdata02:50M:autoextend:max:2000M
innodb_log_group_home_dir=/db01/mysql01/innologs
#---------------------------------------

$ scripts/mysql_install_db --datadir=/db01/mysql01/data  --basedir=$MYSQL_HOME

$ bin/mysqld_safe --defaults-file=$MYSQL_HOME/my.cnf &
$ bin/mysql_secure_installation

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. :)

Installing MySQL on Solaris

In my Sun blog blogs.sun.com/georgetrujillo I have defined how to install MySQL on Solaris using MOCA as a set of guidelines and best practices.

MySQL Architecture using MOCA

Proper installation of a MySQL database environment will make a tremendous difference in the management of the MySQL database server. This image shows key areas that need to be considered in defining the MySQL configuration. MOCA is used to demonstrate important points to consider.

I've received a lot of emails asking about defining a MySQL environment using MOCA. So I defined this image to try to summarize the core configuration on one slide. On the right it shows the MySQL instance and the storage engines.

• The MySQL instance is the global shared memory and session threads for an individual MySQL database server.
• The storage engines control how I/O is performed on tables and indexes. The individual characteristics of each storage engine will determine where and how the tables and indexes for each storage engine will reside.

Software Installation

The MySQL database software should be installed in its own file system. All dynamic files should be moved out of the software location. The software location should be a static environment.

• The MySQL_BASE directory (/opt/mysql) is the directory point for installing different versions of MySQL database software. Each MySQL database version should be installed in a subdirectory below this base directory location. Naming each software subdirectory with the database version makes it easier to manage.
• The MYSQL_HOME directory (ex. 5.1.22 for the MySQL 5.1.22 software release) is the location of a database software install. This directory should be located below the MySQL_BASE location.

MySQL Global Variables

MySQL global variables define the location of important directories and files for an individual database server instance.

• basedir - is the directory location of the database software installation for a specific release. This is equal to the MYSQL_HOME directory location for a database server.
  
• datadir - is the default location for the database files are stored. Each individual database schema will have a subdirectory below this location that will contain database .frm files and MyISAM files. The default location for the InnoDB tablespace and log files will be stored in the directory defined by the datadir parameter.
• log-bin - defines the location of the binary files required to recover a database to a point in time. This location should be different than the datadir location.
• pid-file - defines the location of the PID file containing the unique process id of the mysqld daemon that runs the database server.
• log-error - location of the primary error and event log file.

Primary operating system directories

These primary directory locations need to be well thought out for a database server. Refer to older blogs that describe MOCA in more detail. Below are sample directories that distribute database software, database files, binary logs for recovery, administration files and backup files into different physical locations. The default installation puts all these in one directory structure. Best practices strongly recommend separating the physical location of different MySQL files. It is a good practice to name directory paths with /dbxx so everyone can understand these directories contain database files. The are template directory names. The mysql2 name is the unique name of a MySQL database server.

• /opt/mysql - base location of MySQL software
• /db01/mysql/mysql2/data - base location of database files
• /db02/mysql/mysql2/binlog - base location of binary logs
• /db03/mysql/mysql2/dbadmin/ - base location of administration directories

• /db04/mysql/mysql2/backups/ - base location of backups

More MOCA please ...

There are certain facts about how database servers work. A database configuration should follow best practices to address these facts. Below is a list of certain truths about database servers that are self-evident:

• Default installs by database vendors are designed to be simple to install and under configured to minimize the initial use of resources. Default installs should not be used to run production servers.
• Relational database servers run faster when memory is configured properly.
  
• Balancing disk I/O is important.
  
• Treat your information and the data you need to do recovery as matter and anti-matter. Never put them together.
  
• Separate your database software from your data.
• Separate your information logs and administration files.
• Database software should never be installed as root or as the operating system administrator.
• Database software, data, logs, administration files, recovery files should be kept separate from other types of files.

Default installs do not address the above. The default software for MySQL is designed to be simple to install and minimize the use of initial resources. However new users perform the default install and then use the default install to run an important database. This default install creates a "lot" of problems if you want to use MySQL as a database server. This is why I created MOCA. MOCA is defined further down in this blog.

MOCA stands for the MySQL Optimal Configuration Architecture. This is a summary of best practices that apply to any database server. Every experienced production DBA from Oracle, DB2, SQL Server, Informix and MySQL uses something similar that contains a list of guidelines and best practices that they have learned from years of experience that should be followed. For years Oracle DBAs have followed OFA (the Optimial Flexible Architecture). MOCA is a summary of best practices modeled from OFA and that I have picked up from 20 years of DBA experience. It is not a complete listing but a set of common sense guidelines that should be considered for a database installation.

I strongly recommend that if you are new to database administration you review MOCA. It pains me to see customers using the default install of MySQL that they installed as an operating system administrator userid and then they wonder why they are having so many problems. The default install will create pain and anguish in your life if you use it as a production configuration.

Installing MySQL 5.1.22 on Mac OS using MOCA

Below I have the high level steps I took to perform a simple install of MySQL 5.1.22 on Mac OS 10.4. These instructions can be followed for any MySQL 5.1.xx install. These are not detailed instructions. The objective is to overview a simple installation of MySQL using MOCA. The Mac OS installation will be similar to a Unix or Linux installation. Go to the MySQL documentation to review instructions if you are using a different operating system.

This is not a recommended install for a new MySQL DBA (perform a default installation to get comfortable with MySQL first). If you are an experienced DBA from another platform and understand Unix directory layouts this installation may be okay if you review the MySQL documentation first. Review the blog "The MySQL Optimial Configuration Architecture" for the reason for this layout.

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. Set up the operating system and directory structures (physical storage) for running MySQL.
2. Setup the mysql operating system user id.
3. Install the MySQL software in the MYSQL_HOME directory.
4. Create a startup file (my.cfg).
5. Run the mysql_install_db script to set up the MySQL data directory.
6. Start the MySQL server instance by running mysqld_safe.
7. Test the MySQL Server instance by running the perl script mysql-test-run.pl.
8. Secure the mysql password environment with the mysql_secure_installation script.
9. Login in using the mysql client and verify the installation. Review the data files, log files, binary log, error logs, etc.
10. Define a server startup method. The script mysql.server is a likely option.
11. Define a backup and recovery strategy. Test your back and recovery processes.
12. Have fun with MySQL. :)

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

Create the directory /opt/mysql for placing the 5.1.22 software.
Go to http://dev.mysql.com and choose the 5.1.22 download (mysql-5.1.22-rc-osx10.4-i686.tar.gz). Place this zip file in the /opt/mysql/5.1.22 directory.

Defined the following 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.
/opt/mysql/

/opt/mysql/software
/db01/mysql/mysql01/data - data directory
/db02/mysql/mysql01/mysql-bin - location of binary log files

/db03/mysql/mysql01/ - administration directory
/db03/mysql/mysql01/ - location of the PID file
/db03/mysql/mysql01/startup - location of my.cfg file
/db03/mysql/mysql01/run - location of socket file
/db03/mysql/mysql01/errors - location of error file
/db03/mysql/mysql01/logs - location for general and slow logs
/db03/mysql/mysql01/scripts - administration scripts
/db03/mysql/mysql01/sql - generic sql code

/db04/mysql/mysql01/backups - backup files, exports, miscellaneous

Preinstallation steps:
Checked to see if there was a previous MySQL installation. Seen the MySQL 4.1 installation came with the MacOS system. Verified an older version of MySQL was not currently running. Used the RPMs to deinstalled the MySQL 4.1 installation.

Verified the setup the mysql operating system user account. A mysql user already existed in the /etc/passwd file. Using the Finder window in Mac. These commands only work with Mac OS 10.4. Use the 10.5 method for the new Leopard OS.

• Go to the Applications location for Mac OS.
• Go to Utilities subdirectory.
• Click on the NetInfo Manager.
• When the window pops up, select users. Then click on mysql user.
• Click the lock at bottom of page to make changes. Set a password, home directory (/Users/mysql) and shell (/bin/ksh).
• Save the changes.
• Open a terminal window. Type exec login mysql. Type the new password and verify the mysql userid defaults to the correct home directory (/Users/mysql).
• Use vi or another text editor to create a .profile file in the /Users/mysql directory with the following values:

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

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!

$ cd /db01
$ sudo chown -R mysql:mysql .
$ cd /db02
$ chown -R mysql:mysql .
$ cd /db03
$ chown -R mysql:mysql .
$ cd /db04
$ chown -R mysql:mysql .
$ cd /opt/mysql
$ chown -R mysql: mysql .

Setup up the MySQL software.
Login in as the mysql operating system user id and verify.
$ who am i
mysql ttyp1 Oct 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.22 as the software location for MySQL.
$ cd /opt/mysql
$ gunzip -c mysql-5.1.22-rc-osx10.4-i686.tar.gz tar xvf -
$ ln -s mysql-5.1.22-rc-osx10.4-i686 5.1.22
$ mv mysql-5.1.22-rc-osx10.4-i686.tar.gz file /opt/mysql/software/5.1.22

Set up the startup file (my.cnf) for MySQL
Make sure to review each of these startup parameters.
$ cat /dbadmin/mysql/mysql01/startup/my.cnf
[mysqld]
log-error=/db03/mysql/mysql01/errors/mysql5.1.22.err
pid-file=/db03/mysql/mysql01/localhost.pid
datadir=/db01/mysql/mysql01/data
basedir=/opt/mysql/5.1.22
log-bin=/db02/mysql/mysql01/mysql01-bin

[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. You should see the files ib_logfile0, ib_logfile1, ibdata1 and the subdirectory mysql from the output of the ls command.

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

Start the MySQL daemon using the new startup my.cfg file.
$ cd /opt/mysql/5.1.22
$ 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.
$ cd /opt/mysql/5.1.22/mysql-test
$ perl mysql-test-run.pl > mysql-test-run.output

Login to the mysql server and secure the password environment.
# Login to mysql and see that no passwords have been setup yet.
$ mysql -h localhost -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 -h localhost -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 for 5.0. which includes the following:

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

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. :)

The MySQL Optimal Configuration Architecture

The MySQL Optimal Configuration Architecture (MOCA)

I am a firm believer in establishing best practices for database management and following them. As your databases grow in numbers and size, it becomes critical to establish best practices for your database management. I was asked recently to write down some of these best practices for managing MySQL databases.

Experienced DBAs have been following similar rules for years. They've learned that following these rules can minimize down time, reduce errors, minimize administration costs and improve performance. I am just sharing rules that DBAs have learned for years that to avoid pain and anguish as a DBA these rules or similar rules should be followed.

Key goals of the MySQL Optimal Configuration Architecture (MOCA) include:

• Define a set of guidelines for how to layout your MySQL software, data files, binary log files, administration logs, error files and administration files.
• Separate operating system administration files from database administration files.
  
• Emphasize the importance of standards and naming conventions.
• Define a physical layout to minimize downtime.
• Separate data files from the files needed to recover the data files.
• Defining a physical layout that is scalable to support multiple MySQL servers.
• Minimize administration costs of supporting multiple MySQL servers.
  

MOCA not GOCA

I thought it might be a good idea to name this layout. I initially wanted to name it George's Optimal Configuration Architecture (GOCA) but thought the MySQL Optimal Configuration Architecture (MOCA) rolled off the tongue better. It's easier to say "moca" versus "goca".

The nice thing about MySQL is that it can be a very simple install on platforms like Windows (note it can be an involved install on some platform and releases). Point click and it is up and running on some releases and platforms. If your environment is going to have a large number of MySQL installations your configuration will be more detailed to support larger environments. The configuration layout below is not designed to solve the meaning of life. The goal is to point you in the right direction and to have you consider the ramifications of your database layouts.

MOCA is not a trivial install

If you are new to MySQL or database administration, a default install may be more appropriate for getting comfortable with MySQL. It is important to understand that MOCA is a non-default install is not a trivial install. This install assumes you understand how to configure a MySQL server and you are comfortable with working with a database environment spread across different locations. If you are learning MySQL and it is your first install I would recommend doing a default install.

MOCA is set of best practices that should be used by experienced MySQL DBAs that are comfortable with setting up the MySQL environment and working with a MySQL environment.

Default Installs versus MOCA

As a MySQL DBA its important to note that MySQL is extremely portable across platforms but the default installs in Windows, Linux, Mac OS and Solaris are completely different. The default installs are great for the beginning DBA to learn MySQL. However the default install configurations should not be used for production environments running on different servers.

Developing a standard like the MySQL Optimal Configuration Architecture (MOCA) is important for defining consistency in configurations across different platforms. In different platforms looking for MySQL files (default installation locations) in /var/lib/mysql, /usr/local/mysql, /usr/sbin, C:/Program Files is not a consistent configuration. Read about MOCA and make sure you have consistent configurations across all hardware platforms.

Operating System Filesystem Hierarchy Standards

A key goal of MOCA is to define a set of guidelines and standards for how to layout your MySQL software, data files, binary logs, logs, administration files and to stress the importance of naming conventions.

Developing filesystem hierarchy standards for any operating system is important. The nice thing is OS administrators have usually taken care of this for the DBA. OS filesystem guidelines should not need to change significantly for databases.

Using the Linux Filesystem Hierarchy Standard as an example looks like this:
/initrd /proc /tmp
/bin /lib /root
/boot /lost+found /sbin
/dev /media /usr
/etc /mnt /var
/home /opt /srv

Using Linux as an example:
/etc - Will add startup scripts to make sure MySQL boots at startup, etc.
/home - Will contain home directory for MySQL userid.
/opt - Will contain MySQL software.

The big question is what to name the mount point directories that will hold the database files. I like them to have a pattern naming convention that shows these directories are dedicated to database files. Setting your own pattern is fine as long as it makes sense and is used consistently. A numbering convention should be used so all directories are the same length.

• Less than 100 directories us 00 - 99.
• Less than 1000 directories use 000 - 999.
• Less than 10000 directories use 0000 - 9999.

Example directory names:
Syntax:

• /[dbxx]/[software]/[db server name]/[specific type of directory]

Begin directory hierarchies for database files with (/db01)
Define database directory for mysql (/db01/mysql)
Define database server name for specific server (/db01/mysql/mysql01)
Define specific type of directory (/db01/mysql/mysql01/data)

Example directory structure

/db01/mysql/mysql01/data - MySQL data directory
/db02/mysql/mysql01/binlogs - location of binary log files
/db03/mysql/mysql01/ - administration directory HOME

/db04/mysql/mysql01/backups - backup files, export files and miscellaneous backups

These rules are dependent on the amount of hardware, disks, controllers and memory you have for your database environments. Remember more disks and memory makes you a better DBA. Here are my top recommendations for configuring a MySQL environment. A Unix/Linux format is shown but a similar naming convention should be used with Windows, etc. The naming conventions below are guidelines. The important thing is that once naming conventions are defined they be followed consistently on all database servers.

• Install the MySQL software (MYSQL_HOME) on its own disk partition. This should be a fairly static environment and not have any dynamic files in its location. Use a standard naming convention for each version of MySQL software. Try to avoid putting MySQL software in default operating system locations. Example:

MYSQL_BASE=/opt/software/mysql
MYSQL_HOME=$MYSQL_BASE/4.0 -- Home directory for version 4.0
MYSQL_HOME=$MYSQL_BASE/5.1 -- Home directory for version 5.1
MYSQL_HOME=$MYSQL_BASE/6.0 -- Home directory for version 6.0

• Each MySQL server should have its own administration directory. Directories for log files, scripts, backup startup files, etc. should be defined. The log directories here are only for the binary logs not the system logs (general, slow, query, ...).
  

-- Administration directory structure for server mysql01.
/db03/mysql/mysql01/logs
/db03/mysql/mysql01/scripts
/db03/mysql/mysql01/sql
/db03/mysql/mysql01/startup
/db03/mysql/mysql01/run

• Separate the binary logs location from the system logs. System logs such as the general log, slow query log, error log, etc. should be in a separate admin log.
  
• Place data files and binary log files on separate disks and controllers if possible. Treat data files and binary log files as matter and anti-matter. Never put data files and the files needed to recover the data on the same disks. Develop a consistent naming convention for where to place your data files and your binary log files.
  

-- Location of data files for MySQL servers mysql01 and mysql02.
/db01/mysql/mysql01/data
/db01/mysql/mysql02/data

--Location of binary log files for MySQL servers mysql01 and mysql02.
/db02/mysql/mysql01/binlogs
/db02/mysql/mysql02/binlogs

• Develop a consistent location for disk backups on all your database servers. Additional database exports should be stored in a consistent location.
  

-- Backup location for server mysql01.
/dbbackup/mysqldata/mysql01/backups
/dbbackup/mysqldata/mysql01/exports

-- Backup location for server mysql02.
/dbbackup/mysqldata/mysql02/backups
/dbbackup/mysqldata/mysql02/exports

• Always backup your startup configuration files. Using a version control system like RCS can be a good solution.
• When tuning or changing your database configuration, make small changes and see how they go. Try to avoid making a number of changes at once.
• Before installing a new release of software make sure you understand the current bug report on the new release and you have sufficiently tested the new environment the applications will run in.
• Periodically test your backups and recovery scenarios.
• Don't type DBA passwords on the command line prompt, ever! This also includes avoid using mysqladmin to set passwords at the command line prompt.
  
• Avoid running DML statements on the mysql system tables. For example, use the SET PASSWORD command, not the UPDATE command on the mysql.user table. Another example is use the DROP USER command not the DELETE command on the mysql.user table.
  
• Separate operating system administration from MySQL administration. Avoid putting MySQL files in directories like /etc (/etc/my.cnf), /usr/local (/usr/local/mysql) and /tmp (/tmp/mysql.sock).
• Be operating system neutral. For example, don't name your startup file my.ini and my.cnf on different platforms. Be consistent across all systems (i.e. name the startup file my.cnf on all platforms).
  
• Set SQL_MODE to an appropriate value for your environment. The "traditional" value makes sense in most OLTP environments.
• Understand the benefits and management requirements of the different storage engines.
• For important databases strongly consider the Enterprise support offered by MySQL. It is relatively very inexpensive but very important for supporting a MySQL environment.
• Consider using software to help you manage and monitor a MySQL environment.
  
• Before implementing any configuration (including this one) make sure it is well-tested and has gone through a number of different scenarios.
• Use fully qualified host names (myhostname.mycoolcompany.com). Do not use unqualified host names (myhostname).
• Be nice and supportive to your users. Remember, there are more users and developers than DBAs. If you are in a small shop and you are the DBA and developer, be nice to yourself.
  
• Develop a set of database polices that new users have to read and sign to be given a new account. This should be part of the help desk ticketing system. Get the approval of management and their support of these policies.
  
• If there are a large number of MySQL databases and I/O performance is important it may be necessary to use a storage system that supports striping.
  

Conclusion

Remember that DBA work does not always occur first thing in the morning when you are bright eyed and bushy-tailed. It may occur after working a 12-hour day or at 2:00 a.m. in the morning. The more you develop a consistent method method for how database related files the more you will reduce errors and make it easier to maintain your database environment. Spreading I/O for high activity data files will also improve performance.

This consistent layout will make it easier to write scripts and setup administration practices. Just double check and make sure you are not copying or writing files into the wrong directories.

These guidelines are designed to point out methods of how you want to organize your databases in a large enviroment. Customers are running hundreds and thousands of MySQL databases. Larger environments are also running storage arrays supporting striping and mirroring. Work with your operating system administrators and storage management team to determine the best physical layout of your databases.