Gentoo Tools

(Almost) automatic apache2 vhosts configuration from MySQL

I use this to make it easier to manage my virtual hosts. This is still in the early stages of development and could be done a lot better but this should help some people out there. This is for apache2 on Gentoo with a new enough version that you have httpd.conf (not apache2.conf and commonapache2.conf). Steps:
  • Make sure "Include /etc/apache2/vhosts.d/*.conf" is uncommented at the bottom of the /etc/apache2/httpd.conf
  • Make sure "-D DEFAULT_VHOST" is in your APACHE2_OPTS in /etc/conf.d/apache2
  • Make the db tables:
    CREATE TABLE `apache_hosts` (
        `server_name` varchar(200) NOT NULL default '',
        `server_admin` varchar(255) NOT NULL default '',
        `options` varchar(255) default NULL,
        PRIMARY KEY  (`server_name`)
    )
    CREATE TABLE `apache_redirects` (
        `server_name` varchar(200) NOT NULL default '',
        `redirect` varchar(200) NOT NULL default '',
        `permanent` int(11) default '1',
        PRIMARY KEY  (`server_name`)
    )
    
  • Insert some hosts or redirects. samples:
    INSERT INTO `apache_hosts` VALUES('example.com','[email protected]','AllowOverride ALL');
    INSERT INTO `apache_hosts` VALUES('foo.example.com','[email protected]','');
    INSERT INTO `apache_redirects` VALUES('bounce.example.com','http://example.com',1);
    
  • Make the appropriate folders and make sure apache has read access to them and write access to the logs:
    /data/sites/example.com/www/index.html
                           /logs/
               /foo.example.com/www/index.html
                               /logs/
    
  • Run this perl script to generate the vhosts files. Replace the database login information with your own. It will error the first time, but be quiet the second time each time you add a host. This is ok.:
    #!/usr/bin/perl -w
    #
    # vhosts-regen.pl
    #
    #regenerates apache vhost config files from database
    
    use strict;
    use DBI;
    
    my $confdir = "/etc/apache2/vhosts.d/";
    
    my $db = DBI->connect("dbi:mysql:database=DATABASE;user=USER;password=SECRET")
    or die("Couldn't connect to database: $DBI::errstr\n");
    
    my $sql = "SELECT * FROM apache_hosts";
    my $statement = $db->prepare($sql)
            or die("Couldn't prepare query '$sql': $DBI::errstr\n");
    $statement->execute()
            or die("Couldn't execute query '$sql': $DBI::errstr\n");
    
    my ($server_name, $server_admin, $options);
    $statement->bind_columns(\$server_name, \$server_admin, \$options);
    
    while ($statement->fetch()) {
            if (!($server_name eq "")) {
                    system('rm "'.$confdir.'/'.$server_name.'.conf"');
                    open(FILEOUT, ">$confdir/$server_name.conf");
                    print(FILEOUT '
            ServerName "'.$server_name.'"
            DocumentRoot "/data/sites/'.$server_name.'/www/"
            ServerAdmin "'.$server_admin.'"
            ErrorLog "/var/log/apache2/error_log"
            ErrorLog "/data/sites/'.$server_name.'/logs/error_log"
            CustomLog "/var/log/apache2/access_log" combined
            CustomLog "/data/sites/'.$server_name.'/logs/access_log" combined
            '."\n");
                    if (defined($options)) {
                            print(FILEOUT "         $options");
                    } else {
                            print(FILEOUT "         Options None");
                            print(FILEOUT "         AllowOverride None");
                    }
                            print(FILEOUT '
                    Order allow,deny
                    Allow from all
            
    
    ');
            }
    }
    close(FILEOUT);
    
    $sql = "SELECT * FROM apache_redirects";
    $statement = $db->prepare($sql)
            or die("Couldn't prepare query '$sql': $DBI::errstr\n");
    $statement->execute()
            or die("Couldn't execute query '$sql': $DBI::errstr\n");
    my ($redirect, $permanent);
    $statement->bind_columns(\$server_name, \$redirect, \$permanent);
    
    while ($statement->fetch()) {
            if (!($server_name eq "")) {
                    system('rm "'.$confdir.'/'.$server_name.'.conf"');
                    open(FILEOUT, ">$confdir/$server_name.conf");
                    print(FILEOUT '
            ServerName "'.$server_name.'"
            Redirect ');
                    if ($permanent != 0) {
                            print(FILEOUT "Permanent");
                    }
                    print(FILEOUT " / ".$redirect."
    
    ");
            }
    }
    close(FILEOUT);
    
  • tell apache to reload: "apache2ctl graceful" (this tells apache to load all new threads/processes with the new config and kill off old threads as soon as they are done processing requests)
  • Check your error logs for any problems, fix em!
  • Any time you want to add another host or redirect, insert into mysql, rerun the script, and reload apache

comments powered by Disqus