Search:  
Gentoo Wiki

HOWTO_Setup_mysql_virtual_hosting


Please format this article according to the guidelines and Wikification suggestions, then remove this notice {{Wikify}} from the article


Contents

Introduction

The purpose of this guide is to install and configure mysql in such a way that virtual hosts are able to each have their own set of databases. I will show how to do this using mysqlmanager and therefore I will not be using the older mysqld_multi methods. This guide will also be one of the first of it's kind to demonstrate efficient and correct use of the new srvdir USE flag as there are no other guides around detailing this.

Warning: (2007-10-08) Current status of srvdir is that it was "Accepted but not implemented".

Neither apache or mysql currently support the srvdir flag.

However, the concept of this HowTo is still valid and worthwhile and it can still be implemented. All that is required is that you manually edit the config files to use the path of your choice, and create the directory structure yourself. (The srvdir flag automates some of this configuration, but the end result is the same)

The Current version of apache is 2.2.6 so you can ignore all the references below to unmasking the (now older) version of apache.

 

Keep in mind that creating one mySQL server instance per domain is going to eat up a lot of resources. This approach would not scale well to large quantities of domains. However for high security applications or developer systems, having separate mySQL servers is essential.

What I did instead of one server per domain, is that I used the techniques presented here to create categories of servers. Thus I have one mySQL server for system functions such as the myDNS server and the admin web. Then I created another mySQL instance for email services, another for forums, and finally a separate mySQL instance that is shared by most of the domains/websites (each website gets exclusive access to a database). IMHO using categories of servers works better for most scenarios YMMV. The techniques presented here are flexible enough that you can do all of the above (categories and domains). But before you start I suggest you give some thought to how you want to organize your databases and what your security needs/performance issues are, versus the amount of memory and cpu that you have available. -- codeslinger.compsalot

Assumptions

This guide assumes that you have not yet installed apache, php, or mysql and will detail installation and configuration of the aforementioned packages. Also, only the newer versions of these packages that support the srvdir USE flag are supported by this guide. Presently this will mean that you will have to unmask apache 2.2.3-r1 because although it is the latest release version from the Apache Software Foundation, it has not been fully tested and marked stable in Gentoo yet.

Packages Used

net-www/apache-2.2.3-r1
dev-lang/php
dev-db/mysql
dev-db/phpmyadmin

Installing

You will first need to make sure that you have either added the srvdir USE flag to /etc/make.conf or added it on an individual package basis in /etc/portage/package.use for apache and mysql.

As you follow this guide, you will be taking advantage of the FHS 2.3 /srv directory. Because in-depth usage of the /srv directory is beyond the relevant scope of this HOWTO, I will not attempt to go into lengthy detail about this other than to show this simple directory structure that you should have when finished with this guide:

  /srv
    /localhost
      /www
        /htdocs
        ...
      /mysql
        /datadir
    /example.com
      /www
        /htdocs
        ...
      /mysql
        /datadir

Apache

Add net-www/apache to your /etc/portage/package.unmask and /etc/portage/package.keywords files. Ensure that the srvdir USE flag is set when running the following command

  # emerge -av net-www/apache

After apache is installed, you should notice that the /srv directory has been created and inside it you should see a default directory called localhost which will have a basic directory structure that you can follow with other virtual hosts that you will create under /srv.

PHP

Now go ahead and install PHP which will be necessary for using phpmyadmin. Be sure to also include the apache2 and mysql USE flags in either /etc/make.conf or in /etc/portage/package.use if you have not already done so

  # emerge -av dev-lang/php

MySQL

Again, make sure that the srvdir USE flag is set when installing mysql

  # emerge -av dev-db/mysql

When this has finished, you will notice that in /srv/localhost a directory called mysql has been added and contains a subdirectory called datadir. This directory will contain your databases for the default localhost domain.

phpMyAdmin

Before installing phpMyAdmin you will need to set the vhosts USE flag so that you can easily install phpMyAdmin into virtual hosts

  # emerge -av dev-db/phpmyadmin

This should have also installed webapp-config which we will configure to work with the /srv directory structure. webapp-config allows you to easily install and maintain web-based applications such as phpmyadmin with a minimal amount of hassle.

Configuring

MySQL Instance Manager

The MySQL Instance Manager is a daemon that makes it very easy to monitor and maintain your various msyql instances. Here I will demonstrate how to configure mysql to use mysqlmanager as well as show you how to set up databases for both the default localhost domain as well as another domain called example.com. For more detailed information about the MySQL Instance Manager, refer to the MySQL documentation.

Creating my.cnf

First we need to set up an appropriate my.cnf file to configure mysqlmanager itself as well as each instance of mysqld that we want to run. In my case, I didn't use anything from the default my.cnf. You can if you would like but make sure that you do not place any instance manager-specific options in a [mysqld] section because if mysqld were started without the mysql instance manager, it would not understand any of those options. I have intentionally not even created a [mysqld] section in this example.

File: /etc/mysql/my.cnf
[mysql.server]
use-manager

[manager]
default-mysqld-path             = /usr/sbin/mysqld
socket                          = /var/run/mysqld/manager.sock
pid-file                        = /var/run/mysqld/manager.pid
password-file                   = /etc/mysql/mysqlmanager.passwd
monitoring-interval             = 2
port                            = 1999
bind-address                    = 127.0.0.1

[mysqld1]
mysqld-path                     = /usr/sbin/mysqld
character-set-server            = utf8
default-character-set           = utf8
user                            = mysql
server-id                       = 1
port                            = 3306
socket                          = /var/run/mysqld/mysqld_localhost.sock
pid-file                        = /var/run/mysqld/mysqld_localhost.pid
log-error                       = /var/log/mysql/mysqld_localhost.err
datadir                         = /srv/localhost/mysql/datadir
bind-address                    = 127.0.0.1

[mysqld2]
mysqld-path                     = /usr/sbin/mysqld
character-set-server            = utf8
default-character-set           = utf8
user                            = mysql
server-id                       = 2
port                            = 3307
socket                          = /var/run/mysqld/mysqld_example.sock
pid-file                        = /var/run/mysqld/mysqld_example.pid
log-error                       = /var/log/mysql/mysqld_example.err
datadir                         = /srv/example.com/mysql/datadir
bind-address                    = 127.0.0.1

When creating your own file, you can create as many [mysqldN] sections as you need for your virtual hosts. Keep in mind, however, that it is important to change the following things for each instance you configure:

If you fail to set those to unique values, the improperly configured instance will fail to start.

Create the password-file

You may notice the password-file option in the config file. You will need to create this and then create a user and password for accessing the Instance Manager via the specified port (1999 in the above example):

  # touch /etc/mysql/mysqlmanager.passwd
  # mysqlmanager --passwd >> /etc/mysql/mysqlmanager.passwd

Create the databases

Note: This is a clever but complex approach, there is a much simpler way. Remember that the mySQL database is just a set of files. By following the normal install process of mySQL you will create a default set of databases. Once those are created, run a single instance of mysql and customize the databases as you wish. then shutdown the mysqld server. Now all you have to do is to copy -a the file set to as many locations as you like. I prefer to create a _template folder with all of the default settings and the database. Then when I want to set up a new site I just copy the entire _template folder to a new name. Tada instant site with a minimum of fuss. -- be sure that the mysql account owns the database folder and files or else the server will fail with strange error messages.


There is a configuration script that comes with the mysql ebuild, however it will only allow you to create the default set of databases under /srv/localhost/mysql/datadir. Go ahead and run it (replacing {version} with the version you have installed) to create the default databases

  # ebuild /var/db/pkg/dev-db/mysql-{version}.ebuild config 

As I could not find a way to reuse the built in config script for other virtual hosts, I have adapted the following script from the one in mysql's ebuild that allows you to do the same thing with your own virtual hosts

File: mysql_vhost_install
#!/bin/bash
## Configure default database structure for the given virtual host
## NOTE: A good portion of this script is taken from code in mysql_pkg_config() as found in the mysql ebuild

VHOST="$1"
DATADIR="/srv/${VHOST}/mysql/datadir"

echo "Setting up default databases for virtual host '${VHOST}'"

# Make sure the data directory actually exists
if [[ ! -d "${DATADIR}" ]]; then
        echo -n "Data directory does not exist, would you like to create it?"
        read -p " [y/n] " create_response

        if [[ ${create_response} == "y" ]]; then
                mkdir -p ${DATADIR}
                echo "Data directory created successfully"
        else
                exit
        fi
fi

# Check for already existing database structure
if [[ -d "${DATADIR}/mysql" ]]; then
        echo "MySQL databases appear to already exist, if not please remove all files from '${DATADIR}'"
        exit
fi

# We now should be safe to go ahead and perform the creation of the databases
echo -n "Please enter a password for the mysql 'root' user"
read -rsp ": " pwd1
echo -en "\nRe-enter password for confirmation"
read -rsp ": " pwd2
echo -en "\n"

if [[ "x$pwd1" != "x$pwd2" ]]; then
        echo "The passwords you endered were not the same"
        exit
fi

echo "Creating the default mysql databases and setting permissions"

mysql_install_db --datadir=${DATADIR} | grep -B5 -A999 -i "ERROR"
chown -R mysql:mysql ${DATADIR} 2>/dev/null
chmod 0750 ${DATADIR} 2>/dev/null

echo "Retrieving timezone data for entry into the database"
TZ_FILE="/tmp/zone_data_${RANDOM}"

mysql_tzinfo_to_sql /usr/share/zoneinfo > ${TZ_FILE} 2>/dev/null

echo -n "Starting mysql daemon..."
sockfile="/var/run/mysqld/mysqld${RANDOM}.sock"
pidfile="/var/run/mysqld/mysqld${RANDOM}.pid"

# Start the daemon
mysqld --skip-ndbcluster --user=mysql --skip-grant-tables --basedir=/usr 
--datadir=${DATADIR} --skip-innodb --skip-bdb --skip-networking --max_allowed_packet=8M 
--net_buffer_length=16K --socket=${sockfile} --pid-file=${pidfile} &>/dev/null &

# Wait no more than 5 seconds for the daemon to start
maxtry=5
while ! [[ -S "${sockfile}" || ${maxtry} -lt 1 ]]; do
        maxtry=$((${maxtry}-1))
        echo -n "."
        sleep 1
done

echo "Updating databases"
mysql --socket=${sockfile} -h localhost -e "UPDATE mysql.user SET Password = PASSWORD('${pwd1}') where USER='root'"
mysql --socket=${sockfile} -h localhost -u root -p"${pwd1}" mysql < ${TZ_FILE}

echo "Shutting down mysql daemon"
kill $(< "${pidfile}")
rm -f "${TZ_FILE}"

echo -e "\nAll done"

Note: If you created a plain [mysqld] section in your my.cnf that uses mysqlmanager configuration options, you will most likely have this script give a bunch of errors for the reasons noted above, therefore I recommend that you name your sections [mysqldN] replacing N with a unique numeric value.

To create a set of databases for example.com you would execute the following

  # mkdir /srv/example.com
  # mysql_vhost_install example.com

Running Instance Manager

Note:

If you have previously added mysql to the default runlevel be sure to remove it because it will conflict with the mysqlmanager.

   /etc/init.d/mysql  stop
   rc-update del mysql


Now that you have the databases configured, add the init script to the default runlevel so that it will run when you boot up the computer and then go ahead and start it

   rc-update add mysqlmanager default
   /etc/init.d/mysqlmanager start

If all goes well you should see the .sock and .pid files corresponding to each instance of mysqld in /var/run/mysqld. If you do not see them or one is missing, check your my.cnf file for errors. You can also check on the status of individual instances by doing the following:

  # mysql -h 127.0.0.1 -P 1999 -u {username} -p
  Enter password:
  mysql> show instances;
  +---------------+--------+
  | instance_name | status |
  +---------------+--------+
  | mysqld1       | online |
  | mysqld2       | online |
  +---------------+--------+
  2 rows in set (0.00 sec)

If any say offline then you should check your configuration.

Apache/PHP

I will not go into detail on configuring apache, but I will show a sample virtual host config file

File: /etc/apache/vhosts.d/10_example_com.conf
<VirtualHost *:80>
        ServerAlias example.com
        ServerName  www.example.com
        DocumentRoot /srv/example.com/www/htdocs/

        <Directory "/srv/example.com/www/htdocs/">
                Options Indexes FollowSymLinks
                AllowOverride None
                Order allow,deny
                Allow from all

                php_value mysql.default_host "localhost"
                php_value mysql.default_port 3307
                php_value mysql.default_socket "/var/run/mysqld/mysqld_example.sock"

                php_value mysqli.default_host "localhost"
                php_value mysqli.default_port 3307
                php_value mysqli.default_socket "/var/run/mysqld/mysqld_example.sock"
        </Directory>
        <IfModule itk.c>
                AssignUserID apache apache
        </IfModule>
</VirtualHost>

The important values are the php_value lines that tell PHP which mysql socket to use for connections with the virtual host. The port numbers and socket path are only examples, the actual values must be the same as what you assigned to the mySQL server instance in the my.cnf file.

phpMyAdmin Configuration

Now we will set up phpMyAdmin to run on a virtual host so that users of the virtual host can access/administer the databases for their site easily.

First, edit the file /etc/vhosts/webapp-config. Set the vhost_root line to the following

  vhost_root="/srv/${vhost_hostname}/www"

If you are using the default webapp-config file, you can just comment the current vhost_root line and uncomment the one above it as it is already set to this value.

Go ahead and run webapp-config to install phpMyAdmin into your virtual host, replacing {version} with the installed version of the phpmyadmin package

  # webapp-config -I -h example.com -d mysql phpmyadmin {version}

Now create the phpMyAdmin database, again replacing {version} with your phpMyAdmin version

  # mysql --socket=/var/run/mysqld/mysql_example.sock -u root -p < /usr/share/webapps/phpmyadmin/{version}/sqlscripts/mysql/{version}_create.sql

To create the configuration file for phpMyAdmin we will use the setup script that comes with phpMyAdmin to do this for you

  # mkdir /srv/example.com/www/htdocs/mysql/config
  # chmod o+rw /srv/example.com/www/htdocs/mysql/config

Browse to http://example.com/mysql/scripts/setup.php Click the Add button under Servers and set the following

  Connection type: socket
  Authentication type: cookie

Now let's manually edit /srv/example.com/www/htdocs/mysql/config/config.inc.php and add the following to the config so that all of the irrelevant databases will be hidden

  $cfg['Servers'][$i]['hide_db'] = '(information_schema|mysql|pmadb|test)';

Finally, move the config file to the parent directory and remove the config directory

  # cp /srv/example.com/www/htdocs/mysql/config/config.inc.php /srv/example.com/www/htdocs/mysql/
  # rm -r /srv/example.com/www/htdocs/mysql/config

See also

Retrieved from "http://www.gentoo-wiki.info/Virtual_hosting_using_Apache_and_MySQL"

Last modified: Tue, 19 Aug 2008 07:41:00 +0000 Hits: 17,420