Chroot MySQL on NetBSD

       364 words, 2 minutes

Installing MySQL on NetBSD is really easy with pkgsrc. But chrooting it requires a few particular steps.

Installation

Install the NetBSD system than add the MySQL package:

# pkg_add -uu http://nyftp.netbsd.org/pub/pkgsrc/packages/NetBSD/amd64/5.0/All/mysql-server-5.1.52.tgz 

Configuration

Create the chroot environment where MySQL will be installed:

# set -o braceexpand  
# mkdir -p /home/sql/{etc,tmp}  
# chown mysql:mysql /home/sql/tmp  
# for LINK in home sql var; do ln -s . /home/sql/$LINK; done 

Enable UID and GID identification:

# grep mysql /etc/master.passwd > /home/sql/etc/master.passwd
# pwd_mkdb -d /home/sql /home/sql/etc/master.passwd
# grep mysql /etc/group > /home/sql/etc/group

Copy extra required files:

# tar cpf - /usr/pkg/share/mysql | tar xpf - -C /home/sql

Edit the rc.conf and rc.local files to enable daemon autostart:

# vi /etc/rc.conf
mysqld=YES
mysqld_datadir="/home/sql/data"
mysqld_flags="--chroot=/home/sql"
# vi /etc/rc.local
[ -x /usr/pkg/share/examples/rc.d/mysqld ] &&
        /usr/pkg/share/examples/rc.d/mysqld start

Final steps:

# /usr/pkg/share/examples/rc.d/mysqld start  
# ln -s /home/sql/tmp/mysql.sock /tmp/mysql.sock  
# /usr/pkg/bin/mysql\_secure\_installation 

Quick check of this installation

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 9
Server version: 5.1.52 Source distribution
(...)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (0.00 sec)

mysql> create database jdoe;
Query OK, 1 row affected (0.01 sec)

mysql> grant all privileges on jdoe.* to jdoe@localhost identified by 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| jdoe              |
+--------------------+
3 rows in set (0.00 sec)

mysql> quit
Bye
# ls -alh /home/sql/data/jdoe/
total 1.1K
drwx------  2 mysql  mysql  512B Dec 20 00:18 .
drwx------  4 mysql  mysql  512B Dec 20 00:18 ..
-rw-rw----  1 mysql  mysql   65B Dec 20 00:18 db.opt

Now let’s check the database with external tools:

# netbsd# mysqlcheck -u root -p -A -c
Enter password:
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
(...)
# mysqlcheck -u root -p -A -a
Enter password:
mysql.columns_priv                                 Table is already up to date
mysql.db                                           Table is already up to date
mysql.event                                        Table is already up to date
mysql.func                                         Table is already up to date
(...)

That’s all folks!