Best Practices for Installing MySQL (MOCA)

Open Source Software

Every year Open Source Software (OSS) is getting better, covering more areas of IT, growing in adoption and is increasingly used to gain competitive advantage.  From the LAMP (Linux, Apache, MySQL, Python/Perl/PHP), Java, MariaDB, Openstack and Hadoop, etc, OSS provides a software stack ranging from operating systems, application servers, database servers, big data, Cloud orchestration and Cloud deployments.  In the top global strategic accounts I meet with, I see a consistent pattern in the increasing role OSS is playing in organizations.    

The MySQL Ecosystem Continues to Grow

An excellent example of OSS success is MySQL.  MySQL and it's ecosystem continue to grow in success and popularity.  A healthy OSS product also has a healthy ecosystem.  Oracle has been doing an excellent job of adding important features and functionality, while at the same time companies like Percona and MariaDB are providing enhanced functionality and giving MySQL users choice. 

In setting up metadata databases for Hadoop, MySQL is by far my favorite database to use in Big Data environments.

Where is MySQL today:

·       Estimated 16 million plus databases around the world.

·       Fastest growing database in the Cloud.

·       Popular in big data environments for storing metadata and as a staging database.

·       An extremely popular database that excels in small and medium transactional environments.

·       Excels as an Internet database due to it's speed.  Also very popular as an Embedded database.

·       Replication is native and relatively easy to use to the point it is often used for creating backups, data marts and staging databases.

·       MySQL/Oracle continues to average 60,000 downloads a week and Percona has over 500,000 downloads of it's Percona Server.

·       A energetic user community with multiple international conferences, books, bloggers.

·       Very popular as a data warehouse and data mart due to it's extremely fast read capability.

·       Plug-ins, connectors, 3^rd party applications and tools continue to be developed for MySQL

·       MySQL cluster is an extremely fast HA environment for fast reads and large volume transactions.

MySQL 's Impact on Oracle Environments

A Fortune 250 company I was at recently, said they had 2000 Oracle databases, 4000 SQL Server databases and 2000 MySQL databases.  Another company I was at recently acquires companies on a regular basis said every company they purchase has a large number of MySQL database servers.   MySQL in pure volume of databases is the most popular database for the Internet and the Cloud.    MySQL is used as a meta database and as a staging database in Hadoop environments.  Point being, MySQL with it's continued growth and popularity is gong to continue to have an impact in IT environments. 

One of the nice benefits of a MySQL is it is an easy database server for Oracle DBAs to learn how to manage and support. The best analogy is MySQL is managing MySQL is very similar to managing an Oracle 7-8 database server.   With MySQL's popularity any Java, PHP, Python and Ruby developer is likely to be working with MySQL sometime in the future if not currently

Installing MySQL 's 5.x on Linux using MOCA

Introduction

The following instructions will show 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 modeled after OFA so it is an installation that will be familiar to any Oracle DBA.  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.  Otherwise use the standard MySQL install and start simple.

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.  The install for any MySQL 5.x release is similar.

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