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

MySQL Conference and Expo 2010

The  MySQL Users Conference and Expo 2010 scheduled for April 12 - 15, 2010 in Santa Clara is definitely a conference I highly recommend you attend if you can.  The insights, networking and knowledge gained is invaluable.  The fantastic dynamics of the open source community has to be experienced to be understood.  :)