Modifying the Kernel

Introduction

The Linux kernel is the central program that not only governs how programs interact with one another, but also provides the guidelines on how they should use the computer's core infrastructure, such as memory, disks, and other input/output (I/O) devices for the user's benefit.

Linux drivers, the programs that manage each I/O device, are the staff that keeps all the government departments running. Continuing with the analogy, the more departments you make the kernel manage, the slower Linux becomes. Large kernels also reduce the amount of memory left over for user applications. These may then be forced to juggle their memory needs between RAM and the much slower swap partitions of disk drives, causing the whole system to become sluggish.

The Fedora installation CDs have a variety of kernel RPMs, and the installation process autodetects the one best suited to your needs. For this reason, the Fedora Linux kernel installed on your system is probably sufficient. The installation process chooses one of several prebuilt kernel types depending on the type of CPU and configuration you intend to use (Table 33-1).

Table 33-1: Kernels Found On Fedora Installation CDs

Processor Type	Configuration
i586	Single processor
i586	Multiprocessor (SMP)
i686	Single processor
i686	Multiprocessor (SMP)

The Pros And Cons Of Kernel Upgrades

Despite this best fit installation, you may want to rebuild the kernel at times. For example, there is no installation RPM for multiprocessor systems with large amounts of memory. You may also want to experiment in making a high-speed Linux router without support for SCSI, USB, Bluetooth, and sound but with support for a few NIC drivers, an IDE hard drive, and a basic VGA console. This would require a kernel rebuild.

Rebuilding the kernel in a small business environment is usually unnecessary. If your system starts to slow down and you can't afford to replace hardware or are unable to add more RAM, however, you may want to tune the kernel by making it support only necessary functions or updating built-in parameters to make it perform better. Sometimes new features within the new kernel are highly desirable; for example, the version 2.6 kernel has much more efficient data handling capabilities than the older version 2.4, providing new life for old hardware.

Kernel tuning on a production server shouldn't be taken lightly, because the wrong parameters could cause your system to fail to boot, software to malfunction, or hardware peripherals to become unavailable. Always practice on a test system and keep a backup copy of your old kernel. Whenever possible, hire a consultant with kernel experience to help, and use this chapter and other references as a guide to prepare you for what to expect.

This chapter provides only an overview of the steps to take. It won't make you an expert, but it will expose you to the general process and provide greater confidence when you need to research the task with a specialized guide.

The Kernel Sources Package

You will need to install the kernel source code on your system prior to modifying your kernel. As of Fedora Core 3, the kernel sources come as a source RPM package that matches the version of the kernel you are running. In Fedora Core 2 and earlier, the Kernel sources came as a generic RPM package called kernel-source.

The newer method is more complicated as it requires a number of post installation steps. Though the process is well documented in the release notes section of the Fedora website (http://fedora.redhat.com/docs/release-notes/) there are some clarifications that are needed. These are explained in the following section.

Installing Kernel Sources

The installation process for the kernel sources is long, but not very complicated. Here is how it's done:

1. Determine the version of your kernel with the uname command. In this case it is version 2.6.14-1.1644.

[root@foo tmp]# uname -r
2.6.14-1.1644_FC4smp
[root@foo tmp]#

2. Visit your favorite Fedora operating system download mirror and get the corresponding source RPM package. If you are running the original version of the kernel that came with your installation discs, then the sources will be located in the /core//i386/os/SRPMS/ directory. If the kernel has been updated using yum or some other method, the sources will be located in the /core/updates//SRPMS/ directory. In this example the sources of an updated Core 4 kernel is downloaded from the /core/updates/4/SRPMS/ directory of http://download.fedora.redhat.com using wget.

[root@bigboy tmp]# wget
http://download.fedora.redhat.com/pub/fedora/linux/core/updates/4/SRPMS/kernel-2.6.14-1.1644_FC4.src.rpm
--15:32:22--
http://download.fedora.redhat.com/pub/fedora/linux/core/updates/4/SRPMS/kernel-2.6.14-1.1644_FC4.src.rpm
          => `kernel-2.6.14-1.1644_FC4.src.rpm'
Resolving download.fedora.redhat.com... 66.187.224.20,
209.132.176.20, 209.132.176.220, ...
Connecting to
download.fedora.redhat.com|66.187.224.20|:80...
connected.
HTTP request sent, awaiting response... 200 OK
Length: 40,454,218 (39M) [application/x-rpm]

100%[=========================>] 40,454,218   862.89K/s    ETA 00:00

15:33:10 (842.22 KB/s) -
`kernel-2.6.14-1.1644_FC4.src.rpm' saved
[40454218/40454218]

[root@bigboy tmp]#

3. Install the contents of the RPM file into the /usr/src/redhat/SOURCES and /usr/src/redhat/SPECS directories using the rpm command.

[root@bigboy tmp]# rpm -Uvh kernel-2.6.14-1.1644_FC4.src.rpm
  1:kernel                 ########################################### [100%]
[root@bigboy tmp]#

4. The kernel source directory tree will have to be created next. Enter the /usr/src/redhat/SPECS directory and create the tree using the rpmbuild command with the -bp option.

[root@bigboy tmp]# cd /usr/src/redhat/SPECS
[root@bigboy SPECS]# ls
kernel-2.6.spec
[root@bigboy SPECS]# rpmbuild -bp --target $(arch) kernel-2.6.spec
Building target platforms: i686
Building for target i686
Executing(%prep): /bin/sh -e /var/tmp/rpm-tmp.44004
+ umask 022
+ cd /usr/src/redhat/BUILD
...
...
...
removed `./init/Kconfig.orig'
removed `./init/main.c.orig'
+ find . -name '*~' -exec rm -fv '{}' ';'
+ exit 0
[root@bigboy SPECS]#

5. The tree has now been created in the /usr/src/redhat/BUILD/kernel- directory. You can link, move or copy this directory to become /usr/src/linux depending on your needs and the likelihood of you compiling multiple kernel versions in future. In this case, the tree is moved and then linked.

[root@bigboy SPECS]# cd /usr/src/redhat/BUILD/kernel-2.6.14/
[root@bigboy kernel-2.6.14]# ls
linux-2.6.14  vanilla
[root@bigboy kernel-2.6.14]# mv linux-2.6.14 /usr/src/
[root@bigboy kernel-2.6.14]# cd /usr/src
[root@bigboy src]# ln -s ./linux-2.6.14 linux
[root@bigboy src]# ls
kernels  linux  linux-2.6.14  redhat
[root@bigboy src]#

6. The configuration files for specific kernels shipped in Fedora Core will be located in the configs/ directory. In this case the uname -a command is used to determine the systems CPU type (686) and the kernel type (SMP, Symmetrical Multi Processor), and the relevant configuration file is then copied to become the new /usr/src/linux/.config file to be used during the kernel compilation.

[root@bigboy src]# cd /usr/src/linux
[root@bigboy linux]# uname -a
Linux bigboy.my-web-site.org 2.6.14-1.1644_FC4smp #1 SMP
Sun Nov 27 03:39:31 EST 2005 i686 i686 i386 GNU/Linux
[root@bigboy linux]# cp configs/kernel-2.6.14-i686-smp.config .config
cp: overwrite `.config'? y
[root@bigboy linux]#

7. You can also automatically copy the correct file from the /usr/src/linux/configs/ directory to /usr/src/linux/.config using the make oldconfig command.

[root@bigboy linux]# cd /usr/src/linux
[root@bigboy linux]# make oldconfig

You should now be ready to compile a customized kernel at your leisure, but first I'll discuss Kernel modules.

Kernel Modules

Over the years the Linux kernel has evolved, in the past device drivers were included as part of this core program, whereas now they are loaded on demand as modules.

Reasons For Kernel Modules

There are a number of advantages to this new architecture:

• Updates to a driver, such as a USB controller module, don't require a complete recompilation of the kernel; just the module itself needs recompiling. This reduces the likelihood of errors and ensures that the good, working base kernel remains unchanged.
• An error in a device driver is less likely to cause a fault that prevents your system from booting. A faulty device driver can be prevented from loading at boot time by commenting out its entry in the /etc/modprobe.conf file, or by using the rmmod command after boot time. In the past, the kernel would have to be recompiled.
• Updates to a driver don't require a reboot either, just the unloading of the old and reloading of the new module.
• You can add new devices to your system without requiring a new kernel, only the new module driver is needed. This adds a great deal of flexibility without a lot of systems administrator overhead.

There are some drivers that will always need to be compiled into the kernel to make sure your system boots correctly. For example, routines for basic system functions used in reading and writing files, are an indispensable integrated part of any kernel.

Loadable kernel modules now include device drivers to manage various types of filesystems, network cards, and terminal devices to name a few. As they work so closely with the kernel, the modules need to be compiled specifically for the kernel they are intended to support. The kernel always looks for modules for its version number in the /lib/modules/ directory and permanently loads them into RAM memory for faster access. Some critical modules are loaded automatically, others need to be specified in the /etc/modprobe.conf file.

The kernel recompilation process provides you with the option of compiling only the loadable modules. I won't specifically cover this, but simultaneous recompilation of all modules will be covered as part of the overall recompilation of your kernel.

How Kernel Modules Load When Booting

One question that must come to mind is "How does the kernel boot if the disk controller modules reside on a filesystem that isn't mounted yet?"

the GRUB boot loader resides on its own dedicated partition and uses the /boot/grub/grub.conf file to determine the valid kernels and their locations. The grub.conf file not only defines the available kernels, but also the location of the root partition and an associated ramdisk image that is automatically loaded into memory and that contains just enough modules to get the root filesystem mounted.

Note: In Fedora Linux, the /boot/grub/grub.conf file can also be referenced via the symbolic link file named /etc/grub.conf.

Modules And The grub.conf File

In this example of the /boot/grub/grub.conf file, the kernel in the /boot directory is named vmlinuz-2.6.8-1.521, its RAM disk image file is named initrd-2.6.8-1.521.img, and the root partition is (hd0,0).

#
# File: /boot/grub/grub.conf
#
default=0
timeout=10
splashimage=(hd0,0)/grub/splash.xpm.gz
title Fedora Core (2.6.8-1.521)
       root (hd0,0)
       kernel /vmlinuz-2.6.8-1.521 ro root=LABEL=/
       initrd /initrd-2.6.8-1.521.img

The .img file is created as part of the kernel compilation process, but can also be created on demand with the mkinitrd command.

The (hd0,0).disk definition may seem strange, but there is a file that maps the GRUB device nomenclature to that expected by Linux in the /boot/grub/device.map file.

#
# File: /boot/grub/device.map
#
(fd0)     /dev/fd0
(hd0)     /dev/hda

During the next phase of the boot process, the loaded kernel executes the init program located on the RAM disk, which mounts the root filesystem and loads the remaining modules defined in the /etc/modprobe.conf file before continuing with the rest of the startup process.

Loading Kernel Modules On Demand

It is possible to load add-on modules located under the /lib/modules/ directory with the modprobe command. For example, the iptables firewall application installs kernel modules that it uses to execute NAT and pass FTP traffic. In this example: these modules are loaded with the modprobe command with the aid of the /etc/rc.local script.

#
# File: /etc/rc.local
#
# Load iptables FTP module when required
modprobe ip_conntrack_ftp

# Load iptables NAT module when required
modprobe iptable_nat

Kernel module drivers that are referenced by the operating system by their device aliases are placed in the /etc/modprobe.conf file and are loaded automatically at boot time. In the example, you can see that devices eth1 and eth0 use the natsemi and orinoco_pci drivers respectively.

#
# /etc/modprobe.conf
#
alias eth1 natsemi
alias eth0 orinoco_pci

Linux has a number of commands to help you with modules. The lsmod command lists all the ones loaded. In the example, you can see that iptables, NFS, and the Orinoco drivers are all kernel modules. You can use the modprobe command to load and unload modules or use the insmod and rmmod commands. See the man pages for details.

[root@bigboy tmp]# lsmod
Module                  Size  Used by
...
...
iptable_filter          2048  0
ip_tables              13440  1 iptable_filter
...
...
exportfs                4224  1 nfsd
nfs                   142912  0
lockd                  47944  3 nfsd,nfs
autofs4                10624  1
sunrpc                101064  20 nfsd,nfs,lockd
...
...
natsemi                18016  0
orinoco_pci             4876  0
orinoco                31500  1 orinoco_pci
hermes                  6528  2 orinoco_pci,orinoco
...
...
[root@bigboy tmp]#

Finally, when in doubt about a device driver, try using the lspci command to take a look at the devices that use your PCI expansion bus. Here you can see that the natsemi module listed in the lsmod command has a high probability of belonging to the 01:08.0 Ethernet controller: device made by National Semiconductor.

[root@bigboy tmp]# lspci
...
...
01:07.0 Network controller: Intersil Corporation Prism 2.5 Wavelan chipset (rev 01)
01:08.0 Ethernet controller: National Semiconductor Corporation DP83815 (MacPhyter) Ethernet Controller
01:0c.0 Ethernet controller: 3Com Corporation 3c905C-TX/TX-M [Tornado] (rev 78)
[root@bigboy tmp]#

Creating A Custom Kernel

The installation of the kernel sources creates a file called README in the /usr/src/linux directory that briefly outlines the steps needed to create a new kernel. Take a look at a more detailed explanation of the required steps.

Make Sure Your Source Files Are In Order

Cleaning up the various source files is the first step. This isn't so important for a first time rebuild, but it is vital for subsequent attempts. You use the make mrproper command to do this; it must be executed in the Linux kernel version's subdirectory located under /usr/src. In this case, the subdirectory's name is /usr/src/linux-2.6.5-1.358.

[root@bigboy tmp]# cd /usr/src/linux
[root@bigboy linux]# make mrproper
...
...
...
[root@bigboy linux]#

The ".config" File

You next need to run scripts to create a kernel configuration file called /usr/src/linux/.config. This file lists all the kernel options you wish to use.

Backup Your Configuration

The .config file won't exist if you've never created a custom kernel on your system before, but fortunately, RedHat stores a number of default .config files in the /usr/src/linux/configs directory. You can automatically copy the .config file that matches your installed kernel by running the make oldconfig command in the /usr/src/linux directory.

[root@bigboy tmp]# cd /usr/src/linux
[root@bigboy linux]# ls .config
ls: .config: No such file or directory
[root@bigboy linux]# make oldconfig
...
...
...
[root@bigboy linux]#

If you've created a custom kernel before, the .config file that the previous custom kernel build used will already exist. Copy it to a safe location before proceeding.

Customizing The ".config" File

Table 33-2 lists three commands that you can run in the /usr/src/linux directory to update the .config file.

Table 33-2 Scripts For Modifying The .config File

Command	Description
make config	Text based utility that prompts you line by line. This method can become laborious.
make menuconfig	Text menu based utility.
make gconfig	X-Windows based utility.

Each command prompts you in different ways for each kernel option, each of which generally provides you with the three choices shown in Table 33-3. A brief description of each kernel option is given in Table 33-4.

Table 33-3 Kernel Option Choices

Kernel Option Choice	Description
M	The kernel will load the drivers for this option on an as needed basis. Only the code required to load the driver on demand will be included in the kernel.
Y	Include all the code for the drivers needed for this option into the kernel itself. This will generally make the kernel larger and slower but will make it more self sufficient. The "Y" option is frequently used in cases in which a stripped down kernel is one of the only programs Linux will run, such as purpose built home firewall appliances you can buy in a store. There is a limit to the overall size of a kernel. It will fail to compile if you select parameters that will make it too big.
N	Don't make the kernel support this option at all.

Table 33-4 Kernel Configuration Options

Option	Description
Code maturity level options	Determines whether Linux prompts you for certain types of development code or drivers.
Loadable module support	Support for loadable modules versus a monolithic kernel. Most of the remaining kernel options use loadable modules by default. It is best to leave this alone in most cases.
Processor type and features	SMP, Large memory, BIOS and CPU type settings.
General setup	Support for power management, networking, and systems buses such as PCI, PCMCIA, EISA, ISA
Memory technology devices	Linux subsystem for memory devices, especially Flash devices
Parallel port support	Self explanatory
Plug and Play configuration	Support of the automatic new hardware detection method called plug and play
Block devices	Support for a number of parallel-port-based and ATAPI type devices. Support for your loopback interface and RAM disks can be found here too.
Multidevice support (RAID, LVM)	Support for RAID, 0, 1, and 5, as well as LVM.
Cryptography support (CryptoAPI)	Support for various types of encryption
Networking options	TCP/IP, DECnet, Appletalk, IPX, ATM/LANE
Telephony support	Support for voice to data I/O cards
ATA/IDE/MFM/RLL support	Support for a variety of disk controller chipsets
SCSI support	Support for a variety of disk controller chipsets. Also sets limits on the maximum number of supported SCSI disks and CDROMs.
Fusion MPT support	High speed SCSI chipset support.
I2O device support	Support for specialized Intelligent I/O cards
Network device support	Support for Ethernet, Fibre Channel, FDDI, SLIP, PPP, ARCnet, Token Ring, ATM, PCMCIA networking, and specialized WAN cards.
Amateur Radio support	Support for packet radio
IrDA subsystem support	Infrared wireless network support
ISDN subsystem	Support for ISDN
Old CD-ROM drivers (not SCSI, not IDE)	Support for non-SCSI, non-IDE, non ATAPI CDROMs
Input core support	Keyboard, mouse, and joystick support in addition to the default VGA resolution.
Character devices	Support for virtual terminals and various serial cards for modems, joysticks and basic parallel port printing.
Multimedia devices	Streaming video and radio I/O card support
Crypto Hardware support	Web-based SSL hardware accelerator card support
Console drivers	Support for various console video cards
Filesystems	Support for all the various filesystems and strangely, the native languages supported by Linux.
Sound	Support for a variety of sound cards
USB support	Support for a variety of USB devices
Additional device driver support	Miscellaneous driver support
Bluetooth support	Support for a variety of Bluetooth devices
Kernel hacking	Support for detailed error messages for persons writing device drivers

Configure Dependencies

As I mentioned before, the .config file you just created lists the options you'll need in your kernel. In version 2.4 of the kernel and older, the make dep command was needed at this step to prepare the needed source files for compiling. This step has been eliminated as of version 2.6 of the kernel.

Edit The Makefile To Give The Kernel A Unique Name

Edit the file Makefile and change the line "EXTRAVERSION =" to create a unique suffix at the end of the default name of the kernel.

For example, if your current kernel version is 2.6.5-1.358, and your EXTRAVERSION is set to -6-new, your new additional kernel will have the name vmlinuz-2.6.5-6-new.

Remember to change this for each new version of the kernel you create.

Compile A New Kernel

You can now use the make command to create a compressed version of your new kernel and its companion .img RAM disk file. This could take several hours on a 386 or 486 system. It will take about 20 minutes on a 400MHz Celeron.

[root@bigboy linux-2.6.5-1.358]# make
...
...
...
[root@bigboy linux-2.6.5-1.358]#

Note: In older versions of Fedora the command to do this would have been make bzImage.

Build The Kernel's Modules

You can now use the make modules_install command to copy all the modules created in the previous step to the conventional module locations.

[root@bigboy linux]# make modules_install
...
...
...
[root@bigboy linux]#

Note: In versions of Fedora before Core 3, this was a two step process. The make modules command would compile the modules, but locate them within the Linux kernel source directory tree under the directory /usr/src/. The make modules_install command would then relocates them to where they should finally reside under the /lib/modules/ directory.

Copy The New Kernel To The /boot Partition

The kernel and the .img you just created needs to be copied to the /boot partition where all your systems active kernel files normally reside. This is done with the make install command.

This partition has a default size of 100MB, which is enough to hold a number of kernels. You may have to delete some older kernels to create enough space.

[root@bigboy linux]# make install
...
...
...
[root@bigboy linux]#

Here you can see that the new kernel vmlinuz-2.6.5-1.358-new is installed in the /boot directory.

[root@bigboy linux]# ls -l /boot/vmlinuz*
lrwxrwxrwx 1 root root       22 Nov 28 01:20 /boot/vmlinuz -> vmlinuz-2.6.5-1.358-new
-rw-r--r-- 1 root root  1122363 Feb 27  2003 /boot/vmlinuz-2.6.5-1.358
-rw-r--r-- 1 root root  1122291 Nov 28 01:20 /boot/vmlinuz-2.6.5-1.358-new
[root@bigboy linux]#

Updating GRUB

You should now update your /etc/grub.conf file to include an option to boot the new kernel. The make install command does this for you automatically.

In this example, default is set to 1, which means the system boots the second kernel entry, which happens to be that of the original kernel 2.6.5-1.358. You can set this value to 0, which makes it boot your newly compiled kernel (the first entry).

default=1
timeout=10
splashimage=(hd0,0)/grub/splash.xpm.gz
title Red Hat Linux (2.6.5-1.358-new)
       root (hd0,0)
       kernel /vmlinuz-2.6.5-1.358-new ro root=LABEL=/
       initrd /initrd-2.6.5-1.358-new.img
title Red Hat Linux (2.6.5-1.358)
       root (hd0,0)
       kernel /vmlinuz-2.6.5-1.358 ro root=LABEL=/
       initrd /initrd-2.6.5-1.358.img

Kernel Crash Recovery

Sometimes the new default kernel will fail to boot or work correctly with the new kernel. A simple way of recovering from this is to reboot your system, selecting the old version of the kernel from the Fedora splash screen. Once the system has booted with this stable version, edit the grub.conf file and set the default parameter to point to the older version instead. If this fails, you may want to boot from a CD with the original kernel. You can then try to either reinstall a good kernel RPM or rebuild the failed one over again after fixing the configuration problem that caused the trouble in the first place.

How To Create A Boot CD

The kernel in Fedora Core 2 and higher is too big to fit on a floppy disk, so you'll have to create a boot CD instead. Here are the steps.

1. Each installed kernel has a dedicated subdirectory for its modules in the /lib/modules directory. Get a listing of this directory. Here there are two installed kernels; versions 2.6.5-1.358custom and 2.6.8-1.521.

[root@bigboy tmp]# ls /lib/modules/
2.6.5-1.358custom  2.6.8-1.521
[root@bigboy tmp]#

2. Select the desired kernel and use the mkbootdisk command to create a CD ISO image named /tmp/boot.iso of one of the kernels, in this case 2.6.8-1.521:

[root@bigboy tmp]# mkbootdisk --iso --device /tmp/boot.iso \
                    2.6.8-1.521

3. Burn a CD using the image. This creates a boot CD with the specified kernel, named vmlinuz, and a scaled-down version of the grub.conf configuration file named isolinux.cfg, both located in the isolinux subdirectory of the CD. This example mounts the newly created CD-ROM and takes a look at the isolinux.cfg file to confirm that everything is okay.

[root@bigboy tmp]# mount /mnt/cdrom
[root@bigboy tmp]# ls /mnt/cdrom/isolinux/
boot.cat  boot.msg  initrd.img  isolinux.bin  isolinux.cfg   TRANS.TBL  vmlinuz
[root@bigboy tmp]# cat /mnt/cdrom/isolinux/isolinux.cfg
default linux
prompt 1
display boot.msg
timeout 100
label linux
       kernel vmlinuz
       append initrd=initrd.img ro  root=/dev/hda2
[root@bigboy tmp]#

When you reboot your system with the CD, the boot process automatically attempts to access your files in the /root partition and boot normally. The only difference being that the kernel used is on the CD.

Updating The Kernel Using RPMs

It is also possible to install a new standardized kernel from an RPM file. As you can see, it is much simpler than creating a customized one.

To create an additional kernel using RPMs, use the command

[root@bigboy tmp]# rpm -ivh kernel-file.rpm

To replace an existing kernel using RPMs, you need only one line

[root@bigboy tmp]# rpm -Uvh kernel-file.rpm

Conclusion

Building a customized Linux kernel is probably something that most systems administrators won't do themselves. The risk of having a kernel that may fail in some unpredictable way is higher when you modify it, and, therefore, many system administrators hire experts to do the work for them. After reading this chapter, at least you will have an idea of what is going on when the expert arrives, which can help considerably when things don't go according to plan.

MySQL Configuration

Introduction

Most home/SOHO administrators don't do any database programming, but they sometimes need to install applications that require a MySQL database. This chapter explains the basic steps of configuring MySQL for use with a MySQL-based application in which the application runs on the same server as the database.

Preparing MySQL For Applications

In most cases the developers of database applications expect the systems administrator to be able to independently prepare a database for their applications to use. The steps to do this include:

1. Install and start MySQL.
2. Create a MySQL "root" user.
3. Create a regular MySQL user that the application will use to access the database.
4. Create your application's database.
5. Create your database's data tables.
6. Perform some basic tests of your database structure.

The rest of the chapter is based on a scenario in which a Linux-based application named sales-test needs to be installed. After reading the sales-test manuals, you realize that you have to create a MySQL database, data tables, and a database user before you can start the application. Fortunately sales-test comes with a script to create the tables, but you have to do the rest yourself. Finally, as part of the planning for the installation, you decided to name the database salesdata and let the application use the MySQL user mysqluser to access it.

I'll cover all these common tasks in detail in the remaining sections.

Installing MySQL

In most cases you'll probably want to install the MySQL server and MySQL client RPMs. The client RPM gives you the ability to test the server connection and can be used by any MySQL application to communicate with the server, even if the server software is running on the same Linux box.

You need to make sure that the mysql-server and mysql software RPMs is installed. When searching for the RPMs, remember that the filename usually starts with the software package name followed by a version number, as in mysql-server-3.23.58-4.i386.rpm.

There are a number of supporting RPMs that may be needed, so the yum utility may be the best RPM installation method to use. (For more on downloading and installing RPMs

Starting MySQL

You have to start the MySQL process before you can create your databases. To configure MySQL to start at boot time, use the chkconfig command:

[root@bigboy tmp]# chkconfig mysqld on

You can start, stop, and restart MySQL after boot time using the service commands.

[root@bigboy tmp]# service mysqld start
[root@bigboy tmp]# service mysqld stop
[root@bigboy tmp]# service mysqld restart

Remember to restart the mysqld process every time you make a change to the configuration file for the changes to take effect on the running process.

You can test whether the mysqld process is running with

[root@bigboy tmp]# pgrep mysqld

You should get a response of plain old process ID numbers.

The /etc/my.cnf File

The /etc/my.cnf file is the main MySQL configuration file. It sets the default MySQL database location and other parameters. The typical home/SOHO user won't need to edit this file at all.

The Location of MySQL Databases

According to the /etc/my.cnf file, MySQL databases are usually located in a subdirectory of the /var/lib/mysql/ directory. If you create a database named test, then the database files will be located in the directory /var/lib/mysql/test.

Creating a MySQL "root" Account

MySQL stores all its username and password data in a special database named mysql. You can add users to this database and specify the databases to which they will have access with the grant command. The MySQL root or superuser account, which is used to create and delete databases, is the exception. You need to use the mysqladmin command to set your root password. Only two steps are necessary for a brand new MySQL installation.

1. Make sure MySQL is started.
2. Use the mysqladmin command to set the MySQL root password. The syntax is as follows:

[root@tmp bigboy]# mysqladmin -u root password new-password

If you want to change your password later, you will probably have to do a root password recovery.

Accessing The MySQL Command Line

MySQL has its own command line interpreter (CLI). You need to know how to access it to do very basic administration.

You can access the MySQL CLI using the mysql command followed by the -u option for the username and -p, which tells MySQL to prompt for a password. Here user root gains access:

[root@bigboy tmp]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14 to server version: 3.23.58

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Note: Almost all MySQL CLI commands need to end with a semi-colon. Even the exit command used to get back to the Linux prompt needs one too!

Creating and Deleting MySQL Databases

Many Linux applications that use MySQL databases require you to create the database beforehand using the name of your choice. The procedure is relatively simple: Enter the MySQL CLI, and use the create database command:

mysql> create database salesdata;
Query OK, 1 row affected (0.00 sec)

mysql>

If you make a mistake during the installation process and need to delete the database, use the drop database command. The example deletes the newly created database named salesdata.

mysql> drop database salesdata;
Query OK, 0 rows affected (0.00 sec)

mysql>

Note: Sometimes a dropped database may still appear listed when you use the show databases command explained further below. This may happen even if your root user has been granted full privileges to the database, and it is usually caused by the presence of residual database files in your database directory. In such a case you may have to physically delete the database sub-directory in /var/lib/mysql from the Linux command line. Make sure you stop MySQL before you do this.

[root@bigboy tmp]# service mysqld stop

Granting Privileges to Users

On many occasions you will not only have to create a database, but also have to create a MySQL username and password with privileges to access the database. It is not a good idea to use the root account to do this because of its universal privileges.

MySQL stores all its username and password data in a special database named mysql. You can add users to this database and specify the databases to which they will have access with the grant command, which has the syntax.

sql> grant all privileges on database.* to username@"servername" identified by 'password';

So you can create a user named mysqluser with a password of pinksl1p to have full access to the database named salesdata on the local server (localhost) with the grant command. If the database application's client resides on another server, then you'll want to replace the localhost address with the actual IP address of that client.

sql> grant all privileges on salesdata.* to mysqluser@"localhost" identified by 'pinksl1p';

The next step is to write the privilege changes to the mysql.sql database using the flush privileges command.

sql> flush privileges;

Running MySQL Scripts To Create Data Tables

Another common feature of prepackaged applications written in MySQL is that they may require you to not only create the database, but also to create the tables of data inside them as part of the setup procedure. Fortunately, many of these applications come with scripts you can use to create the data tables automatically.

Usually you have to run the script by logging into MySQL as the MySQL root user and automatically importing all the script file's commands with a <>

The example runs a script named create_mysql.script whose commands are applied to the newly created database named salesdata. MySQL prompts for the MySQL root password before completing the transaction. (You have to create the database first, before you can run this command successfully.)

[root@bigboy tmp]# mysql -u root -p salesdata <>

Viewing Your New MySQL Databases

A number of commands can provide information about your newly created database. Here are some examples:

• Login As The Database User: It is best to do all your database testing as the MySQL user you want the application to eventually use. This will make your testing mimic the actions of the application and results in better testing in a more production-like environment than using the root account.

[root@bigboy tmp]# mysql -u mysqluser -p salesdata

• List all your MySQL databases: The show databases command gives you a list of all your available MySQL databases. In the example, you can see that the salesdata database has been successfully created:

mysql> show databases;
+-----------+
| Database  |
+-----------+
| salesdata |
+-----------+
1 row in set (0.00 sec)

mysql>

Listing The Data Tables In Your MySQL Database

The show tables command gives you a list of all the tables in your MySQL database, but you have to use the use command first to tell MySQL to which database it should apply the show tables command.

The example uses the salesdata database; notice that it has a table named test.

mysql> use salesdata;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------+
| Tables_in_salesdata |
+---------------------+
| test                |
+---------------------+
1 row in set (0.00 sec)

mysql>

Viewing Your MySQL Database's Table Structure

The describe command gives you a list of all the data fields used in your database table. In the example, you can see that the table named test in the salesdata database keeps track of four fields: name, description, num, and date_modified.

mysql> describe test;
+---------------+--------------+------+-----+------------+----------------+
| Field         | Type         | Null | Key | Default    | Extra          |
+---------------+--------------+------+-----+------------+----------------+
| num           | int(11)      |      | PRI | NULL       | auto_increment |
| date_modified | date         |      | MUL | 0000-00-00 |                |
| name          | varchar(50)  |      | MUL |            |                |
| description   | varchar(75)  | YES  |     | NULL       |                |
+---------------+--------------+------+-----+------------+----------------+
6 rows in set (0.00 sec)

mysql>

Viewing The Contents Of A Table

You can view all the data contained in the table named test by using the select command. In this example you want to see all the data contained in the very first row in the table.

mysql> select * from test limit 1;

With a brand new database this will give a blank listing, but once the application starts and you enter data, you may want to run this command again as a rudimentary database sanity check.

Configuring Your Application

After creating and testing the database, you need to inform your application of the database name, the IP address of the database client server, and the username and password of the application's special MySQL user that will be accessing the data.

Frequently this registration process is done by the editing of a special application-specific configuration file either via a Web GUI or from the command line. Read your application's installation guide for details.

You should always remember that MySQL is just a database that your application will use to store information. The application may be written in a variety of languages with Perl and PHP being the most popular. The base PHP and Perl RPMs are installed with Fedora Linux by default, but the packages used by these languages to talk to MySQL are not. You should also ensure that you install the RPMs listed in Table 34.1 on your MySQL clients to ensure compatibility. Use the yum utility if you are uncertain of the prerequisite RPMs needed.

Table 34.1 Required PHP and Perl RPMs for MySQL Support

RPM	Description
php-mysql	MySQL database specific support for PHP
perl-DBI	Provides a generic Perl interface for interacting with relational databases
perl-DBD-MySQL	MySQL database specific support for Perl

Recovering / Changing Your MySQL Root Password

Sometimes you may have to recover the MySQL root password because it was either forgotten or misplaced. The steps you need are:

1) Stop MySQL

[root@bigboy tmp]# service mysqld stop
Stopping MySQL:  [  OK  ]
[root@bigboy tmp]#

2) Start MySQL in Safe mode with the mysqld_safe command and tell it not to read the grant tables with all the MySQL database passwords.

[root@bigboy tmp]# mysqld_safe --skip-grant-tables --skip-networking &
[1] 13007
[root@bigboy tmp]# Starting mysqld daemon with databases from /var/lib/mysql
[root@bigboy tmp]#

Note: In Fedora Core 3 and earlier the mysqld_safe command was named safe_mysqld and the general procedure for password recovery was different.

3) MySQL is now running without password protection. You now have to use the familiar mysql -u root command to get the mysql> command prompt. ( -p flag is not required) As expected, you will not be prompted for a password.

[root@bigboy tmp]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.16

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

4) You will now have to use the mysql database which contains the passwords for all the databases on your system and modify the root password. In this case we are setting it to ack33nsaltf1sh.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> UPDATE user SET Password=PASSWORD("ack33nsaltf1sh") WHERE User="root";
Query OK, 1 row affected (0.00 sec)
Rows matched: 2  Changed: 1  Warnings: 0

mysql>

5) Exit MySQL and restart the mysqld daemon.

mysql> exit
Bye
[root@bigboy tmp]# service mysqld restart
STOPPING server from pid file /var/run/mysqld/mysqld.pid
051224 17:24:56  mysqld ended

Stopping MySQL:  [  OK  ]
Starting MySQL:  [  OK  ]
[1]+  Done                    mysqld_safe --skip-grant-tables --skip-networking
[root@bigboy tmp]#

The MySQL root user will now be able to manage MySQL using this new password.

MySQL Database Backup

The syntax for backing up a MySQL database is as follows:

mysqldump --add-drop-table -u [username] -p[password] [database] > [backup_file]

In the previous section, you gave user mysqluser full access to the salesdata database when mysqluser used the password pinksl1p. You can now back up this database to a single file called /tmp/salesdata-backup.sql with the command

[root@bigboy tmp]# mysqldump --add-drop-table -u mysqluser \
-ppinksl1p salesdata > /tmp/salesdata-backup.sql

Make sure there are no spaces between the -p switch and the password or else you may get syntax errors.

Note: Always backup the database named mysql too, because it contains all the database user access information.

MySQL Database Restoration

The syntax for restoring a MySQL database is:

mysql -u [username] -p[password] [database] < [backup_file] 

So, using the previous example, you can restore the contents of the database with

[root@bigboy tmp]# mysql -u mysqluser -ppinksl1p salesdata \
< /tmp/salesdata-backup.sql 

Note: You may have to restore the database named mysql also, because it contains all the database user access information.

MySQL Table Backup and Restoration

Sometimes you may want to backup only one or more tables from a database. There are some practical reasons for wanting to do this. You may have a message board / forums application that uses MySQL to store its data and you want to create a brand new forum with the same users as the old one so that the users don't have to register all over again.

The MySQL SELECT statement can be used to export the data to a backup file and the LOAD command can be used to import the data back into the new database used by the new forum. In this example the data in the phpbb_users and phpbb_themes tables of the forums-db-old database are exported to files named /tmp/forums-db-users.sql and /tmp/forums-db-themes.sql respectively. The data is then imported into tables of the same name in the forums-db-new database.

mysql> use forums-db-old;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT * INTO OUTFILE '/tmp/forums-db-users.sql' FROM phpbb_users;
Query OK, 1042 rows affected (0.03 sec)

mysql> SELECT * INTO OUTFILE '/tmp/forums-db-themes.sql' FROM phpbb_themes;
Query OK, 1038 rows affected (0.03 sec)

mysql> use forums-db-new;
Database changed
mysql> load data infile '/tmp/forums-db-users.sql' replace  into table forums-db.phpbb_users ;
Query OK, 1042 rows affected (0.06 sec)
Records: 1042  Deleted: 0  Skipped: 0  Warnings: 0

mysql> load data infile '/tmp/forums-db-themes.sql' replace into table forums-db.phpbb_themes ;
Query OK, 1038 rows affected (0.04 sec)
Records: 1038  Deleted: 0  Skipped: 0  Warnings: 0

mysql>

As you can see, the syntax is fairly easy to understand. The REPLACE directive will overwrite any previously existing records with the same unique, or primary, key in the source and destination tables. The IGNORE directive will only insert records where the primary keys are different.

Very Basic MySQL Network Security

By default MySQL listens on all your interfaces for database queries from remote MySQL clients. You can see this using netstat -an. Your server will be seen to be listening on IP address 0.0.0.0 (all) on TCP port 3306.

[root@bigboy tmp]# netstat -an
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State
...
...
tcp        0      0 0.0.0.0:3306             0.0.0.0:*               LISTEN
...
...
[root@bigboy tmp]#

The problem with this is that it exposes your database to MySQL queries from the Internet. If your SQL database is going to be accessed only by applications running on the server itself, then you can force it to listen only to the equivalent of its loopback interface. Here's how.

1) Edit the /etc/my.cnf file and use the bind-address directive in the [mysqld] section to define the specific IP address on which MySQL listens for connections.

[mysqld]
bind-address=127.0.0.1

2) Restart MySQL. The netstat -an command will show MySQL listening on only the loopback address on TCP port 3306, and your application should continue to work as expected.

Basic MyQL Troubleshooting

You can confirm whether your MySQL installation has succeeded by performing these few simple steps.

Connectivity Testing

In the example scenario, network connectivity between the database and the application will not be an issue because they are running on the same server.

In cases where they are not, you have to use the troubleshooting techniques to test both basic connectivity and access on the MySQL TCP port of 3306.

Test Database Access

The steps outlined earlier are a good test of database access. If the application fails, then retrace your steps to create the database and register the database information into the application. MySQL errors are logged automatically in the /var/log/mysqld.log file; investigate this file at the first sign of trouble.

Sometimes MySQL will fail to start because the host table in the mysql database wasn't created during the installation, this can be rectified by running the mysql_install_db command.

[root@bigboy tmp]# service mysqld start
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL:  [FAILED]
[root@bigboy tmp]# tail /var/log/mysql.log
...
...
050215 19:00:33  mysqld started
050215 19:00:33  /usr/libexec/mysqld: Table 'mysql.host' doesn't exist
050215 19:00:33  mysqld ended
...
...
[root@bigboy tmp]# mysql_install_db
...
...
[root@bigboy tmp]# service mysqld start
Starting MySQL:  [  OK  ]
[root@bigboy tmp]#

A Common Fedora Core 1 MySQL Startup Error

You may notice that you can start MySQL correctly only once under Fedora Core 1. All subsequent attempts result in the message "Timeout error occurred trying to start MySQL Daemon.".

[root@bigboy tmp]# /etc/init.d/mysqld start
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL:  [FAILED]
[root@bigboy tmp]#

This is caused by the MySQL startup script incorrectly attempting to do a TCP port ping to contact the server. The solution is:

1) Edit the script /etc/rc.d/init.d/mysqld.

2) Search for the two mysqladmin lines with the word ping in them and insert the string "-u $RANDOM" before the word "ping":

if [ -n "`/usr/bin/mysqladmin -u $RANDOM ping 2> /dev/null`" ]; then
if !([ -n "`/usr/bin/mysqladmin -u $RANDOM ping 2> /dev/null`" ]); then

3) Restart MySQL.

After doing this MySQL should function correctly even after a reboot.

Conclusion

MySQL has become one of the most popular Linux databases on the market and it continues to improve each day. If you have a large project that requires the installation of a database, then I suggest seeking the services of a database administrator (DBA) to help install and fine-tune the operation of MySQL. I also suggest, no matter the size of the project, that you practice an application installation on a test Linux system to be safe. It doesn't necessarily have to be the same application. You can find free MySQL-based applications using a Web search engine, and you can use these to be come familiar with the steps outlined in this chapter before beginning your larger project.