The MySQL Database Installation and Configuration:

The official MySQL web site (http://www.mysql.com/downloads/mysql.html) can provide you both source and precompiled version of the most recent version. With MySQL 5.x often you can set for a binary version.

There are two versions of the MySQL database server available on its web site:

• MySQL Standard  This is the standard version of the MySQL database server, which includes support for both the regular, non-transactional tables and the newer, transaction-safe tables. It is suited for production environments requiring a stable, flexible, and robust database engine.
   
• MySQL Max  This version includes the feature set of the standard version, together with newer, more experimental capabilities. It is not always best suited for production environments, since it usually includes a fair share of not-completely-stable enhancements.

Both these versions of MySQL are licensed under the GPL and may be freely downloaded and used under the terms of that license.

In most cases, MySQL Standard is the version you should use--it’s the version used in all the examples in this book, and it’s stable, feature-rich, and well-suited for most common applications. You should select MySQL Max only if that version includes new features that you need or are keen to try out--or if you’re a geek with a penchant for living life on the bleeding edge all the time.

The recommended way to install MySQL on a Linux system is via RPM. Both suse and Red Hat make the following RPMs available for download on its web site:

• MySQL-server  The MySQL database server, which manages databases and tables, controls user access, and processes SQL queries
   
• MySQL-client  MySQL client programs, which makes it possible to connect to, and interact with, the server
   
• MySQL-devel  Libraries and header files that come in handy when compiling other programs that use MySQL
   
• MySQL-shared  Shared libraries for the MySQL client
   
• MySQL-bench  Benchmark and performance testing tools for the MySQL database server

For a binary distribution, the directory structure for a typical MySQL installation looks like this:

<mysql-install-root>
|-- bin           [client and server binaries]
|-- data          [databases and error log]
|-- include       [header files]
|-- lib           [compiled libraries]
|-- man           [manual pages]
|-- mysql-test    [test suite]
|-- share         [error messages in different languages]
|-- scripts       [startup, shutdown and initialization scripts]
|-- sql-bench     [queries and data files for benchmark tests]
|-- support-files [sample configuration files]
|-- tests         [test cases]

Initially MySQL root password is still empty. Use the following command to set a new root password:

./bin/mysqladmin -u root password "your_root_password"

./bin/mysql -u root -p

You'll be prompted for the MySQL root password. Enter the password you picked in the previous step.

Enter password: your_root_password"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.27-standard

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

mysql>

At the mysql> prompt, type the commands that follow, replacing mypassword with the root password. Press [enter] after each semicolon.

mysql> use mysql;
mysql> delete from user where Host like "%";
mysql> grant all privileges on *.* to root@"%.your_domain" identified by 'your_root_password'' with grant option;
mysql> grant all privileges on *.* to root@localhost identified by 'your_root_password"' with grant option;
mysql> flush privileges;
mysql> exit;

This step allows you to connect to your MySQL server as 'root' from any UW computer.

Once back at your shell prompt, you can verify that your MySQL server is running with the following command:

./bin/mysqladmin -u root -p version

You'll be prompted for the root password again.

If MySQL is running, a message similar to the following will be displayed:

Enter password:
./bin/mysqladmin  Ver 8.41 Distrib 5.0.27, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          5.0.27-standard
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /hw13/d06/accountname/mysql5.sock
Uptime:                 1 min 20 sec

Threads: 1  Questions: 2  Slow queries: 0  Opens: 11  Flush tables: 1  Open tables: 6                 
 Queries per second avg: 0.025

NEWS CONTENTS

• 20040819 : Linux.com Securing MySQL ( Linux.com Securing MySQL, Aug 19, 2004 )

Old News ;-)

[Aug 19, 2004] Linux.com Securing MySQL By: Mike Peters

Thanks to its speed and stability, MySQL has earned a place on millions of servers worldwide. MySQL has a simple and effective security mechanism, but administrators must perform a few additional tasks to make a default installation truly secure. The measures we'll talk about below will enable you to better secure your database, but be sure to secure the underlying operating system too.

Installation

One key to better security is to run MySQL as its own user.

Create such a user and group with the commands:

# groupadd mysql
# useradd -c "MySQL Server" -d /dev/null -g mysql -s /bin/false mysql

Install MySQL in /usr/local/mysql: ./configure --prefix=/usr/local/mysql --with-mysqld-user=mysql --with-unix-socket-path=/tmp/mysql.sock --with-mysqld-ldflags=-all-static
make
su
make install
strip /usr/local/mysql/libexec/mysqld
scripts/mysql_install_db
chown -R root /usr/local/mysql
chown -R mysql /usr/local/mysql/var
chgrp -R mysql /usr/local/mysql

The configure option --with-mysqld-user=mysql enables MySQL to run as the mysql user. The --with-mysqld-ldflags=-all-static option makes it easier to chroot MySQL.

Copy the example configuration file from the MySQL source, support-files/my-medium.cnf, to /etc/my.cnf and set the appropriate permissions: chmod 644 /etc/my.cnf.

Once you have MySQL installed, test the installation. Start MySQL with /usr/local/mysql/bin/mysqld_safe & and log on as the root user, mysql -u root. If you see the MySQL prompt you know the database is running you can proceed to chroot it. If the installation is not working, examine the log files to find out what the problem is. Otherwise, shutdown the server: usr/local/mysql/bin/mysqladmin -u root shutdown

Chrooting MySQL

First, create the necessary directory structure for the database:

mkdir -p /chroot/mysql/dev /chroot/mysql/etc /chroot/mysql/tmp /chroot/mysql/var/tmp /chroot/mysql/usr/local/mysql/libexec /chroot/mysql/usr/local/mysql/share/mysql/english

Now set the correct directory permissions:

chown -R root:sys /chroot/mysql
chmod -R 755 /chroot/mysql
chmod 1777 /chroot/mysql/tmp

Once the directories are set up, copy the server's files:

cp /usr/local/mysql/libexec/mysqld /chroot/mysql/usr/local/mysql/libexec/
cp /usr/local/mysql/share/mysql/english/errmsg.sys /chroot/mysql/usr/local/mysql/share/mysql/english/
cp -r /usr/local/mysql/share/mysql/charsets /chroot/mysql/usr/local/mysql/share/mysql/
cp /etc/hosts /chroot/mysql/etc/
cp /etc/host.conf /chroot/mysql/etc/
cp /etc/resolv.conf /chroot/mysql/etc/
cp /etc/group /chroot/mysql/etc/
cp /etc/master.passwd /chroot/mysql/etc/passwords
cp /etc/my.cnf /chroot/mysql/etc/

Finally, copy the mysql databases that contain the grant tables storing the MySQL access privileges:

cp -R /usr/local/mysql/var/ /chroot/mysql/usr/local/mysql/var
chown -R mysql:mysql /chroot/mysql/usr/local/mysql/var

Now create null device:

mknod /chroot/mysql/dev/null c 2 2
chown root:sys /chroot/mysql/dev/null
chmod 666 /chroot/mysql/dev/null

Edit the password and groups files to remove any entries except for the mysql user and group:

/etc/passwd:
mysql:x:12347:12348:MySQL Server:/dev/null:/bin/false

/etc/group:
mysql:x:12347:

In order for PHP to be able to access MySQL you need to create a link to mysql.sock: ln /chroot/mysql/tmp/mysql.sock /chroot/httpd/tmp/. /chroot/mysql/tmp/mysql.sock and /chroot/httpd/tmp/ need to be on same filesystem. This needs to be done every time you start up the MySQL server. An example startup script below will handle this.

To run MySQL in a chrooted environment as a user other than root, you need to install the chrootuid program. Once you've installed chrootuid, test the server: chrootuid /chroot/mysql mysql /usr/local/mysql/libexec/mysqld &. This will run the server as the mysql user.

The MySQL root user and default accounts

The MySQL root user should not be confused with the system root user. By default, the MySQL root user has no password. You can check this with mysql -u root; if you get a mysql prompt, no root password is set. The first thing you should do is set a strong password for this user. Never give the system root password to the MySQL root user.

To set the initial root password, open a mysql prompt -- mysql -u root mysql -- and enter the following:

mysql> UPDATE user SET Password=PASSWORD('new_password')
    ->             WHERE user='root';
mysql> FLUSH PRIVILEGES;

Don't forget to FLUSH PRIVILEGES; to make the privileges effective.

As well as setting the root password, you should remove anonymous accounts:

mysql> DELETE FROM user WHERE User = '';
mysql> FLUSH PRIVILEGES;

Alternatively, set a password for the anonymous accounts:

mysql> UPDATE user SET Password = PASSWORD('new_password')
    ->     WHERE User = '';
mysql> FLUSH PRIVILEGES;

MySQL privilege system and MySQL users

The MySQL privilege system allows for authentication of users connecting from specific hosts. Authenticated users can be assigned privileges such as SELECT, INSERT, UPDATE, and DELETE on a per database, table, column, or host basis. When a user connects, MySQL first checks if that user is authorized to connect, based on the host and supplied password. If the user is allowed to connect, MySQL then checks each statement to see if the user is allowed to perform the requested action.

When creating new MySQL users, always give the users a strong password, and never store passwords as plain text. Only allow the minimum amount of privileges for a user to accomplish a task, and set those privileges on a per database basis. Some extra time spent planning what privileges to assign to users goes a long way in ensuring the security of your data.

You can create a new user with specific privileges using the GRANT statement. For example:

GRANT USAGE ON myapp.* TO 'someuser'@'localhost' IDENTIFIED BY 'some_pass';
FLUSH PRIVILEGES;

This statement creates a MySQL user named someuser who has access to all tables in the myapp database. The USAGE option sets all of the user's privileges to No, meaning you must enable specific privileges later. You may replace USAGE with a list of specific privileges. IDENTIFIED BY 'some_pass' sets the accounts password to 'some_pass'; GRANT automatically encrypts the password for you. Finally, this user can only connect from localhost. FLUSH PRIVILEGES; makes privilege changes effective.

MySQL access privileges are stored in the grant tables of the mysql database. You should never grant normal users privileges to edit entries in the mysql database. That right should be reserved for the root user. There are several tables in the mysql database that allow for a fine-grained level of control over user privileges.

The user table is the most important of the MySQL grant tables. It contains the usernames and passwords for all users, as well as the hosts from which users can connect. There are are also many fields specifying a wide range of privileges, such as SELECT, INSERT, DELETE, FILE, and PROCESS. You should examine this table and the MySQL manual yourself to become familiar with all the options available. Setting a value of 'N' for a field disables the privilege and 'Y' enables it.

You can change privileges using an SQL UPDATE command or the GRANT statement. If you are using SQL statements such as UPDATE or INSERT to update or set user passwords, be sure to use the PASSWORD() function to encrypt the password in the database. Finally, remember to FLUSH PRIVILEGES; for any changes you make so that they become effective:

UPDATE user SET Host='localhost', Password=PASSWORD('new_pass'), Reload_priv='Y', Process_priv='Y' WHERE
User='admin';
FLUSH PRIVILEGES;

Of the different privileges, most are self-explanatory, but some bear special consideration. PROCESS and SUPER should never be given to untrusted users. A user with these privileges may run mysqladmin processlist, which shows a list of currently executing queries. This list could potentially reveal sensitive data such as passwords.

FILE should also not be granted lightly. This privilege allows users to read and write files anywhere on the filesystem to which the mysqld process has access.

Privileges with system administrative rights or database administrative rights, such as FILE, GRANT, ALTER, SHOW DATABASE, RELOAD, SHUTDOWN, PROCESS, and SUPER, should not generally be given to accounts used by specific applications, especially Web-based applications. Furthermore, accounts for specific applications should have access only to the databases related to that specific application.

The other tables in the mysql database give an even finer-grained level of control over privileges:

db -- controls the access of users to specific databases.

tables_priv -- controls the access of users to specific tables.

columns_priv -- controls the access of users to specific columns of a table.

hosts -- specify the actions which can be performed from a particular host.

One final point is that, if you don't completely trust your DNS, use IP numbers in grant tables in place of host names. This makes it more difficult to spoof hosts.

Local security

In addition to MySQL privileges, there are a number of measures you need to take to improve security on the local machine. Most importantly, never run mysqld as root, as, among other risks, any user with the FILE privilege would then be capable of creating files as the root user.

Make sure that only the mysql user has read and write access to the database directory. Data in the database files can be viewed with any text editor, so any user with read or write access to the files could read or alter data, bypassing MySQL's privileges.

The mysql command history is stored in $HOME/.mysql_history. This file may show sensitive information such as passwords. You should clear the file with echo > $HOME/.mysql_history. To prevent the file being written to in the future, link the .mysql_history files of administrative users to /dev/null: ln -s /dev/null .mysql_history.

If you are using MySQL only on the local machine -- for example for PHP Web-based applications -- then in /chroot/mysql/etc/my.cnf add the line skip-networking to the [mysqld] section. This disables all TCP networking features of the MySQL daemon.

You can also disable the use of the LOAD DATA LOCAL INFILE command, which allows reading of local files and is potentially dangerous. Add the line set-variable=local-infile=0 to the [mysqld] section of /chroot/mysql/etc/my.cnf.

Finally, add the line socket = /chroot/mysql/tmp/mysql.sock to the [client] section of /etc/my.cnf. Notice that we are adding this line to /etc/my.cnf, not /chroot/mysql/etc/my.cnf. This is because, while the MySQL server daemon will use /chroot/mysql/etc/my.cnf, MySQL administrative programs such as mysqladmin are not in the chroot environment and will therefore read configuration from /etc/my.cnf.

Securing remote access

The most important step in securing remote access to your MySQL server is in having a firewall. Your firewall should allow only trusted hosts access to MySQL's port, 3306. Better still is to firewall off your MySQL server altogether and allow access onlythrough a Secure Shell (SSH) tunnel, as described below.

Always use passwords for user accounts, even for trusted client programs. The password in a mysql connection is sent encrypted, but in versions prior to 4.1.1, encryption was not particularly strong. In version 4.1.1 the encryption algorithm was much improved.

Even though the password is sent encrypted, data is sent as plain text. If you are connecting across an untrusted network, you should use an SSH encrypted tunnel. SSH tunneling allows you to connect to a MySQL server from behind a firewall, even when the MySQL port is blocked.

To set up tunnel, use the command ssh ssh_server -L 5001:mysql_server:3306 sleep 99999. You need not have direct access to mysql_server, provided ssh_server does. Now you can connect to port 5001 on the local machine with your favorite database client and the connection will be forwarded silently to the remote machine in an encrypted SSH tunnel.

Backup

Be sure to make regular backups of your databases. MySQL includes two utilities which make this easy, mysqlhotcopy and mysqldump.

To use mysqlhotcopy, a user needs access to the files for the tables that he is backing up, the SELECT privilege for those tables, and the RELOAD privilege, in order to execute FLUSH TABLES. You can backup a database using mysqlhotcopy db_name [/path/to/backup_db_dir].

mysqldump supports more options and is especially useful for copying databases between servers, backing up multiple databases at once, or making backups of the database structure only. Databases can be backed up using one of the following commands:

mysqldump [options] db_name [tables]
mysqldump [options] --databases DB1 [DB2 DB3...]
mysqldump [options] --all-databases

For example, you can back-up all your databases and compress them in one go with the command:
date=`date -I`; mysqldump --opt --all-databases -u user --password="your_pass" | bzip2 -c > databasebackup-$date.sql.bz2

The --opt option is shorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. This should create a backup which is quick and easy to restore. In fact this option is enabled by default in versions 4.1 and later; you can disable it with --skip-opt.

To restore a database from a file created by mysqldump you just need to run mysql -u user -p db_name < backup-file.sql. The -p option tells mysql to prompt for a password.

Server startup

The following script can be used to start your MySQL server:

#!/bin/sh

CHROOT_MYSQL=/chroot/mysql
CHROOT_PHP=/chroot/httpd
SOCKET=/tmp/mysql.sock
MYSQLD=/usr/local/mysql/libexec/mysqld
PIDFILE=/usr/local/mysql/var/`hostname`.pid
CHROOTUID=/usr/local/sbin/chrootuid

echo -n " mysql"

case "$1" in
start)
        rm -rf ${CHROOT_PHP}/${SOCKET}
        nohup ${CHROOTUID} ${CHROOT_MYSQL} mysql ${MYSQLD} >/dev/null 2>&1 &
        sleep 5 && ln ${CHROOT_MYSQL}/${SOCKET} ${CHROOT_PHP}/${SOCKET}
        ;;
stop)
        kill `cat ${CHROOT_MYSQL}/${PIDFILE}`
        rm -rf ${CHROOT_MYSQL}/${SOCKET}
        ;;
*)
        echo ""
        echo "Usage: `basename $0` {start|stop}" >&2
        exit 64
        ;;
esac

exit 0

Summary

Using these procedures will reduce the risk of a potential breakin to your database server. MySQL's extensive privilege system allows you to protect the data stored within the database. As always you should remain vigilant, and be sure to apply patches and upgrades to your server as they become available.

Mike Peters is a freelance consultant and programmer and long-time Linux user.

Recommended Links

MySQL MySQL 5.1 Reference Manual 2 Installing and Upgrading MySQL

LAMP (Linux, Apache, MySQL, PHP) HOWTO Installing MySQL and Apache with PHP support on Linux

How to install MySQL on SuSE Linux laffers.net

MySQL Installation and Configuration

• MySQL Installation and Configuration
• Choosing Between Binary and Source Distributions
• Installing and Configuring MySQL (Linux/Unix)
• Installing MySQL from a Binary Tarball Distribution (Linux/Unix)
• Installing MySQL from a Source Distribution (Linux/Unix)
• Installing and Configuring MySQL on Windows
• Installing MySQL from a Source Distribution (Windows)
• Testing MySQL
• Post-Installation Steps