12 July 2012

FreePBX on Ubuntu


 Originally written for Ubuntu 6.  Should work on newer versions.
In this document there are various boxes, with text inside them.
These are examples of what you see on your screen, what you should
type, and/or the expected responses. For example:
rob@rob-laptop:~$ id
uid=1000(rob) gid=1000(rob)
groups=4(adm),20(dialout),24(cdrom),25(floppy),
29(audio),30(dip),44(video),46(plugdev),
106(lpadmin),110(scanner),112(admin),1000(rob)
In that situation, you would type 'id' and the response would be
similar to the response indicated. If you see an error, that probably
means you've typed something incorrectly, but it could also suggest
other problems. Read the error carefully, and if you don't know how to
fix it, feel free to post to the forums or ask on IRC.
Operating System Installation
Installation of Ubuntu is out of the scope of this document.. As a
reference point this document was written with accepting the defaults
for everything suggested in the installer..
Post Installation Configuration
After your machine has rebooted and you've logged in, you need to
switch to the 'root' account. Whilst installing you were prompted for a
password, which is what you must enter when prompted below:
rob@rob-laptop:~$ sudo su -
Password: Enter Password Here
root@rob-laptop:~#
This gives you full control of the system. It's the equivlent of typing 'sudo' before every command.
You now need to ensure that your machine is able to access the
internet, and once that is working you can proceed with the
installation Usually being able to browse the internet with Firefox is
a good indication that you won't be having any problems.
Check for updates and install required packages
Before running apt-get, you must edit the sources.list file so that you can install from the "universe".
nano +17 -w /etc/apt/sources.list
Remove the # signs from this line and add multiverse at the end:
deb http://us.archive.ubuntu.com/ubuntu/ dapper universe multiverse
Multiverse gives you access to a lot more packages then the standard repository does, useful for future reference.
Unless you've added extra repositories, your sources.list should look something like:
deb http://archive.ubuntu.com/ubuntu/ dapper main restricted universe multiverse
deb http://archive.ubuntu.com/ubuntu/ dapper-updates main restricted universe multiverse
deb http://archive.ubuntu.com/ubuntu/ dapper-backports main restricted universe multiverse
deb http://security.ubuntu.com/ubuntu/ dapper-security main restricted universe multiverse
In addition to this, you must update apt's package lists"
apt-get update
You need to ensure that your machine is up to date with the current
security packages release by Ubuntu. After doing so, an apt-get of the
modules below will install all the requirements for freePBX
root@rob-laptop:~# apt-get install
php5 php5-cli php5-mysql mysql-server php-pear php-db openssh-server
curl sox apache2 subversion build-essential libncurses5-dev libssl-dev
linux-headers-`uname -r` libmysqlclient15-dev
... Please copy-and-paste that line, rather than trying to type it in.
Reading package lists... Done
Building dependency tree... Done
The following extra packages will be installed:
apache-common apache2-common apache2-mpm-prefork apache2-utils
... Several more lines of automatically imported packages ...
mysql-server-5.0 openssh-server php-db php-http php-mail php-net-smtp
php-net-socket php-pear php-xml-parser php4 php4-cli php4-common php4-mysql
php4-pear php5-common sox ssl-cert zlib1g-dev
0 upgraded, 39 newly installed, 0 to remove and 6 not upgraded.
Need to get 40.6MB of archives.
After unpacking 107MB of additional disk space will be used.
Do you want to continue [Y/n]? y
Get:1 http://au.archive.ubuntu.com dapper/main libpcre3 6.4-1.1ubuntu4 [174kB]
Get:2 http://security.ubuntu.com dapper-security/main libapr0 2.0.55-4ubuntu2.1 [132kB]
Get:3 http://au.archive.ubuntu.com dapper/main ssl-cert 1.0.13 [9526B]
Get:4 http://au.archive.ubuntu.com dapper/main curl 7.15.1-1ubuntu2 [168kB]
... The machine now proceeds to download and install packages ...
Setting up sox (12.17.9-1) ...
root@rob-laptop:~#
Downloading and Installing Asterisk
Previous comment:
Options, if you use edgy instead of dapper, you will get the
latest version of asterisk without needing to compile etc etc etc, all
you need to do is: apt-get install asterisk and skip to the section on
MySQL... Dapper also has packaged versions of asterisk and is quite
useable also, and is a lot easier to manage then compiling and
re-compiling to upgrade all the time
Rob's Response:
Well, I tried this, (on 6.06 LTS) and got Asterisk-1.2.7.1, and
Zaptel-1.2.5. Both of these are _woefully_ out of date. Asterisk has 3
Denial-Of-Service bugs and 2 security bugs, and Zaptel doesn't have the
proper echo cancellation enabled in it. Yes. Maybe it might be easier
to type in 'apt-get install asterisk zaptel', but it'll be crap.
Compile from source, it's not that hard. To make it easier, I've put
one box at the bottom of the downloading section that you can
copy-and-paste from to install everything from source.
Downloading
We will be using subversion to download the latest version of the
1.2 branch of Asterisk, Zaptel, LibPRI and Asterisk-Addons.
asterisk-sounds will be the latest version.
root@rob-laptop:/usr/src# svn co http://svn.digium.com/svn/asterisk/branches/1.2 asterisk-1.2
... Lots of files are downloaded ...
root@rob-laptop:/usr/src# svn co http://svn.digium.com/svn/zaptel/branches/1.2 zaptel-1.2
... Zaptel files download ...
root@rob-laptop:/usr/src# svn co http://svn.digium.com/svn/libpri/branches/1.2 libpri-1.2
... LibPRI downloads - Note, this is quite small, only about 15 files. This is normal ...
root@rob-laptop:/usr/src# svn co http://svn.digium.com/svn/asterisk-addons/branches/1.2 asterisk-addons-1.2
... Asterisk-Addons downloads...
root@rob-laptop:/usr/src# svn co http://svn.digium.com/svn/asterisk/trunk/sounds asterisk-sounds
... The default Sounds package downloads now. This can be quite large ...
root@rob-laptop:/usr/src#
Compiling and Installing
You now need to compile and install the latest version of asterisk.
root@rob-laptop:/usr/src# cd libpri-1.2
root@rob-laptop:/usr/src/libpri-1.2# make install
gcc -Wall -Werror -Wstrict-prototypes -Wmissing-prototypes -g -c -o copy_string.o copy_string.c
gcc -Wall -Werror -Wstrict-prototypes -Wmissing-prototypes -g -c -o pri.o pri.c
... libpri compiles ...
install -m 644 libpri.a /usr/lib
if test $(id -u) = 0; then /sbin/ldconfig -n /usr/lib; fi
root@rob-laptop:/usr/src/libpri-1.2# cd ../zaptel-1.2
root@rob-laptop:/usr/src/zaptel-1.2# make install config
cc -I. -O4 -g -Wall -DBUILDING_TONEZONE -DSTANDALONE_ZAPATA
-DZAPTEL_CONFIG=\"/etc/zaptel.conf\" -DHOTPLUG_FIRMWARE -c -o
gendigits.o gendigits.c
cc -o gendigits gendigits.o -lm
./gendigits > tones.h
... zaptel compiles - Note that any errors here are usually
because of you not having the correct version of linux-headers'
installed ...
ZAPTELVERSION="SVN-branch-1.2-r1468" build_tools/make_version_h > version.h.tmp
if cmp -s version.h.tmp version.h ; then echo; else \
mv version.h.tmp version.h ; \
fi
... Zaptel compiles ...
root@rob-laptop:/usr/src/zaptel-1.2# cd ../asterisk-1.2
root@rob-laptop:/usr/src/asterisk-1.2# make install
if cmp -s .cleancount .lastclean ; then echo ; else \
make clean; cp -f .cleancount .lastclean;\
fi
make1: Entering directory `/usr/src/asterisk-1.2'
... Asterisk Compiles ...
root@rob-laptop:/usr/src/asterisk-1.2# cd ../asterisk-addons-1.2
root@rob-laptop:/usr/src/asterisk-addons-1.2# make install
./mkdep -fPIC -I../asterisk -D_GNU_SOURCE -I/usr/include/mysql `ls *.c`
make -C format_mp3 all
... Asterisk-Addons now install ...
root@rob-laptop:/usr/src/asterisk-addons-1.2# cd ../asterisk-sounds/
root@rob-laptop:/usr/src/asterisk-sounds# make install
... Lots of additional sound files are installed here ...
root@rob-laptop:/usr/src/asterisk-sounds#
Copy-And-Paste this, for ease of installation:
cd /usr/src
svn co http://svn.digium.com/svn/asterisk/branches/1.2 asterisk-1.2
svn co http://svn.digium.com/svn/zaptel/branches/1.2 zaptel-1.2
svn co http://svn.digium.com/svn/libpri/branches/1.2 libpri-1.2
svn co http://svn.digium.com/svn/asterisk-addons/branches/1.2 asterisk-addons-1.2
svn co http://svn.digium.com/svn/asterisk/trunk/sounds asterisk-sounds
cd /usr/src/libpri-1.2 && make install
cd /usr/src/zaptel-1.2
sed -i 's!^#define ECHO_CAN_KB1!/* #define ECHO_CAN_KB1 */!' zconfig.h
sed -i 's!/\* #define ECHO_CAN_MG2 \*/!#define ECHO_CAN_MG2!' zconfig.h
make install
cd /usr/src/asterisk-1.2 && make install
cd /usr/src/asterisk-addons-1.2
sed -i 's/_GNU_SOURCE/_GNU_SOURCE -DMYSQL_LOGUNIQUEID/' Makefile
make install
If all you wanted to do was install Asterisk on a Ubuntu machine,
you're done - you now have a fully functional Asterisk box, for you to
play with as you wish. If you want, you can run 'make samples' in the
asterisk-1.2 directory to install some example configuration files for
you to play with. However, since you're reading this on the FreePBX
site, we're now up to isetting the machine up and nstalling FreePBX.
Create user and set permissions
Unfortunately, issues in Asterisk 1.2 require us
to run the web server process as the same user as asterisk. In this
situation, it's easier for us to run httpd as 'asterisk', rather than
asterisk as 'httpd', as there's far less configuration that needs to be
done.
root@rob-laptop:~# addgroup asterisk
Adding group `asterisk' (1001)...
Done.
root@rob-laptop:~# useradd -g asterisk -c "Asterisk PBX" -d /var/lib/asterisk asterisk
root@rob-laptop:~# mkdir /var/run/asterisk
root@rob-laptop:~# chown -R asterisk /var/lib/php5
Using nano (or your favourite editor, but nano is fine), you need to change User apache and Group apache to User asterisk and Group asterisk.
[root@dhcp1 ~]# nano +101 /etc/apache2/apache2.conf (Push Control-X to save when you've finished)
You also want to change AllowOverride None to AllowOverride All
[root@dhcp1 ~]# nano +12 /etc/apache2/sites-enabled/000-default (Push Control-X to save when you've finished)
And then restart asterisk to re-load its configuration.
root@rob-laptop:~# /etc/init.d/apache2 restart
Set up MySQL
Before you can do anything to MySQL, you need to make sure it's running:
root@rob-laptop:~# /etc/init.d/mysql start
Starting MySQL database server: mysqld.
root@rob-laptop:~#
Now, you must cd to the /usr/src directory and get the source to freepbx using svn:
root@rob-laptop:/usr/src/freepbx# cd /usr/src/
root@rob-laptop:/usr/src# svn co https://svn.sourceforge.net/svnroot/amportal/freepbx/branches/2.2freepbx-2.2
A freepbx/amp_conf
A freepbx/amp_conf/astetc
... freePBX now downloads ...
Checked out revision 2574.
root@rob-laptop:/usr/src# cd /usr/src/freepbx-2.2
Now, to configure the databases for freePBX:
root@rob-laptop:/usr/src/freepbx# mysqladmin create asterisk
root@rob-laptop:/usr/src/freepbx# mysqladmin create asteriskcdrdb
root@rob-laptop:/usr/src/freepbx# mysql asterisk < SQL/newinstall.sql
root@rob-laptop:/usr/src/freepbx# mysql asteriskcdrdb < SQL/cdr_mysql_table.sql
They also need to be secured, so that not just anyone can access
them. freePBX will prompt you for a database password when you do the
install. You need to pick that now. We'll assume that you've picked
'asteriskuser' and 'amp109' - you probably shouldn't use these, as they
are well known passwords for Asterisk@Home builds. If anyone's trying
to attack your machine, they will try this.
root@rob-laptop:/usr/src/freepbx# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 5.0.22-Debian_0ubuntu6.06.2-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> GRANT ALL PRIVILEGES ON asterisk.* TO asteriskuser@localhost IDENTIFIED BY 'amp109'; (This is the first username and password asked for below)
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON asteriskcdrdb.* TO asteriskuser@localhost IDENTIFIED BY 'amp109';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> \q
Bye
root@rob-laptop:/usr/src/freepbx#
Now, after all of this, you need to pick a root 'mysql' password.
For this, we'll pretend it's 's33kret'. If you need to do anything else
with mysql, you'll need to provide this password.
root@rob-laptop:/usr/src/freepbx# mysqladmin -u root password 's33kret'
Install freePBX at last!
You're there - you've done the hard yards, and finally you can install freePBX!
WARNING! If you have an existing Asterisk
installation, the script below will overwrite your Asterisk
configuration files. Backup your /etc/asterisk directory before
running.
root@rob-laptop:/usr/src/freepbx# ./install_amp
Checking for PEAR DB..OK
Checking for PEAR Console::Getopt..OK
Checking user..OK
Checking for /etc/amportal.conf../etc/amportal.conf does not exist, copying default
Creating new /etc/amportal.conf
Enter your USERNAME to connect to the 'asterisk' database:
[asteriskuser] (Just push enter if you've done the defaults above, or, fill in the details you entered)
Enter your PASSWORD to connect to the 'asterisk' database:
[amp109] (As above, the password you picked in the MYSQL command)
Enter the hostname of the 'asterisk' database:
[localhost] (Just push enter)
Enter a USERNAME to connect to the Asterisk Manager interface:
[admin] (Just push enter)
Enter a PASSWORD to connect to the Asterisk Manager interface:
[amp111] (Just push enter)
Enter the path to use for your AMP web root:
[/var/www/html]
/var/www
Enter the path to use for your FOP web root:
[/var/www/html/panel]
/var/www/panel
Enter the path to your Apache cgi-bin:
[/var/www/cgi-bin] /usr/lib/cgi-bin
Enter the IP ADDRESS or hostname used to access the AMP web-admin:
[xx.xx.xx.xx] Enter the IP Address of your UBUNTU SERVER HERE
Enter a PASSWORD to perform call transfers with the Flash Operator Panel:
[passw0rd] (Just push enter)
Use simple Extensions [extensions] admin or separate Devices and Users [deviceanduser]?
[extensions](Just push enter)
Enter directory in which to store AMP executable scripts:
[/var/lib/asterisk/bin] (Just push enter)
Created /var/lib/asterisk/bin
Enter directory in which to store super-user scripts:
[/usr/sbin] (Just push enter)
/etc/amportal.conf writtenOK
Reading /etc/amportal.conf..OK
Checking for /etc/asterisk/asterisk.conf..OK
Reading /etc/asterisk/asterisk.conf..OK
Checking for Asterisk 1.2..OK
Checking for selinux..OK
At this stage, you're almost done, but there's quite often a
problem when people have made a typo, or forgotten to put a password in
the mysql server. If you see these lines:
Connecting to database..FAILED
Try running ./install_amp --username=user --password=pass (using your own user and pass)
[FATAL] Cannot connect to database
root@rob-laptop:/usr/src/freepbx#
it means that you haven't done the 'GRANT ALL PRIVILEGES ...'
command in MySQL, or, you've put the wrong password in when you were
doing the installation. All is not lost. If you want to re-run the
installation, with it prompting you again, you can simply delete the
/etc/amportal.conf file (rm /etc/amportal.conf) or edit it and put the correct password in. Then re-run ./install_amp and it should proceed along happily.
root@rob-laptop:/usr/src/freepbx# ./install_amp
Checking for PEAR DB..OK
Checking for PEAR Console::Getopt..OK
Checking user..OK
Checking for /etc/amportal.conf..OK
Reading /etc/amportal.conf..OK
Checking for /etc/asterisk/asterisk.conf..OK
Reading /etc/asterisk/asterisk.conf..OK
Checking for Asterisk 1.2..OK
Checking for selinux..OK
Connecting to database..OK
Checking current version of AMP..2.1.2
Installing new AMP files..OK
Configuring install for your environment..OK
Setting permissions on files..OK
Checking for upgrades..0 found
Generating AMP configs..
Generating Configurations.conf..
Checking for PEAR DB..OK
Checking for PEAR Console::Getopt..OK
Checking for /etc/amportal.conf..OK
Reading /etc/amportal.conf..OK
Reading /etc/asterisk/asterisk.conf..OK
Connecting to database..OK
Please Reload Asterisk by visiting http://192.168.1.53/admin
Generating AMP configs..OK
Restarting Flash Operator Panel..op_server.pl: no process killed
OK
Please Reload Asterisk by visiting http://192.168.1.53/admin
root@rob-laptop:/usr/src/freepbx# modprobe ztdummy
root@rob-laptop:/usr/src/freepbx# amportal start
Starting FreePBX and Asterisk automatically
If you don't have any zaptel hardware, you can automatically start
ztdummy and asterisk by editing /etc/rc.local, and before the 'exit 0'
line insert these two lines:
modprobe ztdummy
/usr/sbin/amportal start &
That ensures that the timing module (ztdummy) is loaded, and that
asterisk is running on bootup. You can also use the /etc/init.d/zaptel
script to start zaptel, hopefully someone with more ubuntu knowledge
can update this page with how to enable it.
Congratulations!
You're done - you now have a fully functional FreePBX
Installation. The first thing to do is log in (Go to the IP address of
the Ubuntu machine above, and click on 'Setup'). The default username
is 'admin' and the default password is 'admin'. Go to the
'Administrators' tab and change the password straight away. After that,
you can visit the Online Module Repository and see what modules are
available.

how to change root password in debian

This is one of the problem faced by many person’s“what if i lost the root password”First thing that comes in mind is “use single user mode” but the answer is NO.
coz debian takes the system security to one more level up and by default ask’s for the root password.

so the BIG QUESTION IS HOW TO proceed further

follow these steps

1) on grub-boot prompt.
press “e” to enter edit mode

2) then press downarrow to reach the line that starts with “kernel “
press “e” again

3) at the end of this line type in “init=/bin/sh” or “/bin/bash”

4) then press enter to make that change and press “b” to boot

in a few seconds you will be on your “#” prompt

5) only one step left
“mount -o remount rw /”

this step is necessary coz in this case root file system is mounted as read only.

6) finally type “passwd” and you get the screen to change the password

and then type in “init 6? or “reboot”

NOTE FOR TECHNICAL USERS
those who are looking for the technical details, the main work is the init command that we passed as an argument to kernel, it told kernel to specifically run the command specified in parameter instead of working on normal routine.

TRICK = if you have any program you wish to run instead of this then you can do that too using init command only.

Securing MySQL: step-by-step

MySQL is one of the most popular databases on the Internet and it is often used in conjunction with PHP. 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 article describes the basic steps which should be performed in order to secure a MySQL database against both local and remote attacks. This is the third and last of the series of articles devoted to securing Apache, PHP and MySQL.
1.1 Functionality
The article assumes that the Apache web server with the PHP module is installed in accordance with the previous articles, and is placed in the /chroot/httpd directory.

Apart from the above we assume the following:

* The MySQL database will be used only by PHP applications, installed on the same host;
* The default administrative tools, such as mysqladmin, mysql, mysqldump etc. will be used to manage the database;
* Remote data backup will be performed by utilizing the SSH protocol.

1.2 Security requirements
In order to achieve the highest possible level of security, the installation and configuration of MySQL should be performed in accordance with the following security requirements:

* MySQL database must be executed in a chrooted environment;
* MySQL processes must run under a unique UID/GID that is not used by any other system process;
* Only local access to MySQL will be allowed;
* MySQL root's account must be protected by a hard to guess password;
* The administrator's account will be renamed;
* Anonymous access to the database (by using the nobody account) must be disabled;
* All sample databases and tables must be removed.

2. Installing MySQL
Before we start securing MySQL, we must install the software on the server. As in the previous articles, we will start installation by creating a unique, regular group and user account on the operating system, which will be dedicated to the MySQL database:

pw groupadd mysql
pw useradd mysql -c "MySQL Server" -d /dev/null -g mysql -s /sbin/nologin
2.1 Compiling MySQL
We will compile and install MySQL software in the /usr/local/mysql directory:

./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

In general, the process of installing the server is almost identical to the one described in the MySQL manual. The only change is the use of a few additional parameters, specified in the ./configure line. The most important difference is the use of --with-mysqld-ldflags=-all-static parameter, which causes the MySQL server to be linked statically. This will significantly simplify the process of chrooting the server, as described in Section 3. With regard to the other parameters, they instruct the make program to install the software in the /usr/local/mysql directory, run the MySQL daemon with the privileges of the mysql account, and create the mysql.sock socket in the /tmp directory.
2.2 Copy configuration file
After executing the above commands, we must copy the default configuration file in accordance with the expected size of the database (small, medium, large, huge). For example:

cp support-files/my-medium.cnf /etc/my.cnf
chown root:sys /etc/my.cnf
chmod 644 /etc/my.cnf
2.3 Start the server
At this point MySQL is fully installed and ready to run. We can start the MySQL server by executing the following command:

/usr/local/mysql/bin/mysqld_safe &
2.4 Test the connection
Try to establish a connection with the database as follows:

/usr/local/mysql/bin/mysql -u root mysql

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.13-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
2 rows in set (0.00 sec)

mysql> quit;

Once the connection is successfully established, we can shutdown the database:

/usr/local/mysql/bin/mysqladmin -u root shutdown

and start securing the software. Otherwise, we should analyze the information stored in the /usr/local/mysql/var/`hostname`.err log file, and eliminate the cause of any problems.
3. Chrooting the server
The first step of securing MySQL is to prepare the chrooted environment, in which the MySQL server will run. The chrooting technique was described in detail in the first article of this series ("Securing Apache: Step-by-Step"), so if you are not familiar with the technique or why chrooting is recommended, please refer to that article.
3.1 Operating system
Like in the previous articles, the target operating system is FreeBSD 4.7. However, the methods presented should also apply on most modern UNIX and UNIX-like systems.
3.2 Prepare chroot environment
In order to prepare the chrooted environment, we must create the following directory structure:

mkdir -p /chroot/mysql/dev
mkdir -p /chroot/mysql/etc
mkdir -p /chroot/mysql/tmp
mkdir -p /chroot/mysql/var/tmp
mkdir -p /chroot/mysql/usr/local/mysql/libexec
mkdir -p /chroot/mysql/usr/local/mysql/share/mysql/english
3.3 Set access rights
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
3.4 Create directory structure
Next, the following files have to be copied into the new directory structure:

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 /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/
3.5 Tighten passwords and groups
From the files: /chroot/mysql/etc/passwords and /chroot/mysql/etc/group we must remove all 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
pwd_mkdb -d /chroot/mysql/etc passwords
rm -rf /chroot/mysql/etc/master.passwd
3.6 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 -R /usr/local/mysql/var/ /chroot/mysql/usr/local/mysql/var
chown -R mysql:mysql /chroot/mysql/usr/local/mysql/var
3.7 Localization
If any language other than English will be used, we should copy the proper charsets from the /usr/local/mysql/share/mysql/charsets directory as well.
3.8 Test the configuration
At this point MySQL is ready to run in the chrooted environment. We can test if it runs correctly by executing the following command:

chrootuid /chroot/mysql mysql /usr/local/mysql/libexec/mysqld &

If any error occurs, we should use the truss command or an alternative, such as ktrace/kdump, strace, etc. This will help us to determine and eliminate the cause of the problems.

Notice, that in order to run the mysqld process, the chrootuid program was used instead of chroot, as in case of Apache or PHP. The main difference is that chrootuid changes the owner of the executing process. In our example, mysqld is being executed in a chrooted environment, but the owner of the process is not root, but mysql user. The chrootuid is not installed by default in many operating systems and it may be necessary to download and install this program manually. The chrootuid software can be downloaded here.

4. Configuring the server
The next step is to configure the database server in compliance with our security requirements.

In case of default installation of MySQL, the main configuration file is /etc/my.cnf. In our case, however, because of running the server in a chrooted environment, we will use two configuration files: /chroot/mysql/etc/my.cnf and /etc/my.cnf. The first one will be used by MySQL server, and the latter will be used by MySQL tools (e.g. mysqladmin, mysql, mysqldump etc.). In both cases, some configuration changes will be required.
4.1 Disable remote access
The first change applies to the 3306/tcp port, on which MySQL listens by default. Because, according to the initial assumptions, the database will be used only by locally installed PHP applications, we can freely disable listening on that port. This will limit possibilities of attacking the MySQL database by direct TCP/IP connections from other hosts. Local communication will be still possible throw the mysql.sock socket. In order to disable listening on the mentioned port, the following parameter should be added to the [mysqld] section of /chroot/mysql/etc/my.cnf:

skip-networking

If, for some reason, remote access to the database is still required (e.g. to perform remote data backup), the SSH protocol can be used as follows:

backuphost$ ssh mysqlserver /usr/local/mysql/bin/mysqldump -A > backup
4.2 Improve local security
The next change is to disable the use of LOAD DATA LOCAL INFILE command, which will help to prevent against unauthorized reading from local files. This matters especially when new SQL Injection vulnerabilities in PHP applications are found.

For that purpose, the following parameter should be added in the [mysqld] section in /chroot/mysql/etc/my.cnf:

set-variable=local-infile=0

In addition, to make the use of the database administrative tools convenient, the following parameter should be changed in the [client] section of /etc/my.cnf:

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

Thanks to that, there will be no need to supply the mysql, mysqladmin, mysqldump etc. commands with the --socket=/chroot/mysql/tmp/mysql.sock parameter every time we run these tools.
4.3 Change admin password
One of the most important steps in securing MySQL is changing the database administrator's password, which is empty by default. In order to perform that, we should run MySQL (if it is not already running):

chrootuid /chroot/mysql mysql /usr/local/mysql/libexec/mysqld &

and change the administrator's password as follows:

/usr/local/mysql/bin/mysql -u root
mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');

It is good practice not to change passwords from the command line, for example, by using the "mysqladmin password" command. This is especially important when other users work on the server. In that case the password could be easily revealed, e.g. by using the "ps aux" command or reviewing history files (~/.history, ~/.bash_history etc), when improper access rights are set to them.
4.4 Remove default users/db
Next, we must remove the sample database (test) and all accounts except the local root account:

mysql> drop database test;
mysql> use mysql;
mysql> delete from db;
mysql> delete from user where not (host="localhost" and user="root");
mysql> flush privileges;

This will prevent the database from establishing anonymous connections and -- irrespective of the skip-networking parameter in /chroot/mysql/etc/my.cnf -- remote connections as well.
4.5 Change admin name
It is also recommended to change the default name of administrator's account (root), to a different, harder to guess one. Such a change will make it difficult to perform brute-force and dictionary attacks on the administrator's password. In this case the intruder will have to guess not only the password, but first and foremost, the name of the administrator's account.

mysql> update user set user="mydbadmin" where user="root";
mysql> flush privileges;
4.6 Remove history
Finally, we should also remove the content of the MySQL history file (~/.mysql_history), in which all executed SQL commands are being stored (especially passwords, which are stored as plain text):

cat /dev/null > ~/.mysql_history
5. Communication between PHP and MySQL
In the previous article ("Securing PHP: Step-by-Step"), I mentioned about the communication problem between PHP and MySQL when one of these programs is being executed in a chrooted environment. Because locally PHP communicates with MySQL by using the /tmp/mysql.sock socket, placing PHP in the chrooted environment means that they cannot communicate with each other. To solve that problem, each time we run MySQL we must create hard link to the PHP chrooted environment:

ln /chroot/mysql/tmp/mysql.sock /chroot/httpd/tmp/

Note that the /chroot/mysql/tmp/mysql.sock socket and the /chroot/httpd/tmp directory must be physically placed on the same filesystem. Otherwise the programs will not be able to communicate with each other -- hard links do not work between filesystems.
6. Final steps
At this point we can create all databases and accounts which will be used by specific PHP applications. It should be emphasized that these accounts should have access rights only to the databases which are used by the PHP applications. In particular, they should not have any access rights to the mysql database, nor any system or administrative privileges (FILE, GRANT, ALTER, SHOW DATABASE, RELOAD, SHUTDOWN, PROCESS, SUPER etc.).

At last, we should also create a shell script that will be used to run MySQL during operating system start up. Sample script is shown below and also available for download:

#!/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

In case of our FreeBSD system, the above script should be placed in the /usr/local/etc/rc.d directory, under the name of mysql.sh.
6.1 Summary
Applying the methods described in the article allows us to significantly increase the security of MySQL. By running the database in a chrooted environment, disabling listening on 3306/tcp port and applying strong passwords to users' accounts we can make the database immune to a many of the attacks that would be possible with the default installation.

Although no method will let us achieve 100% security, applying the outlined methods will at least limit attack possibilities from users who visit our web servers with unfair intentions.

Relevant Links

Securing Apache: Step-by-Step
Securing PHP: Step-by-Step

MySQL Website
chrootuid package
About the author

Artur Maj works as a Principal Software Engineer for Oracle Corporation, in the EMEA Mobile, Wireless & Voice Center of Expertise. He is experienced in designing computer systems, performing security audits as well as providing security training. He is also author of many articles and publications devoted to securing computer systems and software against intruders.