Automatic MySQL Monthly Backups

As the number of users on my systems increase, so do the number of databases. I do a MySQL monthly backup at the first of each month just in case of anything happening that could corrupt the data. Well, this starts to be a time consuming process when the number of databases increases.

So, I thought I would share with everyone a quick solution that I have done for automated monthly backups of the SQL databases.

#!/bin/bash
# Script will backup databases
directory=$(date +%m-%d-%Y)
cd "/home/www/Database Backup"
mkdir $directory
cd $directory
mysqldump –user=USER_HERE –password=PASSWORD_HERE –databases DATABASE_NAME > DATABASE_NAME.sql

So, lets dissect this to make it easy to understand.

directory=$(date +%m-%d-%Y)

This line creates a variable called directory. It will then assign the month, day, and year to this. Since I create a folder each month to hold the backups, this is how I set it up. So, the directory variable will contain "12-01-2008" for December 1st, 2008.

The next couple of lines:

cd "/home/www/Database Backup"
mkdir $directory cd $directory

will move the current directory to where I store the database backups. Then, it will create (mkdir) the new directory name by using "mkdir $directory" as listed above. since "directory" is holding 12-01-2008, this is the same as "mkdir 12-01-2008". By using the variable, however, this will allow the script to change this each time the script is run instead of putting a hard-coded directory. Lastly, it will then go into the new directory that was created.

Lastly, the last bit of code:

mysqldump –user=USER_HERE –password=PASSWORD_HERE –databases DATABASE_NAME > DATABASE_NAME.sql

mysqldump is a program that comes with the MySQL server that will allow you to dump the contents of a database into a file. This way, if you ever need to restore a database, it is just a matter of dropping all the information in the database and then importing it from this file.

For –user=USER_HERE, you need to put your MySQL username in here. Most likely if you are the owner of the system, you will have to use –user=root because the root user has full access to all the databases on the server. Or, you may have another user that simply has read-only access to all of the databases, such as a backup user. In that case, use something like –user=backup. Again, this all depends upon what username you have.

For –password=PASSWORD_HERE, this is where you will put in the password of the user you just specified beforehand.

For –databases DATABASE_NAME, you will list one or more databases after this. So, you could have –databases database1 database2 and so on. However, I have created one mysqldump line per database that I have, and only listed that database after it – that way there is one SQL dump file per database – instead of all the databases being in one file.

Lastly, the > DATABASE_NAME.sql is the name of the file you wish to save it as. So, lets give a quick example of one if you are using user "backup" and password "testing" with database name of database1:

mysqldump –user=backup –password=testing –databases database1 > database1.sql

Now, if you have three databases, database1, database2, and database3 – and you want each of them in an individual file, there are then three of the above lines:

mysqldump –user=backup –password=testing –databases database1 > database1.sql
mysqldump –user=backup –password=testing –databases database2 > database2.sql
mysqldump –user=backup –password=testing –databases database3 > database3.sql

So, if you have 20 different databases and want each in an individual file, you will have 20 different lines where the name of the database and the name of the file saved to is different. If you don't mind having all of your databases backed up into one file, the following line will put all three databases (like above) into one file named database-backup.sql:

mysqldump –user=backup –password=testing –databases database1 database2 database3 > database-backup.sql

The Greening of IT

As an information technology professional, I have been attempting to find different ways to save energy. At my full time job, I manage a center with over 575 computers. That is quite a lot of power. Originally, we had some PCs that were about five years old (from 2003) with the big CRT monitors. In the past few months, I have fully updated all of the computers from those old clunkers to new ones with flat panels. I used a load cord to see how much power they pulled – and together they pull about 94 watts of power even when just first starting up. That is quite a difference from the old CRT and PCs where the CRT by itself pulled that, and the computer pulled up to 120 watts – which fluctuated.

So what other way could you save energy with IT? Well, turn the computers off, of course. At any given point, there may only be 400 of the 575 computers used during the day employee shifts. However, each employee has their own space so all 575 computers are actually used at different points of the day. I found a freeware program online that does exactly what I wanted it to – shut the PCs off after a certain period of idle time. As an example, I have set the program to automatically shut off the computer after a period of an hour of non-use.

So 575 computers on 24 hours a day would consume 1,297 kilowatts per day. Wow, that is a little less than what we use at our house on a monthly basis! Now, lets take an average of 400 computers on for 10 hours a day (8 hour shift plus an hour afterwards for the shutdown to occur). This drops usage down to 375 kilowatts per day. What a huge difference that makes. That is a 71% savings on electric usage from the computers alone in the building.

Could you imagine how much that would save a company on a yearly basis? Lets just say that a kilowatt is 10 cents with all taxes and everything included. Each day, it would cost $129.70 with all computers staying on 24 hours a day. That is $47,340.50 a year just in energy costs for running computers. Now, running an average of 400 computers at only 10 hours a day, it would cost $37.50 a day for energy, or $13,687.50 a year. While this isn't much money to a company, that sure does pay for an employee's salary for a year!

Passed the CompTIA Security+ Test Today

After months upon months of studying, I have finally taken and passed the CompTIA Security+ test today.

I started the process of using NetG (now Skillsoft) online training materials that are provided through my company over a year ago. Because I was taking classes at the University of Illinois each summer, I was never able to fully complete the training materials during the three months. Finally this year, since I am not taking summer classes, I was able to complete all the training materials. In addition to the Skillsoft materials, I used a website that has a nice online learning format for the materials. This is provided by Certiguide. This material is a little bit out of date, but for the most part, the information was correct and good for the test.

I had to drive up to Bloomington at a testing center there since there is not one in the city in which I live. After about 45 minutes, I walked away with a score of 890/900 – apparently I only missed two of the one hundred questions. Originally I thought the test was out of 1,000 points, but this was not correct after reading the summary afterwards.

What is next for me? I am not quite sure yet – as I am debating whether it is really worth the time and money to go after the Linux+ and Server+ exams. I do like the CompTIA tests in the fact that they never expire – unlike other vendor certifications like Cisco.

SNMP Segfaults in Ubuntu Hardy 8.04 – Revisited

After a previous comment by an individual who visited the site, I did a little more research and work on the NET-SNMP 5.4.1 issue where it causes a segfault in the syslog in Ubuntu Hardy 8.04. SNMP then stops working until I restart the service.

I started to notice patterns – like on weekends, SNMP would not segfault but it would always happen on weekdays. It also always seemed to happen in the morning around 8 to 9 am.

After pondering – I remembered that I do work during the weekdays and get to work around 8 am. I then boot up my laptop and connect to the server. Shortly after is when I notice SNMP dies.

Today at work, I locked myself from remote access. Funny enough – no SNMP segfault today! I went into my Cacti graph program through Firefox and all of the graphs seemed fine. After a few hours, I checked Cacti again and still no spots in the graph where no data was attained.

So, it is my belief that there appears to be some sort of conflict with SNMP and PoPToP VPN.

As you recall, Net-SNMP 5.4.1 no longer allows you to use the 'exec' directive and requires you to use the 'extend' directive when adding additional information into SNMP that can be pulled from the system. Maybe afterall, there isn't a problem with this as I last expected. I last reported that if I commented out the 'extend' lines I had added, that the problem didn't show up. So, I still have some research to do on that front. Maybe the two services try to overwrite memory or something?

Still hard to tell.

Copying Linux to a New Server

I had the need to copy my current Linux server to another one for getting fault tolerance back in the environment.

I originally used Ghost and ghosted the current server to a USB stick. This past weekend, I went to Paris and attempted to Ghost the server there. However, big problems started to occur because the servers have the same hard drive, but different motherboards. When I rebooted the server, a Grub Error 12 occurred. I was dead and couldn't go anywhere from there and had to bring it back to the main location to get going.

Last night I used a different program called Paragon Disk Backup. It took a considerable amount of time longer to create the image through Paragon. But, I put the image on the Paris server, rebooted – and then I got Grub Error 12. I followed some instructions online about updating the /boot/grub/menu.lst and /etc/fstab. The UUID of the hard drive was indeed different than what was in these files. I updated the UUID of the new hard drive in these two files, rebooted, and it still showed Grub Error 12.

I decided after spending enough time with this, that it would be best to simply create a tar file of the full Linux installation. On the new server, Ubuntu was installed from scratch off of the CD. After the installation was completed, I used the Ubuntu Live CD to delete all materials on the hard drive except the two files listed above (backed those up to the desktop). I untarred the linux installation onto the hard drive, put the two files back, rebooted, and I was in business!

A few things did have to be changed, however. I had to get the ethernet card updated as it was showing as eth1 instead of eth0. I updated a file with the MAC address of the new card, rebooted, and it was then fixed.

The next step will be to get the replication all setup between the two. I found a program called Unison. Unison is a bidirectional synchronization application that will sync two replicas. One line through a automated job will sync the two replicas up together. It will delete files on one that were deleted on the other, create files on one that was made on the other, and update files between them. Unison uses an algorithm like Rsync to only replicate the bits of data that have changed since the last update. Therefore, if a very large file is updated, it will only replicate the bits of data from that file to the other replica – therefore saving precious bandwidth.

Upgrade from Ubuntu 7.10 to Hardy 8.04

I have just upgraded both servers from Ubuntu Gutsy 7.10 to Ubuntu Hardy 8.04.

For the Decatur server, the process was very straightforward except one caveat. For the Paris server, it was pretty difficult.

Currently the Decatur server is the "live" server and is doing all websites, secure sites, e-mail, DNS, and the works. Paris is a backup mail server and hosts the VMWare Windows 2000 Server that is is running the old WebMail until the end of this month.

With the Decatur server, the only thing that caused a small headache was a change in snmpd. With this new version, the "exec" feature was deprecated and I had to change my lines over to "extend". Welp, the MIB OID grew to a huge number after this change. This, in turn, required me to update some data items within the graphing utility I use – Cacti.

The MIB OID used to be:
.1.3.6.1.4.1.14464.25.x
Now, it turned into:
.1.3.6.1.4.1.2021.25.4.1.2.12.101.120.105.109.95.112.101.114.102.46.112.108.x

I also noticed that snmpd would just unexpectedly die and I would start getting "Timeout: No response from localhost". I haven't had this problem now with the Decatur server after a few days – and I haven't updated anything in the way of snmpd except the above changes.

The Paris server was a bit more complex. With Hardy 8.04, vmware-server is not available through the repositories and upon upgrading to the new version of Ubuntu, it removes vmware-server! So, I had to follow a few setup pages to install vmware-server from source. It then would not start the virtual monitor and would fail on service startup. Turns out, there is a problem in one of the source files where two commas have to be removed. After making this change, I re-ran the vmware-config.pl file and I finally was able to get it to go after a few hours.

MySQL also threw a fit. I had set a port for TCP/IP instead of just using a socket connection. In the first place, the mysqld socket file disappeared from the /var/run/mysqld directory so I had to simply recreate that. I then had to go in and comment out the line that opened the TCP/IP port. This finally got MySQL going.

I now have the same problem with snmpd on the Paris server as I did the Decatur server. snmpd simply dies and no longer listens for requests. This time, it keeps terminating itself every 15 minutes or so. I still have yet to find a fix for this.

I was finally able to get the error out of the syslog. When SNMP dies, I get error: snmpd[7872]: segfault at 0000000c eip b7c4e79b esp bfe63940 error 4

However, I cannot find anything out online about this issue. Sure would be nice if I could roll back to the previous version of SNMP that seemed to work.

HOWTO: MySQL User Authentication in Apache2 on Ubuntu Gutsy 7.10

This small HOWTO ties in with the previous two HOWTOs on getting MySQL user authentication setup in Apache2. My goal for my systems was to have one location for user authentication for e-mail, FTP, and web site access via a MySQL database so I did not have to create individual users on the Linux system itself. Another reason for this is because I run an replicated environment and only making the change on one database is nice so that it automatically replicates to the other server with the new information.

Again, please note that the MySQL database that I am referencing in this HOWTO is based on the last two HOWTOs where I used the same MySQL database. You can read further down about setting up E-Mail, webmail and others to get the MySQL database file that I have used. The MySQL database is called “horde” and the table that contains the user information is “horde_users”.

Ubuntu Linux Gutsy 7.10 has a problem with the libapache-mod-auth-sql through the aptitude repositories and will not work. I attempted this first and would get “user xxx not found: /locationofaccess”. Follow these steps to install the mod-auth-sql from source:

wget http://heanet.dl.sourceforge.net/sourceforge/modauthmysql/mod_auth_mysql-3.0.0.tar.gz
wget http://www.bleb.org/software/mod_auth_mysql-3.0.0-apache-2.2.3.patch
tar zxf mod_auth_mysql-3.0.0.tar.gz
apt-get install apache2-prefork-dev libmysqlclient15-dev; apt-get –purge remove libapache2-mod-auth-mysql
cd mod_auth_mysql-3.0.0
patch < ../mod_auth_mysql-3.0.0-apache-2.2.3.patch
sed -i ‘s|#include <mysql.h>|#include <mysql/mysql.h>|’ mod_auth_mysql.c
apxs2 -c -lmysqlclient -lm -lz mod_auth_mysql.c
apxs2 -i mod_auth_mysql.la
echo ‘LoadModule mysql_auth_module /usr/lib/apache2/modules/mod_auth_mysql.so’ > /etc/apache2/mods-available/auth_mysql.load
a2enmod auth_mysql

Now, in my setup, I wanted to have all the directives directly in the httpd.conf file and did not want to use .htaccess files. To find the complete list of AuthMySQL directives, please see the source information listed below.

In your httpd.conf file, you will have items that start with <Directory> where you want to require authentication for your users. Here is a copy of what I placed in mine:

AuthType Basic
AuthName “NAME_OF_REALM”
AuthUserFile /dev/null #tells apache not to use a passwd file
AuthBasicAuthoritative Off
AuthMySQLEnable On #enables the mod-sql authentication
AuthMySQLAuthoritative On #tells apache to only authenticate by MySQL
AuthMySQLDB horde #the name of the database that contains information
AuthMySQLUser horde #the user to use to access the database
AuthMySQLPassword PASSWORD_HERE #type the user’s password to access the database
AuthMySQLUserTable horde_users #the table that contains the user information
AuthMySQLNameField user_uid #the field in the table that holds the username
AuthMySQLPasswordField user_pass #the field in the table that holds the password
AuthMySQLPwEncryption none #the type of encryption the password is in
require user USERS_HERE #the users that are allowed to access this directory

If all is well, you should be able to restart the Apache server and you are ready to go!

sudo /etc/init.d/apache2 restart

Sources:
Bug #150649 in libapache-mod-auth-mysql (Ubuntu): “gutsy does not have a working apache+mysql authentication solution
mod-auth-mysql Apache Directives

HOWTO: Virtual Domains with Apache2, MySQL, Dovecot (with Quotas), Horde, Vacation, Passwd, imp4, K

This HOWTO will be to setup a full virtual domain mail server using Exim4 as the MTA and SMTP server, Apache2 as the web server, MySQL as the SQL server that will do authentication and hold user settings so no local users have to be created, Dovecot for IMAP and POP3 services, Horde for the user’s web-based interface, imp4 for the webmail portion that ties in with Horde, Kronolith for the user’s calendar, Turba for the user’s address book, Vacation to allow the user to set out of office reply messages, and Passwd to allow the users to change their password when they want to.

Note that this setup is as simple as I can get it; it does not include any kind of SSL security or password encryption on the databases.

sudo apt-get apache2 mysql-server exim4 phpmyadmin

This will install the Apache2 web server. Configuration files for Apache are in /etc/apache2. This will install the MySQL server. Configuration files for MySQL are in /etc/mysql This will install the Exim4 MTA/SMTP server. Configuration files for Exim4 are in /etc/exim4. This will install the web-based PHPMyAdmin interface to allow you to make changes to your MySQL databases.

After installation, it would be a good idea to set a root password for your MySQL server to help secure it. To do this:

mysqladmin -u root password “PASSWORDHERE”

We will get into configuration the Exim4 MTA at a later time when everything else is installed. For now, lets go on to installing the POP3 and IMAP servers. POP3 will allow people that wish to use Outlook or another e-mail client to check their e-mail and store their e-mail on their computer instead of keeping it on your server. IMAP will be the protocol that Horde will use to pull data from the user’s mail folders and show it in the webmail interface.

sudo apt-get install dovecot-common dovecot-imapd dovecot-pop3d

The next two lines will modify the dovecot and Debian-exim user accounts and add them to the “mail” group. This will be needed in order to give dovecot and Exim read/write permissions to the user’s mailboxes to deliver mail and allow the webmail and POP3 services to delete and fetch mail for the user.

usermod -a -G mail dovecot
usermod -a -G mail Debian-exim

After you run the above line, navigate and open /etc/dovecot/dovecot.conf

In this file, enter, at a minimum, the following lines.

base_dir = /var/run/dovecot
protocols = imap pop3 # tells dovecot to use the imap and pop3 daemons
listen = * # tells dovecot to listen on all IP addresses
disable_plaintext_auth = no # allows dovecot to accept plaintext passwords
syslog_facility = mail # will allow dovecot to put log information in the /var/log/mail.err log
mail_privileged_group = mail
first_valid_uid = 8 # this is the uid number for the mail user – only this user is needed for access
last_valid_uid = 8
protocol imap {
login_executable = /usr/lib/dovecot/imap-login
mail_executable = /usr/lib/dovecot/imap
mail_plugins = quota imap_quota
mail_plugin_dir = /usr/lib/dovecot/modules/imap
login_greeting_capability = no
imap_client_workarounds = outlook-idle
}
protocol pop3 {
pop3_uidl_format = %08Xu%08Xv
}
auth default {
mechanisms = plain # sets the default and only authentication mechanism to plaintext
passdb sql {
args = /etc/dovecot/dovecot-sql.conf # indicates the password database information is in this file
}
userdb sql {
args = /etc/dovecot/dovecot-sql.conf # indicates the user database information is in this file
}
user = root
plugin {
quota = maildir
}

After you are done with this file, it is now time to edit the /etc/dovecot-sql.conf file

driver driver = mysql
connect = dbname=horde user=horde password=PASSWORD_HERE host=localhost
default_pass_scheme = PLAIN
password_query = SELECT user_uid as user, user_pass as password FROM horde_users WHERE user_uid = ‘%u’;
user_query = SELECT uid, gid, home, maildir, concat(‘maildir:storage=’, quota) AS quota FROM horde_users WHERE user_uid = ‘%u’;

It is important to note the following items with th above items that should be in the dovecot-sql.conf file.

dbname is the name of the database. In this case, I simply left this as easy as possible to go with the Horde database files. I then logged into PHPMyAdmin and created a user named “horde” which will be used to access the database. You must then enter a password where I have written PASSWORD_HERE.

Now, you are all done with the Dovecot IMAP and POP3 setup!

sudo /etc/init.d/dovecot restart

 The next portion will install the needed items for the Horde user portal which will include imp4 webmail, the turba address book, the kronolith calendar, the vacation out-of-office reply mechanism, and the passwd plugin to allow the users to change their password. Note that more items need to be installed, which are all listed below.

sudo apt-get install libapache2-mod-php5 php-pear php5-dev imagemagick aspell-pt-pt libmagic-dev
make
pear install –onlyreqdeps Log Mail Mail_Mime DB File Date Net_SMTP
pecl install fileinfo
sudo apt-get install horde3 imp4 kronolith2 turba2 sork-vacation-h3 sork-passwd

 If you get any prompts during the above lines that ask if you want to install libc-client without Maildir support, choose yes.

The next step will be to create an Alias in the Apache2 configuration to allow you to get to the website.

Navigate to /etc/apache2/conf.d. In there, create a new file called horde3.conf.

In the horde3.conf file, put the following lines:

# horde3 default Apache configuration
Alias /horde3 /usr/share/horde3
<Directory /usr/share/horde3>
DirectoryIndex index.php
</Directory>

Save the file and exit. This will allow you to get to Horde when are done with the setup. Restart Apache2 by:

/etc/init.d/apache2 restart

The next commands will allow the www-data account to make config changes when you are ready to set Horde up by the web-based system:

chown -R www-data.www-data /etc/horde
chown -R www-data.www-data /usr/share/horde3

It is time to now create the horde database. Because I made my horde database in quite a different manner and added rows, I have taken the opportunity to provide you this file here in a moment.

Before importing this file, go to your phpmyadmin site (http://<ip_of_ubuntu_server>/phpmyadmin) and then login with user root and your password you previously set. After logging in, there will be a blank field on the right frame to create a database. Create a new database simply named ‘horde’ and hit the Create button.

After this, click back to the home screen and choose Privileges. In there, you will need to create a new user that will have access to this database. In my example of dovecot-sql.conf, I used the user of “horde” and you may do the same. Create a new user of your liking and set a password. Ensure that you also put this password in the dovecot-sql.conf file where I put in PASSWORD_HERE. At this point, DO NOT assign any privileges to the user and just hit “Go” at the bottom of the page. You will then be greeted with the next page. Go to the area where you can pull down the box and choose the name of the database that you chose. The page will then automatically load and check all the boxes for privileges for the database and finish.

Whew! After you have finished, it is now time to import the rest of the database. There are two ways to do this, either through the Linux command prompt using the mysqladmin utility or import it in PHPMyAdmin.

If you wish to use the PHPMyAdmin option since we are already there, down the file here:

http://www.bsntech.com/downloads/horde3.sql

In PHPMyAdmin, click the drop-down box on the left pane and choose the database you just created. In the right page that loads, there will be an Import tab. Click on it and then hit Browse and selet the horde3.sql file you just downloaded and hit Go. You are done importing the database!

For Command line:

wget http://www.bsntech.com/downloads/horde3.sql
mysqladmin -u root -p horde < horde3.sql

It will then prompt you for your root username. Enter that and the import should now be done.

**It is important to note that the SQL file – if you downloaded from me – is a custom SQL file that includes all the options for all the features I detail in this HOWTO.

Next, set Horde to identify logins using your MysQL database instead of LDAP. edit /etc/horde/turba2/sources.php.

Search for the line “if (Util::extensionExists(‘ldap’)) {“. Delete it and all the lines up until and including the “}” character after the line “// End LDAP check.”. SQL configuration is before this.

Don’t delete the section “preferences-based address book” that is after it. Then, delete the section begining with “IMSP based addressbook” and all the rest of the file (it’s for Korba servers).

Now, it is time to make changes for imp4. Edit /etc/horde/imp4/servers.php file.

Search for $servers[‘imap’] and make these changes:

set server to localhost
set maildomain to your main mail domain (will be the default)
set smtphost to localhost

You can then delete the rest of the file that starts with the line $servers[‘cyrus’] for less confusion.

Let’s get the passwd portion setup to allow the users to change their password within Horde. Copy the file /usr/share/horde3/passwd/config/backends.php.dist to /usr/share/horde3/passwd/backends.php.

Open the backends.php file and delete everything up until the $backends[‘sql’] line which is near line 247. Under this section, set your options for the SQL server and password policy.

set hostspec to ‘localhost’
set username to the username you allowed dovecot to connect with above; I used the username of horde
set password to the password for the mysql user you created for dovecot as well; again, this would be the password I set for the user horde
set encryption to ‘plain’
set database to the name of the database; above we called it ‘horde’
set table to the name of the table that contains the users in the database. In my SQL file, it is called ‘horde_users’ set user_col to the name that holds the user’s e-mail address(their login). In my SQL file, the field that holds this is ‘user_uid’ set pass_col to the name that holds the user’s password. In my SQL file, the field that holds this is ‘user_pass’ set show_encryption to ‘false’ if not already done so.

Scroll down to the line that contains ); (which should be just before the line that has $backends[‘vmailmgr’].

Delete all of the lines AFTER the line with the );

Save and close the file.

The Vacation feature will be setup through the web-based configuration – which we are ready to do now!

Now, if everything went well, we should be ready to go to the web-based horde. Go to

http://<ip_of_ubuntu_server$gt;/horde3

Click on Administration and then Setup. Click the Horde Application.

Database Tab:
Database back-end is Mysql (mysqli)
Database server: localhost
Username to connect to database: This will be the username created above that you used in dovecot and in the passwd portion of the setup
Password to connect with: This will be the password for the username of the above database user
Database name: horde

Authentication Tab:
Administrator users: Administrator, and then type in your e-mail address that you will use on this system yourself
What backend should we use for authenticating users to Horde: Let a Horde application handle authentiation
The application which is providing authentication: imp

Preferences System Tab:
What preferences driver should we use: SQL Database
Driver configuration: Horde defaults

DataTree System Tab:
What backend should we use for Horde DataTree storage: SQL Database

Mailer Tab:
What method should we use for sending mail: Use a SMTP Server
The server to connect to: localhost
The port to connect to: 25
The local hostname / domain: localhost

Virtual File Storage Tab:
VFS: Files on the local system
real filesystem: /tmp

Image Manipulation Tab:
enter: /usr/bin/convert

Menu Settings Tab:
Should we always display the Horde frameset: Check the box

Now click Generate Horde Configuration

If you indeed set the permissions on the files so that www-data owned them, they should have set. If not, you will have to make sure to set the permissions on the files again per the above chown -R www-data:www-data lines in the instructions and hit Generate Horde Configuration again.

Next is Mail (imp)
location for the binary for spelling: /usr/bin/aspell

You may look around and change any other settings you see fit. Then after you are done, click the Generate Mail Configuration button.

Next is Calendar (Kronolith)
What storage driver should we use: SQL
Database table: kronolith_events
What Free/Busy driver should we use: SQL
Database table: kronolith_storage
server name from which reminder e-mails should be sent: localhost
e-mail address from which reminder e-mails should be sent: set this to whatever you would like
Click the Generate Calendar Configuration button.

Next is Address Book(turba2)
Simply click the Generate Address Book Configuration

Next is Password (passwd)
make changes here as you would like and click the Generate Password Configuration

Lastly is Vacation
The driver to use: SQL
Database table: horde_users
Column which contains user names: user_uid
Column with passwords: user_pass
Column with vacation message: vacation
Column with vacation subject: vacation_subject
Vacation message yes or no: vacation_enabled (this determines if the reply feature is on or off for the account)
Should we log the user automatically: Yes, with the full username
The encryption to use to store the password in the table: plain
Change any other settings you would like and hit the Generate Vacation Configuration

You are done with Horde setup! To make sure that these configuration files cannot be changed over the Internet, make sure to run the following lines. If you want to make changes in the future through the web interface, you will need to follow the above code to chown www-data.. bla bla.

chown root.root -R /usr/share/horde3
chown root.root -R /etc/horde
chmod -R 755 /usr/share/horde3
chmod -R 755 /etc/horde

Now, I would advise you at this time to go into PHPMyAdmin, open the database you created (Horde in the case of this documentation) and then click the horde_users table in the left panel. Click the Insert tab at the top of the right frame and enter your information as I described in the Horde configuration where you needed to put your e-mail address in. This will allow you to get in and change the administration and configurations later on with your account. Let me go through all of the fields in the horde_users table here.

id – simply a field that will automatically generate the next number in sequence to give each line a differentiating figure.
user_uid – this will be the field where you will enter the FULL e-mail address of the user. This will be the user’s user id for logging into the Horde system.
user_pass – this will be the user’s PLAINTEXT password; so type in it as you would but do not encrypt it. There are other HOWTOs on how to setup crypted passwords; I wanted to attempt to make this howto as easy as possible without encryption or SSL certificates.
user_soft_expiration_date – I don’t use this for anything
user_hard_expiration_data – I don’t use this for anything
uid – this is the mail user’s user number on the system. This should ALWAYS be set to the number 8
gid – this is the mail user’s group number on the system. This should ALWAYS be set to the number 8
name – this will be the actual name of the user
home – this will be the user’s home directory. NOTE: This should be the EXACT same directory as the maildir (listed below) except without the /Maildir attached. Otherwise, mail will not be delivered correctly (took me a while to figure this one out).
maildir – the directory where the user’s mail will be stored at.
quota – this is the quota the user should receive in kilobytes. Simply type in 10240 for a 10 MB limit
status – this should ALWAYS be set to User
vacation – this will hold the user’s vacation response message
vacation_enabled – this will hold a ‘y’ if this feature is enabled, a ‘n’ if not
vacation_subject – this will hold the subject of the vacation feature (I do not use this in the config so that Exim may reply with “RE: Out of Office <Subject of the message the user sent>

OK, so lets create a new account with this information now.

user_uid: Type in the e-mail address that you have hosted on your system. For every person that has an e-mail address, you will need to create a new row in the database with this information. For example, this may be notreal@mydomain.com
user_pass: The password for this user’s e-mail account
name: Type in the name of the user so you know who they are.
home: Type in the user’s home mail directory. In the case on my system, I put all e-mail accounts in the /home/mailboxes directory. So, for the example above, you should use /home/mailboxes/notreal@mydomain.com
maildir: Type in the user’s home mail director AND add on Maildir to the end. For example – /home/mailboxes/notreal@mydomain.com/Maildir
Quota: Type in what you want to set for a quota on their account. Typing nothing will have no limit. To find this, use a calculator to type in the number of megabytes and multiply it by 1024 – this number will be in kilobytes

This is all that needs set for a user. Horde will use the other fields dynamically (such as vacation fields) for those purposes.

Let us create a new directory on the server now to allow your mail to be delivered to it. This will need to be done for EACH user you plan to setup on your system.

Using the user example listed above, I need to create a directory for notreal@mydomain.com and the Maildir under it.

First, make sure that you main mailboxes directory is setup (again, this is using the example directory above):

sudo mkdir 0700 /home/mailboxes

Now, lets make the user’s directory:

sudo mkdir 0700 /home/mailboxes/notreal@mydomain.com

And create the Maildir directory:

sudo mkdir -m 0700 /home/mailboxes/notreal@mydomain.com/Maildir/{cur,new,tmp}

And then give the mail user full permissions:

sudo chown -R mail.mail /home/mailboxes *

**It is important to note here that the mail user MUST own ALL of the folders under /home/mailboxes. Otherwise, Exim will not be able to deliver the mail to their accounts and Dovecot will not be able to fetch the mail via POP3 or IMAP for webmail.

OK, so you now have your user account setup. Go back to your Horde website in another window and type in your e-mail address as your username and your password. Make sure the site loads and that you have the Administration feature on there – as long as you gave yourself those privileges in the Horde configuration.

Now, let me explain some of the other tables in the database and walk you through those. After I explain these, we are left with only configuring Exim and you will be done!

Click on the Aliases table. This table will simply list the Aliases for your users. For example, I have several other e-mail addresses that I want delivered to my mail account. In order to do this, you list each of these aliases here and where the mail should be delivered.

Alias – type in the e-mail address that is an alias to the user’s login ID
login – type in the users Horde login here (their main e-mail address they use to login to their mail account)

Don’t worry about the type field as I haven’t a use for this. Just ensure it is always set as domain.

Now, the last table to explain is the domains table. This table will hold all the domains that you locally provide e-mail for or do e-mail relaying for.

Domain – Enter just the domain part. In my case, I have several domains I do hosting for. You will create a row for each of these.
Type – Choose either local or relay. If set to local, that means that you will hold e-mails for this domain. For relaying, it means that you will accept e-mail for this domain, but then send them back out.
Relay_Host – You will use this only for the type where you relay messages. Enter the mail server of where these mails should be immediately delivered to.

So, let’s take a look back. You have the Apache web server done, you have the MySQL and database done, you have Dovecot POP3 and IMAP done, and you have the Horde web portal done that includes e-mail, calendar, address book, password changes, and out of office responses. Now, the last piece is to get your Exim mail system setup to allow you to do a number of things. We will setup SMTP-AUTH to do authentication by your MySQL database for your users – so external users using POP3 can send e-mails out through your server. We will also set it up so Exim can determine whether to send an out-of-office reply based on the MySQL database, and to deliver or relay mail based on your Aliases and Domain tables.

All of this configuration will take place in the /etc/exim4/exim4.conf.template file if you chose to use a flat-file system instead of the other system of configuration.

Before we go into the more complicated file, there may be another file that you used for setup. If so, lets get that taken care of quickly. This file is /etc/exim4/update-exim4.conf.conf

dc_eximconfig_configtype=’internet’
dc_local_interfaces=’127.0.0.1′ # this will be the IP addresses of your system; they will be separated by semicolons
dc_relay_domains=” # set this as nothing because the domains table will figure out what to relay
dc_localdelivery=’mysql_delivery’ # this will be a feature we set in the exim4.conf.template file

OK, we are all done with update-exim4.conf.conf if you indeed have this file. There may be some other items in there that will be left as-is. If you use a smart-hose for all outgoing mail (like your ISP’s mail server), you will have to do some research on how to do that.

Now, lets open /etc/exim4/exim4.conf.template and blow through all of these changes:

under MAIN CONFIGURATION SETTINGS, enter the following line just under the exim_path setting:

hide mysql_servers = localhost/horde/horde/PASSWORD_HERE

The above line indicates to use the localhost mysql server, the horde database, the horde user, and then type your password for the horde user (or another user if you opted to not use the horde user).

find the line that starts domainlist local_domains (should be around line 47 or so) and overwrite with the following. This will cause Exim to look your ‘local’ domains in your ‘domain’ table and delivery them locally:

domainlist local_domains = @:localhost:${lookup mysql{SELECT domain FROM domains WHERE type=”local” and domain=”${domain}” }}

A few lines down there is a domainlist relay_to_domains and overwrite with the following:

domainlist relay_to_domains = ${lookup mysql{SELECT domain FROM domains WHERE type=”relay” and domain=”${domain}”}}

The next step will be a change around line 74 that starts LOCAL_DELIVERY. Overwrite this with:

LOCAL_DELIVERY=mysql_delivery

The next sections will be down much further in the configuration which will require a lot of scrolling or the use of a search/find feature.

This will be done in the ROUTERS section of the configuration file to let Exim find a match. These will find to delivery the mail locally or to deliver the mail locally but send an out-of-office reply.

Search for the line router/250_exim4-config_lowuid. Just underneath the line of # marks, enter the following:

mysql_aliases:
debug_print = “R: mysql_aliases for $local_part@$domain”
driver = redirect
allow_defer
allow_fail
data = ${lookup mysql{SELECT login FROM aliases WHERE alias=’${local_part}@${domain}’ AND type=”domain”}}

mysql_sys_aliases:
debug_print = “R: mysql_sys_aliases for $local_part@$domain”
driver = redirect
allow_fail
allow_defer
data = ${lookup mysql{SELECT login FROM aliases WHERE alias=’${local_part}@${domain}’ AND type=”system”}}

localuser:
driver = accept
local_parts = ${lookup mysql{SELECT REPLACE(user_uid,’${quote_mysql:@$domain}’,”) \
as user FROM horde_users WHERE user_uid=’${quote_mysql:$local_part@$domain}’}{$value}}
transport = LOCAL_DELIVERY
cannot_route_message = Unknown user

Search for hub_user_smarthost: and enter the following lines after the .endif in that group:

vacation:
debug_print = “R: vacation for $local_part@$domain”
driver = accept
condition = ${if eq{${lookup mysql{SELECT vacation_enabled FROM horde_users WHERE user_uid=”${local_part}@${domain}”}}}{y}}
no_verify
no_expn
unseen
transport = vacation

So, the ROUTERS section is now done. This leaves us with the TRANSPORTS section that tells Exim some additional commands for delivering the mail.

Find the line that says mail_spool: and enter these lines after that group of text:

vacation:
debug_print = “T: vacation for $local_part@$domain”
driver = autoreply
reply_to = “${local_part}@${domain}”
to = ${sender_address}
from = “${local_part}@${domain}”
subject = “Re: Out of Office Reply: $h_subject”
text = ${lookup mysql {SELECT vacation FROM horde_users WHERE user_uid=”${local_part}@${domain}”}}

mysql_delivery:
debug_print = “T: mysql_delivery for $local_part@$domain”
driver = appendfile
delivery_date_add
envelope_to_add
return_path_add
mode_fail_narrower = false
mode = 0600
maildir_format
maildir_tag = ,S=message_size
maildir_use_size_file = true
maildir_quota_directory_regex = ^(?:cur|new|\..*)$
current_directory = /
directory = ${lookup mysql{SELECT maildir FROM horde_users WHERE user_uid=”${local_part}@${domain}”}}
user = ${lookup mysql{SELECT uid FROM horde_users WHERE user_uid=”${local_part}@${domain}”}}
group = ${lookup mysql{SELECT gid FROM horde_users WHERE user_uid=”${local_part}@${domain}”}}

The vacation transport above will auto-reply to a sender that sent a message to someone with their auto-reply feature turned on and pull the ‘vacation’ field from the user’s row in the table and e-mail that back to the individual after setting a subject line of “Re: Out Of Office Reply: . The mysql_delivery transport will simply deliver mail for your local virtual domains and will pull the maildir field from the user’s row so Exim knows where to place the newly deliverd mail.

Almost there! The last changes below will allow users that use Outlook or other mail clients to send their e-mails through your server by using SMTP Authentication – for users you have allowed to send mail through your system, of course!

These lines will be all the way at the bottom of your Exim4 configuration. Search for the line that says “begin authenticators”. If there already items under there that start out plain: and login: delete them and replace with these lines:

plain:
driver = plaintext
public_name = PLAIN
server_set_id = $2
server_condition = ${lookup mysql{SELECT user_uid FROM horde_users WHERE user_uid=”$1″ AND user_pass=”$2″}{1}fail}
server_advertise_condition = true

login:
driver = plaintext
public_name = LOGIN
server_prompts = “Username:: : Password::”
server_condition = ${lookup mysql{SELECT user_uid FROM horde_users WHERE user_uid=”$1″ AND user_pass=”$2″}{1}fail}
server_set_id = $1
server_advertise_condition = true

This will authenticate the users by reading in their username (e-mail address) and password and authenticate it against the MySQL database. If the credentials match up, then your mail server will accept the message for relaying to another person on your system or across the Internet. Otherwise, it will fail unless they are sending a message to a person on your system or a domain you accept relaying for.

OK, you should now be done. I would highly request anyone that has comments, suggestions, or bugs with the above HOWTO to post a comment or e-mail me so they can be corrected. I used several sources to put together this HOWTO and I have well over 40+ hours of work in getting my system setup. Below are the sources I used to get my feet on the ground.

Mail Server Setup With Exim, MySQL, Cyrus-Imapd, Horde Webmail On Centos 5.1
How to Install Horde3 on Ubuntu

components/ com_zoom/ www/ images/ filetypes/ image.png – What??

So I have been converting the web sites over from a Windows 2000 IIS server to a Linux Apache2 server. In the process, I noticed a lot of pictures on my Zoom Media Gallery were broken and would not work!

I looked at the properties and the site was trying to pull them from:

https://www.bsntech.com/components/com_zoom/www/images/filetypes/image.png

This was clearly wrong and I don't know what that showed up like that!

This came up on locations where I had pictures nested within another gallery. For example, my Busch Gardens pictures has another gallery within it for the date that I visted since I have went more than once. The other galleries that had the pictures in the main folder worked just fine.

Well, I looked at my jos_zoom table in the database and I noticed that I had the Windows backward slash "\" to go between the directory – like "Busch_Gardens\4-24-2005" for an example. After I changed these slashes to the forward slash "/" which made it "Busch_Gardens/4-24-2005", problem fixed!

I hope this will help some of you others out there that search for this so you can correct this on your site too!

HOWTO: ProFTPd using MySQL Authentication & User Information on Ubuntu Gutsy 7.10

This HOWTO will describe how to setup ProFTPd using MySQL as the authentication mechanism for your users and user home directories. This HOWTO ties in with my previous HOWTO for setting up a fully-fledged e-mail system with WebMail, POP3, and SMTP authentication. This works perfectly on Ubuntu Linux Server Gutsy 7.10.

First, we want to download the ProFTPd software from the repsitories:

sudo apt-get install proftpd

After the system is setup, it will automatically have all of the ProFTPd modules downloaded. The ProFTPd module location is at /usr/lib/proftpd. If you would like to navigate there, you can ensure that the "mod_sql" module is installed by looking for the "mod_sql.a" and "mod_sql.la" files. If they are, lets continue on.

The next step is to modify the ProFTPd configuration file. The ProFTPd configuration location is /etc/protfpd/proftpd.conf.

Open this file up and there are a few things to do. Here is the code that I used:

ServerName "SERVER_NAME_HERE"
ServerType standalone
DefaultServer on
TimeoutNoTransfer 600
TimeoutStalled 600
TimeoutIdle 1200
DefaultRoot ~ # This will ensure that the users can only work within their home directory
Port 21
MaxInstances 5
AllowOverwrite on

OK, so those are all the basic options. Ensure you put the name of your server where I put in SERVER_NAME_HERE.

Now, lets do the configuration for using SQL. Note that with the configuration below, I used a MySQL database. The database name was called "horde" (so that way I have one database for all authentication on my systems), the table within the database that contains user information is "horde_users", the username to gain access to the database is "horde3" and the password I simply put PASSWORD_HERE.

SQLBackend mysql
SQLConnectInfo horde horde3 PASSWORD_HERE
SQLAuthTypes Plaintext
SQLAuthenticate users # I am authenticating the users only, not authenticating groups
SQLUserInfo horde_users user_uid user_pass NULL NULL www NULL

The last line of code that starts with SQLUserInfo, lets go over that quickly here.

horde_users – The name of the table within database 'horde' (specified in SQLConnectInfo) that contains all the user information
user_uid – The field that contains the user's login ID they will use to login to FTP
user_pass – The field that contains the user's plaintext password they use to authenticate their account
NULL – This is put in there because there is no need to pull the user's ID number Linux assigns.
NULL – This is put in there because there is no need to pull the group ID number Linux assigns
www – This is the field that contains the user's home directory. This will ensure that when the user logs in, they will go to this directory that they have full control of.
NULL – This was put in there because none of the users have a shell that they have access to.

OK, thats it! Save the configuration file and then restart ProFTPd:

sudo /etc/init.d/proftpd restart

As long as you have information filled out and the database created, you should now be able to login to FTP!