A DBA's Journey in the Sun: George Trujillo Blog

Summary of some of the advantages of using NetBeans.[Read More]

This blog contains a summary as well as links discussing the advantages of Solaris and OpenSolaris over Linux.  This blog entry has been a good eye opener for me.  I received information that was weighted towards Solaris and decided I did not want to filter it because I thought seeing people's perspective is good.  The Linux additions have been fantastic and thanks to all the Linux feedback.  We all want truth, so hearing from both sides is excellent.  :)
[Read More]

As our global world deals with the current economic challenges you will see more and more streamlining and cost cutting by organizations.  Where open source and MySQL make sense, they are going to save organizations tons of money and allow them more operational flexibility.[Read More]

 

My presentation on MySQL in Second Life went really well.  I presented in one of the Sun auditoriums that was really cool.  It was all glass, very futuristic look.  This presentation was really popular at the Sun CEC in Las Vegas, so I customized it for Second Life and got a lot of great feedback and emails.   I'm now going to be delivering some customized MySQL and Oracle presentations for Sun employees.

I've been getting a lot of emails of people volunteering to help with the MySQL second life project and also requests for different types of sessions.   I will keep people in the loop as new presentations get scheduled.

MySQL Sessions at Sun CEC 2008

I was asked to put together a MySQL training track at the Sun CEC 2008 conference in Las Vegas.  I've never had so much fun delivering at a conference.   During the Sun Customer Engineer Conference  we delivered 11 sessions preparing attendees for eventually the MySQL DBA certification. We started off with attendees from multiple tracks sitting in the first session which gave us a body count of 84.  Over the entire week, these sessions  regularly had 70+ plus seats filled.  Highlights of the sessions include:

• Marc Sodano - discussing strategic directions in MySQL education.
• George Trujillo - delivering 9 sessions on MySQL DBA topics.
• Martin Mickos - positioning MySQL and defining directions and strategies for MySQL.
• David Stokes - discussing MySQL certification and directions.
  
• Anders Karlsson - delivering real life stories of MySQL  being used at very large web companies.
• Domas Mituzas - discussing how MySQL is being used at Wikipedia one of largest web sites in the world.
  
• Cliff Conklin - delivering sessions on Storage Engines and Transactions.
  

Attendees took MySQL Associate Exam

David Stokes the MySQL Certification Manager was there to discuss certification, define directions in MySQL certification and show attendees the path to MySQL certifications.  David also provided a lot of the energy and ideas surrounding the conference.

On the last day we offered the Associate exam  with fifty people passing and two failing.

50 exams taken
48 CMA Certifications  (94 or 96% success rate)

Attendees will now be working towards MySQL DBA certifications.

Tons of success stories: Here are a few.

Attendee after attendee from other tracks after hearing about the awesome MySQL sessions came in trying to join.  "The MySQL sessions were fun, entertaining and I never learned so much in one week".  "Some of the best presenters I've ever heard at a conference."  "The week was excellent."   The best I ever had at a CEC".  "Best organized sessions of the conference."  "MySQL rocks!"

Guest speakers a big success!

• Marc Sodano
• David Stokes
• Anders Karlsson
• Domas Mituzas
• Cliff Conklin

Marten Mickos at Sun CEC

I've gotten incredibly excited about Sun products that greatly expand the capability of MySQL. What's been great is the excitement is coming from customers.  If you are using MySQL you really need to take a look about how these Sun products are leveraging MySQL in customer environments.   The powerful features and cost savings are very significant so every customer should be looking at how they can benefit from these products.

ZFS  - fast file system supporting volume management, snapshots and copy on write clones.
Glassfish – Application server
Zones/Containers - isolated virtual servers within a single operating system instance
Zone Cluster  - high availability solution
Dtrace – powerful diagnostics tool
Open Solaris – powerful open source operating system

Sun Storage 7000 series - highly cost-effective storage solution

I have a webinar to immerse attendees in the Oracle architecture, products, terminology and key features.  This fast paced presentation is designed to give someone with a database background, a detailed overview of the Oracle database server.   SQL Server, DB2, MySQL, Postgres, Ingres, SQLLite, etc. DBAs  and developers will get an understanding of how the Oracle database server works as well as key products and features.  At my blog   web.mac.com/george.trujillo, if you select the Oracle database tab, the key slides can be found with descriptions.  You will need to go into the archive to get  some of the podcasts.  As I get time, I will complete the audio podcasts for each slide. 

Topics include:

• Oracle Architecture
• Database versions
• Database products
• Real Application Clusters (High Availability with multiple servers)
• Data Guard (Standby databases)
• Streams (Data Sharing)
•  Tools
• Enterprise Manager (Grid Control and Database Control)
• Data Pump
• SQL*Plus
• Metadata
• Cost-Based Optimizer
• Backup and Recovery
• Recovery Manager
• Flashback
• ETL
• Schemas
• Database Applications
• PL/SQL
• Java
• XML
• PHP
• .NET
• Fusion Middleware
• J2EE
• Web Services
• Service Oriented Architecture
• BPEL
• XML
• Application Servers
• ADF
• Frameworks

 

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.24 but it would be the same steps for the 5.1.30 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.

Why MOCA? 

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). MySQL 5.1 - Downloaded from dev.mysql.com.

Look for existing MySQL software

This install uses 5.1.24, 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.

 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.24
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.24. 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 No thanks, just take me to the downloads!

MySQL Directory Organization 

Organize how MySQL files and software will be located:
/opt/mysql/5.1.24 - 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.24.
# mkdir -p /opt/mysql/5.1.24
# 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

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.24-rc-solaris10-x86_64.tar.gz
$ tar xvf mysql-5.1.24-rc-solaris10-x86_64.tar
$ ln -s mysql-5.1.24-rc-solaris10-x86_64 5.1.24
$ cp $MYSQL_HOME/support-files/my-small-cnf   /dbadmin/mysql/mysql/startup/my.cnf

[mysqld]
log-error=/db03/mysql/mysql01/errors/mysql5.1.24.err
pid-file=/db03/mysql/mysql01/localhost.pid
datadir=/db01/mysql/mysql01/data
basedir=/opt/mysql/5.1.24
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

Start the MySQL database server pointing to the defined locations.
$ cd /opt/mysql/5.1.24
$ 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 <filename>

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

$ 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 

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

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

A quick getting started guide to learning MySQL.[Read More]