-- Execute the tabinfo stored routine using the CALL command.
Saturday, October 24, 2009
-- Execute the tabinfo stored routine using the CALL command.
Monday, September 28, 2009
|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
- 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.
Friday, September 4, 2009
New InnoDB Plugins contain information on:
- Compressed InnoDB tables
- Compressed InnoDB buffer pool
- Current InnoDB transactions
- Transaction Locks
- Blocking transactions
mysql> SELECT table_name, engine, table_type FROM information_schema.tables
WHERE table_name LIKE 'innodb%'
mysql> SHOW PLUGINS;
mysql> SHOW PLUGINS;
InnoDB parameters example:
#*** INNODB Specific options ***
|Name||Old Default||New Default|
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%';
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%';
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:
Setting the InnoDB Plugin on Windows:
mysql> SHOW VARIABLES WHERE variable_name like '%plugin%' OR variable_name like '%builtin%';
#*** INNODB Specific options ***
Thursday, September 3, 2009
- Improved performance and scalability.
- Important management features that will be very helpful to MySQL DBAs.
For documentation on installing MySQL 5.1.38 or upgrading from previous MySQL releases, look here:
Wednesday, August 26, 2009
Monday, July 13, 2009
Technical Advantages moving from a MySQL Server to an Oracle database server
- Scalability: Much higher vertical scalability and higher OLTP transaction capability. Ability to set up shared server architecture for more user connection scalability.
- Metadata: Lots of very detailed metadata information as well as wait events for tuning.
- Stored Routines: Compiled stored procedures with much higher scalability. Java and .NT stored procedure capability.
- SQL functionality: Lots of SQL functions and analytical SQL functions. Lots of XML functionality built into database server.
- Backups: Hot online backups with Oracle's Recovery Manager
- Automatic Management: Lots of automatic management with Automatic Database Diagnostic Management (ADDM), Advisors, Automatic Workload Repository (AWR) , Automatic Segment Management, Shared Memory Management, etc.
- Data Warehousing: Bitmap indexes, materialized views, analytical functions, star joins, sort-merge joins, hash joins, etc.
- Grid Control: Oracle Enterprise Manager is a very robust enterprise tool for managing and monitoring multiple tiers of an Oracle infrastructure.
- Inexpensive disk strategy: Striping and mirroring capability with Oracle's Automatic Storage Management (ASM).
- 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
- 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.
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.
Tuesday, June 30, 2009
- 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.
Congratulations to the Workbench team for their great effort in this release.
Thursday, June 11, 2009
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.
- 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
MySQL equivalent of the Oracle HR schema script
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.
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;
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)
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)
CREATE SEQUENCE locations_seq\
START WITH 3300\
INCREMENT BY 100\
ALTER TABLE locations
ADD ( FOREIGN KEY (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)
ALTER TABLE departments
ADD ( FOREIGN KEY (location_id)
REFERENCES locations (location_id)
CREATE SEQUENCE departments_seq\
START WITH 280\
INCREMENT BY 10\
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)
CREATE TRIGGER emp_sal_min BEFORE INSERT ON employees
FOR EACH ROW
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();
ALTER TABLE job_history
ADD (FOREIGN KEY (job_id)
ALTER TABLE job_history
ADD ( FOREIGN KEY (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)
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
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
- 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
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.
- http://dev.mysql.com/workbench/?page_id=11 (Comparison of Community and Enterprise)
- http://dev.mysql.com/workbench/?p=13 (Insight from Michael Zinner)
Always looking at new tools
Why I was looking for a new visual database design tool
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
- 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.
Thursday, May 28, 2009
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.
- 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.
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.
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.
- 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.
When customers ask if they should be purchasing the MySQL Enterprise edition, I ask them these following questions.
- 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.
- Do you have the expertise to configure and tune your database server?
- 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?
Thursday, May 21, 2009
I'm planning on attending the Dallas MySQL group meeting and listening to Brian.
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.
- MySQL way: c:> mysqld --remove servicename
- Windows way: c:> sc delete servicename
Saturday, May 16, 2009
- 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.
Wednesday, May 13, 2009
- 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
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
When meeting with MySQL customers I always get asked about GUI interfaces.
- I am always asked, what GUI to you recommend with MySQL? Two of the most popular are SQLYog and Navicat for MySQL customers.
Thursday, May 7, 2009
It's now on to Java One and Oracle Open World.
Tuesday, May 5, 2009
Friday, May 1, 2009
My conference starts out this weekend with:
- IOUG Board of Directors meeting
- Conference committee meeting
- IOUG, Quest and OAUG board of directors reception
- Mindy's big birthday bash
- 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.
Second Life Presentation on Sun Solaris Campus May 5, 2009 9:00 am PDT
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
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.
- Separating database software from other software.
- Separating data and index files, log files for recovery, administration and backup files.
- Developing standard naming conventions.
- Defines a flexible configuration that can support multiple database servers on same platform.
- A consistent configuration for multiple servers and versions of MySQL database software.
This installation looks more complex than it is. I use this configuration for all beginning MySQL DBA classes.
- Remove old versions of MySQL if they exist. Setup up operating system user mysql and mysql user environment.
- Set up directories and directory permissions for all MySQL data files.
- Setup MySQL software and install MySQL software as mysql operating system user (not as root). Configure the my.cnf configuration file.
- Create the mysql database (mysql_install_db) and setup the security environment (mysql_secure_installation). Start the mysql database server.
- Test the shutdown and startup of the database server.
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 ------
export MYSQL_BASE MYSQL_HOME
--- end of .bash_profile file -------
Set your environment by sourcing your profile file.
$ cd $MYSQL_HOME
$ . ./.bash_profile
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
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
#port = 3426
Add the following entries to the my.cnf file to the [client] group.
#port = 3426
#socket = /dbadmin/mysql/mysql01/run/mysql.sock
$ 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.
$ 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
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
MySQL needs to write to a socket. If you don't specify one, a default one is chosen which may not have the appropriate permissions. You can specify the socket file in the command line as below. Make sure the permissions are set properly (owned by mysql).
$ mysql -uroot -p -socket=/dbadmin/mysql/mysql01/run/mysql.sock
$ ps -ef |grep mysql
Clean up the database server by adding passwords and getting rid of anonymous users. If there are problems with the mysql_secure_installation script, then set the password manually and get rid of the anonymous accounts and any accounts with no passwords.
$ cd $MYSQL_HOME
$ mysqladmin --defaults-file=/dbadmin/mysql/mysql01/startup/my.cnf shutdown
$ cd $MYSQL_HOME
$ bin/mysqld_safe --defaults-file= /dbadmin/mysql/mysql01/startup/my.cnf
George Trujillo at Collaborate 09 Week of May 3, 2009
Demystifying MySQL for Oracle DBAs and Developers Tues @ 9:45am - 10:45am, 224AGeorge Trujillo continues his popular demystifying series that started with Demystifying Oracle Fusion Middleware, to now include a MySQL demystifying series. This presentation will use George's MOCA best practices blueprint for installing, configuring and tuning a MySQL database server. This fast paced detailed technical presentation will cover: MySQL Architecture, Installation, Configuration, Key Features, Security, MySQL Tools, Storage Engines, transactional capabilities and strategic directions. This is a can't miss presentation for any Oracle DBA wanting to understand MySQL.