Saturday, October 24, 2009

Accessing Metadata through Stored Routines

Accessing metadata can be optimized by using stored routines.  Stored routines provide the ability to filter the data in a more useful way.  For example, when I'm looking at table data I usually want to look at the index information also.  So I use a stored routine called tabinfo that gives me key information I need for tables and indexes.

-- Create the tabinfo stored procedure.
DROP PROCEDURE IF EXISTS  tabinfo;

CREATE PROCEDURE tabinfo(ptableschema  VARCHAR(30))
SELECT  t.table_name, engine, table_rows,
                i.column_name, i.index_name, i.cardinality
FROM     information_schema.tables t, information_schema.statistics i
WHERE  t.table_name = i.table_name
      AND  t.table_schema = i.table_schema
      AND  t.table_schema = ptableschema
ORDER BY t.table_rows DESC;


-- Execute the tabinfo stored routine using the CALL command.
mysql> CALL tabinfo('world');

Monday, September 28, 2009

Oracle Versus MySQL Classes

I'm always being asked what is the difference between Oracle classes and MySQL classes. What are the similarities and what are some of the differences? So I thought I'd jot down a few notes.

Course similarities:
Oracle Classes
MySQL Classes
Oracle Database 11g: Introduction to SQL
MySQL for Beginners
Oracle Database 11g: Introduction for Exp. SQL Users
MySQL for Developers
Oracle Database 11g: SQL Fundamentals II
MySQL Developer Techniques
Oracle Database 11g: PL/SQL Developer
MySQL Stored Procedures Techniques
Oracle Database 11g: Administrator Workshop I
MySQL for DBAs
Oracle Database 11g: Performance Tuning
MySQL Performance Tuning
Oracle Database 11g: RAC Administration
MySQL Cluster

Notes on some differences:
Oracle RAC (MySQL Cluster), PL/SQL (MySQL Stored Procedures), Streams (MySQL HA) have a lot more depth and complexity than MySQL equivalents.
MySQL does not have a class for MySQL Enterprise Monitor (Oracle Grid).
MySQL has PHP class, while Oracle has Java, Fusion Middleware, APEX and Forms developer classes

Tuesday, September 8, 2009

MySQL Continues to be a "Tipping Point" in the IT Industry




















MySQL continues to be a tipping point for the IT industry.  In March of 2008 and January 2009 I talked about MySQL being a very important factor in the IT industry.  Today, MySQL  continues to play an important role in the direction of the IT industry.  Some facts about MySQL in a down economy:
  • While technical conferences are being canceled or running at 50% attendance of previous years, the MySQL conference ran at 100% attendance.
  • MySQL continues to enjoy significant revenue growth.
  • The number of MySQL database servers continue to increase at a constant pace.
  • The passion of Dolphins remains very high.
So it will be interesting to watch what happens to MySQL in the next year.  Who knows there may be a future appendix to "The MySQL Story".    "Everybody, please fasten your seat belts."

Friday, September 4, 2009

Configuring INFORMATION_SCHEMA Plugin Tables for InnoDB Plugin

There are seven new INFORMATION_SCHEMA tables (plugins) available with the InnoDB plugin.  They need to be loaded to be used.

New InnoDB Plugins contain information on:
  • Compressed InnoDB tables
  • Compressed InnoDB buffer pool
  • Current InnoDB transactions
  • Transaction Locks
  • Blocking transactions
These INFORMATION_SCHEMA plugins are available by setting the plugin-load parmeter:

plugin-load=innodb=ha_innodb_plugin.dll;innodb_trx=ha_innodb_plugin.dll;innodb_locks=ha_innodb_plugin.dll;innodb_cmp=ha_innodb_plugin.dll;innodb_cmp_reset=ha_innodb_plugin.dll;innodb_cmpmem=ha_innodb_plugin.dll;innodb_cmpmem_reset=ha_innodb_plugin.dll;

mysql> SELECT table_name, engine, table_type FROM information_schema.tables
             WHERE table_name LIKE 'innodb%'
 
table_nameenginetable_type
INNODB_CMP_RESETMEMORYSYSTEM VIEW
INNODB_TRXMEMORYSYSTEM VIEW
INNODB_CMPMEM_RESETMEMORYSYSTEM VIEW
INNODB_CMPMEMORYSYSTEM VIEW
INNODB_CMPMEMMEMORYSYSTEM VIEW
INNODB_LOCKSMEMORYSYSTEM VIEW


Before loading plugin tables: 

mysql>  SHOW PLUGINS;
NameStatusTypeLibraryLicense
binlogACTIVESTORAGE ENGINEGPL
ARCHIVEACTIVESTORAGE ENGINEGPL
BLACKHOLEACTIVESTORAGE ENGINEGPL
CSVACTIVESTORAGE ENGINEGPL
FEDERATEDDISABLEDSTORAGE ENGINEGPL
MEMORYACTIVESTORAGE ENGINEGPL
MyISAMACTIVESTORAGE ENGINEGPL
MRG_MYISAMACTIVESTORAGE ENGINEGPL
partitionACTIVESTORAGE ENGINEGPL
InnoDBACTIVESTORAGE ENGINEha_innodb_plugin.dllGPL

After loading plugin tables: 

mysql>  SHOW PLUGINS;

NameStatusTypeLibraryLicense
binlogACTIVESTORAGE ENGINEGPL
ARCHIVEACTIVESTORAGE ENGINEGPL
BLACKHOLEACTIVESTORAGE ENGINEGPL
CSVACTIVESTORAGE ENGINEGPL
FEDERATEDDISABLEDSTORAGE ENGINEGPL
MEMORYACTIVESTORAGE ENGINEGPL
MyISAMACTIVESTORAGE ENGINEGPL
MRG_MYISAMACTIVESTORAGE ENGINEGPL
partitionACTIVESTORAGE ENGINEGPL
InnoDBACTIVESTORAGE ENGINEha_innodb_plugin.dllGPL
INNODB_TRXACTIVEINFORMATION SCHEMAha_innodb_plugin.dllGPL
INNODB_LOCKSACTIVEINFORMATION SCHEMAha_innodb_plugin.dllGPL
INNODB_CMPACTIVEINFORMATION SCHEMAha_innodb_plugin.dllGPL
INNODB_CMP_RESETACTIVEINFORMATION SCHEMAha_innodb_plugin.dllGPL
INNODB_CMPMEMACTIVEINFORMATION SCHEMAha_innodb_plugin.dllGPL
INNODB_CMPMEM_RESETACTIVEINFORMATION SCHEMAha_innodb_plugin.dllGPL

InnoDB parameters example:
[mysqld]
basedir="C:/mysql/5.1.38/"
datadir="C:/mysql/Data/"

#*** INNODB Specific options ***
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.dll;innodb_trx=ha_innodb_plugin.dll;innodb_locks=ha_innodb_plugin.dll;innodb_cmp=ha_innodb_plugin.dll;innodb_cmp_reset=ha_innodb_plugin.dll;innodb_cmpmem=ha_innodb_plugin.dll;innodb_cmpmem_reset=ha_innodb_plugin.dll

plugin_dir="C:/mysql/5.1.38/lib/plugin"

default-storage-engine=INNODB
innodb_data_home_dir="C:/mysql/innodb"

innodb_data_file_path=ibdata1:1G;ibdata2:1G:autoextend
innodb_autoextend_increment=100
innodb_log_group_home_dir="C:/mysql/innologs"

innodb_buffer_pool_size=120M
innodb_log_file_size=100M

InnoDB Plugin Parameters

 Here is the set of new parameters available with the InnoDB Plugin 1.0.4


NameScopeDynamicDefault
innodb_adaptive_flushingGLOBALYESTRUE
innodb_change_bufferingGLOBALYESinserts
innodb_file_formatGLOBALYESAntelope
innodb_file_format_checkGLOBALYESON
innodb_io_capacityGLOBALYES200
innodb_read_ahead_thresholdGLOBALYES56
innodb_read_io_threadsGLOBALNO4
innodb_spin_wait_delayGLOBALYES6
innodb_stats_sample_pagesGLOBALYES8
innodb_strict_modeGLOBAL|SESSIONYESFALSE
innodb_use_sys_mallocGLOBALNOTRUE
innodb_write_io_threadsGLOBALNO4

Parameters with New Defaults for InnoDB Plugin 1.0.4
NameOld DefaultNew Default
innodb_additional_mem_pool_size1MB8MB
innodb_buffer_pool_size8MB128MB
innodb_log_buffer_size1MB8MB
innodb_max_dirty_pages_pct9075
innodb_sync_spin_loops2030
innodb_thread_concurrency8

mysql> SELECT version();
+------------------+
| version()        |
+------------------+
| 5.1.38-community |
+------------------+

mysql> SHOW VARIABLES LIKE 'innodb_version';
+----------------+-------+
| Variable_name  | Value  |
+----------------+-------+
| innodb_version  | 1.0.4   |
+----------------+-------+


InnoDB Parameters After Setting Plugin

Query show variables like '%innodb%';
Output From Command:show variables like '%innodb%';
Variable_nameValue
have_innodbYES
ignore_builtin_innodbON
innodb_adaptive_flushingON
innodb_adaptive_hash_indexON
innodb_additional_mem_pool_size8388608
innodb_autoextend_increment8
innodb_autoinc_lock_mode1
innodb_buffer_pool_size134217728
innodb_change_bufferinginserts
innodb_checksumsON
innodb_commit_concurrency0
innodb_concurrency_tickets500
innodb_data_file_pathibdata1:10M:autoextend
innodb_data_home_dir(null)
innodb_doublewriteON
innodb_fast_shutdown1
innodb_file_formatAntelope
innodb_file_format_checkAntelope
innodb_file_io_threads4
innodb_file_per_tableOFF
innodb_flush_log_at_trx_commit1
innodb_flush_method(null)
innodb_force_recovery0
innodb_io_capacity200
innodb_lock_wait_timeout50
innodb_locks_unsafe_for_binlogOFF
innodb_log_buffer_size8388608
innodb_log_file_size5242880
innodb_log_files_in_group2
innodb_log_group_home_dir.\
innodb_max_dirty_pages_pct75
innodb_max_purge_lag0
innodb_mirrored_log_groups1
innodb_open_files300
innodb_read_ahead_threshold56
innodb_read_io_threads4
innodb_replication_delay0
innodb_rollback_on_timeoutOFF
innodb_spin_wait_delay6
innodb_stats_on_metadataON
innodb_stats_sample_pages8
innodb_strict_modeOFF
innodb_support_xaON
innodb_sync_spin_loops30
innodb_table_locksON
innodb_thread_concurrency0
innodb_thread_sleep_delay10000
innodb_use_sys_mallocON
innodb_version1.0.4
innodb_write_io_threads4
50 rows in set (0.00 sec)


InnoDB Parameters Before Setting Plugin

 mysql> SHOW VARIABLES LIKE 'ignore_builtin_innodb';
+-----------------------+-------+
| Variable_name             | Value |
+-----------------------+-------+
| ignore_builtin_innodb    | OFF    |
+-----------------------+-------+

Output From Command:  show variables like '%innodb%';
Variable_nameValue
have_innodbYES
ignore_builtin_innodbOFF
innodb_adaptive_hash_indexON
innodb_additional_mem_pool_size1048576
innodb_autoextend_increment8
innodb_autoinc_lock_mode1
innodb_buffer_pool_size8388608
innodb_checksumsON
innodb_commit_concurrency0
innodb_concurrency_tickets500
innodb_data_file_pathibdata1:10M:autoextend
innodb_data_home_dir(null)
innodb_doublewriteON
innodb_fast_shutdown1
innodb_file_io_threads4
innodb_file_per_tableOFF
innodb_flush_log_at_trx_commit1
innodb_flush_method(null)
innodb_force_recovery0
innodb_lock_wait_timeout50
innodb_locks_unsafe_for_binlogOFF
innodb_log_buffer_size1048576
innodb_log_file_size5242880
innodb_log_files_in_group2
innodb_log_group_home_dir.\
innodb_max_dirty_pages_pct90
innodb_max_purge_lag0
innodb_mirrored_log_groups1
innodb_open_files300
innodb_rollback_on_timeoutOFF
innodb_stats_on_metadataON
innodb_support_xaON
innodb_sync_spin_loops20
innodb_table_locksON
innodb_thread_concurrency8
innodb_thread_sleep_delay10000
innodb_use_legacy_cardinality_algorithmON
37 rows in set (0.00 sec)

Configuring the InnoDB Plugin (1.0.4) in MySQL 5.1.38

The InnoDB Plugin (1.0.4) is  pretty straight forward configuration. These instructions are for MySQL 5.1.38.  There is a new release of InnoDB Plugin with the 5.1.41 release. With MySQL you can use the default version of InnoDB or the new Plugin but not both.  If using a non-default configuration make sure and set the plugin_dir variable to point to the ha_innodb_plugin library for your system. 

To use the new plugin set the following parameters in your my.ini or my.cnf file.

Make sure you set the paths right for your setup and the right MySQL release like 5.1.40, etc.  

Setting the InnoDB Plugin on Unix/Linux:

[mysqld]
ignore-builtin-innodb
plugin_dir=/opt/mysql/5.1.38/lib/mysql/plugin
plugin-load=ha_innodb_plugin.so


Setting the InnoDB Plugin on Windows:
[mysqld]
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.dll
plugin_dir="C:/mysql/5.1.38/lib/plugin"

mysql> SHOW VARIABLES WHERE variable_name like '%plugin%' OR  variable_name like '%builtin%';
Variable_name
Value
ignore_builtin_innodb
ON
plugin_dir
C:/mysql/5.1.38/lib/plugin

My initial settings using the InnoDB Plugin on Windows
[mysqld]
basedir="C:/mysql/5.1.38/"
datadir="C:/mysql/Data/"

#*** INNODB Specific options ***
ignore-builtin-innodb
plugin_dir="C:/opt/mysql/5.1.38/lib/plugin"
plugin-load=innodb=ha_innodb_plugin.dll


default-storage-engine=INNODB
innodb_data_home_dir="C:/mysql/innodb"
innodb_data_file_path=ibdata1:1G;ibdata2:1G:autoextend
innodb_autoextend_increment=100
innodb_log_group_home_dir="C:/mysql/innologs"

innodb_buffer_pool_size=120M
innodb_log_file_size=100M

Thursday, September 3, 2009

MySQL 5.1.38 Releases with InnoDB Plugin

The MySQL 5.1.38 release is available today with the InnoDB Plugin (1.0.4) included in the software distribution.  The InnoDB Plugin offers some key features:
  • Improved performance and scalability.
  • Important management features that will be very helpful to MySQL DBAs.    
I recommend you look into the new features available with the InnoDB Plugin. The InnoDB Plugin (1.0.4) is at end of beta cycle. This storage engine can replace the default InnoDB storage engine by making a few configuration changes.  Blog on Thursday, April 9, 2009 InnoDB Plugin Has Some Very Cool Features highlights the Oracle InnoDB Plugin.

For documentation on installing MySQL 5.1.38 or upgrading from previous MySQL releases, look here:
To look at the changes in the 5.1.38 release compared to the previous release look here.  

Wednesday, August 26, 2009

Oracle Fusion Middleware 11g Launch - Denver

This was accidently posted to the MySQL blog site.  The full article on Oracle Fusion middleware can be found at http://fusioninsidersedition.blogspot.com.

Monday, July 13, 2009

Moving from MySQL to an Oracle Database Server

I was asked recently in one of my blogs what are some of the pure technical advantages of moving from MySQL to Oracle. I kind of laughed when I read the question because nothing is likely to stir up more perspectives and emotions than asking about moving from one database server vendor to another. So here goes ten technical considerations of moving to an Oracle database server from a MySQL server. I'm keeping the list to pure database server technology. There are also a lot of reasons to move to MySQL as well from other database server vendors (my whole blog is on this).

Technical Advantages moving from a MySQL Server to an Oracle database server
  1. Scalability: Much higher vertical scalability and higher OLTP transaction capability. Ability to set up shared server architecture for more user connection scalability.
  2. Metadata: Lots of very detailed metadata information as well as wait events for tuning.
  3. Stored Routines: Compiled stored procedures with much higher scalability. Java and .NT stored procedure capability.
  4. SQL functionality: Lots of SQL functions and analytical SQL functions. Lots of XML functionality built into database server.
  5. Backups: Hot online backups with Oracle's Recovery Manager
  6. Automatic Management: Lots of automatic management with Automatic Database Diagnostic Management (ADDM), Advisors, Automatic Workload Repository (AWR) , Automatic Segment Management, Shared Memory Management, etc.
  7. Data Warehousing: Bitmap indexes, materialized views, analytical functions, star joins, sort-merge joins, hash joins, etc.
  8. Grid Control: Oracle Enterprise Manager is a very robust enterprise tool for managing and monitoring multiple tiers of an Oracle infrastructure.
  9. Inexpensive disk strategy: Striping and mirroring capability with Oracle's Automatic Storage Management (ASM).
  10. Flashback technology: Lots of recovery capability with Flashback technology such as Flashback Database, Flashback Table, Flashback Versions Query, etc.

Anyway, a few things to talk about over coffee and cookies. :)

Wednesday, July 1, 2009

Differences between Oracle and MySQL

Some key differences for DBAs between Oracle and MySQL database servers include:
  • Different tools used to manage and monitor database servers.
  • Oracle architecture is process based, MySQL architecture is thread based.
  • Different tools used for backup and recovery.
  • Database specific SQL syntax.
  • Database specific SQL functions.
  • Different syntax for stored routines. MySQL has no packages.
  • MySQL routines are not compiled and run in each session thread and not in global memory.
  • MySQL only supports row-level triggers.
  • Different startup and shutdown processes.
  • Oracle RAC is a shared disk solution while MySQL Cluster is a shared nothing solution.
  • The default configuration for MySQL is very lenient in terms of data integrity. A MySQL DBA must tighten down data integrity for it to work like traditional databases.
  • With Oracle, the CREATE DATABASE command is used to create the physical storage for the database server.
  • With MySQL, the mysql_install_db script is used to create the physical storage for the database server.
  • The term "database" in Oracle means all the physical files associated with an instance.
  • The term "database" in MySQL means a schema. The term database and schema can be used interchangeably in MySQL.
  • In Oracle a database user owns all the schema objects.
  • In MySQL the database schema owns all the schema objects.
  • Oracle supports role based security. With MySQL, scripts and stored routines are used to programatically organize security permissions.
  • Oracle has a lot more feature functionality that makes it very popular.
  • MySQL has a lot less functionality than Oracle that makes it very popular.
  • Oracle has tons of options for creating a table. Different characteristics and behavior are defined with these options.
  • The key with MySQL tables are defining a storage engine with a table. The storage engine defines characteristics such as row level locking versus table level locking, referential integrity, support for different types of indexes and features. Different optimizations, storage, tuning and backup and recovery are required for each storage engine. Storage engines are a KEY feature in MySQL.
  • MySQL supports different ISOLATION levels of READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ AND SERIALIZABLE.
Oracle DBAs are not just managing Oracle databases any longer. They are supporting, SQL Server, MySQL, Business applications, application servers in a lot of Oracle environments. The more software you can support in an organization the more marketable you are. Its important that Oracle DBAs be able to support the corporate environments that are running database software from multiple vendors and the software surrounding databases.

There are reasons that MySQL customers like the light weight, easy to use MySQL database server. If an Oracle DBA compares MySQL to Oracle feature by feature they are going to be very disappointed. An Oracle DBA needs to look at the feature/functionality that makes MySQL so popular. The feature/functionality is not tons of features, its that it is light weight, fast and easy to manage.

The closest analogy I can make to an experienced Oracle DBAs, is that a MySQL database server is similar in management to what an Oracle version 7 database was like. MySQL uses cache hit ratios, has minimal wait events and DBAs still need to write scripts to surround the MySQL database.

Starting up and Shutting Down a MySQL Server

There are different ways for starting up and shutting down a MySQL server. A MySQL server when it starts up it starts up all the way. When a MySQL server shuts down it shuts down all the way. MySQL cannot startup and and shutdown at different levels the way an Oracle database server can.

MySQL Optimal Configuraton Architecture

Oracle DBAs understand the importance of defining guidelines and standards and using them in the configuration and management of database servers. Years ago the Optimal Configuration Architecture (OFA) developed a base set of guidelines and naming conventions for Oracle DBAs.

I developed the MySQL Optimal Configuration Architecture (MOCA) modeled after OFA to give new MySQL DBAs a set of guidelines and standards to consider when configurating MySQL database servers.

Managing MySQL Storage

Storage needs to be managed for any MySQL instance. A MySQL database server does not have the same flexibility as an Oracle database server in terms of how to lay out the data storage. It is important to learn the data storage defaults and options.


Tuesday, June 30, 2009

Managing the MySQL Instance

An Oracle DBA needs to learn how to manage a MySQL Instance. The MySQL instance has different memory buffers and caches like any database server. Part of MySQL's uniqueness is that it has memory areas to be configured that are associated with different types of storage engines.

Learning the MySQL Architecture

MySQL is an database server and anyone managing the MySQL database server has to learn the architecture, and feature functionality like any other database server. The biggest mistake to make with a new MySQL environment is thinking it is a toy and trivial to work with. A MySQL server needs to be properly configured, tuned and managed like any other database server.

The key to learning any database server is to learn its feature/functionality and the architecture.

Oracle vs. MySQL

As MySQL continues to explode in the marketplace, there are more Oracle and SQL Server DBAs learning MySQL. For Oracle DBAs, its important to understand that MySQL is a different type of database server.
  • Oracle is the aircraft carrier of database servers. Oracle has by far more features than any other database server. Oracle supports a number of database server solutions such as Real Application Clusters (RAC), Data Guard (standby database) and Oracle Streams (replication).
  • MySQL is the speed boat of database servers. MySQL is a very light weight database server that excels in web applications. MySQL is designed to be fast, easy to use and simple to install.
I'm always asked about porting Oracle applications to MySQL or MySQL applications to Oracle. Well if someone buys an aircraft carrier they probably bought it for specific reasons and the same for a speed boat. Its very unlikely that someone is going to switch from one to the other and be happy. However, each database server has areas where it excels.

MySQL Workbench 5.1.16 is GA!

MySQL Workbench Version 5.1.16 the General Availability build can be downloaded at http://dev.mysql.com/downloads/workbench/5.1.html.


Congratulations to the Workbench team for their great effort in this release.

Thursday, June 11, 2009

Converting an Oracle Schema to MySQL

Both Oracle and MySQL provide a number of sample schemas so users can build different schema environments and load them with data. So I thought I'd get one of the Oracle sample schemas and go through the exercise of converting it to a MySQL schema. I could have gone in either direction, since this is more of a MySQL blog I made MySQL the destination. Before I start let me introduce some of the Oracle and MySQL sample schemas.

Oracle Sample Schemas:
  • Human Resources: (HR) is a simple schema containing tables with basic primary and foreign key relationships such as: employees, departments, jobs, locations, regions, countrys, jobs, etc. This can be extended to support Oracle Internet Directory demos.
  • Order Entry (OE) is a more complex set of tables demonstrating the different datatypes supported by Oracle. Tables dealing with products, sales, customers, etc. are used in this schema.
  • Online Catalog (OC) is a supporting schema that demonstrates object-relational database objects built inside the OE schema.
  • Product Media (PM) is a schema that supports multimedia datatypes. Audio, video, images, large text data are all used in this schema.
  • Queued Shipping (QS) to demonstrate Oracle Advanced Queuing capabilities. Advanced Queuing and XML are used in this schema.
  • Sales History (SH) is a schema supporting a large number of tables. Features like partitioning and Oracle analytics can be used with this schema.

MySQL Sample Schemas:
  • World is a basic schema using three tables City, Country and CountryLanguages that use MyISAM tables.
  • Sakila is a schema that adds additional complexity using an online DVD store to demonstrate more of the MySQL 5.1 features.
  • Employees is a schema with a much larger data set containing employees, deparments, salaries, titles, etc that are very intuitive to work with. There are approximately 4 million records to work with. Different storage engines and partitioning are often benchmarked using this schema.
Summary of changes made to convert Oracle Schema to MySQL Schema

Here is a summary of changes made to convert the Oracle schema script to a MySQL schema script. The goal here is to provide a level of understanding of converting a schema.
  • Comment out SQL*Plus specific commands.
  • Change REM to --
  • Change VARCHAR2 to VARCHAR
  • Change NUMBER to INT
  • Change NUMBER(x,x) to DECIMAL(x,x)
  • Change CONSTRAINT keyword Syntax
  • For InnoDB tables, define PRIMARY KEY as part of the CREATE TABLE command and not a separate CREATE INDEX command.
  • Make sure tables are created as InnoDB. set storage_engine=innodb;
  • Modify constraint syntax for FOREIGN KEY definitions.
  • Fully quality PRIMARY KEY column in FOREIGN KEY constraint.
  • Remove READ ONLY clause with CREATE VIEW.

Converting the Oracle HR sample schema to MySQL

I decided the Oracle HR schema would be a good sample schema to convert to a MySQL schema to demonstrate things to consider in creating schema scripts. I started with the cre_hr.sql script and converted it to a cre_hr_mysql.sql script. The Oracle cre_hr.sql script I started with can be found at:
  • http://www.oracle.com/technology/obe/sqldev_obe/osdm/files/hr_cre.sql

MySQL equivalent of the Oracle HR schema script

Here is the corresponding MySQL create script. The only thing I did not address were the sequential incremental values used with Oracle sequences. I included the Oracle CREATE SEQUENCE commands in so it would show the adjustments I made to use autoincrement but also show that the incremental values would need to be addressed. A little more work needs to be done here to verify the data loads would create the same values in an Oracle environment. As I mentioned my main goal was to give someone a feel for the code differences in creating a schema between Oracle and MySQL.

-- hr_cre_mysql.sql


/*
Notes of changes:
Comment out SQL*Plus specific commands.
Change REM to --
Change VARCHAR to VARCHAR
Change INT to INT
Change INT(x,x) to DECIMAL(x,x)
Change CONSTRAINT keyword Syntax
For InnoDB tables, define PRIMARY KEY as part of the CREATE TABLE command and not a separate CREATE INDEX command.
Make sure tables are created as InnoDB.
set storage_engine=innodb;
Modify constraint syntax for FOREIGN KEY definitions.
Fully quality PRIMARY KEY column in FOREIGN KEY constraint.
Remove READ ONLY clause with CREATE VIEW.
*/

DROP DATABASE IF EXISTS hr;
CREATE DATABASE hr;
USE hr;

CREATE TABLE regions
( region_id INT NOT NULL
, region_name varchar(25),
primary key (region_id)
);


CREATE TABLE countries
( country_id CHAR(2) NOT NULL
, country_name varchar(40)
, region_id INT
, PRIMARY KEY (country_id)
) ;


ALTER TABLE countries
ADD (FOREIGN KEY (region_id)
REFERENCES regions(region_id)
) ;

CREATE TABLE locations
( location_id INT(4)
, street_address varchar(40)
, postal_code varchar(12)
, city varchar(30) NOT NULL
, state_province varchar(25)
, country_id CHAR(2)
, PRIMARY KEY (location_id)
) auto_increment=3300;

/*
CREATE SEQUENCE locations_seq\
START WITH 3300\
INCREMENT BY 100\
MAXVALUE 9900\
NOCACHE\
NOCYCLE;\
*/

ALTER TABLE locations
ADD ( FOREIGN KEY (country_id)
REFERENCES countries(country_id)
) ;

CREATE TABLE departments
( department_id INT(4)
, department_name varchar(30) NOT NULL
, manager_id INT(6)
, location_id INT(4)
, PRIMARY KEY (department_id)
) auto_increment=280;

ALTER TABLE departments
ADD ( FOREIGN KEY (location_id)
REFERENCES locations (location_id)
) ;

/*
CREATE SEQUENCE departments_seq\
START WITH 280\
INCREMENT BY 10\
MAXVALUE 9990\
NOCACHE\
NOCYCLE;\
*/

CREATE TABLE jobs
( job_id VARCHAR(10)
, job_title VARCHAR(35) NOT NULL
, min_salary INT(6)
, max_salary INT(6)
, PRIMARY KEY (job_id)
) ;


CREATE TABLE employees
( employee_id INT(6)
, first_name varchar(20)
, last_name varchar(25) NOT NULL
, email varchar(25) NOT NULL
, phone_INT varchar(20)
, hire_date DATE NOT NULL
, job_id varchar(10) NOT NULL
, salary DECIMAL(8,2)
, commission_pct DECIMAL(2,2)
, manager_id INT(6)
, department_id INT(4)
, UNIQUE KEY (email)
, PRIMARY KEY (employee_id)
) auto_increment=207;

DELIMITER //
CREATE TRIGGER emp_sal_min BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF (salary <= 0 ) THEN call unknown(); END IF; END// DELIMITER ; ALTER TABLE employees ADD ( FOREIGN KEY (department_id) REFERENCES departments(department_id)); ALTER TABLE employees ADD ( FOREIGN KEY (job_id) REFERENCES jobs (job_id)); ALTER TABLE employees ADD ( FOREIGN KEY (manager_id) REFERENCES employees(employee_id)) ; ALTER TABLE departments ADD ( FOREIGN KEY (manager_id) REFERENCES employees (employee_id)) ; /* CREATE SEQUENCE employees_seq\ START WITH 207\ INCREMENT BY 1\ NOCACHE\ NOCYCLE;\ */ CREATE TABLE job_history ( employee_id INT(6) NOT NULL , start_date DATE NOT NULL , end_date DATE NOT NULL , job_id VARCHAR(10) NOT NULL , department_id INT(4) , PRIMARY KEY (employee_id, start_date) ) ; DELIMITER // CREATE TRIGGER jobhist_dates BEFORE INSERT ON job_history FOR EACH ROW BEGIN IF (end_date > start_date ) THEN call unknown();
END IF;
END//

DELIMITER ;

ALTER TABLE job_history
ADD (FOREIGN KEY (job_id)
REFERENCES jobs(job_id));


ALTER TABLE job_history
ADD ( FOREIGN KEY (employee_id)
REFERENCES employees(employee_id));

ALTER TABLE job_history
ADD (FOREIGN KEY (department_id)
REFERENCES departments(department_id)) ;


CREATE OR REPLACE VIEW emp_details_view
(employee_id, job_id, manager_id,
department_id, location_id, country_id,
first_name, last_name, salary,
commission_pct, department_name, job_title,
city, state_province, country_name, region_name)
AS SELECT
e.employee_id, e.job_id, e.manager_id,
e.department_id, d.location_id, l.country_id,
e.first_name, e.last_name, e.salary,
e.commission_pct, d.department_name, j.job_title,
l.city, l.state_province, c.country_name, r.region_name
FROM
employees e, departments d, jobs j,locations l,
countries c, regions r
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.country_id = c.country_id
AND c.region_id = r.region_id
AND j.job_id = e.job_id ;

Tuesday, June 9, 2009

MySQL Workbench - Export Features

MySQL Workbench has some nice export features. They include:
  • Forward engineer SQL Create Script
  • Forward Engineer SQL ALTER Script
  • Synchronize With SQL Create Script
  • Export as PNG
  • Export as SVG
  • Export as Single Page PDF
  • Export as Single Page PostScript File
Export options (just choose File Menu then Export option):












MySQL Workbench 5.2 - Some nice features

MySQL Workbench 5.2 Alpha

My understanding is the MySQL Workbench 5.2 has the core 5.1 modeling features as well as the new Query Browser. The Query Browser has been easy to use but the feature functionality is still in the alpha phase. Adding the Query Browser in the 5.2 release adds a nice dimension of being able to look at the data model, physical table and data very easily. So far I have found the tool very intuitive and have not had to spend any time figuring things out. So far I have not done any benchmarking on the product.

New Query Browser




















Reverse Engineering Feature


I used the reverse engineering feature with the Sakila sample schema and it was point and click and the data model was built in just a second. Notice the ERD diagram in the top left, hierarchy of schemas and objects on right side and detailed table definition at bottom left.


MySQL Workbench - Visual Database Design Tool

What is MySQL Workbench?

MySQL Workbench is a visual database design tool available on Window, Linux and Mac OS. The MySQL Administrator and Query Browser tools have been deprecated. The database GUI interfaces will be rolled into the MySQL Workbench 5.2 release (currently alpha). MySQL Workbench is available in a Community (free) and Enterprise edition (paid license). Below are a few good links to look at:

Always looking at new tools

I've been looking for a better tool to work in a MySQL environment when I want to demo relationships between data models, physical tables, data and SQL code. It has to be a tool that the students can pick up easily without spending a lot of time understanding how it works. It's always an investment to learn another tool. So I've downloaded the MySQL Workbench 5.2 alpha release and really like what I've seen so far. I like it so much I'm going to start giving demonstrations for my work using the Workbench 5.2 release. I understand it's alpha but for my needs it was really easy to pick up and start using it right away. Anyone using Workbench for a production environment should be using 5.0 (GA) or 5.1 (Beta but soon to go GA).

Why I was looking for a new visual database design tool

I actually did not have it on my top three list of tools to look at initially. I just had some spare time this afternoon and downloaded the latest release of MySQL Workbench. I picked the alpha release because I wanted to look at the latest features. I just wanted to play with it but it was so easy to use that I starting playing with it seriously. I was doing a complex example this last week of looking at a customer issue from a number of perspectives to show problems that come up in database environments.

Really easy to build a data model, physical schema and use query browser

Before I knew it, I had built a data model, the physical schema, populated the key tables with data and did some query benchmarks. I realized in this personal exercise I had built an easy to follow demo that I wanted to use to show students some specific examples of database servers and troubleshooting problems. I also seen that I could have students new to MySQL Workbench be able to easily follow me by using the tool as well. I then started reading the whitepapers and documentation to understand the tool better. Before I knew it I was liking the product more and more. I realized that in about ten minutes I can easily convert a lot of users using SQL GUI tools in a MySQL environment easily to the MySQL Workbench 5.2 alpha release. But for how I am using it, its okay that it is alpha. Obviously before using any alpha release make sure you do your due diligence and understand if it is okay to use an alpha product for your situation. I'm just doing demos so I want a tool that meets my needs for easy to use demonstrations and a tool that is easy for students to use as well in following my demos.

MySQL Workbench Features

MySQL Workbench is a visual database design tool. Some of the key features available in this release:
  • Manage database schema objects: tables, views, indexes, stored procedures, functions and triggers.
  • Easy to use query browser tool.
  • Choice of diagramming notation.
  • Model validation.
  • Schema validation.
  • Forward and reverse engineering.
  • Change management.
  • Reporting functionality.
I've liked what I've seen in MySQL Workbench so I will be demoing with it and using the functionality in my future classes.

Thursday, May 28, 2009

What is MySQL?

It's been an incredible journey coming from the Oracle world (which I love) and having the privilege of joining MySQL and watching MySQL be on the verge of going public and then being part of the Sun acquisition and now watching the Oracle acquisition move forward. To say its been one of the richest experiences in my professional career would be an understatement.

Which makes me think, what is MySQL? If you asked 20 different people you would get 20 different answers. Some of the most common I believe would be:
  • A small open source company that wanted to disrupt the IT industry and did!
  • A company with incredible leadership that had a vision that every single employee was committed and dedicated to that vision.
  • A free open source database that helped redefine the database industry.
  • A very successful database product that has incredible scale out capability that leverages open source and the LAMP stack.
  • A product that opened up more options for organizations during a time of an economic down turn.
Today and twenty years from now, when I think of MySQL I do not just think of a database software product that has disrupted the IT industry. I think of:
  • A culture based on sharing, teamwork, commitment to a vision and love of open source.
  • A community that is entirely team based with an incredible sense of loyalty to each other and the goals defined by the leadership.
  • A leadership team that believed in speaking to the employees and not at them.
  • An environment built on empowering individuals to make the sum greater than the parts.
  • The incredible dedication and selflessness of the employees.
  • An environment that cultivated incredible talent and was able to define a vision that people believe in.
The spirit, dedication, belief in the vision and camaraderie is as strong in the MySQL community as it was over a year and a half when I joined MySQL. The culture, Qua, karma, kismit or what ever you want to call it, is I believe growing stronger.

I'm a big reader of business books and I believe we will see some successful business books on the MySQL story. Yet if these books talk about the MySQL success story or the MySQL product they will be capturing a small part of what MySQL is. To me, the MySQL story has to include the vision, the incredible culture and the unique group of people that have come together to disrupt the IT industry.

I write this blog entry because I always find it interesting the people that like to tear down our heroes, spread Fear Uncertainty and Doubt (FUD) and scare people with worse case scenarios. I on the other hand believe we should believe its okay to have heroes, believe that good things can happen and never underestimate what individuals that believe in a vision can accomplish.

The Big Misperception by new MySQL Customers

When meeting with MySQL customers I find they usually have some very strong misperceptions of using the free version (Community Version) of MySQL and that they have no need to purchase the Enterprise Version of MySQL.

First of all, most new MySQL users do not install, configure, tune or manage the MySQL database server properly. MySQL is the easiest database server in the world to install, but if used in a production environment it needs to be set up properly. When an organization purchases an Oracle license for six or seven figures they understand they had better get the appropriate expertise to manage their Oracle database server. When downloading MySQL for free, they think anybody can manage the MySQL server because it is free and anybody can install it. There is a big difference between installing and playing with a MySQL database server and running it in a production environment.

MySQL is extremely popular with web applications. Often traditional OLTP and DW DBAs are not familiar with architectures surrounding the MySQL database server or how web applications need to be configured differently than a traditional OLTP database or data warehouse application.

When using the MySQL server in a production environment it needs to be configured properly for it to run right. Problems new customers can have when using MySQL:
  • Database server running slow, hanging and possibly crashing because it was not configured properly for the production server environment it will be running in.
  • Batch operations taking 40 - 60 hours when they should be taking 20 - 60 minutes because the database server is not tuned properly.
  • High cost of using MySQL because customers do not understand all the features of MySQL.
  • Not using MySQL for the right type of application or using the wrong architecture for their MySQL solution.
New customers talk about how they do not need to purchase the Enterprise version of MySQL and how support is not necessary. MySQL support is arguably the best support in the IT industry and MySQL support people are well recognized experts in MySQL. MySQL support people are not call takers, they are experienced experts in MySQL. So often one support can justify the cost of purchasing a MySQL Enterprise License.

When customers ask if they should be purchasing the MySQL Enterprise edition, I ask them these following questions.
  1. If your database server crashes and it costs your company money or impacts your business you should be looking at support. Production database servers should be running in a supported environment.
  2. Do you have the expertise to configure and tune your database server?
  3. Do you understand the ramifications of how you are configuring MySQL, the architecture it will run under and what features you are using? If not, you could be setting yourself up for problems down the road?
I guess the question is, can your business afford to run the free version of MySQL or should you be purchasing the supported Enterprise release of MySQL.? It's so funny, because no one would every run an important Oracle database server without some type of support. So I always wonder why a customer would run the community version of MySQL and not the supported version of MySQL for critical applications?

Thursday, May 21, 2009

Brian Miezejewski speaking at the Dallas MySQL Users Group

Brian Miezejewski at Dallas MySQL Users Group

Brian Miezejewski will be speaking at the Dallas MySQL Users Group meeting June 1st in Dallas. Brian is a top respected MySQL consultant. If Brian is talking about performance tuning MySQL, then any MySQL DBAs in the area should be attending to listen to what he has to say.

I'm planning on attending the Dallas MySQL group meeting and listening to Brian.




Dallas MySQL Users Group Meeting June 1st, 2009

Meeting: June 1st
7:00 PM
Suite 700 16000
Dallas Tollway

Setting the MySQL Temporary Directory

One of the common mistakes made by new MySQL DBAs is forgetting to consider the MySQL temporary directory. If an internal heap table exceeds the size of the MySQL parameter TMP_TABLE_SIZE, MySQL will create a temporary MyISAM file on the disk to buffer the data.

MySQL will use the TMPDIR environmental variable to set the default location for storing temporary files. If this environmental variable is not set, the OS default will be used (i.e. /tmp, /usr/tmp or /var/tmp, etc.).

The mysqld --tmpdir can be set to multiple paths used in a rotating method. Paths are separated by colon characters (Unix) or semi-colons (Windows).

Remember that temporary files are created as hidden files. This makes sure that temporary files are deleted if mysqld is terminated.

Make sure to NOT set the TMPDIR path to the MySQL data directory filesystem.

Removing a MySQL Service FROM Windows

After shutting down and removing a MySQL database server from a Windows platform it is important to remember to remove the Windows service associated with it. There are two different ways to remove a MySQL server.
  • MySQL way: c:> mysqld --remove servicename
  • Windows way: c:> sc delete servicename

Saturday, May 16, 2009

Oracle SQL Developer with MySQL: Looks great so far.
















So far I am pretty excited about using Oracle SQL Developer with MySQL. I spent about ten minutes installing Oracle SQL Developer, the MySQL J Connector and setting up SQL Developer to connect to MySQL.

I've only been using it a few minutes and so far its been great to work with. Oracle SQL Developer has matured into a nice interface so its been really fun to work with. In terms of ease of use and being a nice interface to work with its been awesome! Now I need to start testing all the features and doing some migrations between Oracle and MySQL.

Installing and Configuring Oracle SQL Developer with MySQL

Installing Oracle SQL Developer was way too easy!

Installing Oracle SQL Developer is pretty straight forward to install. You will need to install the Oracle SQL Developer software and the MySQL J Connector (JDBC driver). It then requires about two minutes to point and click through the setup of the MySQL connection using the JDBC driver in SQL Developer. Oracle SQL Developer has the following downloads:
  • Oracle SQL Developer for Windows.
  • Oracle SQL Developer for Mac OS X platforms.
  • Oracle SQL Developer RPM for Linux.
  • Oracle SQL Developer for other platforms.

Install MySQL J Connector:
You will first need to download the MySQL J Connector (JDBC Driver) at the MySQL download site..

Install Oracle SQL Developer
Then download Oracle SQL Developer software from Oracle OTN. . You need to go to the Oracle Technology Network (OTN) and set up a user account (it is free). I installed it on Mac OS. I just unzipped the zip file in the Applications directory and started Oracle SQL Developer. Here is the main window in SQL Developer.

















After starting Oracle SQL Developer, go to the Tools Menu and choose Preferences. Then open up the Database Hierarchy and choose Third Party JDBC Drivers.
















Then click on the Add Entry button to add the JAR file containing the JDBC Connector. I put the J Connector file in /Users/George/Library/Application Support/SQL Developer directory. In the File Browswer I then choose: Library | Application Support | SQL Developer, then selected the mysql-connector-java-5.1.7-bin.jar file. You will then see the jar file added.



















Then right click on the Connections Icon in the main window. It will then bring up a tab window showing Oracle and MySQL. Choose the MySQL tab. Enter a connection name, username and password. Then test the connection. You will then see your new MySQL connection below the Connections Icon in the main window. Double click on your MySQL connection and start writing queries. Then hit the green arrow button to execute the SQL statement.

















Oracle has some great tutorials on how to get started with SQL Developer.


Have fun!

Wednesday, May 13, 2009

The Importance of Peripheral DBA Skills

At http://trubix.blogspot.com I've written a blog on the importance of peripheral DBA skills. In the MySQL world, the following peripheral skills are very helpful for managing MySQL environments:
  • LAMP skills - Linux, Apache, PHP/Perl/Python.
  • Application Servers - knowledge of Apache, JBoss, LightHTTP, connection pooling, etc.
  • Monitoring skills - Enterprise Monitor, Nagios, Cacti, etc.
  • Ability to support developers - PHP, Java, .NET.
  • Networking skills.

Saturday, May 9, 2009

New Things to Look at: OEM Grid Control with MySQL Plug-in

My customers tell me Oracle is winning the war with the Oracle Enterprise Manager (OEM) as the tool of choice for managing the Oracle Infrastructure across multiple tiers. With Oracle 11g, Oracle will be adding more RAC integration/functionality into the middle-tier with OEM. Part of the power of OEM is that it manages Oracle Database Servers, Application Servers, Services, Applications and the environment surrounding the Oracle Infrastructure.

OEM provides a number of plug-ins so I need to look into how having OEM monitor and manage MySQL with the OEM MySQL Plug-in. OEM also provides plug-ins for monitoring other database servers as well as other application servers. The capability to manage multiple tiers of heterogenous software is some very serious functionality. So seeing how well it manages MySQL is going to be fun.

New Things to Look at: InnoDB Plug-in

Just about every week I am asked by customers what storage engine they should be using. As well as, what storage engine should we be paying attention to in the future (i.e. Falcon, Maria, InnoDB, etc.).

It's finally time to start looking very seriously at the InnoDB plug-in. It's in the final beta releases while Maria and Falcon are still in alpha stage. The really nice thing about the InnoDB storage engine is it works very similar to how the Oracle works with a database buffer cache, redo log cache and undo data. It's always very easy for me to explain how InnoDB works to Oracle DBAs. There is also an InnoDB Hot Backup utility that I always hear positive things from customers using it.

I will be following with my thoughts and impressions of the Oracle InnoDB Plug-in.

Friday, May 8, 2009

New Things to Look: Oracle SQL Developer with MySQL

GUI Interfaces to use with MySQL

When meeting with MySQL customers I always get asked about GUI interfaces.
  1. I am always asked, what GUI to you recommend with MySQL? Two of the most popular are SQLYog and Navicat for MySQL customers.
Looking at Oracle SQL Developer

I think it is time to see how well SQL Developer works with MySQL. Oracle's SQL Developer has matured into a very nice tool and its free. I also want to see how well it migrates objects between MySQL and Oracle databases. SQL Developer has a lot of very cool functionality and all the interfaces are well designed. SQL Developer is also a Java application so you have the same look and feel on Linux, Mac OS and Windows.


Thursday, May 7, 2009

Collaborate 09 - Wrapup

The Oracle users Collaborate 09 was an absolutely fantastic technical conference. The presentations were exceptional in their quality. This conference was invaluable for networking and the discussions with Oracle user industry leaders from around the world. The discussions, insights and perceptions that were discussed every night were in my opinion even more valuable that the technical presentations. The presentations, podcasts and recordings will be available for two weeks following the conference.

It's now on to Java One and Oracle Open World.


Tuesday, May 5, 2009

Collaborate 09 - George Trujillo

Most of my blog entries this week are going to be at http://web.mac.com/george.trujillo since I'm at the Collaborate 09 Users conference.

Friday, May 1, 2009

Oracle Collaborate 09 - Oracle Users Conference

The Oracle User Community's big yearly event, Collaborate 09 in Orlando begins this week. This is going to be a hot conference bringing together the Oracle user communities top technology experts and industry leaders together. During this time of dynamic change it is an invaluable experience to bring get Oracle's top industry leaders for a week of meetings, presentations, discussions and networking events. Include top members from the Oracle product groups and Oracle's largest third party vendors and its a formula for invaluable networking.

My conference starts out this weekend with:

Saturday

  • IOUG Board of Directors meeting
  • Conference committee meeting
  • IOUG, Quest and OAUG board of directors reception
  • Mindy's big birthday bash
Sunday
  • IOUG University events - I'm planning on attending Michael Ault's Tuning university.
  • SELECT Magazine Expert Panel
  • IOUG Volunteer Reception
  • IOUG SIG Reception









Second Life Presentation on Sun Solaris Campus

This virtual world presentation will be delivered at the Sun Microsystems Islands on the Solaris Campus. Up above, I'm chilling at the Solaris Campus on one of the Sun virtual islands.

Second Life Presentation on Sun Solaris Campus

The MySQL open source database continues to increase in popularity with usage estimated at 12 million database servers worldwide. This presentation will help Unix/Linux and storage management administrators understand the reasons for the growing popularity of MySQL. Topics will include:

  • Positioning MySQL in the database market.
  • How is MySQL different than
  • Understanding the MySQL architecture.
  • Strengths and weaknesses of MySQL.
  • Key features of MySQL.
  • Understanding the storage engine story in MySQL.
  • MySQL strategic directions.

The focus of this presentation is on helping attendees understand the philosophy, features, benefits and popularity of MySQL.

Demystifying MySQL for Solaris Administrators: Sun Solaris Campus Event










Second Life Presentation on Sun Solaris Campus May 5, 2009 9:00 am PDT

This virtual world presentation will be delivered at the Sun Microsystems Islands on the Solaris Campus. Up above, I'm visiting the Solaris Certification Center.

Demystifying MySQL for Solaris Administrators

George Trujillo (Ty Valdez) and Steve Jones will be presenting a Demystifying MySQL for Solaris Administrators the week of May 4, 2009 in Second Life. This presentation is a very detailed technical presentation designed for experienced Unix/Linux administrators to understand installation, configuration and management of MySQL on Solaris platforms. MySQL best practices along with Solaris features that can leverage database management such as ZFS will be included. Topics will include:

  • Strategies for laying out database servers on Solaris (Unix/Linux) platforms.
  • Understanding the MySQL architecture.
  • Installing MySQL using MOCA (GOCA) for best practices.
  • Starting and stopping the MySQL database server.
  • Top ten things to configure after creating a MySQL database server.
  • Using MySQL with ZFS.
  • Managing MySQL on a Solaris platform.



Monday, April 27, 2009

Installing MySQL 5.1 on Solaris using MOCA

Introduction

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

If you are new to MySQL or to Solaris, I recommend using the default package install for MySQL. The MySQL default install is recommended for someone new to MySQL or the operating system platform. If the default package install makes more sense for you, then you can stop reading. This install is for MySQL 5.1.33 but it would be the same steps for any 5.1.xx installation.

Why Perform a Manual Install

The default install with MySQL is great for users new to MySQL. It is simple, requires a few point and clicks and you are up and running. The problem with a default install is that it is designed to be a very simple install and take minimum resources. The default install also puts MySQL files in different locations on the filesystem dependent on the OS release and platform. The default install is not how an experienced DBA would want to set up a production database environment. It is much better to be able to control the layout and configuration of the database software for production database environments and for platforms where multiple MySQL servers may be installed in the future.

    This install assumes you have a fundamental understanding of Solaris and have an understanding of MySQL database administration fundamentals. Oracle DBAs will find this installation very similar to the concepts of the Optimal Flexible Architecture (OFA).
    For experienced MySQL DBAs a manual install is much better. For this purpose I created a best practices configuration and white paper called MOCA (MySQL Optimal Configuration Architecture). This is based on DBA best practices and should be very similar to Oracle, DB2 and SQL Server production DBAs. There are certain fundamental truths about how database servers should be installed, configured and managed. My MOCA whitepaper addresses these fundamental truths. This manual install will follow MOCA standards and conventions.

Why MOCA?

Visit mysql-dba-journey.blogspot.com to get the details of the reasons behind MOCA and why it is based on best practices. There is also an example of installing MySQL on Mac OS that is very similar to a Linux install. In summary, it focuses on:
  1. Separating database software from other software.
  2. Separating data and index files, log files for recovery, administration and backup files.
  3. Developing standard naming conventions.
  4. Defines a flexible configuration that can support multiple database servers on same platform.
  5. A consistent configuration for multiple servers and versions of MySQL database software.

Installation Summary

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

  1. Remove old versions of MySQL if they exist. Setup up operating system user mysql and mysql user environment.
  2. Set up directories and directory permissions for all MySQL data files.
  3. Setup MySQL software and install MySQL software as mysql operating system user (not as root). Configure the my.cnf configuration file.
  4. Create the mysql database (mysql_install_db) and setup the security environment (mysql_secure_installation). Start the mysql database server.
  5. Test the shutdown and startup of the database server.

Installation Environment

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

    Before installing MySQL on my platform, make sure there are no previous versions of MySQL preinstalled. Unless you want the older version of MySQL, your life will be much easier if you remove any previous releases.
    Read through this installation a few times before starting.

Look for existing MySQL software

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

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

VM Fusion Choices for Installing Solaris 10

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

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

Removing older versions of MySQL

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

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

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

Setup new mysql user if one does not exist. If a mysql user does exist, set up a password, default shell, default directory, etc.
No mysql user was found so I added one. Add the mysql group, mysql user, password and home directory.
# groupadd -g 300 mysql
# useradd -u 300 -g 300 -d /export/home/mysql -s /usr/bin/bash -c "MySQL DBA" mysql
# passwd mysql
# mkdir /export/home/mysql
# chown -R mysql:mysql /export/home/mysql

Login and verify the mysql user setup

# exec login mysql (or su - mysql)

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

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

Set your environment by sourcing your profile file.

$ cd $MYSQL_HOME
$ . ./.bash_profile

Downloading MySQL

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

MySQL Directory Organization

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

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

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

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

# mkdir /db03/mysql/$MYSQL_NAME/startup

# mkdir /db03/mysql/$MYSQL_NAME/run


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

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

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

Before going further

Double (triple) check all directory paths and permissions. 99.99% of issues with manual installs are typos in the directory paths, typos in the file names or permission issues with directories. Double check all paths. When you try to bring up database server, if it defaults to the default areas its because it can;t find a directory or doesn't have permission for directories specified so it will then try the default locations.

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

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

Be careful with the my.cnf configuration file.

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

$ mysql --help | more

$ mysqld --help --verbose | more

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

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

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

[mysqld]

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

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

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

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

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

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

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

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

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

mysql test

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

If there are socket errors:

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

Solution:

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

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

Verify the mysqld background process is running as well as the mysqld_safe monitoring process. The mysqld background process should be up and running.

$ ps -ef |grep mysql

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

$ cd $MYSQL_HOME
$ bin/mysql_secure_installation

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

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

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

You're up and running have fun. Once you are confortable with this configuration layout, you can create a Unix shell script that will automate almost the entire process. With a shell script automation the install takes about ten minutes.