Open Source Software
Every year Open Source Software (OSS) is getting better,
covering more areas of IT, growing in adoption and is increasingly used to gain
competitive advantage. From the LAMP
(Linux, Apache, MySQL, Python/Perl/PHP), Java, MariaDB, Openstack and Hadoop, etc,
OSS provides a software stack ranging from operating systems, application
servers, database servers, big data, Cloud orchestration and Cloud
deployments. In the top global strategic accounts I meet with, I see a consistent pattern in the increasing role OSS is playing in organizations.
The MySQL Ecosystem Continues to Grow
An excellent example of OSS success is MySQL. MySQL and it's ecosystem continue to grow in
success and popularity. A healthy OSS
product also has a healthy ecosystem.
Oracle has been doing an excellent job of adding important features and
functionality, while at the same time companies like Percona and MariaDB are
providing enhanced functionality and giving MySQL users choice.
In setting up metadata databases for Hadoop, MySQL is by far my favorite database to use in Big Data environments.
Where is MySQL today:
·
Estimated 16 million plus databases around the
world.
·
Fastest growing database in the Cloud.
·
Popular in big data environments for storing
metadata and as a staging database.
·
An extremely popular database that excels in
small and medium transactional environments.
·
Excels as an Internet database due to it's speed. Also very popular as an Embedded database.
·
Replication is native and relatively easy to use
to the point it is often used for creating backups, data marts and staging
databases.
·
MySQL/Oracle continues to average 60,000
downloads a week and Percona has over 500,000 downloads of it's Percona Server.
· A energetic user community with multiple international
conferences, books, bloggers.
·
Very popular as a data warehouse and data mart
due to it's extremely fast read capability.
·
Plug-ins, connectors, 3rd party
applications and tools continue to be developed for MySQL
· MySQL cluster is an extremely fast HA
environment for fast reads and large volume transactions.
MySQL 's Impact on Oracle Environments
A Fortune 250 company I was at recently, said they had 2000
Oracle databases, 4000 SQL Server databases and 2000 MySQL databases. Another company I was at recently acquires
companies on a regular basis said every company they purchase has a large
number of MySQL database servers. MySQL
in pure volume of databases is the most popular database for the Internet and
the Cloud. MySQL is used as a meta
database and as a staging database in Hadoop environments. Point being, MySQL with it's continued growth
and popularity is gong to continue to have an impact in IT environments.
One of the nice benefits of a MySQL is it is an easy database
server for Oracle DBAs to learn how to manage and support. The best analogy is
MySQL is managing MySQL is very similar to managing an Oracle 7-8 database
server. With MySQL's popularity any Java,
PHP, Python and Ruby developer is likely to be working with MySQL sometime in
the future if not currently
Installing MySQL 's 5.x on Linux using MOCA
Introduction
The
following instructions will show an installation of MySQL on Linux using the
MySQL Optimal Configuration Architecture (MOCA) for someone with fundamental
knowledge of MySQL and basic Linux administration skills. 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 modeled after OFA so it is an
installation that will be familiar to any Oracle DBA. MOCA is designed
for someone with some experience with MySQL, it is not for someone brand new to
MySQL. MOCA is okay if you have a strong database background and good
solid Linux skills. Otherwise use the standard MySQL install and start simple.
If
you are new to MySQL or to Linux, I recommend using a default install with
a rpm install or use yum to do the install for you. 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 someone wanting to setup a flexible and
scalable configuration. This configuration is for MySQL 5.1.42 and it
will work for any 5.1.x install. The
install for any MySQL 5.x release is similar.
If
you are ready to get started you can skip down to the Start the Installation
section. If you scroll to the very bottom of this blog, I have a
summary of the specific commands for the setup.
A Default Install using RPM
or YUM
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. This simple layout is excellent in its simplicity but it is
not how a production server should be set up. The default install also
spreads MySQL files in different locations like /etc, /usr/bin, /usr/local that
is not flexible and violates standard best practices for database
servers. The default install is not how an experienced DBA would want to
set up a production database environment.
Why
Perform a Manual Install Using a Tar Ball
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. Defining
consistent standards, guidelines and best practices that are flexible and
scalable are the key goals of MOCA.
•
This install assumes you have a
basic understanding of Linux and MySQL database administration. 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). I wanted to call it GOCA (George's Optimal Flexible
Architecture) but MOCA seemed to flow better. MOCA 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. A Solaris install using MOCA can be found at blogs.sun.com/georgetrujillo.
MOCA focuses on:
1. Separating
database software from other software and files.
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 across an enterprise.
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 an operating system (OS) user
called "mysql" and the environment for this OS 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 . Start the mysql database server
(mysqld_safe).
5. Setup
the security environment (mysql_secure_installation)
6. Test
the shutdown and startup of the database server to test the server setup.
Start the Installation
The
environment for this installation is:
•
Enterprise Linux downloaded from
Oracle's OTN
website. Red Hat, Fedora and CentOS can be used as well. I chose
Enterprise Linux because I like it and it is great for running Oracle and MySQL
on same system so I can do ETL and play with the two database servers. I
installed the MySQL tar package (ball) x86/x64 image mysql-5.1.42-linux-x86_64-glibc23.tar.gz.
Downloaded from the http:/dev.mysql.com
site.
•
Before installing MySQL on any
platform, make sure there are no previous versions of MySQL preinstalled that
were loaded with the OS. 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.
•
Note: I use the # prompt to
signify I am performing steps as root and the $ prompt to show I am performing
steps as the mysql OS user.
Look for existing MySQL
software
This install uses MySQL
5.1.42, although these installation procedures can be used for any 5.1.x
installation. Dependent on the version of Linux and your hardware, different
packages may need to be installed or removed (old MySQL installations).
So you can see my Linux
environment I ran the following command at the shell.
# more /etc/redhat-release
Red Hat Enteprise Linux
Server release 5.4 (Tikanga)
Check to see if MySQL is installed
on your current system.
# grep mysql /etc/passwd
# rpm -q mysql
# find /usr/local -name
'*mysql*' - print
# find /usr/bin -name
'*mysql*' - print
# find / -name
"*mysql*' - print # look everywhere for MySQL installations
Remove any MySQL old files
or packages. Then verify old MySQL files are gone. If you leave the old
MySQL software that is okay, but make sure MySQL is finding your MySQL files
and not the old software (prime example, using the /etc/my.cnf file instead of
yours).
# rpm -e mysql
Downloading MySQL
Go to http://dev.mysql.com
and find the Downloads tab. Find the distributions and choose the install
release you want. I prefer a manual install so I choose the Linux Tar
Packages file mysql-5.1.42-linux-x86_64-glibc23.tar.gz. Select a mirror. On the
Select a Mirror page, I choose No thanks, just take me to the downloads!
Note: Be aware that this
MySQL image worked for me with the OS and hardware I was using. Make sure
you have the right image for your environment. If commands fail because
you can't run the mysql executables, you probably have the wrong image for your
environment.
For a separate Centos 5.2
install on different hardware I used this image:
mysql-5.1.42-linux-i686-icc-glibc23.tar.gz
For my Linux environment the
file downloaded to the /root/Desktop directory.
Setup the MySQL
software
The first set of commands
are run as the root OS user (# prompt) to set up the environment.
In the /opt/mysql directory unzip and untar the MySQL software and build a
symbolic link. This will set up the MYSQL_HOME directory to be in
/opt/mysql/5.1.42 location.
# mkdir -p /opt/mysql
Copy tar file to /opt/mysql
directory and setup up MySQL software.
# cp
/root/Desktop/mysql-5.1.42-linux-x86_64-glibc23.tar.gz /opt/mysql
# cd /opt/mysql
# tar zxvf
mysql-5.1.42-linux-x86_64-glibc23.tar.gz
# ln -s
mysql-5.1.42-linux-x86_64-glibc23 5.1.42
Setup the MySQL directory
locations. Here we are going to put all files below the /db01/mysql01
mount point. The naming pattern mysql01 signifies database storage for a
specific MySQL database server in this layout. I like to put my InnoDB
data and log files in their own location.
# mkdir -p /db01/mysql01
# cd /db01/mysql01
# mkdir data binlogs admin
backups innodata innologs
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
/home/mysql -s /bin/bash -c "MySQL DBA" mysql
# passwd mysql
# chown -R mysql:mysql
/opt/mysql /db01/mysql01
Login and verify the mysql
user setup. From this point, once you login as mysql user, complete the
database server as the mysql user and not as the root OS user.
# su - mysql (or exec login
mysql)
Then define a default
profile file using your favorite text editor. I chose the bash shell.
--- .bash_profile file
------
PS1='$PWD: '
MYSQL_BASE=/opt/mysql
MYSQL_HOME=/opt/mysql/5.1.42
export MYSQL_BASE MYSQL_HOME
PATH=$MYSQL_HOME/bin:$PATH
--- end of .bash_profile
file -------
Set your environment by
sourcing your profile file.
$ cd $MYSQL_HOME
$ . ./.bash_profile
MySQL Directory
Organization
A good way to separate MySQL
files and software:
•
/opt/mysql/5.1.42
- Symbolic
link to software directory location
•
/db01/mysql01/data
- Data directory for MySQL
•
/db01/mysql01/binlogs
- Binary log files location
•
/db01/mysql01/admin
- Administration files location
•
/db01/mysql01/backups
- Backup files location
•
/db01/mysql01/innodata
- InnoDB shared location
•
/db01/mysql01/innologs
- InnoDB transaction logs location
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. To common gotchas:
•
Using the wrong binary for your hardware or OS.
•
Search paths find a preinstalled version of mysql that was
loaded with the OS. This command will show you the default search path
MySQL uses.
$ mysqld --help --verbose | more
When you try to bring up database server, if it defaults
to the default areas its because it cannot find a directory or doesn't have permission
for directories specified so it will then try the default locations.
Use a template file in the
support-files directory.
$ cd /opt/mysql/5.1.42
$ cp
support-files/my-small.cnf my.cnf
Add
the following entries to the my.cnf file in the [mysqld] group. This separates
all your dynamic administration files, data files, and binary log files to
different locations.
[mysqld]
basedir=/opt/mysql/5.1.42
datadir=/db01/mysql01/data
log-error=/db01/mysql01/admin/mysql01.err
pid-file=/db01/mysql01/admin/mysql01.pid
log-bin=/db01/mysql01/binlogs/mysql01-bin
innodb_data_home_dir=/db01/mysql01/innodata
innodb_data_file_path=ibdata01:50M;ibdata02:50M:autoextend:max:2000M
innodb_log_group_home_dir=/db01/mysql01/innologs
#-----------------------------------------------------
I would also recommend
changing the parameter thread_stack=128k to a minimum of 256k.
Create
the mysql database files for the MySQL database server (instance.) This will
create the default database schemas and the physical file layer.
$ cd $MYSQL_HOME
$
scripts/mysql_install_db --datadir=/db01/mysql01/data
--basedir=$MYSQL_HOME
Start the MySQL database
server pointing to the defined locations.
$ cd /opt/mysql/5.1.42
$ bin/mysqld_safe
--defaults-file=$MYSQL_HOME/my.cnf &
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
Verify you can get into the
server. Initial setup has no passwords. If this works you have a
good server. The show command should display the mysql, test and
information_schema databases.
$ mysql
mysql> show
databases;
mysql> exit
Clean up the database server
by adding passwords and getting rid of anonymous users. The MySQL
database super user is called root. This step will add a password for this
MySQL database user.
$ cd $MYSQL_HOME
$
bin/mysql_secure_installation
Shutdown
the MySQL server to verify you can shutdown and startup the MySQL instance.
$ mysqladmin -uroot -p
shutdown
$ bin/mysqld_safe --defaults-file=
$MYSQL_HOME/my.cnf &
You're
up and running have fun. Once you are comfortable 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 five minutes.
Summary of steps
to perform setup
Below
is a summary of steps minus all the text.
#
---- Steps performed as root OS user. ---------------
#
mkdir -p /opt/mysql
#
cp /root/Desktop/mysql-5.1.42-linux-x86_64-glibc23.tar.gz /opt/mysql
#
cd /opt/mysql
#
tar zxvf mysql-5.1.42-linux-x86_64-glibc23.tar.gz
#
ln -s mysql-5.1.42-linux-x86_64-glibc23.tar.gz 5.1.42
#
mkdir -p /db01/mysql01
#
cd /db01/mysql01
#
mkdir data binlogs admin backups innodata innologs
#
groupadd -g 300 mysql
#
useradd -u 300 -g 300 -d /home/mysql -s /bin/bash -c "MySQL DBA"
mysql
#
passwd mysql
#
chown -R mysql:mysql /opt/mysql /db01/mysql01
#
su - mysql
#
---- Steps performed as mysql OS user ---------------
$
cd $MYSQL_HOME
$
cp support-files/my-small.cnf my.cnf
#
Make these additions in my.cnf file below [mysqld]
#----------------------
basedir=/opt/mysql/5.1.42
datadir=/db01/mysql01/data
log-error=/db01/mysql01/admin/mysql01.err
pid-file=/db01/mysql01/admin/mysql01.pid
log-bin=/db01/mysql01/binlogs/mysql01-bin
innodb_data_home_dir=/db01/mysql01/innodata
innodb_data_file_path=ibdata01:50M;ibdata02:50M:autoextend:max:2000M
innodb_log_group_home_dir=/db01/mysql01/innologs
#---------------------------------------
$ scripts/mysql_install_db
--datadir=/db01/mysql01/data --basedir=$MYSQL_HOME
$
bin/mysqld_safe --defaults-file=$MYSQL_HOME/my.cnf &
$ bin/mysql_secure_installation
