System Advancements at the Monastery

“If you want to change your direction If your time of life is at hand Well don’t be the rule be the exception A good way to start is to stand.” — Song “Put One Foot In Front Of The Other” by Jules Bass

With my recent post, “Introduction to SQLite,” I have received some questions concerning MySQL. Many fine books have been written on MySQL and those books cover MySQL much better than a simple blog post could ever hope to do. Still, there are some interesting links that might be useful for those looking to setup a MySQL server. The purpose of this post is to introduce the reader to sources of information on MySQL and follow up with some basic installation instructions. The sources linked in this post will provide a starting point to find additional in-depth discussions on MySQL.

Theres is no definitive source of information for MySQL. No “Hitchhikers Guide to MySQL.” The closest source fitting that description would be, “MySQL 5.0 Reference Manual.” While it contains a wealth of information, it is somewhat boring. The good news is a wealth of information from different sources does exist. These sources focus in on different areas of interest, allowing you to access material on what is important to you. While Douglas Adams might not have been involved in writing any MySQL references, at least you can choose to delve into details on topics that interest you in formats that may hold your attention (books, articles, podcasts, slides, videos).

Documents and Articles

There is nothing like the printed document when doing an installation, even if it is in electronic format. As Prof. Joseph M. Jacobson once stated, “If books had been invented after the computer, they would have been considered a big breakthrough. Books have several hundred simultaneous paper-thin, flexible displays. They boot instantly. They run on very low power at a very low cost.” Below are a few documents and articles that are most helpful when setting up MySQL.

• MySQL 5.0 Reference Manual - the best place to start is the reference manual. Generally, I prefer printed version verses accessing material online. Since the reference manual is 2372 pages, I would suggest keeping to the electronic version. In relation to security, please pay particular attention to:
  • 2.9. MySQL Installation Using a Source Distribution.
  • 5.3. General Security Issues.
  • 5.4. The MySQL Access Privilege System.
  • 5.5. MySQL User Account Management.
• Center for Internet Security Benchmark for MySQL Versions 4.1, 5.0, and 5.1 Community Editions by Mike Eddington, Leviathan Security Group.
• MySQL: The Complete Reference, chapter 14: Security, Access Control, and Privileges by Vikram Vaswani.
• 10 steps to fortify the security of your MySQL installation.
• Securing MySQL: step-by-step by Artur Maj.
• Secure MySQL Database Design by Kristy Westphal.
• Security and the real world by Kristian Köhntopp.
• How MySQL Treats Security Vulnerabilities by Kaj Arno.
• Getting started with MySQL Proxy by Giuseppe Maxia.

Forums and Blogs

Documents and articles are great, but frequently they can become out of date. While most details remain the same even after versions advance or the software is implemented under different operating systems, the devil is always in the details. Forums and blogs are a good source for finding resolution to those annoying details that can stop an installation cold. Below is a listing of a few forums and blogs that might be of great help if you run into one of those devilish details.

• MySQL Forums - this forum is off the MySQL site and there are many forums of special interest, including a Security Forum. If you run into problems with the installation, there is an Install Forum.
• Planet MySQL - offers blogs, news, and opinions on MySQL. If you try to access blogs.mysql.com, you will be redirected to planetmysql.org.
• Sheeri by Sheeri Kritzer Cabral, MySQL and Oracle DBA for The Pythian Group. Also see her podcast, OurSQL MySQL Database Podcast.
• Roland Bouman’s blog, by Roland Bouman, Training Course Curriculum Developer for MySQL AB and contributor to O’Reilly Databases site.
• The Data Charmer by Giuseppe Maxia, Database Analyst and Designer. Contributor to O’Reilly Databases site (and a whole lot more).
• MikeKruckenbery by Mike Kruckenberg, coauthor of Pro MySQL.
• MySQL-dump.
• MyQSL QA by Jonathan Miller, Senior Lead Quality Assurance Developer.
• MySQL Musings by Mats Kindahl, lead developer at MySQL with replication as main focus.
• Senior MySQL DBA Blog by Farhan Mashraqi.
• MySQL Performance Blog by Peter Zaitsev.

Slides/Videos

One of the more interesting way to view information on many a topic is through having it given in a presentation. The speaker has to reduce topics to bullet points and hopefully do the presentation in an interesting and informative manner. While many of the below presentations were given at conferences and user groups across the planet, the presentations have been generously provides for online access. Below are a few videos and slides to serve as an introduction on MySQL topics.

• MySQL Basics by Chad Godfirnon
• An Overview of the MySQL Engine and its Latest Features by Timour Katchaounov
• 5.1 In Depth by Brian Aker
• A Googly MySQL Cluster Talk by Stewart Smith
• Performance Tuning Best Practices for MySQL by Jay Pipes, co-author of Pro MySQL (Apress, 2005)
• MySQL Community Meetup: MySQL Proxies by Alan Kasindorf
• MySQL Backups by Sheeri Kritzer
• Scalability: YouTube Scalability by Cuong Do Cuong
• MySQL Security by Sheeri Kritzer Cabral
• Testing the Security of Your Site by Sheeri Kritzer Cabral
• Auditing MySQL for Security and Compliance by Mehlam Shakir
• Why Are Databases So Hard To Secure? by Sheeri Kritzer Cabral
• Best Practices for Securing MySQL 5.0 by Jimmy Guerrero, Senior Product Manager, MySQL AB
• 2007 MySQL User Conference And Expo Presentations and Videos

Installing MySQL

Many operating systems come with MySQL already installed. If MySQL is not installed, download it from the MySQL download page. In addition to the sources above, documentation on installation for particular operating systems can be found on the MySQL Documentation page. There are instruction for:

• Installing on Windows
• Installing from RPM Packages on Linux
• Installing on Mac OS X
• Installing on Solaris
• Installing on Netware
• Installing from tar.gz Packages on Other Unix-Like Systems
• Installation Using a Source Distribution

Once installed, there is much more configuration than can be adequately covered in a blog post. The below instructions are meant as a demonstration on what is involved in the installation of MySQL. Please refer to MySQL installation documentation, books, and other information source for full details particular to your setup.

Additional steps may be taken to better secure the database. For example, it may be desirable to chrooting the MySQL server. The instructions below do not include those steps. Please see Artur Maj excellent article for additional information. The decisions to setup such an environment depends greatly on how the database will be used. Defense in depth is a great strategy, but security cannot be implemented without understanding. One has to be careful not to add so many layers of security that implementation becomes impossible. Under some circumstances, chrooting the MySQL server is a valid strategy. That is not the case in all environments and uses.

The option for the zlib compression library will be used in this installation based on future requirements for the database. On most operating systems, zlib is installed under the /usr directory (/usr/lib, /usr/include). Make sure zlib is installed on your system.

If your get the error message “Cannot find libmysqlclient_r under /usr/local/mysql” it is because your client programs are using threads. You need to compile a thread-safe version of the MySQL client library using the –enable-thread-safe-client configure option. This creates a libmysqlclient_r library with which you should link your threaded applications. For additional information, see the MySQL documentation “Section 26.2.16. How to Make a Threaded Client.”

The MYSQL team compiles Mac OS X with the following configuration:

CC=gcc CFLAGS="-O3 -fno-omit-frame-pointer" CXX=gcc \
CXXFLAGS="-O3 -fno-omit-frame-pointer -felide-constructors \
    -fno-exceptions -fno-rtti" \
    ./configure --prefix=/usr/local/mysql \
    --with-extra-charsets=complex --enable-thread-safe-client \
    --enable-local-infile --disable-shared

This is close to what we will be using, with a few changes. The configuration option –enable-local-infile allows “LOAD DATA LOCAL” to be used. See MySQL documentation “5.3.4. Security Issues with LOAD DATA LOCAL” for reasons this may not be desirable. The -fno-omit-frame-pointer Indicates whether the compiler should use the frame pointer for IA32 (Intel Architecture, 32-bit). The option -felide-constructors tells the C++ compiler to use elide constructors when this plausible. Error detection is done through the -fno-rtti -fno-exceptions options. The –with-extra-charsets=complex adds code to all standard programs that enables them to handle latin1 and all multi-byte character sets within the binary. For our purpose, this is not needed. The –disable-shared option disables the use of shared libraries.

The below instructions are meant to show the basic steps in setting up a MySQL database. Additional steps may be required depending on your requirements. The additional sources provided in this post are meant to provide assistance in determining solutions for the requirements of your environment.

 root# cd /usr/local/src
 /usr/local/src root# groupadd mysql
 /usr/local/src root# useradd -g mysql mysql
 /usr/local/src root# wget \
 http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-5.0.51a.tar.gz\
/from/http://mirror.services.wisc.edu/mysql/
 /usr/local/src root# /sbin/md5 mysql-5.0.51a.tar.gz
MD5 (mysql-5.0.51a.tar.gz) = a83dbdbb91267daf73d2297a9c283dd1
 /usr/local/src root# mkdir /usr/local/mysql-5.0.51a
 /usr/local/src root# ln -s /usr/local/mysql-5.0.51a /usr/local/mysql
 /usr/local/src root# tar xzf mysql-5.0.51a.tar.gz
 /usr/local/src root# cd mysql-5.0.51a
 /usr/local/src/mysql-5.0.51a root# CC=gcc \
      CFLAGS="-O3 -fno-omit-frame-pointer" \
      CXX=gcc CXXFLAGS="-O3 -fno-omit-frame-pointer \
       -felide-constructors -fno-exceptions -fno-rtti" \
       ./configure --prefix=/usr/local/mysql \
       --enable-assembler \
       --with-mysqld-ldflags=-all-static \
       --enable-thread-safe-client \
       --with-zlib-dir=/usr
 /usr/local/src/mysql-5.0.51a root# make
 /usr/local/src/mysql-5.0.51a root# make test
 /usr/local/src/mysql-5.0.51a root# make install
 /usr/local/src/mysql-5.0.51a root# scripts/mysql_install_db --user=mysql
 /usr/local/src/mysql-5.0.51a root# cd /usr/local/mysql
 /usr/local/mysql root# chown -R root .
 /usr/local/mysql root# chgrp -R mysql .
 /usr/local/mysql root# chown -R mysql var

At this point, you will want to add the mysql binaries to your PATH, start up the MySQL server, and run the mysql_secure_installation command.

 /usr/local/mysql root# export PATH="/bin:/sbin:/usr/bin:/usr/local/bin:/usr/sbin:/usr/local/mysql/bin"
 /usr/local/mysql root# bin/mysqld_safe --user=mysql &
 /usr/local/mysql root# /usr/local/mysql/bin/mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n] Y
New password: SecretPassword
Re-enter new password: SecretPassword
Password updated successfully!
Reloading privilege tables..
 ... Success!

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] Y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] Y
 ... Success!

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] Y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] Y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

 /usr/local/mysql root# mysql -u root -p

Please do not use SecretPassword as the root account password. That is used only to demonstrate a password is entered. If you ever need to grant full access to a particular database (say snort) to user (say snortadmin) do the following:

 /usr/local/src root# mysql -u root -p
 mysql> create database snort;
 mysql> GRANT ALL PRIVILEGES ON snort.* TO
    'snortadmin'@'localhost' IDENTIFIED BY 'newpasswd' WITH GRANT OPTION;

Now, shutdown the MySQL server, create a MySQL configuration file, and finally bring the server back up. Depending on your sites requirement, you may want to set a few security variables in the MySQL configuration file:

skip-networking
skip-show-database
secure-auth
safe-user-create
skip-automatic_sp_privileges
set-variable=local-infile=0

Generally, you want to build a server configuration file with only the commands required to run the server in the manner you desire. For simplicity, and to keep this post size from becoming too large, we are going to copy an existing MySQL configuration file from support-files to a default location of /etc/my.cnf. That file will be modified. The “skip-networking” line will limit access to the MySQL database to local communication only through the mysql.sock socket. The “skip-show-database” line will limit the SHOW DATABASES statement only to users who have the SHOW DATABASES privilege. The “secure-auth” line dsallow authentication by clients that attempt to use accounts that have old (pre-4.1) passwords. With the “safe-user-create” line a user cannot create new MySQL users by using the GRANT statement unless the user has the INSERT privilege for the mysql.user table or any column in the table. With the line “skip-automatic_sp_privileges” the server does not automatically add and drop the EXECUTE and ALTER ROUTINE privileges to the creator of a stored routine, if the user cannot already execute and alter or drop the routine.

There is an option to run MySQL with the –secure-file-priv=path option limits the effect of the LOAD_FILE() function and the LOAD DATA and SELECT … INTO OUTFILE statements to work only with files in the specified directory. You can also disable the use of LOAD DATA LOCAL INFILE command by adding the line “set-variable=local-infile=0.” MySQL clients and libraries in binary distributions are compiled with the –enable-local-infile option, to be compatible with MySQL 3.23.48 and before. When we configured from source, we did not include the –enable-local-infile option.

First, we should confirm that the MySQL server is running on port 3306. Then, we will modify the MySQL configuration file adding the lines discussed above, restart the MySQL server, and confirm the changes have been made.

 /usr/local/src root# lsof -i TCP:3306
COMMAND   PID   USER   FD   TYPE    DEVICE SIZE/OFF NODE NAME
mysqld  56676 _mysql   10u  IPv4 0x91abe64      0t0  TCP *:mysql (LISTEN)

 /usr/local/src root# /usr/local/mysql/bin/mysqladmin -u root -p shutdown
 /usr/local/src root# cd /usr/local/src/mysql-5.0.51a
 /usr/local/src/mysql-5.0.51a root# cp support-files/my-medium.cnf /etc/my.cnf
 /usr/local/src/mysql-5.0.51a root# chown root:sys /etc/my.cnf
 /usr/local/src/mysql-5.0.51a root# chmod 640 /etc/my.cnf
 /usr/local/src/mysql-5.0.51a root# vi  /etc/my.cnf 

Add the lines above under the [mysqld] section.
Comment out the "port = 3306" lines.

 /usr/local/src/mysql-5.0.51a root# cd /usr/local/mysql
 /usr/local/mysql root# bin/mysqld_safe --user=mysql &
 /usr/local/mysql root# lsof -i TCP:3306
 /usr/local/mysql root# ls -la /tmp/mysql.sock
 /usr/local/mysql root# mysql -u root -p

The final command “lsof -i TCP:3306″ should confirm that the “skip-networking” line in /etc/my.cnf stopped MySQL from listening on port 3306. The command “/tmp/mysql.sock” will confirm the creation of the file for local communication. If you created the user “snortadmin” you can confirm the “skip-show-database” command and that we cannot load a local file into the database.

 root# mysql -u snortadmin -p
 mysql> show databases;
ERROR 1227 (42000): Access denied; you need the SHOW DATABASES privilege for this operation
 mysql> use snort;
 mysql> create table test (fulltxt TEXT);
 mysql> LOAD DATA LOCAL INFILE '/etc/passwd' INTO TABLE test;
ERROR 1148 (42000): The used command is not allowed with this MySQL version

Perl and MySQL: DBD::mysql and DBI

Many operating systems will have DBD::mysql installed. The binary distribution for the operating system and version of the MySQL can be downloaded and installed. The following instruction on the configuration, compilation, and installation of the source is provided as one example of how the Perl modules DBD::mysql and DBI can be installed. When upgrading the MySQL database, make sure to reinstall DBD::mysql.

 root# cd /usr/local/src
 /usr/local/src root# wget http://search.cpan.org/CPAN/\
 authors/id/C/CA/CAPTTOFU/DBD-mysql-4.006.tar.gz
 /usr/local/src root# wget http://search.cpan.org/CPAN/\
 authors/id/T/TI/TIMB/DBI-1.604.tar.gz
 /usr/local/src root# tar xzf DBD-mysql-4.006.tar.gz
 /usr/local/src root# tar xzf DBI-1.604.tar.gz
 /usr/local/src root# cd DBI-1.604
 /usr/local/src/DBI-1.604 root# perl Makefile.PL
 /usr/local/src/DBI-1.604 root# make
 /usr/local/src/DBI-1.604 root# make test
 /usr/local/src/DBI-1.604 root# make install
 /usr/local/src/DBI-1.604 root# cd ../DBD-mysql-4.006
 /usr/local/src/DBD-mysql-4.006 root# perl Makefile.PL
 /usr/local/src/DBD-mysql-4.006 root# make
 /usr/local/src/DBD-mysql-4.006 root# make test
 /usr/local/src/DBD-mysql-4.006 root# make install

Additional Sources

Below are a few sources of information with continously changing content.

• Developer Articles
• O’Reilly ONLamp
• O’Reilly Databases Site
• Database Journal
• Dev Shed
• SitePoint

Conclusions

There are many more tools and much more information available on MySQL. The above sources are meant only as a starting point. As Confucius once wrote, “a journey of a thousand miles begins with a single step.” Or if you prefer Jules Bass, “Put one foot in front of the other and soon you’ll be walking cross the floor.” You do not need to have watched each video, gone through each presentation, read every document, nor be familiar with the content on every blog concerning MySQL in order to begin working with MySQL. One has to start somewhere. The best piece of advice is simply take one step at a time and start now. In the immortal words of Meredith from Grey’s Anatomy, “The early bird catches the worm; a stitch in time saves nine. He who hesitates is lost. We can’t pretend we haven’t been told. We’ve all heard the proverbs, heard the philosophers, heard our grandparents warning us about wasted time, heard the damn poets urging us to ‘seize the day’. Still sometimes we have to see for ourselves. We have to make our own mistakes. We have to learn our own lessons. We have to sweep today’s possibility under tomorrow’s rug until we can’t anymore, until we finally understand for ourselves like Benjamin Franklin meant. That knowing is better than wondering; that waking is better than sleeping. And that even the biggest failure, even the worst most intractable mistake beats the hell out of never trying.”