MySQL Architecture using MOCA

Proper installation of a MySQL database environment will make a tremendous difference in the management of the MySQL database server. This image shows key areas that need to be considered in defining the MySQL configuration. MOCA is used to demonstrate important points to consider.

I've received a lot of emails asking about defining a MySQL environment using MOCA. So I defined this image to try to summarize the core configuration on one slide. On the right it shows the MySQL instance and the storage engines.

• The MySQL instance is the global shared memory and session threads for an individual MySQL database server.
• The storage engines control how I/O is performed on tables and indexes. The individual characteristics of each storage engine will determine where and how the tables and indexes for each storage engine will reside.

Software Installation

The MySQL database software should be installed in its own file system. All dynamic files should be moved out of the software location. The software location should be a static environment.

• The MySQL_BASE directory (/opt/mysql) is the directory point for installing different versions of MySQL database software. Each MySQL database version should be installed in a subdirectory below this base directory location. Naming each software subdirectory with the database version makes it easier to manage.
• The MYSQL_HOME directory (ex. 5.1.22 for the MySQL 5.1.22 software release) is the location of a database software install. This directory should be located below the MySQL_BASE location.

MySQL Global Variables

MySQL global variables define the location of important directories and files for an individual database server instance.

• basedir - is the directory location of the database software installation for a specific release. This is equal to the MYSQL_HOME directory location for a database server.
  
• datadir - is the default location for the database files are stored. Each individual database schema will have a subdirectory below this location that will contain database .frm files and MyISAM files. The default location for the InnoDB tablespace and log files will be stored in the directory defined by the datadir parameter.
• log-bin - defines the location of the binary files required to recover a database to a point in time. This location should be different than the datadir location.
• pid-file - defines the location of the PID file containing the unique process id of the mysqld daemon that runs the database server.
• log-error - location of the primary error and event log file.

Primary operating system directories

These primary directory locations need to be well thought out for a database server. Refer to older blogs that describe MOCA in more detail. Below are sample directories that distribute database software, database files, binary logs for recovery, administration files and backup files into different physical locations. The default installation puts all these in one directory structure. Best practices strongly recommend separating the physical location of different MySQL files. It is a good practice to name directory paths with /dbxx so everyone can understand these directories contain database files. The are template directory names. The mysql2 name is the unique name of a MySQL database server.

• /opt/mysql - base location of MySQL software
• /db01/mysql/mysql2/data - base location of database files
• /db02/mysql/mysql2/binlog - base location of binary logs
• /db03/mysql/mysql2/dbadmin/ - base location of administration directories

• /db04/mysql/mysql2/backups/ - base location of backups

More MOCA please ...

There are certain facts about how database servers work. A database configuration should follow best practices to address these facts. Below is a list of certain truths about database servers that are self-evident:

• Default installs by database vendors are designed to be simple to install and under configured to minimize the initial use of resources. Default installs should not be used to run production servers.
• Relational database servers run faster when memory is configured properly.
  
• Balancing disk I/O is important.
  
• Treat your information and the data you need to do recovery as matter and anti-matter. Never put them together.
  
• Separate your database software from your data.
• Separate your information logs and administration files.
• Database software should never be installed as root or as the operating system administrator.
• Database software, data, logs, administration files, recovery files should be kept separate from other types of files.

Default installs do not address the above. The default software for MySQL is designed to be simple to install and minimize the use of initial resources. However new users perform the default install and then use the default install to run an important database. This default install creates a "lot" of problems if you want to use MySQL as a database server. This is why I created MOCA. MOCA is defined further down in this blog.

MOCA stands for the MySQL Optimal Configuration Architecture. This is a summary of best practices that apply to any database server. Every experienced production DBA from Oracle, DB2, SQL Server, Informix and MySQL uses something similar that contains a list of guidelines and best practices that they have learned from years of experience that should be followed. For years Oracle DBAs have followed OFA (the Optimial Flexible Architecture). MOCA is a summary of best practices modeled from OFA and that I have picked up from 20 years of DBA experience. It is not a complete listing but a set of common sense guidelines that should be considered for a database installation.

I strongly recommend that if you are new to database administration you review MOCA. It pains me to see customers using the default install of MySQL that they installed as an operating system administrator userid and then they wonder why they are having so many problems. The default install will create pain and anguish in your life if you use it as a production configuration.

Create aliases to make MySQL administration easier

When working in a Unix/Linux/Mac OS environment, aliases can make things a lot easier if you are running the same commands over and over again. Here are a few aliases you can define to simplify running MySQL commands. Then just type the alias name. Put these in one of your configuration files dependent on the Unix shell you are running so these aliases will be automatically set in your environment.

Alias examples ($ represents the command line prompt):

$ alias myalias='alias | grep mysql'

$ alias myenv='env | grep mysql '

$ alias mystat='mysqladmin -u root -p status'

$ alias myestat='mysqladmin -u root -p extended-status'

$ alias myping='mysqladmin -u root -p ping'

$ alias myrun='ps -aux | grep mysql | grep -v grep'

$ alias mysql='mysql -u root -p'

$ alias mystop='mysqladmin -u root -p shutdown'

$ alias mysafe='nohup /opt/mysql/5.1.22/bin/mysqld_safe -- defaults-file=/dbadmin/mysql/startup/my.cnf & '

To view your aliases type the following:

$ alias

There are a lot of additional aliases you can define to make your daily tasks easier. Normally aliases are put in a startup file (in the $HOME directory) that is executed each time a Unix shell is started.

• In the Korn shell (ksh) put the aliases in the .kshrc file.
  
• In the Bash shell (bash) put the aliases in the .bashrc file.
  

Favorite 3rd party software with MySQL

Here are the results of a small poll I conducted on favorite 3rd party software with MySQL. The poll results are:

• SQLYog 48%
• Nagios 15%
• Other 14%
• Toad 10%
• Navicat 7%
• DBArtisan 3%
• Innodb Hot Backup 1%
• BMC 1%
• Cfengine 1%
• Zmanda 0%

Since this was an informal poll (take with a grain of salt), so I wanted to add a few insights I have found in customers that I have been working with.

• SQLYog and Nagios are very popular in MySQL environments.
• Navicat has its own fan base in the MySQL world.
• Toad althought popular in the Oracle world is very quirky when used with MySQL.
• Zmanda althought it did not get any votes, I do see it being used quite a bit by students in my classes.
• I was really surprised that I do not see a lot of people using InnoDB Hot Backup.

The Incredible Growth of Open Source

Getting Hooked on Open Source

Open source is kind of like M&Ms. It's hard to have just one. Once people understand open source, they start looking at more ways open source can be used in their organizations. The cost savings can be dramatic. In the old days people would say "You won't get fired by choosing IBM". Those days are over. You may not get fired by buying a large vendor solution, but a lot of people may get laid off or outsourced due to large vendor costs and licensing.

Open source by definition is a "sharing" environment. Every class I teach students are sharing different open source products they use for Wikis, ticketing systems, communication, monitoring software, operating systems, database servers, application servers, development environments, etc. What's more they are also sharing scripts, ideas and planning on collaborating more after the class ends. This is a very cool environment for a technologist.

The Microsoft Word Effect

I look at it as the Microsoft effect. You go out and buy Microsoft Word for $329.99. Most people use Word for setting bold, a few fonts, spell checking, italics etc. People pay over $300 for one vendor (Microsoft) product and 95% of all users don't even use 5% of that product. I went and bought iWork that has Pages, Keynote and Numbers, the Apple equivalents of Word, PowerPoint and Excel for $99 for a five system family pack. This wasn't an open source solution, the point being that why keep paying large amounts from vendor software if you are not using a lot of their features. Arguably by going to iWork I not only saved a lot of money but also got a lot more functionality that the vendor (Microsoft) product. It took being able to think out of the box and thinking I had to buy a large vendor product.

I also bought Notebook software for my Mac to help keep myself organized and to take notes. One of my students then showed me Tiddlywiki and I can use it for free on all platforms. I keep a copy of it on my flashdrive and I take it everywhere with me. There are a lot of points if you look at open source, you have a lot more options and will likely save a lot of money.

Open Source Can Dramatically Reduce IT Budgets

Companies are seeing that open source solutions such as Linux, MySQL, JBoss, Apache, Eclipse, RT, etc can save them tremendous amounts on their IT budgets. If you need all the feature functionality from a major vendor then it makes sense to pay their prices. In most companies their are numerous project areas where open source can provide large cost savings and still meet core functional requirements. Using open source may not only keep you from getting fired, it may also keep most of your employees from getting outsourced or laid off.

The Fear of Open Source

Don't misunderstand what I am saying. The features provided from the large vendors can be very important to an organization. There is a reason you pay large amounts of money for large vendor features. If you need an aircraft carrier and you have the money for it, go get it and you'll sleep soundly. For extremely large mission critical systems I would highly recommend looking at the best solutions money can buy if you need it. What I'm saying is if you don't need an aircraft carrier then look for the right sized solution that will meet your needs.

A lot of managers are scared to move off the security of large vendors and to look at open source. In the future you are going to see managers looking for more flexibility, nimbleness and the need to manage costs more effectively as growth occurs.

Globalization has allowed large corporations world wide to compete with U.S. companies. Open source is growing significantly in popularity in places like Europe and China. With low cost solutions like open source, in the future you will see smaller companies begin to compete more effectively in the U.S. as well. U.S. companies need to look not only at the strength and flexibility of the IT organizations but also at the costs of their IT organizations to compete locally as well as world wide.

Good Articles on Open Source Growth

Different industry groups are predicting up to 27% growth annually in open source software by 2010. Small businesses have always been a core component of the U.S. economy. Small businesses and individuals can often compete against much larger companies at a fraction of the large company costs by using the Internet and open source. Why shouldn't large organizations also leverage these costs benefits? Especially since your competiton is probably using open source.

Here are a few interesting articles on open source growth.

• The Growth of Open Source in Organizations
• Unisys sees open source growth on the horizon
• Growth Through Reaching the Participation Fringe

I always welcome hearing about different open source solutions.

MySQL and VMWARE

VMWARE keeps popping up in more and more clients I go to. Virtualized environments are continuing to evolve. Companies are looking for more ways to stream line costs and improve efficiency and virtualized environments are one way to accomplish this.

Observations on virtualized database environments is that this is still an area that needs to mature a lot before being put in production environments. Don't get seduced to the dark side unless you understand all the issues. Thoughts on virtualization for MySQL:

• Production databases - No way, nope, nyet, nien, bu shi, iie.
  
• Test databases - When bench marking and performance tuning are not factors at the time, virtualized environments can play a role here. Has to be used in a controlled environment.
  
• Development databases - Extremely efficient and cost effective.

I personally run Apple MacBook Pros and enjoy working with VMWare Fusion and Parallels. In my simple test environments I am running MySQL Servers, Oracle Servers, Eclipse, JDeveloper, Apache, JBoss and the Oracle Application Server in Solaris, Enterprise Linux and Fedora 7 environments. I'll eventually load a version of Vista. I save my VMs and then it is really easy for me to create a new copy of my VM from a backup. VM is a great environment for new MySQL DBAs to play around with different configurations, running multiple servers, etc. DBAs can also use VM's to create a new test or development database environment in a matter of minutes.

Definition of Virtualization

We are all seeing that virtualization will be an area of continued growth. Virtualization from Wikipedia:
In computing virtualization is a broad term that refers to the abstraction of computer resources. One useful definition is "a technique for hiding the physical characteristics of computing resources from the way in which other systems, applications, or end users, interact with those resources. This includes making a single physical resource (such as a server, an operating system, an application, or storage device appear to function as multiple logical resources; or it can include making multiple physical resources (such as storage devices or servers) appear as a single logical resource."

As you are well aware, each vendor has their own definition of virtualization. In the Wikipedia definition (above), they define platform virtualization, resource virtualization as well as some good virtualization examples.

<sup>The Role of MySQL in Virtualization

As databases continue their rapid growth and proliferation, organizations are looking for more ways to reduce hardware and software costs. Large vendors don't want customers to look at hardware and software from a commodity perspective. However, powerful open source solutions are giving organizations a lot more flexibility in purchase decisions. Open source solutions offer much more cost effective solutions with MySQL, Linux, Intel (and AMD) hardware with muliple CPUs. We can definitely expect that solutions like MySQL and Linux will play a large role in virtualized environments today and in the future.</sup>

There is also a new forum at http://forums.mysql.com on discussing VMWare and MySQL if you have comments.

MySQL: Favorite 3rd Party Software

I've started a new poll on popular 3rd party software used with MySQL. I've recently been involved in a lot of discussions on favorite software used by MySQL DBAs. I realize there are a lot of great solutions. I narrowed the list to a few software solutions that I've had a lot of discussions about in the last few weeks with students and clients.

I decided to restart the poll by adding SQLYog. Below are the initial numbers before restarting the poll. I won't restart the poll again. These numbers will be included in the summary blog after the poll closes.

• Nagios 6
• Cfengine 0
• Navicat 3
• Quest (Toad) 6
• Zmanda 0
• Embarcadero (DBArtisan) 1
  
• Innodb Hot Backup 1
• Other 6

As databases proliferate in numbers, companies are struggling more with deciding what is the right tool to monitor and manage our databases. Especially with MySQL which is likely to run in a heterogeneous environment. Do I use open source tools or do I use tools like BMC, Quest and Embarcadero to help me manage a heterogeneous environment? I've been involved in a lot of discussions on this in the last year. I hope you are lookig forward to seeing how the numbers shake out as much as I am.

I hope you don't get frustrated if you don't see your favorite tool on the list. I wanted to keep the list to a top ten and on software that has been hot in a lot of conversations lately and make sure this wasn't just my list. At the same time Lake City, CO in the San Juan Mountains did not make my top ten list in CO, still not sure how that happened. Lake City, CO is an absolutely beautiful place in Colorado.

In this list is a mix of tools very popular in heterogenous environments, mixed with tools that are popular in the open source world. It includes development tools, DBA and monitoring tools. The goal is to get a feel if organizations lean towards heterogeneous tools or if for MySQL they focus on tools specific to MySQL environments.

Another issue not addressed by this poll is the management of multi-tiered infractructures which brings in the application server and web server. The whole question of how to manage multi-tiered infrastructures is a completely different poll.

The list includes:

• Nagios
• SQLYog
  
• Cfengine
• Navicat
• Quest (Toad)
  
• Zmanda
• Embarcadero (E/R studio)
  
• Innodb Hot Backup
• BMC
  
• Other

I did not include MySQL Enterprise Monitor or MySQL Query Browser since they are MySQL tools. The goal was to see what products outside of MySQL stand out.

Poll on Favorite Development Environments for MySQL

I did an informal poll on favorite development environments for MySQL and I was surprised to see how PHP seemed to be signiciantly more popular in MySQL environments versus Java, Ruby on Rails or .NET. Here are the results of 99 responders:

1. PHP 52
2. Other 23
3. Java 13
4. Ruby 5
5. .NET 5
6. Access 1

The Growing Popularity of MySQL: The friendly kid in the playground.

I was sitting at dinner the other night with some MySQL DBAs that have worked with Oracle and SQL Server and we were talking about the differences between the different databases and the different paths to success. These impressions were just points of discussion over dinner.

Oracle - For Oracle we talked about how Oracle grew with an incredible sales team and as Oracle grew, the feature/functionality grew with it. Now it is the aircraft carrier of database servers. If I'm a Fortune 1000 company and I need to run financials, Oracle's Jedi skills are strong in this area. I've worked with Oracle technology for 20 years and I am a fan of Oracle for the right environments where cost is not an issue. If you are in large complex environments, Oracle has strong complex tools for solving complex problems.

SQL Server - Great marketing. Whoever came up with the name of "SQL Server", this name is brilliant, this person should be drinking pina coladas in Aruba. Great point and click database functionality with good price points compared to Oracle and DB2. In a world of point and click people, SQL Server is popular and easy to work with relatively speaking.

MySQL - An incredible wave of customers looking at the great price point of running MySQL servers for projects. The cost savings for projects can be incredible. MySQL is like the friendly kid on the block that has cool toys. MySQL makes sense to be able to run in environments that are also running Oracle, SQL Server and DB2 for a great price point on separate projects. So MySQL plays nice with everyone. It also has great features like replication and clustering. MySQL also has great portability with Linux, Windows, Solaris, HP-UX, AIX, etc. MySQL also has very simple upgrades compared to other database servers. The power and popularity of open source is growing dramatically. The Jedi powers of open source are very strong. As Linux has become incredibly popular as a low cost powerful solution, MySQL has become very popular as well. A lot of companies are realizing on a lot of projects they just need core relational database ACID features and in this environment companies are seeing MySQL as an excellent solution.

DB2 - Someone described DB2 by comparing it to the T-34 Russian tank used in WWII. Not pretty, fancy or a lot of features compared to products like Oracle but it does an excellent job as a powerful database server.

Anyway, just a few shared conversation thoughts on different database servers all tongue in cheek. :)

Two Blogs on Understanding Bug Issues in MySQL 5.1

Nothing frustrates datababase users of all database vendors more than to not be aware of known bugs in new releases. It's difficult when you have to find them out one at a time. Especially since customers going to new releases are often champions of the database vendor. Everyone understands that are there are likely to be bugs in new releases especally if the release is not in GA yet. MySQL has done an excellent job of posting information on known bugs for release 5.1.

The new features in MySQL 5.1 are likely to push the envelope of users wanting to go to a new release sooner versus later. Features like events, partitioning, row-level replication, server log tables, replication between clusters, the mysql_upgrade program, XML functions and the Load emulator are creating a lot of excitement among existing and new customers.

Check out these two excellent blogs to get information on outstanding bugs surrounding release 5.1:

http://blogs.mysql.com/kaj/2007/10/20/mysql-51-errata-sheet
http://www.jpipes.com/index.php?/archives/205-Want-to-Use-MySQL-5.1-A-Bug-List-Made-For-You....html

Thanks Kaj and Jay for these blogs. Its great that MySQL is providing this type of support for customers using the 5.1 release.

MySQL: Which programming language for MySQL?

I started a poll on programming languages for MySQL because I am trying to decide which programming language to get up to speed on for some personal website development. Maybe even some online learning to share things I learn.

After watching the poll, I realize there were some others I should have put on the list. The problem is once you start the voting you can't change the poll. I tried to change it and lost 19 votes. Sorry everyone, it was an accident.

I'm going to go on a mini-journey for about a month researching which of these languages I pursue in more depth. I will ask all the people in my travels which they prefer. I'll follow with my results. I'd welcome any input on these or other languages, in terms of what you like or don't like about them. Oh by the way I'm not an HTML person but I figure I can get someone to do some of the HTML design for me. My objectives are to find a language that:

• I can get up to speed quick on.
• Has a lot of examples, demos on the web.
• Doesn't require I make magic with HTML or web interface design.
• Something that is likely to stay hot so the time invested won't be wasted.
• Something that is fun to work with.
• Something you'd defend in a room of other programming experts.

To give you my programming background. I have programmed a lot in Unix environments. In a former life I used to write a lot of low level code in C. I've worked a lot with C++ and very comfortable with Unix shell scripting, Perl, etc. I have spent a good part of recent years in the Java world. I speak J2EE, Web Services, SOA, BPEL, frameworks, design patterns, Struts, Spring, ADF, XML, etc. I'm not a web interface designer.

My dilemma. Although I am a big fan of Java, for building personal web sites, I think Java is too heavyweight for personal websites. I'm not a .NET person, nor will I be. I'm looking at:

• PHP
• Ruby on Rails
• AppleScript
• Ajax
  
• Adobe Captivate
• Python, Perl and PHP
  

MySQL Administration Tools

Which tools to use as a MySQL DBA to monitor and manage a MySQL environment with multiple MySQL servers? Coming from an Oracle world, an Oracle DBA is used to OEM Grid Control, Embarcadero, Quest Software, BMC, etc. So what tools make the most sense in a MySQL world?

I'm also a bit twidler from way back. In this day of technology, vi is still my favorite editor. The days of writing cool scripts as a DBA, is like the day of the Gunfighter, those days are over. Every script written adds more administration and maintenance to a complex environment. Although I grew up in the days of writing awk, sed, shell and perl scripts using software for monitoring and management is more scalable and cost-effective for managing multiple databases. In this complex environment, DBAs need to use software to help with the monitoring and maintenance of a large complex database environment.

The following tools are popular in MySQL environments:

• Mytop - Is a tool for monitoring threads and MySQL performance similar to the Unix top program.
• phpMyAdmin - A web-based MySQL DBA administration tool for managing MySQL, users, tables, running queries, etc.
• innotop - An InnoDB and MySQL monitor tool.
• Nagios - is a monitoring tool for hosts, services and networks.
• MySQLMonitor - The MySQL Enterprise Network Monitoring and Advisory Services tool is a tool for monitoring a MySQL environment with multiple advisors that offers a lot of benefits.
  

Additional tools recommended by readers.

• Cacti - A network graphing solution.
  
• OpenNMS - An open source network management platform.
  

Installing MySQL 5.1.22 on Mac OS using MOCA

Below I have the high level steps I took to perform a simple install of MySQL 5.1.22 on Mac OS 10.4. These instructions can be followed for any MySQL 5.1.xx install. These are not detailed instructions. The objective is to overview a simple installation of MySQL using MOCA. The Mac OS installation will be similar to a Unix or Linux installation. Go to the MySQL documentation to review instructions if you are using a different operating system.

This is not a recommended install for a new MySQL DBA (perform a default installation to get comfortable with MySQL first). If you are an experienced DBA from another platform and understand Unix directory layouts this installation may be okay if you review the MySQL documentation first. Review the blog "The MySQL Optimial Configuration Architecture" for the reason for this layout.

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. Set up the operating system and directory structures (physical storage) for running MySQL.
2. Setup the mysql operating system user id.
3. Install the MySQL software in the MYSQL_HOME directory.
4. Create a startup file (my.cfg).
5. Run the mysql_install_db script to set up the MySQL data directory.
6. Start the MySQL server instance by running mysqld_safe.
7. Test the MySQL Server instance by running the perl script mysql-test-run.pl.
8. Secure the mysql password environment with the mysql_secure_installation script.
9. Login in using the mysql client and verify the installation. Review the data files, log files, binary log, error logs, etc.
10. Define a server startup method. The script mysql.server is a likely option.
11. Define a backup and recovery strategy. Test your back and recovery processes.
12. Have fun with MySQL. :)

Installation Environment
Hardware: MacBook Pro laptop , 2GB of memory, dual core
Operating System: Mac OS 10.4
Disk: 160 GB
MySQL: RC 5.1.22 for Mac OS X (TAR packages)

Create the directory /opt/mysql for placing the 5.1.22 software.
Go to http://dev.mysql.com and choose the 5.1.22 download (mysql-5.1.22-rc-osx10.4-i686.tar.gz). Place this zip file in the /opt/mysql/5.1.22 directory.

Defined the following 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.
/opt/mysql/

/opt/mysql/software
/db01/mysql/mysql01/data - data directory
/db02/mysql/mysql01/mysql-bin - location of binary log files

/db03/mysql/mysql01/ - administration directory
/db03/mysql/mysql01/ - location of the PID file
/db03/mysql/mysql01/startup - location of my.cfg file
/db03/mysql/mysql01/run - location of socket file
/db03/mysql/mysql01/errors - location of error file
/db03/mysql/mysql01/logs - location for general and slow logs
/db03/mysql/mysql01/scripts - administration scripts
/db03/mysql/mysql01/sql - generic sql code

/db04/mysql/mysql01/backups - backup files, exports, miscellaneous

Preinstallation steps:
Checked to see if there was a previous MySQL installation. Seen the MySQL 4.1 installation came with the MacOS system. Verified an older version of MySQL was not currently running. Used the RPMs to deinstalled the MySQL 4.1 installation.

Verified the setup the mysql operating system user account. A mysql user already existed in the /etc/passwd file. Using the Finder window in Mac. These commands only work with Mac OS 10.4. Use the 10.5 method for the new Leopard OS.

• Go to the Applications location for Mac OS.
• Go to Utilities subdirectory.
• Click on the NetInfo Manager.
• When the window pops up, select users. Then click on mysql user.
• Click the lock at bottom of page to make changes. Set a password, home directory (/Users/mysql) and shell (/bin/ksh).
• Save the changes.
• Open a terminal window. Type exec login mysql. Type the new password and verify the mysql userid defaults to the correct home directory (/Users/mysql).
• Use vi or another text editor to create a .profile file in the /Users/mysql directory with the following values:

export MYSQL_BASE=/opt/mysql
export MYSQL_HOME=$MYSQL_BASE/5.1.22
PATH=$PATH:$MYSQL_HOME/bin

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!

$ cd /db01
$ sudo chown -R mysql:mysql .
$ cd /db02
$ chown -R mysql:mysql .
$ cd /db03
$ chown -R mysql:mysql .
$ cd /db04
$ chown -R mysql:mysql .
$ cd /opt/mysql
$ chown -R mysql: mysql .

Setup up the MySQL software.
Login in as the mysql operating system user id and verify.
$ who am i
mysql ttyp1 Oct 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.22 as the software location for MySQL.
$ cd /opt/mysql
$ gunzip -c mysql-5.1.22-rc-osx10.4-i686.tar.gz tar xvf -
$ ln -s mysql-5.1.22-rc-osx10.4-i686 5.1.22
$ mv mysql-5.1.22-rc-osx10.4-i686.tar.gz file /opt/mysql/software/5.1.22

Set up the startup file (my.cnf) for MySQL
Make sure to review each of these startup parameters.
$ cat /dbadmin/mysql/mysql01/startup/my.cnf
[mysqld]
log-error=/db03/mysql/mysql01/errors/mysql5.1.22.err
pid-file=/db03/mysql/mysql01/localhost.pid
datadir=/db01/mysql/mysql01/data
basedir=/opt/mysql/5.1.22
log-bin=/db02/mysql/mysql01/mysql01-bin

[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. You should see the files ib_logfile0, ib_logfile1, ibdata1 and the subdirectory mysql from the output of the ls command.

$ cd /opt/mysql/5.1.22
$ scripts/mysql_install_db --defaults-file=/db03/mysql/mysql01/startup/my.cnf
$ cd /db01/mysql/mysql01
$ ls -la

Start the MySQL daemon using the new startup my.cfg file.
$ cd /opt/mysql/5.1.22
$ 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.
$ cd /opt/mysql/5.1.22/mysql-test
$ perl mysql-test-run.pl > mysql-test-run.output

Login to the mysql server and secure the password environment.
# Login to mysql and see that no passwords have been setup yet.
$ mysql -h localhost -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 -h localhost -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 for 5.0. which includes the following:

• MySQL Administrator 1.2
  
• MySQL Query Browser 1.2
  
• MySQL Migration Toolkit 1.1 (currently not available for Mac OS in bundle)
  

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

Informal Poll: What are the most important areas for you to learn related to MySQL?

I did a short informal poll to see what are the most important areas for you to learn related to MySQL. These numbers were very consistent with the last class I taught in terms of new students priorities.
17 (68%) Performance Tuning
13 (52%) Management Best Practices
13 (52%) Backup and Recovery
9 (36%) High Availability
5 (20%) PHP
4 (16%) Stored Procedures
4 (16%) Linux
0 (0%) Apache or JBoss

Get involved with MySQL Conference and Expo 2008

Enhancing your career through conferences and user groups

If you are an IT professional, one of the best things you can do for your career is to get involved in a conference, business or technical users group. Especially as a supporter of open source: collaboration, exchange of ideas, networking with peers, industry recognition are great reasons for attending a users conference. Some of the benefits of attending a conference or joining a users group includes:

• Networking and meeting peers and establishing new friendships.
• Improving your technical and business skills.
• Learning how the market you work in is changing.
• Meeting new people and building mutually beneficial business relationships.
• Having recognized experts share their knowledge.
• Access to additional sources of information.
• Sharing of ideas and exchanging of information.
• Getting expert advice.
• Improving your interpersonal and communication skills.

Every poll I've ever seen shows attendees of conferences and user groups are more successful in their careers and have higher salaries than those who do not. Constantly improving your skills, knowledge and networking are very important to your career and your wallet.

Oracle, DB2 and SQL Server DBAs at MySQL Users Conference and Expo:
Most companies are running multiple database products. If you are a DBA of another vendor adding an additional database you can support makes you more marketable to companies. A lot of people think MySQL is trivial because it is a database popular in SMB and open source environments. MySQL can be very easy to work with it but it can also get very detailed when running in high profile environments. As a professional DBA it is good to have additional databases you can support.

Incredible value in attending a conference:
It often boggles my brain that MySQL DBAs have to often fight to be able to attend a MySQL conference. An attendee will be listening to the top industry experts for a week. Attending one presentation can often pay for the conference ten times over. Attending a conference and being able to attend presentations by leading experts is the best return on training dollars you can get. There is great value in attending presentations on topics related to MySQL such as PHP, Java, Linux, Windows, JBoss, Apache, LAMP, etc. You can get ideas, techniques and successful methods for supporting MySQL environments.

Catch the Enthusiasm of MySQL

MySQL AB is an organization fully dedicated to open source. Every DBA religion (SQL Server, DB2, Oracle, etc.) has its zeolots. I have also carried the banner for years and enjoyed it. At MySQL the open source environment has an incredible amount of energy and enthusiasm for sharing from the open source community and working with the open source community to keep making MySQL and other open source solutions better and better. In the open source world, it becomes natural to communicate with other open source enthusiasts in Russia, Australia, China, etc. We all feel "the force" of open source. Attending the MySQL User Conference and Expo in 2008 will allow you to share a lot of the energy and enthusiasm of the MySQL and open source community.

To get the most out of attending a conference:

Volunteer! This is the best way to meet new people and feel a part of the conference. Join a group or a conference/volunteer committee at a conference and get involved in key technology areas. Introduce yourself to key leaders of the Users Group and ask them to help you maximize your attending the conference.

Enjoying and staying a part of a users conference or users group is like going to high school. If you are a new student, the easiest way to meet people is to join the band, sports team or the debate team. A conference and users group works the same way. A conference is always looking for new volunteers to bring new energy, ideas and enthusiasm to an organization. A conference cannot succeed without great volunteers. Conferences and user groups are always looking for someone like yourself to get involved. If you attend a technical conference, find out how you can volunteer. It is one of the easiest ways to feel more a part of the organization and get to meet new people. I would also recommend seeking out leaders and ask them how you can get more involved. Some of the top people in conferences and users groups have said they never imagined themselves being a leader but someone encouraged them and the more they got involved the more they enjoyed it. The more effort you put into joining an organization the more you will get out of it.

The benefits and enjoyment of joining any organization is directly correlated to how much you get involved. Individuals are often shy, nervous or do not feel comfortable networking at a group they are new to. Volunteering with different organizations is one of the easiest ways to meet new people and feel a part of an organization. Getting involved within a users group and attending a conference offers a lot of benefits.

Remember, VOLUNTEER and make a difference to an organization you want to be a part of and reap the benefits of all the networking and fun you will have. I hope to see you at the conference! If you attend any of my presentations I will try to make you laugh with at least one joke.

The MySQL Optimal Configuration Architecture

The MySQL Optimal Configuration Architecture (MOCA)

I am a firm believer in establishing best practices for database management and following them. As your databases grow in numbers and size, it becomes critical to establish best practices for your database management. I was asked recently to write down some of these best practices for managing MySQL databases.

Experienced DBAs have been following similar rules for years. They've learned that following these rules can minimize down time, reduce errors, minimize administration costs and improve performance. I am just sharing rules that DBAs have learned for years that to avoid pain and anguish as a DBA these rules or similar rules should be followed.

Key goals of the MySQL Optimal Configuration Architecture (MOCA) include:

• Define a set of guidelines for how to layout your MySQL software, data files, binary log files, administration logs, error files and administration files.
• Separate operating system administration files from database administration files.
  
• Emphasize the importance of standards and naming conventions.
• Define a physical layout to minimize downtime.
• Separate data files from the files needed to recover the data files.
• Defining a physical layout that is scalable to support multiple MySQL servers.
• Minimize administration costs of supporting multiple MySQL servers.
  

MOCA not GOCA

I thought it might be a good idea to name this layout. I initially wanted to name it George's Optimal Configuration Architecture (GOCA) but thought the MySQL Optimal Configuration Architecture (MOCA) rolled off the tongue better. It's easier to say "moca" versus "goca".

The nice thing about MySQL is that it can be a very simple install on platforms like Windows (note it can be an involved install on some platform and releases). Point click and it is up and running on some releases and platforms. If your environment is going to have a large number of MySQL installations your configuration will be more detailed to support larger environments. The configuration layout below is not designed to solve the meaning of life. The goal is to point you in the right direction and to have you consider the ramifications of your database layouts.

MOCA is not a trivial install

If you are new to MySQL or database administration, a default install may be more appropriate for getting comfortable with MySQL. It is important to understand that MOCA is a non-default install is not a trivial install. This install assumes you understand how to configure a MySQL server and you are comfortable with working with a database environment spread across different locations. If you are learning MySQL and it is your first install I would recommend doing a default install.

MOCA is set of best practices that should be used by experienced MySQL DBAs that are comfortable with setting up the MySQL environment and working with a MySQL environment.

Default Installs versus MOCA

As a MySQL DBA its important to note that MySQL is extremely portable across platforms but the default installs in Windows, Linux, Mac OS and Solaris are completely different. The default installs are great for the beginning DBA to learn MySQL. However the default install configurations should not be used for production environments running on different servers.

Developing a standard like the MySQL Optimal Configuration Architecture (MOCA) is important for defining consistency in configurations across different platforms. In different platforms looking for MySQL files (default installation locations) in /var/lib/mysql, /usr/local/mysql, /usr/sbin, C:/Program Files is not a consistent configuration. Read about MOCA and make sure you have consistent configurations across all hardware platforms.

Operating System Filesystem Hierarchy Standards

A key goal of MOCA is to define a set of guidelines and standards for how to layout your MySQL software, data files, binary logs, logs, administration files and to stress the importance of naming conventions.

Developing filesystem hierarchy standards for any operating system is important. The nice thing is OS administrators have usually taken care of this for the DBA. OS filesystem guidelines should not need to change significantly for databases.

Using the Linux Filesystem Hierarchy Standard as an example looks like this:
/initrd /proc /tmp
/bin /lib /root
/boot /lost+found /sbin
/dev /media /usr
/etc /mnt /var
/home /opt /srv

Using Linux as an example:
/etc - Will add startup scripts to make sure MySQL boots at startup, etc.
/home - Will contain home directory for MySQL userid.
/opt - Will contain MySQL software.

The big question is what to name the mount point directories that will hold the database files. I like them to have a pattern naming convention that shows these directories are dedicated to database files. Setting your own pattern is fine as long as it makes sense and is used consistently. A numbering convention should be used so all directories are the same length.

• Less than 100 directories us 00 - 99.
• Less than 1000 directories use 000 - 999.
• Less than 10000 directories use 0000 - 9999.

Example directory names:
Syntax:

• /[dbxx]/[software]/[db server name]/[specific type of directory]

Begin directory hierarchies for database files with (/db01)
Define database directory for mysql (/db01/mysql)
Define database server name for specific server (/db01/mysql/mysql01)
Define specific type of directory (/db01/mysql/mysql01/data)

Example directory structure

/db01/mysql/mysql01/data - MySQL data directory
/db02/mysql/mysql01/binlogs - location of binary log files
/db03/mysql/mysql01/ - administration directory HOME

/db04/mysql/mysql01/backups - backup files, export files and miscellaneous backups

These rules are dependent on the amount of hardware, disks, controllers and memory you have for your database environments. Remember more disks and memory makes you a better DBA. Here are my top recommendations for configuring a MySQL environment. A Unix/Linux format is shown but a similar naming convention should be used with Windows, etc. The naming conventions below are guidelines. The important thing is that once naming conventions are defined they be followed consistently on all database servers.

• Install the MySQL software (MYSQL_HOME) on its own disk partition. This should be a fairly static environment and not have any dynamic files in its location. Use a standard naming convention for each version of MySQL software. Try to avoid putting MySQL software in default operating system locations. Example:

MYSQL_BASE=/opt/software/mysql
MYSQL_HOME=$MYSQL_BASE/4.0 -- Home directory for version 4.0
MYSQL_HOME=$MYSQL_BASE/5.1 -- Home directory for version 5.1
MYSQL_HOME=$MYSQL_BASE/6.0 -- Home directory for version 6.0

• Each MySQL server should have its own administration directory. Directories for log files, scripts, backup startup files, etc. should be defined. The log directories here are only for the binary logs not the system logs (general, slow, query, ...).
  

-- Administration directory structure for server mysql01.
/db03/mysql/mysql01/logs
/db03/mysql/mysql01/scripts
/db03/mysql/mysql01/sql
/db03/mysql/mysql01/startup
/db03/mysql/mysql01/run

• Separate the binary logs location from the system logs. System logs such as the general log, slow query log, error log, etc. should be in a separate admin log.
  
• Place data files and binary log files on separate disks and controllers if possible. Treat data files and binary log files as matter and anti-matter. Never put data files and the files needed to recover the data on the same disks. Develop a consistent naming convention for where to place your data files and your binary log files.
  

-- Location of data files for MySQL servers mysql01 and mysql02.
/db01/mysql/mysql01/data
/db01/mysql/mysql02/data

--Location of binary log files for MySQL servers mysql01 and mysql02.
/db02/mysql/mysql01/binlogs
/db02/mysql/mysql02/binlogs

• Develop a consistent location for disk backups on all your database servers. Additional database exports should be stored in a consistent location.
  

-- Backup location for server mysql01.
/dbbackup/mysqldata/mysql01/backups
/dbbackup/mysqldata/mysql01/exports

-- Backup location for server mysql02.
/dbbackup/mysqldata/mysql02/backups
/dbbackup/mysqldata/mysql02/exports

• Always backup your startup configuration files. Using a version control system like RCS can be a good solution.
• When tuning or changing your database configuration, make small changes and see how they go. Try to avoid making a number of changes at once.
• Before installing a new release of software make sure you understand the current bug report on the new release and you have sufficiently tested the new environment the applications will run in.
• Periodically test your backups and recovery scenarios.
• Don't type DBA passwords on the command line prompt, ever! This also includes avoid using mysqladmin to set passwords at the command line prompt.
  
• Avoid running DML statements on the mysql system tables. For example, use the SET PASSWORD command, not the UPDATE command on the mysql.user table. Another example is use the DROP USER command not the DELETE command on the mysql.user table.
  
• Separate operating system administration from MySQL administration. Avoid putting MySQL files in directories like /etc (/etc/my.cnf), /usr/local (/usr/local/mysql) and /tmp (/tmp/mysql.sock).
• Be operating system neutral. For example, don't name your startup file my.ini and my.cnf on different platforms. Be consistent across all systems (i.e. name the startup file my.cnf on all platforms).
  
• Set SQL_MODE to an appropriate value for your environment. The "traditional" value makes sense in most OLTP environments.
• Understand the benefits and management requirements of the different storage engines.
• For important databases strongly consider the Enterprise support offered by MySQL. It is relatively very inexpensive but very important for supporting a MySQL environment.
• Consider using software to help you manage and monitor a MySQL environment.
  
• Before implementing any configuration (including this one) make sure it is well-tested and has gone through a number of different scenarios.
• Use fully qualified host names (myhostname.mycoolcompany.com). Do not use unqualified host names (myhostname).
• Be nice and supportive to your users. Remember, there are more users and developers than DBAs. If you are in a small shop and you are the DBA and developer, be nice to yourself.
  
• Develop a set of database polices that new users have to read and sign to be given a new account. This should be part of the help desk ticketing system. Get the approval of management and their support of these policies.
  
• If there are a large number of MySQL databases and I/O performance is important it may be necessary to use a storage system that supports striping.
  

Conclusion

Remember that DBA work does not always occur first thing in the morning when you are bright eyed and bushy-tailed. It may occur after working a 12-hour day or at 2:00 a.m. in the morning. The more you develop a consistent method method for how database related files the more you will reduce errors and make it easier to maintain your database environment. Spreading I/O for high activity data files will also improve performance.

This consistent layout will make it easier to write scripts and setup administration practices. Just double check and make sure you are not copying or writing files into the wrong directories.

These guidelines are designed to point out methods of how you want to organize your databases in a large enviroment. Customers are running hundreds and thousands of MySQL databases. Larger environments are also running storage arrays supporting striping and mirroring. Work with your operating system administrators and storage management team to determine the best physical layout of your databases.

Top Ten Things to do before installing MySQL

I think it is very important that DBAs (especially new ones) do sufficient planning, physical design, risk analysis and backup/recovery planning before installing MySQL. MySQL is an easy database to install and configure on a wide range of platforms.

However if sufficient planning is not performed up front, a DBA is going to have problems as their databases grows, issues come up and more users start accessing the system. A top 10 list of things to do before installing a MySQL database include:

1. Scalability: Understand the purpose and potential growth of your database.
2. Physical Design: Determine the appropriate physical layout of your database. The defaults are not always the best long term layout.
3. Storage Engines: Select the appropriate storage engines and how they impact performance and your backup/recovery strategies.
4. Backup and Recovery: Layout your backup and recovery strategy. Pick the appropriate backup/recovery tools for your environment. Then test your backup and recovery strategies sufficiently. Test recovery as well as backups. Understand your recovery times.
   
5. Database Environment: Understand the number of databases that will be created and the maintenance ramifications of the database layout.
6. Disaster Recovery: What are the business costs and ramifications of this environment (database server, host server, site, network, ...) being down? Perform appropriate risk analysis.
7. Security: Understand how security is being managed and maintained for your users and your databases. Make sure you secure your userids and passwords.
   
8. SQL_MODE and Isolation Level: Set the appropiate SQL_MODE and Isolation Level and make sure you understand the ramifications of the these selections. If running an OLTP environment I highly recommend setting the SQL_MODE to a minimum of traditional.
   
9. Storage: Understand your physical storage layout. Make sure you know the ramifications, limitations, scalability and performance of using separate disks, storage arrays, etc.
10. Best Practices: Define a set of best practices that determine your physical layouts, naming conventions, management tools, infrastructure management and document everything.

A few areas that did not make my top ten but I would give honorable mention.

• Make sure and secure the hardware platforms, networks and operating systems where your databases will be running. Along with that take out operating system features you should not be using on a database server. This includes features such as Telnet, web services, print services, etc. These unnecessary OS features have security issues to watch out for and add additional weight (impact performance) to the operating system.
• Read the README files and research the release you are moving to.
• Determine what software you are going to use to manage your MySQL database environment. If you have multiple MySQL databases you should consider the MySQL Network Monitoring and Advisory Service Advisors.
  

Dependent on your enterprise software licensing, the following advisers are available from MySQL.

• Administration Advisor
• Schema Advisor
• Performance Advisor
• Security Advisor
• Memory Usage Advisor
• Replication Advisor
• Custom Advisor
  

The five P's apply to database configurations. Proper Planning Prevents Pitiful Performance. I had a higher pain threshold when I was younger versus today. I have found that proper planning can help you avoid a lot of pain, anguish and late evenings as a DBA.

Optimal MySQL configurations

I read a book one time called the "Seven Habits of Highly Successful People". After I read it I thought what a waste of money I already knew everything that was in the book. There were no new ideas in it. I then realized a number of the seven habits I was not doing. And if I did them I would be more successful. I then realized maybe there is a reason the book stays in the New York Times best seller list year after year.

The following points are similar to the seven habits. They are a set of good practices and guidelines that if you follow, will make your database management a lot easier to support. If you follow them, you'll spend a lot less time working nights and weekends.

Any set of database management best practices need to include:

• A standard way of organizing software and data on disks.
  
• Separate database software from operating system and other third party software.
• Separate data from log files. The files that store your data and the files need to recover your data should never reside on the same disk drives and controllers.
• Data, index files and log files should be distributed to reduce I/O bottlenecks and to maximize recovery and reduce administration.
• Having proven and tested (on a regular basis) backups.
• Facilitate the management of installs, upgrades and backups. The routine tasks required of DBAs.
• Prepare for and manage the growth of data and databases.
• Minimize the fragmentation of tables and indexes.
• Have appropriate safe guards so disk or hardware failures will have a minimal impact. DBAs have to work with the people making the purchases so they have the right hardware and disk configurations. Distributing data across multiple disk drives reduces the impact of individual disk failures.
• There should be a consistent pattern for directory structures for software, backups, data files, log files, administration scripts, start up files and other other files associated with MySQL databases.
• The change in software directories, data directories, login directories should have minimal if no impact on database scripts and applications.
• Administrative information for each database such as logs, backup scripts, administration scripts should be separate for each database in a well organized directory structure pattern.
• Files of different types should be organized in separate directory structures.
• Separate file systems should exist for software, data, log files, backups, etc. as much as possible.
• Protecting the hardware platforms and operating systems that are running MySQL databases.
• Use a consistent naming pattern for all data files, index files, log files, scripts, database names and file systems supporting files related to MySQL databases.
• If your organization is using storage drives, make sure you understand are are working closely with the storage management teams to optimize any striping and mirroring of database storage.
• Software to manage and monitor a complex environment. The Enterprise Monitor tool from MySQL can be found at http://mysql.com/products/enterprise/benefits.html.

Database and software configurations need to address the constant tasks a DBA performs.

• Adding new users.
• Duplicating databases.
• Managing changes for development, test and production database environments.
• Upgrades of operating systems, databases, applications and hardware.
• Adding storage for data increases.
• Creating new databases.
• Adding new hardware, disk drives, etc.
• Constantly distributing I/O workloads.

I realize if you are a new DBA this can be a little overwhelming. Starting simple can be the best approach and as you get more experience following more of the above practices will become important. If you are an experienced DBA review the above guidelines and your best practices to make sure your management practices are honed to a fine edge.

Skillsets for the successful MySQL DBA

DBAs have spent years developing their expertise in database management best practices, backup/recovery, performance tuning and writing really cool scripts. Today's successful DBA needs skills beyond just the management of their databases. These skills include:

• Operating Systems: Having sufficient knowledge in the operating systems their databases run in.
• Best Practices: Maintaining best practices for database management.
• Automation: Automating your database management.
• Supporting Multi-tiered Infrastructures: Being able to manage databases in a multi-tiered environment.
• Supporting Development: Being able to support or a least understand the development environment for your databases.

Operating Systems

If your database is running on a Windows, Linux or Unix systems the more knowledgeable you are in the operating systems of your database platforms the better you are at troubleshooting and performance tuning your database environment. The successful DBA will
optimize the operating systems their MySQL databases are running on.

• Todays operating systems such as Windows, Linux and Unix are very robust operating systems. Most operating systems are installed with services and RPMs that are not needed for the database platform. For most production servers running MySQL, OS features such as FTP, Telnet, Print Servers, etc. should not be running on the server platform running MySQL. Having the operating systems loaded with unnecessary features creates security violations and has an overweight operating system taking unnecessary resources away from the databases. A server running databases should have the operating system optimized for running database servers.

Best Practices

Most DBAs are not supporting a single database. They are probably part of a team supporting multiple databases. DBAs need to have a consistent set of management guide lines (see Optimal Configuration for MySQL article) for managing their databases. Setting up an optimal configuration for MySQL database environments and following a set of consistent guide lines will minimize mistakes, reduce down time and ensure optimal configurations for performance.

DBAs also need to have a method for managing their infrastructure information. Errors that occur once are likely to occur again. Each DBA should be logging errors and everything they do to manage their database environments. DBAs need to periodically review how they are spending their time and the issues they address on a daily basis. This approach will often have a DBA recognize consistent patterns of their management that can help them identify areas that need to be worked on. Sometimes seeing the forest through the trees can be difficult. Periodically (weekly, monthly) reviewing daily tasks can help identify trouble areas that need to be worked on.

Automation

The traditional DBA has worked on writing scripts to manage enterprise environments. DBAs get excited when they write a really cool script for managing their databases. They show this script to other DBAs, and its like a caveman showing other cavemen fire for the first time. There are a lot of ohhhs and aughhhs. But writing scripts is no longer the best way to manage databases. The more scripts you write:

• The more you are building a high maintenance environment. More scripts will keep adding to the complexity and maintenance of their environments. I was at a client site that was running over a hundred databases. They were upgrading their operating system and databases to a higher release in a multi-phased approach. They wanted me to go out and update their backup scripts on 140 platforms. It would be better to develop a centralized method to running backups versus having different scripts that have to be constantly updated. Using a scheduler to facilitate tasks that need to be automated is much better than constantly updating cron scripts across different platforms.
• The more you are dependent on the skills of an individual person. Once a new DBA moves in they often replace the scripts written by a previous DBA. They don't want to bet their job on scripts written by someone else. This reoccurring pattern can become a significant waste for organizations over a period of time.
  

Software should be used for:

• Scheduling reoccurring tasks. Backups, batch jobs, maintenance routines, reports, etc. should all be coordinated through a central repository.
• Monitoring your databases as well as the operating systems, file systems, application servers, software services and all other components of an environment that impact the database.

I realize smaller systems may have not the budget or infrastructure for automation software. However there is a lot of free software out there that can help you with automation and monitoring.

Supporting Multi-tiered Infrastructures

Web applications are the standard way to develop applications today. Most new applications are web applications and not client/server applications. Web applications include application servers. Web applications are written in Java, PHP, Ruby on Rails, Groovy, etc. DBAs don't have to be an expert in Java and the other web development environments. However they should at least be conversational about the technologies surrounding their databases.

The application server is also becoming a central piece of the architecture. Internet applications are retrieving thousands or millions of records across the network and then processing them on the application server. Middleware developers are very knowledgeable on developing applications for the middle tier, however their priority is usually not how to optimize these applications for database performance. The issues between the database server and the application server are creating a lot of issues in organizations. The more a MySQL DBA can support Apache, JBoss or other application servers accessing MySQL the more valuable they are in the market place.

Stored procedures can be a great way to reduce network traffic. Processing and filtering the database in the database server before sending the data to the application server can reduce the network traffic between the database server and the application server. Also a database server is usually much more efficient processing and filtering data that Java, PHP or any other web development language.

Supporting Development

Java, PHP, Ruby on Rails, Groovy and other web development languages are becoming common place in MySQL development environments. A DBA that can support developers provide a lot of increased value to an organization.

Conclusion

The one constant for MySQL DBAs is that the following areas are going to continue to grow in their MySQL environments:

• The number of databases they have to support.
• The volume of data in databases is going continue to increase at ever increasing rates.
• Clients are going to request faster and faster response times.
• Requirements for less and less down time.

The world of database administration requires that MySQL DBAs constantly work smarter and just not harder. MySQL DBAs that are constantly working on improving their best practices, automation and skills in the environments their MySQL databases run in are going to increase in value to their organizations and in the market place.

The good thing is that MySQL databases are becoming more and more popular in today's competitive environments. Open source environments using Linux, Apache, JBoss, PHP and Java are becoming more and more attractive to corporate America as well as the small business and government areas. The skill sets identified in this article will definitely help separate you from other DBAs in the marketplace.

Key Features in MySQL 5.1

For those of you new to MySQL 5.1 , I am going to provide a list of key features of MySQL so you can get a feel for the functionality of MySQL.

• Partitioning - Partitioning provides scalability, performance and administration flexibility to working with large tables and indexes.
  
• Row level replication - Row level replication adds to replication functionality in MySQL.
• Database events - Allow DBAs to schedule batch events from within the database.
• Table Logs - Give DBAs the option of logging to database tables instead of operating system files.
  
• Platform Independence - MySQL can run on most versions of Unix, Linux, Windows and Mac OS.
• Scalability - MySQL has grown so it can support 100s of gigabyte and terabyte sized databases. MySQL is known for very fast access.
• Stored Procedures and Triggers - MySQL supports a database programming language almost exactly like Oracle’s PL/SQL language. It is a very easy transition for Oracle developers.
• Powerful Storage Options - MySQL supports a good variety of storage engines (at the table level) for leveraging transactional, read performance etc. to allow a DBA to maximize the the configuration of the database for the business. For example, InnoDB contains strong transactional features, while MyISAM provides for very fast access for web based applications.
• Views - MySQL supports traditional views (logical tables) as well as in-line views (in FROM clause).
• Large Table Support- MySQL supports a number of partitioning options to support very large tables.
• Traditional relational database functionality - MySQL supports traditional transaction features such as commits, rollbacks, row-level locking and foreign keys. Joins, sub-queries are also supported.
• High Availability Architecture - MySQL supports a replication architecture for availability and scalability. This allows multiple MySQL databases to be spread across multiple systems using high-speed interconnects.
• Full-text Searches - MySQL supports full text searches. Very impressed with how easily it was to implement this functionality. Initial tests are very fast.
• Globalization - Strong support for Unicode for multiple character sets.
• Large variety programming interfaces - An extremely large variety of APIs for PHP, Java, Ruby on Rails, Groovy, .NET, Python, etc. Supports ODBC and JDBC as well.
• GIS Functionality - MySQL supports two-dimensional data for accessing and processing.
• Clustering - Clustering spreads software components across multiple machines for high availability and fail-over. Clustering has techniques for keeping the different platforms synchronized.

MySQL Licensing

MySQL is available under GNU Public License (GPL), so someone can download the source or the binaries and use them for free. Commercial licenses and support are available for higher profile and production environments. MySQL Enterprise is available and offers support, consulting and online knowledge base. More details can be found at www.mysql.com/support/. Make sure you read the license agreements carefully.

MySQL Releases

• Alpha - New release requiring testing. Allows organizations to be on the bleeding edge.
• Beta - Major changes are not expected but testing is required and bugs are likely to be found.
• Gamma - Beta release has stabilized and searching for final bugs before going GA.
• GA - General Availability (GA) is a mature stable version for production environments.

Before Going to Any New Release

Before going to any new release make sure you have looked at a bug report of the new features. Most customers are still on 4.0. You always want to make sure your eyes are wide open and you are aware of potential issues with new features. As an early adopter you may need to weigh the benefits of new features versus potential issues with a new release.

Excellent links on MySQL 5.1 Release

Below are a couple of links on MySQL 5.1 that you should definitely look at.
http://dev.mysql.com/tech-resources/articles/mysql-5.1-recap.html
http://www.planetmysql.org/kaj/?p=131

Middle-tier Terminology and Concepts

If you are at a party, barbecue, tail-gate or other social event the topic of middleware or middle-tier may come up. This article will teach you enough to join in or maybe more importantly to walk away.

There are a lot of different languages used to build web applications. The key point to remember is that all of them end up generating HTML code that is returned to a browser. This article is going to focus on key areas of Java environments.

Key technology components developers may need to learn independent of vendors:

• Java 2 Enterprise Edition (J2EE)
• Web Services
• Object Relational Mappers (ORM)
• Service Oriented Architecture (SOA)
• eXtensible Markup Language (XML)
• Security Assertion Markup Language (SAML)
• Business Process Execution Language (BPEL)
• Java Frameworks and Design Patterns
• Lightweight Directory Access Protocol (LDAP)
• Hyper Text Markup Language (HTML)

This technical foundation is based on proven industry standards. Defining this technical foundation is helping organizations understand the skill sets their teams will need to support current and future web-based applications.

The ability to quickly build composite Internet applications is very important to a lot of organizations. This can significantly reduce costs and time to market. The ability to have a high degree of reuse is also an important key for this. The tools we’ve listed have become a popular way to achieve these results.

Terminology and Concepts

The following are high level definitions of key technical features, concepts and terminology:

• Eclipse, NetBeans and JDeveloper - These are Integrated Development Environments (IDEs) used by developers to build web-based applications. There are wizards and interfaces for developing with J2EE, SQL, HTML, Web Services, SOA, JSF, JSP, Servlets, EJBs, Struts, etc. This IDE should be looked at as a development environment for developing database and Internet applications.

• Java 2 Enterprise Edition (J2EE) - J2EE is an industry standard for developing enterprise multi-tiered applications. J2EE is an architecture and framework for enterprise wide applications. J2EE applications include JSPs, JSFs, Servlets, ADF Faces, EJBs, etc.
• Struts and Frameworks (ADF) - Struts is a J2EE development framework that is based upon best practice design patterns for building Web applications. Struts is based upon the Model-View-Controller that separates User Interface, Business and Data logic. J2EE frameworks can be complex and it can be difficult to determine how to organize different components. Struts helps organize J2EE components into a well organized Web application. Spring is a multi-layered Java/J2EE application framework.
• Hibernate and TopLink - Object Relational mappers that manages the communication between Java (object-oriented) applications and the database (relational).
• XML - XML has become the universal language for transmitting data structures independent of the environment. J2EE, Web Services, SOA and BPEL use XML.
• Web Services - Web (software) Services uses XML standards and transport communication protocols to exchange data between applications. Web services allow different types of applications to communicate. Web services are standards that define the semantics for how software communicates.
• Service Oriented Architecture (SOA) - SOA is an architecture that defines how loosely coupled software services communicate with each other. SOA increases reusability, that allows different software services to identify and communicate with each other.
• Business Process Execution Language (BPEL) - BPEL is a standard for organizing reusable web services into more than one type of process flow.
• Identity Management - Identity Management provides enterprise management of user identities across resources inside and outside the firewall.
• Single Sign-On (SSO) - SSO provides unified authentication allowing a user to logon once and SSO will manage single sign-on capability across applications.
• Clustering - Clustering spreads software components across multiple machines for high availability and failover. Clustering has techniques for keeping the different platforms synchronized.

Conclusion

Current tools hide a lot of the technical complexity behind middle-tier solutions. However, dependent upon the customizations made to applications, developers may need a strong understanding of the technical components of middle-ware technology.

There are three types of developers that need to learn the products in the middle-tier Technology Platform:

• Traditional Developers - Developers that have used Oracle Forms, Reports, Discoverer, PowerBuilder, Cobol, Visual Basic, C/C++, etc. are going to need to learn the technologies supported in Internet development environments. Most Java developers are going to have to be knowledgeable with some or all of these technologies.
• Traditional Applicaiton Developers - If you are a developer who has customized Oracle, Siebel, PeopleSoft, JD Edwards, SAP, Retek, Stellent and you are not likely to hit the lottery jackpot, then learning to develop web applications is in your future.
• Internet Developers - Developers building Internet applications are likely to work with some or all of the products we have discussed in this article. Internet developers may be working with Eclipse instead of JDeveloper, or Hybernate instead of TopLink or Apache or JBoss. However the principles of Frameworks, ORMs, Design Patterns, J2EE, Managing Web Services, XML and SOA are still going to be technical areas that need to be understood. Internet developers may also be using PHP, Python, Javascript, Ruby on Rails, Groovy or other web development solutions.

It is important to understand that components such as J2EE, Web Services, XML, SOA, etc are not just tied to any vendor. They are based on open standards. Skills in the technology discussed in this article are incredibly valuable in any Internet development environment. LAMP or some variation of LAMP can provide some very powerful yet very inexpensive solutions compared to large vendors such as IBM and Microsoft.

These open standard components are moving into your future like a freight train. They require skills that are going to be incredibly marketable and valuable in the future.

Looking at MySQL and Oracle

I am going to build a list of features, commands and concepts from MySQL and Oracle. This comparison will not go into the pros and cons or which database has more options or features. The goal is to give someone a feel for the differences. I am a big evangelist for Oracle and MySQL technologies. This is a list I will build over a period of time.

The following are websites for downloads, documentation, code, blogs, forums, whitepapers, newsletters, etc. Be careful about going to these sites you will get hooked and spend hours on these two great sites.

• dev.mysql.com (MySQL)
• otn.oracle.com (Oracle)

Feature/Functionality

The Oracle Database Server is the flagship of large enterprise databases. Oracle excels at the level of data warehouses or running large enterprise applications like the financials for a Fortune 100 company. At the same time MySQL is generating a lot of momentum because customers are seeing they can implement it at the fraction of the cost and complexity of Oracle. Each database has their place in the market in which they fit well.

MySQL does not have all the functionality of the Oracle database server, but that is also what makes it so attractive. Not everybody needs all the functionality that comes with the Oracle database server. Most companies I go to are paying for all of Oracle's functionality but only using a very small percentage of what it can offer. Also, as web applications become more popular more functionality is being built in the middle-tier versus using database functionality.

With MySQL it is either free or its commercial licenses are a fraction of Oracle's cost. MySQL environments usually require less hardware, resources and money to get them up and running. The cost comparison makes it a great option for Small and Medium Businesses (SMB), government and state organizations and for projects that do not need the enterprise flagship functionality of Oracle. As more and more web applications are being built, J2EE and Internet developers are building more functionality in the middle-tier, negating a lot of the functionality that comes with an enterprise database server.

I've been very impressed with how fast MySQL can process different types of SQL statements and how good of a job the optimizer does in the MySQL environment. The code is lighter and more efficient because it does not have as many options to wade through. Sometimes being light and nimble can offer tremendous advantages for application developers that understand how to leverage the functionality of MySQL.

Products

Below is a list of popular products used with MySQL and Oracle:

MySQL is an open source solution. Products like Eclipse, Apache and JBoss are used to offer a multi-tiered web solution using MySQL. MySQL usually uses other open source products to provide a full multi-tiered offering. LAMP or a derivative of LAMP is seen in MySQL multi-tiered environments.

Oracle offers a complete line of products with JDeveloper, Oracle Application Server, SQL Developer, etc. Third party products are available as well (i.e. Toad, SQL Navigator, UniCenter, ...). Open source products such as JBoss and Eclipse can also be used in Oracle environments. The big difference is that Oracle offers a large line of vendor commercial products. The table below only shows Oracle specific products under the Oracle section.


This table will provide an example of what a MySQL and Oracle environment may look like.

MySQL	Oracle
Eclipse	Oracle JDeveloper
Struts, Spring	Application Development Framework
J2EE, PHP, Ruby on Rails, Groovy, .NET	J2EE
Hibernate	TopLink
Apache, JBoss	Oracle Application Server
TCP/IP, Unix Sockets, Shared Memory, NT Pipes	Oracle Net Services
Mysqladmin, MySQL Administrator, Phymyadmin Enterprise Monitor	OEM Database Control
MySQL Query Browser, Toad, Navicat	Oracle SQL Developer
mysql client tool mysqlimport mysqlcheck mysqldump myisamchk myisampack	SQL*Plus client tool SQL*Loader dbverify Data Pump, RMAN dbms_repair package
MySQL (GPL) MySQL Enterprise	Oracle Express Oracle Standard Oracle Enterprise
OpenLDAP, Fedora Directory Server	Oracle Internet Directory

Note: The reader needs to understand the products are usually not always equivalent. Some examples include:

• Oracle environments also support Ruby on Rails, .NET, Groovy, .NET, etc. However, the big push by Oracle is to use J2EE since Oracle Fusion applications run using J2EE. Also, organizations usually do not purchase a 100K or 1M Oracle license to run Ruby on Rails for their enterprise. Yet, Oracle does offer Oracle Express, a free database up to 4GB of storage where PHP, Ruby on Rails become a better option.
• Mysqldump is a very simple tool for creating a backup. Oracle Data Pump and Recovery Manager (RMAN) are tools that are more enterprise level tools.
• MySQL does not need additional networking software to work. It uses built-in networking protocols like TCP/IP. Oracle Net Services runs on top of TCP/IP. Oracle Net Services may or may not be free depending on what you are using on the client side. I realize Oracle has EZ Connect but it is not a replacement for Oracle Net Services.
  

Databases

One of the differences between MySQL and Oracle is how they treat the term database. A MySQL database is closer to an Oracle schema and all the storage for that schema. I’ll add an architectural picture of MySQL and Oracle that will help with this.

MySQL

1. The MySQL physical server environment is made up of all the individual databases, binary log, and additional files and logs.
   
2. A MySQL database is how to organize a group of database objects such as tables, views, triggers, etc.
3. There would be a separate MySQL database for a human resource and a sales system to organize each set of objects separately.
   

Oracle:

1. In Oracle the physical server environment is a database that encompasses all the physical files (storage) for a specific Oracle server (instance). This includes all files for data files, index files, redo log files, archive log files, etc.
2. Oracle organizes database objects in a schema. A schema is an Oracle user and all tables, views, sequences, synonyms, stored procedures, etc. the objects and resources owned by the user.
3. Oracle would use a separate “schema” for storing information for a human resource and a sales system if they were in the same database.
   

Summary:
A MySQL server environment is made up of server memory, background processes associated with multiple databases.

An Oracle server environment is made up of server memory and background processes (instance) associated with a single database organized with multiple schemas.

Startup Files
MySQL - The my.cfg or my.ini is the startup file for a MySQL server environment.

Oracle - An init.ora or spfile.ora file is used to start an Oracle server instance.

Metadata
The way MySQL and Oracle DBAs access metadata is similar but totally different. :)

With MySQL there are a large number of SHOW commands that will display detailed information on MySQL databases (data schemas), table structures etc. With Oracle the data dictionary views of USER_%, ALL_%, DBA_%, V$, etc. are used. The SHOW command in Oracle is only or the SQL*Plus client interface that will display local SQL*Plus variables in terms of how the SQL*Plus client is configured.

Both environments allow GLOBAL (instance) and LOCAL (session) variables to be defined. Below MySQL commands (mysql>) and Oracle commands (SQL) for accessing metadata will be compared.

-- Display server parameters.
mysql> SHOW VARIABLES;
SQL> SELECT name, value FROM V$parameter;

-- Set the sort buffer size.
mysql> SET GLOBAL sort_buffer_size=20 * 1024 * 1024;
SQL> ALTER SYSTEM SET sort_area_size=20971520;

-- Display information on triggers.
mysql> SHOW TRIGGERS LIKE 'fin%'\G
SQL> SELECT trigger_name, trigger_body FROM dba_triggers
WHERE trigger_name LIKE 'FIN%';

Storage Options

MySQL uses storage engines. Each of these storage engines deals with transactions, memory allocations, etc. differently. Very important a DBA understand the ramifications between the different types of storage. Some of the more popular storage engines include

• MyISAM
• InnoDB
• Memory
• Federated
• Archive
• Blackhole
• Falcon - New transactional storage available in MySQL 6.0.

MySQL stores data for tables in flat files that reside on the disk. RAW is available but not used much any more.
Oracle does not have the equvalent of storage engines. Oracle supports some of the following types of table storage:

• HEAP - regular table (default). Supports ACID transactions as well as being used able to be used for data warehouses.
  
• IOT - Index organized table. Storage is in a b-tree structure.
• External - Table definition that is mapped to an external file.

Oracle storage is managed in tablespaces. Tablespaces can store multiple data segments (tables) and index segments (indexes). Tablespaces organize Oracle storage structures. Tablespaces can store their data using RAW devices, operating system files and Automatic Storage Management (ASM). ASM uses Oracle internal storage mechanisms to write directly to physical devices without a file system. ASM can perform striping and mirroring using an Oracle Logical Volume Manager (ASM instance).

Creating Tables
Both MySQL and Oracle both support:
Regular tables
Indexes
Temporary tables
Partitioning

The big difference is that MySQL storage engines determine the functionality of MySQL tables. Another big difference is that MySQL uses AUTO_INCREMENT functionality to increment primary keys and unique identifiers. Oracle uses sequences to increment unique identifiers.

Stored procedures and triggers
MySQL and Oracle both support stored procedures and triggers. The MySQL stored procedure functionality is fairly new. However, the core of the language for writing stored procedures is available in MySQL. Oracle's procedural language has been around since 1991, so it obviously is more mature.

What's nice is that both of them use an ADA like syntax, so the core syntax of both languages looks almost exactly alike. Going from Oracle PL/SQL to MySQL's stored procedures would be a very easy transition.