Activity 3: Installing and Configuring MySQL Database

Objectives:
• Give the students a guide through MySQL’s installation and configuration process

• Install MySQL from a binary source

• Teach the students how to set MySQL administrative password

• Learn how to start and stop MySQL

• Learn MySQL configuration and optimization issues

Concepts

The Source Installation Process

The MySQL developers have gone to great lengths to produce optimized packages and binaries
for a wide array of operating systems and you should use them whenever possible. However, if you are
working with a platform for which no binaries exists, require a particularly exotic configuration or
happen to be a rather controlling individual then you also have the option to install from a source.

The source installation process is indeed somewhat more complicated than installing binaries or
packages. For starters, you should possess at least rudimentary knowledge of how to use build tools like
GNU gcc and make and you should have them installed on your operating system.

Setting the MySQL Administrator Password

When installing the MySQL server, the root (administrator) account password is left blank.
Therefore, you must take care to add a password immediately.

MySQL will let you dig your own grave in the sense that passwords such as 123, abc and your
mother’s name are all perfectly acceptable. Choosing a password that is at least eight characters long
and consists of a combination of numeric and alphabetical characters of varying case.

Failing to setup an administrator password means that anybody with access to the operating
system can shut down the daemon not to mention completely destroy your database server and its
data.

Starting and Stopping MySQL

The MySQL server daemon is controlled via a single program located in the INSTALL-DIR/bin
directory.
 Although you’ll ultimately want the MySQL daemon to automatically start and stop in
conjunction with the operating system, you’ll need to manually execute this process during the
configuration and later application testing , stages.

The script responsible for starting the MySQL daemon is called mysqld_safe, which is located in
the INSTALL-DIR/bin. This script can only be started by a user possessing sufficient execution privileges,
typically either root or a member of the group mysql.

Keep in mind that mysqld_safe will not execute unless yo first change to the INSTALL-DIR
directory. In addition, the trailing ampersand is required, because you’ll want the daemon to run in the
background.

The mysqld_safe script is actually a wrapper around the mysqld server daemon, offering
features that are not available by calling mysqld directly such as run-time logging and automatic restart
in case of error.

Stopping MySQL server

Although MySQL server daemon can be started only by a user possessing the file system
privileges necessary to execute the mysqld_safe script, it can be stopped by a user possessing the
proper privileges as specified within the MySQL privilege database. Keep in mind that this privilege is
typically left solely to the MySQL root user, not to be confused with the operating system root user; just
understand that MySQL users are not the same as operating system users and that MySQL user
attempting to shut down the server must possess adequate privilege for doing so.

Starting and Stopping MySQL Automatically

When the occasion arises that a server needs to be rebooted, unexpectedly shuts down, it is
imperative that all mission-critical services are properly exited and automatically reactivated on a
system boot.

Linux is capable of operating system in several different system states, each of which is defined
by the set of services made available to the user when the state is in control of the system. Each such
runlevels are available, although typically only seven are of interest to the user.

Runleve Description
l

0 Halt

1 Single-user Mode

2 Empty (user-definable)

3 Non-windowed multiuser mode

4 Empty (user-definable)
5 Full multiuser mode (with windowing)

6 Reboot

Whether and in what order services are started or terminated within each runlevel is
determined by examining the first three characters of each symbolic link found in the respective
runlevel folder. If the symbolic link begins with an S, that service will be initiated in that runlevel. If it
begins with a K, it will be terminated. The two-digit integer following this first character determines the
order in which that service will be initiated or terminated. The higher the number, the later its fate will
be addressed.

Configuring and Optimizing MySQL

Unless otherwise specified, MySQL assumes a default set of configuration settings upon each start of the
MySQL server daemon. Although the default settings are probably suitable for users who require
nothing more than a standard deployment, you’ll at least want to be aware of what can be tweaked,
because such changes not only will better adapt your deployment to your specific hosting environment
but could also greatly enhance the performance of your application based on its behavioral
characteristics. For example, some applications might be update-intensive, prompting you to adjust the
resources that MySQL requires for handling write/modifications queries. Other applications might need
to handle large number of connections, prompting you a change to the number of threads allocated to
new connections.

The mysqld_safe wrapper

The mysqld_safe wrapper adds a few extra safety-related logging features and system-integrity
features to the picture when the daemon is started. Given these useful features, myqld_safe is the
preferred way to start the server, although you should keep in mind that it’s only a wrapper and should
not be confused with the server itself.

Literally hundreds of MySQL server configuration options are at your disposal, capable of fine-
tuning practically every conceivable aspect of the daemon’s operation, including MySQL’s memory
usage, logging sensitivity and boundary settings such as maximum number of simultaneous connections,
temporary tables and connection errors among others.

Managing Connection Loads

A well-tuned MySQL server is capable of working with many connections simultaneously. Each
connection must be received and delegated to a new thread by the main MySQL thread, a task that
although trivial, isn’t instantaneous. The back_log parameter determines the number of connections
that are allowed to queue up while this main thread deals with a particularly heavy new connection
load. By default this is set to 50.
 Keep in mind that you can’t just set this to a very high value and assume it will make MySQL run
more efficiently. Both your operating system and Web server may have other maximum settings in place
that could render a particular high value irrelevant.

Setting the Maximum Allowable Simultaneous Connections

The max_connections parameter determines the maximum permitted number of simultaneous

connections to the database. By default this is set to 100. You can check the maximum number of
connections simultaneously opened by your database by reviewing the max_used_connections
parameter, available by executing SHOW STATUS. If you see this number is approaching the century
mark, consider bumping the maximum upward. Keep in mind that as the number of connections
increases, so will memory consumption because MySQL allocates additional memory to every
connection it opens.

Setting MySQL’s Communication Port

By default, MySQL communicates in port 3306; however, you can reconfigure it to listen on any
other port by using the port parameter.

Disabling DNS Resolution

Enabling the skip-name-resolve parameter prevents MySQL form resolving hostnames. This
means that all Host column values in the grant tables consist either of an IP address or localhost. If you
plan to use solely IP addresses or localhost, enable this parameter.

Limiting Connections

Enabling the skip-networking parameter prevents MySQL from listening for TCP/IP connections, a wise
idea if your MySQL installation resides on the same server from which you’ll be initiating connections.

Setting the MySQL Daemon User

The MySQL daemon should run as a non-root user, minimizing the damage if an attacker were
to ever successfully enter the server via a MySQL security hole. Although the common practice is to run
the server as a user mysql, you can run it as any existing user provided that the user is the owner of the
data directories.

The my.cnf File

Configuration changes can be made on the command line when starting the MySQL daemon via
its wrapper , mysqld_safe. However, there exists a much more convenient method for tweaking the
startup parameters as well as the behaviors opf may MySQL clients. You can maintain these tweaks
within MySQL configuration file, my.cnf.
 At startup, MySQL looks in several directories for the my.cnf file with each directory determining
the scope of the parameters declared within. The location and relative scope of each directory is
highlighted below:

• /etc/my.cnf
Global configuration file.
All MySQL server daemons located in the server refer first to this file.

• DATADIR/my.cnf
Server-specific configuration.
This file is placed in the directory by the server installation. A somewhat odd, yet crucial
characteristic of this configuration file is that it references only the data directory specified at
the configuration time even if a new data directory is specified at run time.

Procedures:

The Source Installation Process (complete all task as root)

1. Create the necessary group and owner.

root@AMD64:/home/sysadmin# groupadd mysql

root@AMD64:/home/sysadmin# useradd -g mysql mysql

2. Download the necessary source installation files:

FTP server: ________________________________

File: ______________________________________
Username: ________________________________
Password: _________________________________
Location to download to your local machine: /usr/src

3. Download additional software components for MySQL.

root@AMD64:/home/sysadmin# apt-get install libncurses5-dev

4. Decompress the software.

root@AMD64:/home/sysadmin# cd /usr/src
root@AMD64:/home/sysadmin# tar -xvzf mysql-VERSION.tar.gz
 root@AMD64:/home/sysadmin# cd mysql-VERSION

5. Configure, make and install MySQL. A C++ compiler and make program are required. Using
recent versions of GNU gcc and make programs is strongly recommended. Keep in mind that –
OTHER-CONFIGURATION_FLAGS is a placeholder for any configuration settings that determine
several important characteristics of the MySQL server such as installation directory location. It is
left to you to decide which flags best suit your special needs.

root@AMD64:/home/sysadmin# ./configure --prefix=/usr/local/mysql

root@AMD64:/home/sysadmin# make
root@AMD64:/home/sysadmin# make install

6. Copy the MySQL configuration file (my.cnf) into its typical location and set its ownership.

root@AMD64:/home/sysadmin# cp support-files/my-medium.cnf /etc/my.cnf

root@AMD64:/home/sysadmin# chown -R mysql .
root@AMD64:/home/sysadmin# chgrp -R mysql .

7. Install the MySQL database. Mysql_install_db is a shell script that logs in to the MySQL database
server, creates all of the necessary tables and populates them with initial values.

root@AMD64:/home/sysadmin# scripts/mysql_install_db - -user=mysql

8. Update the installation permissions:

root@AMD64:/home/sysadmin# chown -R root .

9. Logout root.

root@AMD64:/home/sysadmin# exit

Starting MySQL
1. The following command is used to start MySQL
 sysadmin@AMD64:~$ cd INSTALL-DIR
sysadmin@AMD64:~$ ./bin/mysqld_safe - -user=mysql &

Setting MySQL Administrator Password

1. You can set the administrator’s password using mysqladmin utility.

sysadmin@AMD64:~$ cd INSTALL-DIR/bin
sysadmin@AMD64:~$ mysqladmin –u root password ‘new password’

Stopping MySQL
1. The process of stopping MySQL server is as follows:

sysadmin@AMD64:~$ cd INSTALL-DIR/bin
sysadmin@AMD64:~$ mysqladmin -u root -p shutdown
Enter password: ********