Installing MySQL 5.1 on Solaris 10 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 set of 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 uses MySQL 5.1.24 and it will work for any 5.1.x install (i.e. 5.1.42).

 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 and Linux.    In summary, MOCA 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  MySQL DBA classes.

  1. Remove old versions of MySQL if they exist.  Setup up operating system user called "mysql" and the environment for this user.
  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 Solaris 10 - Downloaded DVD iso image from www.sun.com website. I installed the Solaris 10 05/08 x86/x64 image for this demo (sol-10-u5-ga-x86-dvd.iso). MySQL 5.1 I 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 that are not being used.
    Read through this installation a few times before starting.

Look for existing MySQL software

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

Check to see if MySQL is installed 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 on Solaris

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/

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

Login and verify the mysql user setup

# su - mysql (or exec login mysql)

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

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

Set your environment by sourcing your profile file.

$ cd $MYSQL_HOME
$ .   ./.bash_profile

Downloading MySQL 

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

MySQL Directory Organization 

A good way to separate MySQL files and software:
/opt/mysql/5.1.24 - Symbolic link to software directory location
/db01/mysql/mysql01/data          - data directory
/db02/mysql/mysql01/binlogs     - location of binary log files
/db03/mysql/mysql01/admin       - main administration directory
/db04/mysql/mysql01/backups    - location of backup files

Create the following base (parent) directory to download the MySQL software into.
# mkdir -p /opt/mysql
# export MYSQL_NAME=mysql01

The directory pattern of "mysql01" will be used to uniquely identify all physical files associated with this specific MySQL database server.
Setup data directory structure
# mkdir -p /db01/mysql/$MYSQL_NAME/data

Setup mysql administration directory structure
# mkdir -p /db03/mysql/$MYSQL_NAME
# cd  /db03/mysql/$MYSQL_NAME/
# mkdir logs errors sql startup 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.24-rc-solaris10-x86_64.tar.gz
$ tar xvf mysql-5.1.24-rc-solaris10-x86_64.tar
$ ln -s mysql-5.1.24-rc-solaris10-x86_64 5.1.24
$ cp $MYSQL_HOME/support-files/my-small-cnf   $MYSQL_HOME/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]
log-error=/db03/mysql/mysql01/errors/mysql01.err
pid-file=/db03/mysql/mysql01/mysql01.pid
datadir=/db01/mysql/mysql01/data
basedir=/opt/mysql/5.1.24
log-bin=/db02/mysql/mysql01/mysql-bin

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

ib_logfile0  ib_logfile1  ibdata1      mysql        test 

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

If there are socket errors:

i.e.  MySQL client cannot start with the error "cannot connect to the MySQL server through socket <filename>

Solution:

Each MySQL needs to write to a unique socket file.  If you don't specify one, a default one is chosen which may not have the appropriate permissions.   Make sure the permissions are set properly (owned by mysql).  The socket needs to match for the mysqld and the client side.  Make sure the socket definitions match in the my.cnf file in the [mysqld] and [client] groups.


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.

$ mysqladmin --defaults-file= $MYSQL_HOME/my.cnf   shutdown -uroot -p
$ cd $MYSQL_HOME

$ bin/mysqld_safe --defaults-file= $MYSQL_HOME/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.
Comments:

This does not work exactly as stated. All entries of "/dbadmin/mysql/mysql/startup/my.cnf" should be "/dbadmin/mysql/mysql/run/my.cnf" as there is no startup directory (versioning error in the guide?)

Also, when I ran this I got the following error in my /dbadmin/mysql/mysql/errors/mysql5.1.25.err:
080624 11:56:48 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

This is because it requires the database be available before mysqld_safe is run. Running the following command before running mysqld_safe fixed the problem.

./scripts/mysql_install_db --datadir=/db01/mysql/mysql/data

Otherwise, works as expected on Solaris 10 5/08 with MySQL 5.1.25

Posted by Andrew D. on June 24, 2008 at 03:23 AM MDT #

Howdy George!

I found a typo, you created mysql group and user but never assigned the group to user:

#useradd -u 300 -d /export/home/mysql -s /usr/bin/bash -c "MySQL DBA" mysql

Need to add -g 300.

Thanks!

Wences

Posted by Wences Michel on December 21, 2008 at 02:35 AM MST #

Thanks Wences! I'll make the change now.

Posted by George Trujillo Jr on December 21, 2008 at 02:43 AM MST #

Howdy George!

Figure it out my previous posting and got it running. Did something tto the effect below:

#mkdir /db03/mysql/mysql01/admin
#mkdir -p /dbadmin/mysql
#su - mysql
>cd /dbadmin
>ln -s /db03/mysql/mysql01 ./
>cp $MYSQL_HOME/support-files/my-small.cnf /dbadmin/mysql/mysql/startup/my.cnf

+++++++++++++
also another typo, my-small.cnf, not my-small-cnf for:
$ cp $MYSQL_HOME/support-files/my-small-cnf /dbadmin/mysql/mysql/startup/my.cnf
should be my-small.cnf

++++++++++++++

Posted by Wences Michel on December 21, 2008 at 07:20 AM MST #

Thank you for this blog
I found it very helpful

Posted by Douglas on February 01, 2009 at 10:49 PM MST #

bash-3.00# ./mysql_install_db --datadir=/usr/local/mysql/data
./mysql_install_db: /usr/local/bin/hostname: cannot execute
WARNING: The host '' could not be looked up with resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges !
Installing MySQL system tables...
090322 23:36:12 [Warning] option 'max_join_size': unsigned value 184467440737095 51615 adjusted to 4294967295
090322 23:36:12 [Warning] option 'max_join_size': unsigned value 184467440737095 51615 adjusted to 4294967295
OK
Filling help tables...
090322 23:36:12 [Warning] option 'max_join_size': unsigned value 184467440737095 51615 adjusted to 4294967295
090322 23:36:12 [Warning] option 'max_join_size': unsigned value 184467440737095 51615 adjusted to 4294967295
OK
what is the problem?
thanks

Posted by seyfullah baskoylu on March 22, 2009 at 08:38 AM MDT #

Thanks for the blog. Very useful
However, I had to specify root user for shutdown as
./bin/mysqladmin --defaults-file=/opt/mysql/MySQLNithya/admin/startup/my.cnf -u root shutdown

So was wondering what I might have done wrong?Any clues?

Posted by Nithya Srinivasan on October 21, 2009 at 06:34 AM MDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

This blog is my perspective of Sun in relation to Oracle, MySQL, Oracle Fusion Middleware, Education and Virtual Reality platforms. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today