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.