Installing MySQL 5.1 on Solaris using MOCA

Introduction

The following instructions will lay out an installation of MySQL on Solaris using the MySQL Optimal Configuration Architecture (MOCA) for someone knowledgeable in MySQL/Solaris administration. MOCA is a set of best practices I put together to lay out a guidelines for installing and configuring a MySQL database server. MOCA is designed for someone with experience with MySQL, it is not for someone brand new to MySQL.

If you are new to MySQL or to Solaris, I recommend using the default package install for MySQL. 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 MySQL 5.1.33 but it would be the same steps for any 5.1.xx installation.

Why Perform a Manual Install

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. The default install also puts MySQL files in different locations on the filesystem dependent on the OS release and platform. The default install is not how an experienced DBA would want to set up a production database environment. 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.

This install assumes you have a fundamental understanding of Solaris and have an understanding of MySQL database administration fundamentals. 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). This 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. In summary, it focuses on:

1. Separating database software from other software.
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.

Installation Summary

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

1. Remove old versions of MySQL if they exist. Setup up operating system user mysql and mysql user environment.
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 setup the security environment (mysql_secure_installation). Start the mysql database server.
5. Test the shutdown and startup of the database server.

Installation Environment

The environment for this installation is below: Mac OS 10(Leopard) running VM Fusion with Solaris 10 - Downloaded DVD iso image from www.sun.com website. I installed the Solaris 10 05/08 x86/x64 image for this demo (sol-10-u5-ga-x86-dvd.iso). I also used MySQL 5.1 - Downloaded from dev.mysql.com.

Before installing MySQL on my platform, make sure there are no previous versions of MySQL preinstalled. Unless you want the older version of MySQL, your life will be much easier if you remove any previous releases.

Read through this installation a few times before starting.

Look for existing MySQL software

This install uses 5.1.33, these installation procedures can be used for any 5.1.x installation. Dependent on the version of Solaris, different packages may need to be installed or removed (old MySQL installations).

Check to see if you see MySQL on your current system.
# grep mysql /etc/passwd #
# find /usr/local -name '*mysql*' - print # look here for MacOS, Unix/Linux
# find /var -name '*mysql*' - print # good place to start with Solaris
# find / -name "*mysql*' - print # look everywhere for MySQL installations

VM Fusion Choices for Installing Solaris 10

My choices for installing Solaris 10 in a VM Fusion environment. During the installation you will be asked to hit F2 to continue. On a MAC that will be EscapeKey-2 or FN-F2. Solaris Interactive US-English Networked - DHCP IPv6 - No You may need to specify the amount of disk space to use. I allocated 10228 MB.

You should now be able to log in as root. With Solaris choose the Java Desktop Environment or the Common Desktop Environment (CDE), this is a personal preference.

Removing older versions of MySQL

Check for MySQL packages installed and remove them.
# pkginfo | grep mysql
The following packages SUNWmysqlr, SUNWmysqlt, SUNWmysqlu were found and removed.
# pkgrm SUNWmysqlr
# pkgrm SUNWmysqlt
# pkgrm SUNWmysqlu

Remove old MySQL files from common directories.
# sudo rm /usr/local/mysql
# sudo rm -rf /Library/StartupItems/MySQLCOM/

Set up the mysql user. Start by checking to see if there is an existing MySQL user.
# grep mysql /etc/passwd

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 /export/home/mysql -s /usr/bin/bash -c "MySQL DBA" mysql
# passwd mysql
# mkdir /export/home/mysql
# chown -R mysql:mysql /export/home/mysql

Login and verify the mysql user setup

# exec login mysql (or su - mysql)

Then define a default profile file using your favorite text editor.

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

Set your environment by sourcing your profile file.

$ cd $MYSQL_HOME
$ . ./.bash_profile

Downloading MySQL

Go to http://dev.mysql.com and go to downloads. Find the distributions and choose the install release you want. I chose 5.1.33. I prefer a manual install so I choose the Solaris Tar Packages the Solaris 10 64-bit install. Select a mirror. On the Select a Mirror page, I choose "No thanks, just take me to the downloads"!

MySQL Directory Organization

Organize how MySQL files and software will be located:
/opt/mysql/5.1.33 - Symbolic link to software directory location
/db01/mysql/mysql01/data - data directory
/db02/mysql/mysql01/binlogs - location of binary log files
/db03/mysql/mysql01/admin - main administration directory
/db04/mysql/mysql01/backups - location of backup files

I created the following directories to download the MySQL software in /opt/mysql/5.1.33.
# mkdir -p /opt/mysql/5.1.33
# export MYSQL_NAME=mysql01

Setup data directory structure
# mkdir -p /db01/mysql/$MYSQL_NAME/data

Setup mysql administration directory structure
# mkdir -p /db03/mysql/$MYSQL_NAME
# mkdir /db03/mysql/$MYSQL_NAME/logs
# mkdir /db03/mysql/$MYSQL_NAME/errors
# mkdir /db03/mysql/$MYSQL_NAME/sql

# mkdir /db03/mysql/$MYSQL_NAME/startup

# mkdir /db03/mysql/$MYSQL_NAME/run

Setup binary log structure
# mkdir -p /db02/mysql/$MYSQL_NAME/binlogs

Setup backup directory structure for backups and exports.
# mkdir -p /db04/mysql/$MYSQL_NAME
# mkdir /db04/mysql/$MYSQL_NAME/backups
# mkdir /db04/mysql/$MYSQL_NAME/exports

Set permissions and ownership for MySQL file directories.
# chmod -R 750 /db*/mysql/* /opt/mysql/*
# chown -R mysql:mysql /db*/mysql/* /opt/mysql/*

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. Double check all paths. When you try to bring up database server, if it defaults to the default areas its because it can;t find a directory or doesn't have permission for directories specified so it will then try the default locations.

Setup the MySQL software (as the MySQL OS user, not the root OS user)

All following commands are run as the mysql OS user. In the /opt/mysql directory unzip and untar the MySQL software as the mysql OS user.
$ cd /opt/mysql
$ gunzip mysql-5.1.33-solaris10-64bit.tar.gz
$ tar xvf mysql-5.1.33-solaris10-64bit.tar
$ ln -s mysql-5.1.33-solaris10-64bit 5.1.33

Be careful with the my.cnf configuration file.

At the operating system prompt you can type the following command. If you scroll down you will find the default search path. All commands like mysql_secure_installation, mysql, mysql_install_db, etc. all look in a search path for the configuration file. Make sure any program is finding the right configuration file in the search path. Here are commands that will show you the search path:

$ mysql --help | more

$ mysqld --help --verbose | more

Use one of the sample configuration files provided with the distribution to get started.

$ cp $MYSQL_HOME/support-files/my-small-cnf /dbadmin/mysql/mysql/startup/my.cnf

Add the following entries to the my.cnf file to the [mysqld] group. This separates all your dynamic administration files, data files, and binary log files to different locations. A separate port is defined away from the default.

[mysqld]

datadir=/db01/mysql/mysql01/data
basedir=/opt/mysql/5.1.33
log-error=/db03/mysql/mysql01/errors/mysql5.1.33.err
pid-file=/db03/mysql/mysql01/localhost.pid

log-bin=/db02/mysql/mysql01/mysql-bin
#port = 3426
#socket=/dbadmin/mysql/mysql01/run/mysql.sock

Add the following entries to the my.cnf file to the [client] group.
[client]
#port = 3426
#socket = /dbadmin/mysql/mysql01/run/mysql.sock

Build a symbolic link from the default location to the real startup file. The step of adding a symbolic to the actual startup file is something I avoid but some environments like this addition.

$ cd $MYSQL_HOME
$ ln -s /dbadmin/mysql/mysql01/startup/my.cnf my.cnf

Create the mysql database files for the MySQL instance. This will create the default database schemas and database files.

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

Verify data files and directories have been created in the datadir directory.

$ cd /db01/mysql/mysql01/data
$ ls

mysql test

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

If there are socket errors:

i.e. MySQL client cannot star twith the error "cannot connect to the MySQL server through socket

Solution:

MySQL needs to write to a socket. If you don't specify one, a default one is chosen which may not have the appropriate permissions. You can specify the socket file in the command line as below. Make sure the permissions are set properly (owned by mysql).

$ mysql -uroot -p -socket=/dbadmin/mysql/mysql01/run/mysql.sock

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

Clean up the database server by adding passwords and getting rid of anonymous users. If there are problems with the mysql_secure_installation script, then set the password manually and get rid of the anonymous accounts and any accounts with no passwords.

$ cd $MYSQL_HOME
$ bin/mysql_secure_installation

Shutdown the MySQL server to verify you can shutdown and startup the MySQL instance. I like switching to the my.cnf in the startup directory versus in the default software directory.

$ mysqladmin --defaults-file=/dbadmin/mysql/mysql01/startup/my.cnf shutdown
$ cd $MYSQL_HOME

$ bin/mysqld_safe --defaults-file= /dbadmin/mysql/mysql01/startup/my.cnf

You're up and running have fun. Once you are confortable 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 ten minutes.

George Trujillo at Collaborate 09: Demystifying MySQL for Oracle DBAs and Developers

George Trujillo at Collaborate 09 Week of May 3, 2009

I will be presenting two presentations at the IOUG Collaborate 09 Conference in Orlando FL, the week of May 3rd 2009. Here is one I have been asked to present. This presentation is a very detailed technical presentation designed for experienced Oracle professionals to get an understanding of the MySQL database server under the hood.

Demystifying MySQL for Oracle DBAs and Developers Tues @ 9:45am - 10:45am, 224A

George Trujillo continues his popular demystifying series that started with Demystifying Oracle Fusion Middleware, to now include a MySQL demystifying series. This presentation will use George's MOCA best practices blueprint for installing, configuring and tuning a MySQL database server. This fast paced detailed technical presentation will cover: MySQL Architecture, Installation, Configuration, Key Features, Security, MySQL Tools, Storage Engines, transactional capabilities and strategic directions. This is a can't miss presentation for any Oracle DBA wanting to understand MySQL.

George Trujillo at Collaborate 09: What every Oracle Professional needs to know about MySQL

George Trujillo at Collaborate 09 Week of May 3, 2009

I will be presenting two presentations at the IOUG Collaborate 09 Conference in Orlando FL, the week of May 3rd 2009. Here is one I have been asked to present. This presentation is a non-technical presentation talking about MySQL positioning, strengths and strategic directions.

What every Oracle Professional needs to know about MySQL Mon @ 3:45pm - 4:45pm, 240CD

The MySQL open source database continues to increase in popularity with usage estimated at 11 million database servers worldwide. This presentation will help Oracle professionals who are considering the addition of MySQL or just getting started with MySQL: how the MySQL database can be utilized, MySQL strategy and architecture, and key functionalities. Similarities and differences between database vendors and best practices will be included. The focus of this presentation is on helping attendees understand the philosophy, features, benefits and popularity of MySQL.

Trends in Database Design and Optimized Application Development

I'm constantly going out to customers and looking at their database environments and the challenges they are facing. Of course, sometimes my perspective can be rather unique because customers never invite me in to show me how fast their databases are or how great things are. Usually by the time I get called in, the environment has gotten very dynamic.

What never ceases to amaze me is how on average database design seems to keep getting worse and applications are more poorly written than ever before. There are a number of industry and organization reasons for this but these reasons constantly stand out at customer sites:

1. Developers Designing Databases: Developers are usually the ones meeting with the customers and doing a lot of the initial design on smaller projects. The problem with this is there is a big difference between understanding what a primary and foreign key are and designing a database. Or there is a data modeler who can design beautiful databases in theory but has no idea how that database design is going to work in a production environment.
2. Most Developers Do Not Know How to Write Optimized Queries: Back in the old days there were database developers that had a strong understanding of SQL development. However the current generation of Java, PHP, .NET and Ruby developers are great with their languages and writing APIs but the major percentage of them have no idea how to write optimized queries for their applications.
3. Take Out Databases: In this generation of fast food take out and web applications, its all about getting something out the door quick. Which usually means minimal time for proper database design and testing. This is definitely the Dilbert world of databases.

I find these problems to be much more severe in MySQL environments versus Oracle environments. If an organization pays a million dollars for an Oracle license they are going to make sure they get some people that know that they are doing to design and manage a database project. When someone downloads a MySQL database for free for a small web application they think they can use anybody and the project will turn out okay.

When teaching performance tuning classes in Oracle and MySQL I always ask the students what percentage of companies do they think do a good job of designing their databases and their index optimization paths. I always get single digits for the response to these questions. Oh well, I guess this is what makes the database world so fun. :)

Top things that I have found that greatly improves database application success includes:

• Getting the right expertise to design your databases.
• Training developers on how to write good queries for their database applications.
• Design patterns.
• Taking the time to do it write.
• Use case scenarios.

Tracking the Storage Engine Race

There are a number of new and enhanced storage engines that promise increased scalability, performance and important new online features. All this competition with storage engines is going to create a win for the MySQL community. This competition is what open source is all about. If you do not continue to innovate and improve you cannot expect to be a leader in the open source world. The nice thing about MySQL 5.1 is the plug-in capability that allows the addition and removal of storage engines.

The top ones I have been tracking include:

• Falcon (still in alpha)
  
• Maria (still in alpha)
  
• InnoDB (final release candidate stages)
  
• XtraDB (unable to verify release, since only a few months old may be alpha or beta)
  
• PBXT (beta I believe )
  

Instead of just comparing benchmarks and features, what I am focusing on is the adoption by my customers. All these storage engines are promising optimized architectures,

increased performance, scalability and improved metadata for diagnostics and tuning. For most of these it is still too early to tell. However, I will be talking to customers every week to gauge their thoughts and adoption rates on these storage engines.

No matter which one I pick, I'll always upset someone. But its no fun staying on the fence, so I would definitely give the early lead to the InnoDB plug-in. The reason is it is on the final release candidate stages and is the first next generation storage engine used with the MySQL 5.4 scalability and performance release. It will be fun to watch the growing popularity of these storage engines. I would love to hear some detailed feedback on your thoughts of these new storage engines. Be aware that most of these storage engines are still in alpha or beta, so I recommend being very conservative before using them in a production environment.

Working with MySQL Hints

A number of developers look at SQL tuning hints as a way to performance tune queries. Where the best way to tune SQL code is not to use hints. Hints add high maintenance code and try to influence the Cost Based Optimizer which can have negative impacts in the future. What if the data distributions change or the optimizer adds new features in the future? The best way to tune SQL code is to look at how the database server environment can be optimized so the optimizer can make the best decisions as possible. However, if you cannot get the optimizer to make the right decision a SQL hint may be required but it should usually be the choice of last resort.

Examples:
SELECT * FROM mytab IGNORE INDEX (col1_idx)
WHERE col1='A' AND col2='B';

SELECT * FROM mytab USE INDEX (col1_idx,col2_idx)
WHERE col1='A' AND col2='B' AND col3=3;

Here are some of MySQL SQL Tuning Hints:

• STRAIGHT_JOIN
• IGNORE INDEX [FOR JOIN]
• FORCE INDEX [FOR JOIN]
• USE INDEX [FOR JOIN]
• SQL_NO_CACHE
• SQL_CACHE
• The HIGH_PRIORITY
• The LOW_PRIORITY
• INSERT DELAYED
• INSERT LOW_PRIORITY
• INSERT DELAYED (MyISAM, MEMORY, and ARCHIVE tables)
• SQL_BUFFER_RESULT
• SQL_BIG_RESULT
• The SQL_BIG_RESULT
• SQL_SMALL_RESULT

Oracle to buy Sun!

Today's announcement that Oracle has agreed to buy Sun definitely starts the MySQL conference which begins this morning with a bang. I expect Oracle InnoDB presentations to be standing room only!

• Oracle announcement: http://www.oracle.com/sun/index.html
• Sun announcement: http://www.sun.com/third-party/global/oracle/index.jsp
  

The MySQL conference just got very interesting for the attendees! Popular user conferences bring together industry leaders from around the world, so the activity at the conference has definitely be raised to a very high level.

For me personally this potential acquisition is great news. It brings together the two company's technologies where I have spent most of my career working with.

MySQL Users Conference 2009 - Santa Clara, CA

The MySQL Users Conference is going to be an excellent conference with a lot of change going on in the MySQL world. Some of the key areas to be looking at include:

• MySQL strategic directions.
  
• Increasing vertical scalability of MySQL with upcoming MySQL software releases, new and enhanced storage engines and patches.
  
• Enhanced diagnostics with DTrace.
• New BI software products and tools.
• Performance tuning, performance tuning, performance tuning.
• Evolution of next generation of storage engines. Lots of cool stuff here.
• Advanced DBA tricks and techniques.
  

InnoDB Plugin Has Some Very Cool features

Reducing Administration Costs and Down Time
DBAs are always going to be challenged with growth, scalability and performance as long as databases get bigger and more users access them. Reducing administration and down time are always key issues in our 24 x 7 environments. The new InnoDB Plugin has some very important features that DBAs are going to want to take a serious look at. So I thought I would point out some of the highlights.

The InnoDB Plugin

InnoDB is the primary transaction storage engine through the 5.x releases of MySQL. The InnoDB Plugin allows MySQL DBAs to replace (SKIP_INNODB) the default InnoDB storage engine with an enhanced version of InnoDB (plugin) that offers some important enhanced features and performance. This InnoDB plugin is in the final beta (release candidate) releases.

Key Features
The InnoDB Plugin offers some of these important key features:

• Fast Index Creation - The ability to add and drop secondary indexes without copying table data. Index maintenance is always an issue. The performance benchmarks on this feature are significant.
• Out of Line Data - BLOB, Text and VARCHAR columns can be stored out of line. By default only 768 bytes are stored inline for large columns. However even this 768 bytes is not good for your table storage. If the INNODB_FILE_FORMAT is set to Barracuda (default is Antelope) and the ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED is set, the only a 20 byte pointer is stored in line and the rest of the data is stored out of line. New InnoDB file formats are likely to use additional animal names in alphabetical order.
• Enhanced Metadata - New metadata in INFORMATION_SCHEMA on locking and compression. Enhanced metadata definitely helps with dealing with locking issues and getting better information on compression. The new INFORMATION_SCHEMA metadata tables include: INNODB_CMP, INNODB_CMP_RESET, INNODB_CMPMEM_RESET, INNODB_CMPMEM, INNODB_TRX, INNODB_LOCKS and INNODB_LOCK_WAITS. DBAs have been waiting a long time to get detailed locking and transaction information. These new metatables are awesome!
• Data Compression - Data compression support for InnoDB. There is a new attribute called ROW_FORMAT that can be set to compressed. The KEY_BLOCK_SIZE is another new parameter that can compress in 1K, 2K, 4K, 8K or 16K bytes. The LZ77 compression algorithm compresses data and indexes. Compressed data is uncompressed in the InnoDB buffer cach for reading. Compression requires the INNODB_FILE_TABLE option to be set. Example:

CREATE TABLE mycooltable (col1 INT PRIMARY KEY) ENGINE=InnoDB
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

• Truncate Table - TRUNCATE TABLE command reclaims space in .ibd file.
• Optimization Statistics - More control over statistics generation. New features allow better statistics generation to determine the key distributions on indexes. This is a very important feature for the cost based optimizer to make good decisions on indexed columns that have uneven distributions of key values.
• Dynamic Parameters - Dynamic parameter eliminates need to bounce database server when modification is required. It is very important that DBAs minimize downtime so these making the following parameters dynamic is pretty important:

• innodb_file_per_table
• innodb_stats_on_metadata
• innodb_lock_wait_timeout
• innodb_adaptive_hash_index

InnoDB Plugin Installation

The installation of the InnoDB Plugin can pretty straight forward and only takes a few minutes once the InnoDB Plugin is downloaded into the proper location. Of course every version and platform has its own unique idiosyncrasies (or the devil is in the details).