/Guides/FreeBSD/Databases/

General Information

MySQL is one of the most popular databases on the Internet.  Besides its undoubted advantages, such as easy of use and relatively high performance, MySQL offers simple, but very effective security mechanisms.  Unfortunately, the default installation of MySQL, and in particular the empty root password and the potential vulnerability to buffer overflow attacks, makes the database an easy target for attacks.

This howto Covers:

1. Installing MySQL
   
2. Chrooting MySQL
   
3. MySQL Permissions
   
4. Configuring MySQL

Installation

Lets start out by installing MySQL from the ports tree.

# #	cd /usr/ports/databases/mysql323-server make install clean

This should work fine with the other versions of MySQL inside the ports tree.  You may also want to go over what options you would like to include, such as building MySQL:

#	make BUILD_OPTIMIZED=yes WITH_LINUXTHREADS=yes

Whatever options you may pass don't forget to put inside your pkgtools.conf file.

Now we need to make sure any portupgrades of MySQL does not overwrite our MySQL database.

#	vi /usr/local/etc/pkgtools.conf

Search for the MAKE_ARGS section.  You should make it look like this below:

MAKE_ARGS = {       'databases/mysql323-server' => 'SKIP_INSTALL_DB=yes' }

Save and exit.  Now we need to configure the MySQL client.

#	cp /usr/local/share/mysql/my-medium.cnf /etc/my.cnf

Note:  Replace my-medium.cnf with whatever suites your server's enviroment.

The following step is optional.  Most of the time there is no real need to have an open port for MySQL unless you have other machines on the network that need quick access to the database server.  So, instead we are going to use a unix socket file.

#	vi /usr/local/etc/rc.d/mysql-server.sh

Change this line:

safe_mysqld --user=mysql --datadir=${DB_DIR} ..etc..etc..

to look like:

safe_mysqld --user=mysql --skip-networking --datadir=${DB_DIR} ..etc..etc..

Chrooting MySQL

Now we chroot our server.

# # # # # # # # # # # # # # # #

mkdir -p /chroot/mysql/dev mkdir -p /chroot/mysql/bin mkdir -p /chroot/mysql/sbin mkdir -p /chroot/mysql/etc mkdir -p /chroot/mysql/tmp mkdir -p /chroot/mysql/var/tmp mkdir -p /chroot/mysql/var/db mkdir -p /chroot/mysql/var/log mkdir -p /chroot/mysql/var/run mkdir -p /chroot/mysql/usr/local/bin mkdir -p /chroot/mysql/usr/local/libexec mkdir -p /chroot/mysql/usr/local/share/mysql mkdir -p /chroot/mysql/usr/libexec mkdir -p /chroot/mysql/usr/bin mkdir -p /chroot/mysql/usr/sbin mkdir -p /chroot/mysql/usr/lib

Next, the following files have to be copied into the new directory structure:

# # # # # # #

cp /usr/local/libexec/mysqld /chroot/mysql/usr/local/libexec/ cp -Rv /usr/local/share/mysql /chroot/mysql/usr/local/share/ cp /etc/hosts /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/

MySQL Permissions

From the files: /chroot/mysql/etc/passwords and /chroot/mysql/etc/group we must remove all of the lines except the mysql account and group.  Next, we have to build the password database as follows (this applies only to FreeBSD):

# #	cd /chroot/mysql/etc vi group

Remove every entry except for the sys and mysql group

#	vi passwords

Same again, remove every entry except for root and mysql.  Then change the root shell to /sbin/nologin.  You also want to change your root password to something other than your system's password.

#	pwd_mkdb -d /chroot/mysql/etc passwords

You will receive an error of:

pwd_mkdb: warning, unknown root shell

That is fine.

#	rm -rf /chroot/mysql/etc/master.passwd

Special Considerations

As in case of the Apache web server, we have to create a special device file /dev/null:

#	ls -al /dev/null

crw-rw-rw-  1 root  sys    2,   2 Jun 21 18:31 /dev/null

# # #

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

We must also copy the mysql database, which contains grant tables created during MySQL installation:

#	cp -Rv /var/db/mysql /chroot/mysql/var/db/

Configuration

Now we need to copy needed files for MySQL to run inside the chrooted enviroment.

# # # # # # # # # # # # # #

install -C /bin/cat /chroot/mysql/bin/ install -C /bin/date /chroot/mysql/bin/ install -C /bin/hostname /chroot/mysql/bin/ install -C /bin/ls /chroot/mysql/bin/ install -C /bin/rm /chroot/mysql/bin/ install -C /bin/sh /chroot/mysql/bin/ install -C /sbin/nologin /chroot/mysql/sbin/ install -C /usr/bin/limits /chroot/mysql/bin/ install -C /usr/bin/nohup /chroot/mysql/bin/ install -C /usr/bin/sed /chroot/mysql/bin/ install -C /usr/bin/tee /chroot/mysql/bin/ install -C /usr/bin/touch /chroot/mysql/bin/ install -C /usr/bin/umask /chroot/mysql/bin/ install -C /usr/lib/libc.so.5 /chroot/mysql/usr/lib/

Note:  (Use libc.so.5 if you are running FreeBSD 5x, If you are running 4x use libc.so.4)

# # # # # # # # # # # # #

install -C /usr/lib/libc_r.so.5 /chroot/mysql/usr/lib/ install -C /usr/lib/libcrypt.so.2 /chroot/mysql/usr/lib/ install -C /usr/lib/libm.so.2 /chroot/mysql/usr/lib/ install -C /usr/lib/libreadline.so.4 /chroot/mysql/usr/lib/ install -C /usr/lib/libstdc++.so.4 /chroot/mysql/usr/lib/ install -C /usr/lib/libutil.so.3 /chroot/mysql/usr/lib/ install -C /usr/lib/libwrap.so.3 /chroot/mysql/usr/lib/ install -C /usr/lib/libz.so.2 /chroot/mysql/usr/lib/ install -C /usr/libexec/ld-elf.so.1 /chroot/mysql/usr/libexec/ cp /usr/local/bin/my* /chroot/mysql/usr/local/bin/ cp /usr/local/bin/safe_mysqld /chroot/mysql/usr/local/bin/ install -C /usr/sbin/chown /chroot/mysql/usr/sbin/ install -C /var/run/ld.so.hints /chroot/mysql/var/run/

The access rights to the above directories should be set as follows:

# # # #

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

Now let's startup our database for the first time and test out the enviroment.

# # #

chroot /chroot/mysql /bin/sh /usr/local/bin/safe_mysqld & exit

Now, make sure the server is running.

# ps -ax | grep mysql 60586  p0  S      0:00.01 /bin/sh /usr/local/bin/safe_mysqld 60601  p0  S      0:00.03 /usr/local/libexec/mysqld --basedir=/usr/local --datadir=/var/db/mysql --user=mysql --pid-file=/var 60603  p0  S+     0:00.00 grep mysql

Now let's tell our MySQL client how to access the server so we don't need to chroot ourselves every time we wish to run a query.

#	vi /etc/my.cnf

Inside the [client] section, modify the socket option.

socket          = /chroot/mysql/tmp/mysql.sock

MySQL User Accounts

# mysql mysql> drop database test; mysql> use mysql; mysql> DELETE FROM user WHERE NOT (host="localhost" and user="root"); mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');

Now let's create a different name for our super user and then remove the root user.

mysql> INSERT INTO user VALUES ('localhost','db_admin',PASSWORD('password'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'); mysql> DELETE FROM user WHERE (user="root"); mysql> flush privileges; mysql> commit; mysql> quit

Now let's be sure this worked.

# mysql ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO) # mysql -u db_admin -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 4 to server version: 3.23.58-log Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> u mysql mysql> SELECT * FROM user; mysql> quit

Startup Script

Now we need to make our startup file.

# # #

cd /usr/local/etc/rc.d rm mysql-server.sh vi mysql-server.sh

#!/bin/sh DB_DIR=/var/db/mysql PIDFILE=${DB_DIR}/`/bin/hostname -s`.pid case "$1" in         start)                 if [ -x /usr/local/bin/safe_mysqld ]; then                         /usr/bin/limits -U mysql                         nohup /sbin/chroot /chroot/mysql /usr/local/bin/safe_mysqld --user=mysql --skip-networking \                         --datadir=${DB_DIR} --pid-file=${PIDFILE} > /dev/null &                         echo -n ' mysqld'                 fi                 ;;         stop)                 if [ -f /chroot/mysql/${PIDFILE} ]; then                         /bin/kill `cat /chroot/mysql/${PIDFILE}` > /dev/null 2>&1 && echo -n ' mysqld'                 else                         echo "mysql-server isn't running"                 fi                 ;;         *)                 echo ""                 echo "Usage: `basename $0` { start | stop }"                 echo ""                 exit 64                 ;; esac

Congratulations.  Save, exit, kill your current running server, and start it up with the startup script.  Now, whenever there is a new portupgrade or buildworld, it's important to keep the binaries inside the chroot updated.  So here's a handy little script that will accomplish this for us:

#!/bin/sh echo "Updating Mysql Libs" cd /chroot/mysql for file in `find usr/ -type f `; do   if [ /$file -nt $file ]; then     cp -v /$file $file   fi done

References Used:

1. Security Infocus
   
2. Screaming Electron (For the script help)

Author: Leigh Renfrow
soup4you2 at mac dot com

Hi.

Is anybody else having problems using this with MySQL 4.0.24 and release 5.4?  Many of the library files don't exist on my system, or have slightly different names.

/usr/lib/libcrypt.so.2 Doesn't exist.  There is a libcrypt.so, however.  And .so.2 is here:
/lib/libcrypt.so.2

/usr/lib/libm.so.2 doesn't exist.  /usr/lib/libm.so does.

/usr/lib/libreadline.so.4 isn't there.
It's in /usr/compat/linux/usr/lib/libreadline.so.4

etc.

I try chrooting and I get errors like this:
chroot /chroot/mysql /bin/sh                                  
/libexec/ld-elf.so.1: Shared object "libc.so.5" not found, required by "sh"

What am I doing wrong?

You have done nothing wrong. It seems that the guide is outdated and would need to be updated for FreeBSD 5.x or higher.

I personally don't see the benefit of chrooting MySQL, it has a good security track record, also it makes it a pain to easily dump databases back into it, using phpMyAdmin which uses a INSERT INTO USING <some file> or some other syntax when a file is uploaded.