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

MySQL Scalability and Performance Directions

MySQL is continuing to grow at a rapid pace in the market place. Continued high growth areas for MySQL continue to include the web application, gaming and embedded systems space. For small and medium sized OLTP environments MySQL continues to increase in popularity. MySQL can have incredible scalability as long as it scales horizontally. However with today's hardware adding more CPU and memory capability, it is important that MySQL be able to grow to much larger sizes through vertical scalability.

There are a lot of upcoming changes in the MySQL world that are going to add significant performance, scalability and feature/functionality. The key areas I will be talking about include:

• MySQL Version 6 - (currently in alpha) will add significant performance, availability, scalability and online features.
• Falcon - (currently in alpha) New storage engine with MySQL 6 that will add increased scalability, availability and online features to replace the InnoDB storage engine.
  
• Maria - (currently in alpha) New crash safe storage engine that will add increased scalability, availability and transaction safe features to replace the MYISAM storage engine.
• InnoDB Plugin - (close to end of beta) Oracle offers an InnoDB plug-in that provides a number of important performance, scalability, online functionality and reduced maintenance features.
  

Each storage engine: Falcon, Maria and the InnoDB Plugin provide a lot of important enhancements that customers are going to want. It will be important to watch the maturity of each of these storage engines as they move from alpha and release candidate versions to general availability (production).

Performance Tuning Knowledge is Important for the New DBA

As MySQL continues to expand in the market place, I am seeing a common mistake new DBAs make. They are waiting too long to develop their performance tuning skills. Whenever I work with new DBAs I always tell them it is important to focus on three areas:

1. Understand the architecture and how things work. It is not good to guess.
   
2. Get very good at back up and recovery if you want to keep your job.
3. Get very good at performance tuning if you want to enjoy your job.
   

It is important that in your future as a DBA you plan on taking a Performance Tuning class. The reasons are:

• Most databases get bigger (add more data) and have more users over time. So performance tuning will always be a factor in managing your systems as your systems grow.
• It's important to take a performance tuning class sooner than later. Reason is you could be making decisions that will have very negative performance ramifications in the future, if you do not understand performance factors. Future scalability is impacted by performance decisions made today.
  
• Understanding performance tuning makes you a better DBA. Being good at performance tuning requires you understand how a database server works in more detail. This understanding will help with diagnostics and trouble shooting for everything you do as a DBA.

Being good at performance tuning helps you be a more proactive DBA versus being a reactive DBA. Reactive DBAs are always putting out fires and fixing things after the fact. If you want to enjoy your work as a DBA focus on being a proactive DBA.

The New Tipping Point: The Sun Open Source Software Stack

Sun's Software Stack - At the right place at the right time

The technical industry has always had tremendous ebbs and flows. Just ask powerful or up and coming companies like Digital Equipment Corporation, Silicon Graphics, Informix, Netscape, Atari, and even Sun. Yet sometimes, "everything is in the timing".

With the current economic environment, it is safe to say that all organizations are taking a very serious look at how to reduce costs. This perspective is likely to continue through at least 2009 and 2010. Sun's software stack is positioned in the right place at the right time. Sun's current software stack will significantly reduce operational costs and provide more flexibility over operational budgets. As costs continue to impact organizations, you will see more traditional organizations follow the direction of start up companies.

Ninety percent of start up companies in the U.S. are using the LAMP stack (Linux/Apache/MySQL/PHP,Perl). The LAMP stack is a proven software stack that offers tremendous cost savings over proprietary licenses. Sun's software stack is a faster, cost effective and enterprise version of the LAMP stack. So we can expect more and more organizations looking at and moving to the Sun software stack. I'm seeing this every week in my training classes. My students are selling me on all the benefits they are receiving from this stack. And a major percentage of my students are from proprietary vendor environments.

Major Areas of the Sun Software Stack

Major areas of the software stack that will dramatically reduce costs versus alternative options:

• MySQL - The fastest growing database server in the industry. The adoption rate is increasing every month.
• Glassfish - A powerful, fast enterprise application server that is a fraction of the cost of its competitors. A great solution for customers that are growing out of their Apache and JBoss servers.
• Open Storage - With the ever increasing volumes of data that organizations need to work with, storage will continue having a significant impact on IT budgets.
• Identity Management - Fast enterprise identity management. Sun has one of the best LDAP solutions out in the industry.
  
• Solaris/OpenSolaris - Offer proven powerful software solutions that can significantly reduce OS costs.
• Java - Key software for mobile applications and large enterprise business applications.
  

So as the trend continues, watch as the Sun software stack increases in popularity. There are reports that the government is evaluating increasing its use of open source to reduce license costs. It won't take much more for the Sun software stack to become a major tipping point in the software industry.

Recent MySQL Trends

In working with customers every week I am seeing some trends that I would like to share with everyone.

1. There is a tremendous upswing in Oracle customers looking at MySQL. A year ago about half the customers were evaluating MySQL, now I am seeing about 90% saying they have been mandated to look at using MySQL for future DBA projects.
2. Must stronger acceptance by the traditional Oracle DBA as to the features and benefits of MySQL.
3. A large number of customers new to MySQL are not very knowledgeable about the environments surrounding MySQL database servers. Mention words like Memcache, InfoBright, KickFire and most customers are not sure what they are.
4. Customers are still using a wide variety of tools to monitor and manage MySQL database servers. However, I do see an upswing on customers looking at the MySQL Enterprise Monitor.
5. Too many customers underestimate the need to get their administration teams trained on performance tuning and advanced database features. MySQL is a database server that needs to be tuned and managed correctly just like any other database server. Too many customers think all they have to do is install it and they are done.
6. There is a lot of interest in MySQL version 6 with the new optimizations, storage engines and availability features.
7. Too many customers are not sure on whether to use Ruby, Groovy, Java, .NET, PHP, etc. I am constantly surprised on how customers are still trying to figure out the best tools to use for development for specific projects. Maybe this will never change.
8. More and more web developers do not understand how to write good database code. This leads to big performance issues down the road when database usage begins to go up.
9. Web developers are almost never using database vendor specific features. They want databases that are fast, easily deployable and easy to work with.
10. From my perspective in working with customers, MySQL is growing at the fastest rate I've seen in the last few years. Is their any area of technology that is growing faster than MySQL?

MySQL and Oracle: Top 10 things I love about them

Today's Competitive Global Economy Environment for DBAs
With today's competitive environments, the more marketable you are the better. Being flexible and being able to support different solutions is very important. Environments surrounding databases are getting more complex, so increasing your expertise in different areas is important. There are a lot of options with application servers, storage, data warehousing, high availability, etc. Another strong skill combination is Oracle and MySQL. Oracle is the largest database vendor and MySQL is the fastest growing so you are going to see both a lot in the future so why not be able to support both.

What I love about MySQL
I've spent the last two years working with open source and MySQL. It's been two of my coolest years in technology. My top ten things I love about MySQL:

1. In working with MySQL customers their is a lot of innovation and think out of the box mentality.
2. MySQL environments use a lot of different software and tools. Shell scripting, perl scripts, awk, sed all remind me of the Oracle 7 environments which were a lot of fun to work in. Being very strong with Linux/Unix and the tools makes you a more marketable MySQL DBA.
3. MySQL surrounding software such as Apache, JBoss, Memcache, Cacti, Nagios, Mon, phpMyAdmin, PHP, etc. makes it a fun environment for the technical junkie.
4. MySQL's ability to leverage the power of the Internet is incredible to be a part of.
5. The open software environment that is all about contribuing to the greater good, sharing knowledge so everybody grows and mass collaboration is incredible powerful. Its fun to be part of the wave.
6. The MySQL mentality has been one of the richest experiences of my life. Everybody being tied in virtually, knowing my peers in Germany, Sweden and all over the world as well as my peers in the US has make it fantastic to be part of a company that really understands how to function as a "team" in a global econony.
7. The MySQL environment of we are a team, we all work together, share and work to accomplish the goals of the team and the company. MySQL is one of the few companies that can really get everyone working together and motivated towards a common goal.
8. Learning the power of MySQL and why more new MySQL database servers are being created than all the proprietary vendors combined.
9. MySQL's empowering of the individual employee to accomplish great things. Where the emphasis is that the individual employee really does matter.
   
10. The incredible vision of the leaders of MySQL and the absolutely fantastic people within the MySQL teams.
    

What I love about Oracle
I've spent spent the last 20 years working with Oracle (RAC, Data Guard, Streams, backup/recovery, performance tuning, Oracle Fusion Middleware (SOA, BPEL, Web Services, XML, Oracle Business Rules, JDeveloper). My top ten things I love about Oracle products:

1. The database server is an aircraft carrier solution with a task force. :) When you have complex problems to solve Oracle has powerful tools to solve them.
2. Oracle RAC is a very robust, scalable clustered solution that is great to work with.
3. Oracle Streams while complex allows you to solve tons of data sharing problems.
4. Oracle Fusion Middleware offers a great middle-tier enterprise solution for large companies. Working with BPEL, SOA, Web Services, XML, etc. is like being a kid in a candy store.
   
5. I've just flat out loved working with Oracle products and Oracle environments.
   
6. The fantastic people I've met in Oracle user groups, the list is endless.
7. The incredibly dedicated people I've met at Oracle that work in all the different product groups.
8. Oracle's done a great job of integrating organizations they've purchased and achieving their visions.
9. I've always had fun performance tuning Oracle multi-tiered architectures. Maybe this is an incurable disease (ha ha).
10. I've always found the more I've learned about Oracle products the more money I've been able to make (always a good thing). My personal investment in Oracle technology has always paid off.
    

LAMP Skills for the MySQL DBA

MySQL runs on lots of Operating Systems
The MySQL database server runs with more different operating systems than any other database. The reason being is not only does it run on all the popular operating systems but you can download the source, make a few tweaks and run it on any operating system (if you're a bit twiddler). Yet the place where you're likely to see MySQL the most is with LAMP environments. Of course the L in Linux means Red Hat, SUSE, Centos, Fedora, Ubuntu, etc.

Operating Systems I use with MySQL
The operating systems I use the most with MySQL include Red Hat, Windows, Fedora 10, Mac OS, Solaris and OpenSolaris.

• Red Hat and Solaris - use the most when working with customers.
• Mac OS, Fedora and OpenSolaris - for trying different things and playing around with MySQL. Although I do see some customers using OpenSolaris and CentOS for their production environments.
  
• Windows - use mainly for teaching beginning MySQL courses.

VMs I use with Mac OS
I like using Parallels and VM Fusion with my Mac OS environment. Both of these VMs allow me to install any operating system I want and play with MySQL, Oracle, Oracle Fusion Middleare and LAMP stack components.

Software skills to have around MySQL
For most MySQL DBAs in general, they need to have skill in popular software surrounding MySQL. These software skills include:

• Linux administration - basic admin skills, cron, shell scripting and UNIX power tools (awk, sed, REs, PERL,...)
• Application Servers - Get good with an application server. Apache is a great one to start out with. Then you can migrate to JBoss, Glassfish or the Oracle Application Server dependent on your goals. MySQL excels in web environments so it really helps for a MySQL DBA to understand the middle-tier in a lot of companies.
• Popular software - Around MySQL you usually see a lot of oftware like memcache, Nagios, mon, cacti, SQLyou, Navicat, Toad, phpMyAdmin, etc. MySQL is like the open source Sun and a lot of open source revolves around MySQL.
  
• Development - I'm still seeing lots of PHP, Java and Ruby around MySQL. Of course .NET is always a player.
• Benchmarking software - mysqlslap, sql-bench, jmeter, MyBench, sysbench, Apache Bench and supersmack are all good skills to have.
  

LAMP Skills for the MySQL DBA
It's important for any MySQL DBA to increase their value to an organization and marketability by having strong LAMP or LAMP equivalent skills. As well as be able to work with the software that is used in lots of companies that surround MySQL.

Evolving trends and directions for DBAs and Developers: How do DBAs stay marketable?

I visit a large number of organizations every year to deliver training, short term consulting as well as business and technology seminars in the area of database servers, security, software development and middle-tier technology. So I thought I would share some of the trends I have seen in 2008 that I believe will continue in 2009.

I'm always being asked the following questions; "How do I stay marketable?" or "What trends are occurring in technology that impact DBAs and Developers?". The trends are pretty obvious, the question is what conclusions do we draw from them.

IT Continuing Trends for 2009

Some of the noticeable trends:

• Common DBA skills (administration, backup/recovery, tuning) are becoming more of a commodity and easier to outsource every day. A recent quote from a high end recruiter "I don't have any use for technical DBAs. Now applications DBAs are worth their weight in gold".
  
• Oracle Application DBAs (Financials, ERP, Hyperion, ...) are becoming more valuable and marketable every day. This is the skillset of the future, not the basic technical DBA skills.
• Middle-tier applications increase in importance and visibility. The key in most organizations is their middle-tier business applications (vendor and custom). An Oracle database license can be a few million dollars. An Oracle or SAP Financials implementation can cost 100 million dollars. I wonder where an organization will place their emphasis?
  
• Oracle Fusion Middleware skills (Oracle Application Server, web services, BPEL, SOA, XML, ...) are becoming more valuable than ever. As Oracle Fusion applications roll out in the future, these middle-tier skills will increase in marketability and demand.
  
• Middle-tier architecture skills working with application servers, middle-tier caching, web services, J2EE, PHP, Ruby on Rails are increasing in demand.
• Architectural skills and high availability expertise across all tiers are needed more than ever. If a system is slow, there has to be people that can do problem resolution and performance tuning across all tiers.
• Cross platform expertise. DBAs that can support Oracle, MySQL and SQL Server environments are more valuable than a technical DBA that can only support one environment.
• Virtualization will increase significantly. Oracle's VM and Sun's VM (Containers, Zones) will see an increase in database and application servers running production environments using VMs. Sun Containers are a very powerful way of setting up cloning and failover. So VMs not only provide very cost and environmental effective ways of implementing servers but offer significant advantages in administration and high availability. One of the biggest success stories with Sun Containers was an Oracle Hyperion implementation that the consultants told me that can't now imagine implementing this any other way.
• Open source will continue to see significant growth in the next year. The reduced cost and flexibility of open source solutions will significantly help organizations be cost effective and competitive.
  

The best present you can give yourself for the next year is to learn new skills to increase your marketablity and value to organizations.

MySQL Profiling: SQL Tuning

MySQL provides a number of different tools for tuning SQL statements. Some of the key SQL tuning tools include:

• EXPLAIN - Displays execution plans generated by the MySQL Cost Based Optimizer.
• Status Variables - Contains statistics on SQL run time activity.
• Profiling - Contains run time statistics on each phase in the execution of individual SQL statements.
  

Main Phases in Processing a SQL Statement

Profiling allows access to very detailed run time statistics on each phase of processing a SQL command. The main phases include:

• Parsing the SQL statement.
• Generating an execution plan.
• Performing an execution and fetch.
• Cleaning up resources.

Profiling Benefits

Profiling provides the following benefits:

• Ability to understand time spent in each phase of processing a SQL command.
• Displays if the OS performed a disk read because the requested block is not in memory.
• The number of waits that occurred while the thread processed the SQL command.
• Disk activity generated by the SQL statement.
• Resources allocated for processing the SQL command.
• Approximations of time required to process each step of the SQL statement.
• Comparision of execution times relative to other SQL statements.
• If you're into the source code, each C function and line number where it is executed for each step of processing the SQL statement.
  

Profiling Resources in MySQL

The profiling resources available beginning in MySQL 5.1.28 include:

• SHOW PROFILE command.
• SHOW PROFILES command.
• Information_schema.profiling table.

Turning Profiling ON

Profiling is turned OFF (0) by default. Profiling is turned ON (1) at the session level. The PROFILING_HISTORY_SIZE parameter is used to determine how many statements are kept in the history. The default is 15 statements. The query id will change for each SQL statement executed in a session.

mysql> SET PROFILING=1;


SHOW PROFILES

The SHOW PROFILES command will display the query id, the duration and the SQL command executed.

mysql> SHOW PROFILES;
+----------+------------+------------------------------------------------------
| Query_ID | Duration | Query
+----------+------------+--------------------------------------------------------
| 1 | 0.00073900 | SELECT Co.Name, Ci.Name, Ci.Population FROM CountryList Co, CityList Ci WHERE Co.Code = Ci.CountryCode AND Ci.Population > 8000000 |
| 2 | 0.00086100 | SELECT query_id, seq,state, duration, source_function FROM information_schema.profiling WHERE query_id = 1 |
...


SHOW PROFILE

SHOW PROFILE [type [, type] ... ]
 [FOR QUERY n]
 [LIMIT row_count [OFFSET offset]]

type:
 ALL
| BLOCK IO
| CONTEXT SWITCHES
| CPU
| IPC
| MEMORY
| PAGE FAULTS
| SOURCE
| SWAPS

mysql> SHOW PROFILE FOR QUERY 3;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.000091 |
| freeing items | 0.000040 |
| logging slow query | 0.000007 |
| cleaning up | 0.000007 |
+--------------------+----------+
4 rows in set (0.00 sec)

INFORMATION_SCHEMA.PROFILING

There are much more interersting queries you can get out of this database object, but this simple query fits in the blog eaily and provides a hint of detailed information available.

mysql> SELECT query_id, duration, state
FROM information_schema.profiling WHERE query_id = 8;
+----------+----------+--------------------+
| query_id | duration | state |
+----------+----------+--------------------+
| 8 | 0.000100 | starting |
| 8 | 0.000068 | Opening tables |
| 8 | 0.000008 | System lock |
| 8 | 0.000013 | Table lock |
| 8 | 0.000028 | init |
| 8 | 0.000015 | optimizing |
| 8 | 0.000016 | statistics |
| 8 | 0.000017 | preparing |
| 8 | 0.001850 | executing |
| 8 | 0.000162 | Sending data |
| 8 | 0.000008 | end |
| 8 | 0.000006 | query end |
| 8 | 0.000069 | freeing items |
| 8 | 0.000034 | removing tmp table |
| 8 | 0.000007 | closing tables |
| 8 | 0.000005 | logging slow query |
| 8 | 0.000007 | cleaning up |
+----------+----------+--------------------+
17 rows in set (0.01 sec)

Thoughts on Profiling

The profiling feature needs to mature more in MySQL, yet this early implementation does provide information that can be useful for evaluating SQL statements. Combining profile information, thread information and session state information together provides different perspectives into the evaluation of SQL processing.

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

Why use Second Life? What's so great about it?

As organizations become more global and virtual, there is an increased need to find better ways for people to collaborate and work together virtually. Today we have wikis, IRC, Skype, messaging and webcasts, but none of these methods do a good job of creating great interaction, collaboration, camaraderie, friendships as meeting in person. For example, I recently had to work with someone on organizing a number of database training sessions at a conference to prepare people for certification. We used email, IRC and Skype to collaborate on ideas. A few weeks later we met in person. During the two hour dinner we came up with more ideas, communicated more effectively, built a stronger working relationship and friendship. We kept building on ideas each other had and after the dinner we both agreed we accomplished more in two hours meeting in person than we ever could have accomplished using any of the above mentioned communication methods.

Organizations and people are required to work more virtually and remotely which has a tremendous impact on communication. While doing nothing to address the lack of social interaction and how this impacts people. So what do we do when we can not meet directly with people? Virtual reality and virtual worlds using avatars are a new method organizations are looking into to have virtual environments communicate more effectively. Second Life is the virtual environment I have been working with. Sun Microsystems, IBM, Cisco and a large number of fortune 1000 companies and universities are building large virtual environments. On the Sun Islands in Second life an avatar's conduct is held to the same standards as if a Sun employee, parter or customer was at a Sun Microsystems office.

Below are some thoughts and experiences I have found in second life.

• If you look at how far people stand from each other in real life, when working with avatars studies have shown that avatars stand the same distance from each other when having a conversation as in real life.
• I was waiting for a meeting to start and in the virtual world it was raining. My avatar was in a virtual world standing in virtual rain. However it started to bother me that I was standing in the rain and I ended up moving my avatar inside. I felt this interesting because it bothered me that I was uncomfortable standing in virtual rain. I should not have cared.
• Children, teenagers and adults are learning to work together differently in real life. If you look at webkinz, World of Warcraft, Madden Football, these are extremely popular games that allow someone to experience virtual reality. Even Barnes and Noble is moving to avators. What people often miss is that these virtual environments are changing how people think, learn and interact with each other.
• If your avatar is struck, knocked down or bumped into, someone will have a similar mental response as if they would in real life.
• Organizations are finding they can train people more effectively and at greatly reduced costs using avatars and virtual reality.
  

Working with other avatars in virtual worlds creates friendships, stronger working relationships and more collaboration and exchange of ideas than any other electronic method of communication we have today. I can often communicate with you more effectively with much higher results than if I was working with you in real life. Let me give you a few examples:

• What if I was trying to get you certified to repair jet engines? I would need a jet engine, lots of spare parts, tools and space to teach you. What if I instead build a jet engine in virtual reality. In virtual reality we could rotate the entire jet engine in a few seconds. In the virtual world we could how you how to take parts off, look at them three dimentionally, and put new parts on. The cost savings and effectiveness of this approach would be incredible. What if you wanted to work extra hours or on weekends to prepare for your certification? You could do this easily in a virtual world. This same approach could be used to teach brain surgery or anything else. Now obviously this does not replace real life interaction but this can be a cost effective and powerful way of training and communicating with people.
• Virtual reality has been used as a strong therapy for people who have been disabled and can no longer walk or get out of the house easily.
• Schools are using virtual reality to take students to Mexico and visit the Aztec ruins, ancient Greece and transport to all kinds of historical worlds. Virtual reality has been shown to be an excellent method to get kids interested in exploring and imagining different worlds and cultures.
  
• If you were a martian and teleported instantly everywhere you would have no need for a vehicle like an automobile. Well what if I tried to explain to you what an automobile was if you had no point of reference? If you were on Mars and I was on Earth, I'm not sure Skype, IRC, Powerpoints or a wiki would be effective. What if instead I was able to show you in a virtual world a three dimensional automobile. What if I could virtually show you the moving parts of a car and how it operates. Then showed someone getting in a vehicle, starting it and driving around. I believe this virtual method of communication could be very effective.

Now because virtual worlds and avatars look like a game people do not respect its communication potential. They think virtual worlds are only for playing, people that have a lot of spare time on their hands or a waste of time. Well hear are two examples:

• When telephones were first developed a leading scientific magazine of the time said the phone was a play toy for the rich. The magazine said it would be too expensive to run phone lines all over a city (let alone a country) and phones were just a fad.
• When the internet first came out a lot of leading technical journals said it was a toy, was used for porn, you could meet bad people on it and was a waste of time. No one at that time could have imagined, caller id, GPS, email or text messaging, itunes, paying for things with your cell phones, cameras on phones or how small phones could be.

In the next few years, I believe we are going to see a tremendous evolution of technology of virtual worlds and as we communicate more globally the need for stronger and more effective communication will continue to grow.

Top Ten Keys to Delivering a Great Presentation

Here is my list of top ten things to do to deliver a great presentation.

1. Make sure you properly prepare your presentation.
   
2. Create a great first impression in the first ten seconds.
   
3. Show enthusiasm and energy for your topic.
4. Speak to the audience. Use the works "I", "you", "we" to engage the audience.
5. Make eye contact with individuals throughout the presentation. Make each person feel as if you are talking to them.
6. Break your presentation into different pieces. First five minutes, last five minutes, then break the rest of your presentation into 5-10 minute sections.
7. Use variety: telling a story, visual aids, props, music, sound. Make sure you use your props wisely.
8. Good body posture and movement.
9. Audience will care more about what you say versus what is in your slides or presentation materials.
10. Have fun and make sure you use your "own" style not someone else's style.
    

Being a "Great" Presenter

Being an excellent presenter is one of the most important skills you need to have to be successful in your career. Excellent presenters:

• Make more money.
• Have larger social and business networks.
• Have more opportunities brought to them.
• Have more opportunities to positively impact other people.

At the same time, when lists of top fears are shown, public speaking is always number one on the list of top fears. Public speaking is always at the top of the list ahead of cancer, dying in a plane crash, etc. There are so many excellent ways to develop your public speaking.

• Starting small at a local ToastMasters would be a great way to meet others that present.
• Practice giving a ten minute presentation and video taping your presentation is a good way to practice privately.
• There are lots of great books on developing your presentation skills.
• Joining local business, social and technology user groups is another way to get an opportunity to present.
• There are excellent online resources. Below I have a few sites to look at.
• Watch other people present. See what they do well and don't do well. You can attend local business and social user groups, business chambers, etc.
  

If you practice by video taping your presentation, keep improving it until you see someone you would enjoy listening to. Networking groups are always looking for speakers to present to them. Just make sure you find some ways to practice your presentation before speaking in front of other people.

Here are some good sites for learning more about delivering presentations:

• http://ezinearticles.com/?What-Makes-A-Great-Presenter?&id=8872
• http://www.knowhr.com/blog/2006/11/03/how-to-give-a-great-presentation
• http://www.creativekeys.net/PowerfulPresentations/article1078.html
• http://www.lawcrossing.com/article/index.php?id=2987
• http://www.flyingsolo.com.au/p234839702_Presentation-skills-What-makes-a-great-presenter.html
  

Developing your presentation skills is one of the more important career decisions you can make! Try some of the above sources to develop your presentation skills and start finding ways to start presenting to different groups. The key is to start small and slowly build to presenting to larger groups.

Good luck and I'd love to hear some success stories on how you got started on presenting!

MySQL Presentation in Second Life

I really enjoyed delivering a MySQL positioning presentation in Second Life. I'm in the process of organizing 3D interactive presentations on MySQL and Oracle. As these presentations get scheduled I will be contacting different organizations within Sun to attend.

Sun CEC 2008: MySQL Sessions the buzz of the conference

There were a lot of great stories and sessions at the Sun CEC 2008 conference. The MySQL sessions were some of the most popular sessions of the conference. My focus was on delivering sessions that would teach Sun engineers and partners why MySQL is exploding in the market place. Additional sessions developed specific MySQL DBA skills for Sun engineers. More details can be found at:

http://blogs.sun.com/GeorgeTrujillo/entry/sun_cec_2008_in_las

Thanks to all the attendees and their great efforts during the hands on lab sessions.

Marten Mickos at Sun CEC 2008: MySQL Sessions

During the Sun CEC 2008 MySQL sessions training track it was great to get Marten Mickos to speak to the Sun audience. As I was lining up guest speakers for my MySQL sessions I really wanted to get Marten there to address questions Sun employees have on the MySQL acquisition and to discuss future directions of MySQL with Sun. Marten as one of the key leaders in open source as always did a great job of positioning MySQL within Sun for the Sun employees and partners.

Highlights I picked up from the presentation:

• MySQL has a very large and constantly growing community embracing open source and MySQL. With over 70,000 downloads a day, MySQL continues to increase its user base and popularity. This popularity opens up more and more opportunities for MySQL Enterprise licenses and Sun products.
  
• MySQL Enterprise licenses are a lot easier to understand and at a fraction of the cost of proprietary solutions.
• MySQL's objective is to be "fast", "reliable" and "easy to use" versus trying to be feature crazy.
• MySQL is not trying to compete directly against Oracle in the large Oracle OLTP environments. MySQL excels in web environments for very fast read performance is critical.
  
• How MySQL generates revenue today and how revenue will continue to grow as a Sun product.
• Where MySQL is today and where it is going.

Thanks Marten for the great job of showing Sun employees and partners the direction and future of MySQL.

Sun CEC 2008: November 10, 2008

This morning started with a nice breakfast and the opening general session. Key speakers include:

• Daniel J. Berg - CTO Global Sales and Services and VP of EM Systems Engineering
• Peter Ryan - Execute VP Global Sales and Services
• Jonathan Schwartz - CEO and President
• Hal Stern - Senior VP Systems Engineering

Highlights from the General Session

Open source is disruptive technology. Open source is putting pressure on proprietary companies.

Sun is almost a cult versus just a company. Today, Sun has a great story with open source. Customers can achieve tremendous savings by using Sun open source solutions.

Monetization of MySQL acquisition
Very important for Sun to be able to sell out of their traditional base. MySQL opens tremendous new opportunities for Sun. From Eric Schmidt (CEO at Google), "When you look at Google, our products are our ads". At Sun, storage, ZFS, Java, MySQL and their incredible popularity are ads for Sun. An extremely large customer said the proprietary vendors are absolutely killing their IT budget, so they are moving to MySQL to change their history of paying unbelievable amounts of money to proprietary vendors. MySQL is constantly proving their ability to disrupt the industry by saving customers large amounts of money.

Takeaway messages from Jonathan:

• "Please have fun when you come to work."
• "Be active with customers about our products."
• "Now is the time to be talking to customers about what Sun is doing."

Quote: "At Sun, We are innovators, we create the future. At Sun we are not bigger than our competitors, but we are smarter. Our flexibility and agility is a key. Our technicalese is our strength."

Sun does not have the resources and funds to do the massive marketing like IBM and HP. However, the large number of leads from MySQL and turning these leads into sales is a great opportunity for Sun. Part of Sun's future success is due to customers looking at Sun for innovation.

The Platform is a Service

Even in a down economy, there is growth. Cloud computing is one of those areas. Refactoring of applications, horizontal scaling, server consolidation, Web 2.0, SAAS, etc. are all key focus areas for customers. End users are becoming the developer. Facebook, Wikipedia, Twitter, Google analytics are generating a new level of data generation. Today there are 280 exabytes of data approximately with 10 times grown in next three years. One zetabyte will be added in next few years. Managing extremely large amounts of data is becoming a serious challenge for customers.

The Platform is a Service

Packaging this development environment is important. The platform is a service. Platform services are now being provided by large companies. Infrastructure and platforms are important services. Virtualization is a key for both of these. Virtualization will create new services.

Tuning MySQL, leveraging MySQL, LAMP, SAMP with Sun technology is important. Semi-structured data is exploding. We are moving to a new class of systems. High speed networking with large network storage is very important to customers. Sun's Open Storage solutions are going to change how the industry does business and how it transforms customers business. A simple Web 2.0 model, analytics, with extremely large data growth is a key for customers. "Join the Web 3.0 model". "Embrace the Could, make it real". Cloud computing is the future. Social networks are changing how companies manage their data. Four important areas:

• Systems
• Microelectronics
• Software and Services
• Cloud computing

The evolution of Second Life with Sun and customers is growing. Security around the server, the cloud and Second Life is a challenge for customers.

Sun's ipod?

One of the biggest frustrations of Sun employees is they feel that Sun does not put enough effort in marketing products. There was a lot of discussion on Sun's pespective on how to market Sun products and the role of marketing. When I was at Oracle World, one leading industry analyst told me "Sun's problem is they have great innovation no one knows about and they don't leverage".

What is Sun's ipod? Sun is not a consumer device company. The ipod is a great user device. It's changed how people listen to music. Matchbox, for a dollar allowed kids to have a great imagination experience. The book "Peak Performers" was mentioned. Sun needs to participate in the community to make customers understand how to leverage technology. This is the challenge for Sun.

"Knowledge is a. rare thing -- you. gain by giving it"

Sun needs to show customers how things should be done. Systems engineering and delivery needs to show best practices to customers. Ivan Sutherland and his love of technology was discussed. Ivan has one of my favoriate quotes, "Knowledge is a. rare thing -- you. gain by giving it". Charles Garfield, "People how do something are people that know what do do next". In this tuburlent environment, Sun need's to show customers what to do next.

From Hal Stern, "if you have a great idea, be aggressive, implement it." "Figure out what needs to be done and just go do it". "Courage is what it takes to overcome fear. Fear is perceived risk". "Share your ideas, take your ideas to the market". "It takes courage to challenge the market".

The following areas have been constantly highlighted: open source, networking virtualization and storage.

Second Life rocks at Sun CEC 2008

Second life is playing a key role at the Sun CEC. Keynotes, general sessions and great presentions are all occurring in second life. Second life is giving remote attendees a great live experience of the conference.

Hal Stern will come inworld at 1pm PT today, and 5:30pm PT with his avatar with a pressentation and slides specifically for the virtual audience. Here is the wiki
https://cetwo.sfbay.sun.com/display/VIRTUALWORLDS/CEC+2008+in+Second+Life

Ty Valdez Presenting in Second Life - Understanding the Popularity and Growing Emergence of MySQL

Understanding the Popularity and Growing Emergence of MySQL

Friday, November 14, 2008 8:00 a.m. PST

MySQL is growing at an incredible rate. Daily downloads are 75,000 and growing. More and more organizations are expanding their use of this popular database. This presentation will discuss the growing emergence of MySQL in the database industry. Topics will include:

• Key factors in MySQL's growing popularity.
• Positioning MySQL against other databases.
• Popular features in the MySQL database server.
• MySQL Strategic directions.

The presentation will also discuss upcoming MySQL activities and direction in Second Life.

The Sun 2008 Customer Engineering Conference

I'm definitely looking forward to presenting the MySQL sessions here at the Sun 2008 Customer Engineering Conference (CEC). The conference is at the Paris Hotel here in Las Vegas. The conference starts for me today (Sunday, November 9) with a walk through from 5:00-6:30pm. All the training track managers need to go through the walkthrough. The welcome reception is then at the Paris, Pavilion/Hang Space from 6:00-8:00pm. I am definitely looking forward to the conference getting started.

Complements to Paul Gehring and his team. I think they did an excellent job with the walk throughs for the track managers. Well executed planning and organization meeting for conference.

The welcome reception was a lot of fun. Food was awesome and environment was very festive. Also got some cool chotski at the event. Kudos to Sun and vendors, getting cool chotski was appreciated at the open reception. Yes we do love da chotski!

Loved the new computer bag received for the conference. It's a real high quality bag, and since my old Sun laptop bag broke two weeks ago the timing was perfect. Appreciated getting the nice high quality bag instead of the cheap ones you sometimes get at some conferences.

The hotel rooms in the Paris hotel are fantastic.

Ty Valdez and George Trujillo presenting at Sun CEC in Las Vegas

Ty Valdez and George Trujillo will be delivering multiple training sessions on the MySQL database server at the Sun Customer Engineering Conference (CEC) in Las Vegas during the week of November 9th - 14th, 2008. Details of the presentations can be found at http://blogs.sun.com/georgetrujillo. Key areas of presentation include:

• Positioning MySQL and MySQL database installation
• Understanding the MySQL architecture
• Storage engines and table/index management
• Client programs and MySQL Administrator
• Starting, stopping and configuring the MySQL Server
• Information_schema, administration logs and diagnostics
• Locking and transactions
• User management and security
• Optimizing queries, database and the server
• Character set support, scaling and miscellaneous
• Backup and recovery