MySQL blog focused on DBA topics, MySQL development and open source infrastructure environments.

Thursday, January 14, 2010

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:

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)

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

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.

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.

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.

I use something like MOCA for a long time. Can you run multiple instances on the same server with this directory structure? I susally put the pid and the socket file in a separate dir, and I have a dir created for tmp, relay logs and log files too.

Yes, absolutely. You can definitely have multiple MySQL database servers pointing to the same MySQL home directory. Obviously just make sure all other physical paths point to unique locations and names. If you ever upgrade one of the database servers, you just move the home directory for the upgraded server to point to new location. MOCA is very flexible.

Thanks for this post. One thing I observed is that I had to add a socket command to the do_query function in teh bin/mysql_secure_installation, since I elected to move the socket file to a tmp dir in the context of mysql01

be wary of putting the multiple instances of mysql on the same server, this does not fall in line with best practices for server architecture as it introduces "2" single points of failure. i.e if the os has issues then you have 2 instances down instead of 1.

Welcome to the MySQL DBA Blog

This blog records my continuing journey as an Oracle DBA in the world of open source and MySQL. I am a methodologist who believes in best practices and working to integrate technology into business processes. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.