Commands I use, but often forget


Importing (Restoring) Many MySQLDump Text Files:

~ from a user (paraphrased): “If we have 100+ mysqldump .txt files, how can we easily import them all, in order to restore a database, without typing each file name in by hand? (i.e. from the shell prompt, piped to the mysql monitor:
mysql mydb <>). Wouldn’t it be better for the script to write all the tables to one text sql file?”

=> I suppose the script could be rewritten, but it’s more flexible the way it is, considering that one can use the Linux shell prompt to do the same thing. Assuming that the database name is ’test’, and that the files have the extension of ‘.txt’, you can use this command:

Read more

Share

Connecting to MySQL 4.1 with Old Libraries

If you need to connect to a MySQL 4.1 Database using an old mysql library, you need to change the password for the user account that you want to connect with, so that it uses an old password. The new version of the database uses a different password algorithm which is much longer then the old version, which causes problems with the old library. To fix this you have to set the user password with the Old_password( ) function.

SET PASSWORD FOR ‘some_user’@’some_host’ = OLD_PASSWORD(‘mypass’);

http://dev.mysql.com/doc/mysql/en/password-hashing.html

Read more

Share

Setting up Postfix (TLS) / Courier-IMAP (SSL) / MySQL / Postfixadmin

This is an install guide for setting up a secure mail server using Postfix, Courier-IMAP, MySQL, and Postfixadmin on Debian Woody.

Before we begin here are a few notes regarding how things are handled:

  • Virtual domains - All domains including the local domain are handled as virtual domains. This allows a central location for all mail.
  • All interactions with the server are authenticated and encrypted using libsasl2 and TLS/SSL (Please see the packages section for what to install)
1. Packages to Install

This install guide assumes the following packages are already installed:
  • mysql-client
  • mysql-server
  • openssl (to create the certificate)
  • apache
To add Postfix add the following line to your /etc/apt/sources.list

deb http://www.backports.org/test/postfix/ ./

Install the following packages:
  • Postfix 2.1.4-4
  • Postfix-mysql 2.1.4-4
  • Postfix-pcre 2.1.4-4
  • Postfix-tls 2.1.4-4
  • libsasl2 (the Cyrus SASL library)
  • libsasl2-modules
  • libsasl2-modules-sql
Note: Make sure you use the source above to install the packages. DON’T use the stable version of backports to install Postfix and libsasl. The stable version of Postfix is linked against libsasl1 which does not allow authentication via the libsasl2 sql module.

Comment out the previous package line and add the following line in your sources.list file:

deb http://www.backports.org/debian stable courier

Install the following packages:
  • courier-authdaemon 0.47-2
  • courier-authmysql 0.47.2
  • courier-base 0.47.2
  • courier-imap 3.0.8-2
  • courier-imap-ssl 3.0.8-2
Finally unpack the latest version of Postfixadmin (2.1.0 as of this writing) into your web directory.


2. Create the Tables in MySQL


Go to the postfixadmin directory that you unpacked and run the DATABASE_MYSQL.TXT in MySQL to setup the complete table structure for Postfix.

It should create the following 7 tables under a database called ‘postfix’:
  • admin
  • alias
  • domain
  • domain_admins
  • log
  • mailbox
  • vacation
3. Create a Mail Directory

Create a directory to have all your virtual users mail dropped in, this directory needs
to be owned by Postfix.
% mkdir /usr/local/virtual

% chown -R postfix:postfix /usr/local/virtual
% chmod -R 771 /usr/local/virtual
4. Postfix Configuration

Use the following /etc/postfix/main.cf file. The items in <> are values you need to change.

myhostname= «span style=“font-style: italic;">your.hostname>
mydomain= «span style=“font-style: italic;">your.domain>
mydestination =
myorigin = /etc/mailname

virtual_maps = mysql:/etc/postfix/mysql_virtual_alias_maps.cf
virtual_alias_maps = $virtual_maps
virtual_gid_maps = static:«span style=“font-style: italic;">postfix group id from /etc/passwd>
virtual_mailbox_base = «span style=“font-style: italic;">/your/mailbox/path>
virtual_mailbox_domains = mysql:/etc/postfix/mysql_virtual_domains_maps.cf
virtual_mailbox_maps = mysql:/etc/postfix/mysql_virtual_mailbox_maps.cf
virtual_minimum_uid = «span style=“font-style: italic;">postfix user id from /etc/passwd>
virtual_transport = virtual
virtual_uid_maps = static:«span style=“font-style: italic;">postfix user id from /etc/passwd>

# SMTP Authentication
smtpd_sasl_auth_enable = yes
broken_sasl_auth_clients = yes
smtpd_sasl_security_options = noanonymous
smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reje
ct_unauth_destination
smtpd_use_tls = yes
smtpd_tls_cert_file = /etc/postfix/smtpd.cert
smtpd_tls_key_file = /etc/postfix/smtpd.key
append_dot_mydomain = no

Notes: Leave mydestination blank if you want your local domain to be considered virtual. If you list your local domain in mydestination, as is commonly the case, you need to have a system user for every email user. Postfix will look for a system user corresponding to the email user and if none is found it will bounce the mail. It will also not deliver to the mailbox path.

Create the following files in /etc/postfix:

mysql_virtual_alias_maps.cf

user = postfix
password =
hosts = localhost
dbname = postfix
table = alias
select_field = goto
where_field = address

mysql_virtual_domains_maps.cf

user = postfix
password =
hosts = localhost
dbname = postfix
table = domain
select_field = description
where_field = domain

mysql_virtual_mailbox_maps.cf

user = postfix
password =
hosts = localhost
dbname = postfix
table = mailbox
select_field = maildir
where_field = username

Now go to /etc/postfix/master.cf and change first smtp line to the following:

smtp inet n - n - - smtpd

This prevents postfix from running in a chrooted jail.

5. Postfix, Authenticated SMTP and TLS encryption

By default postfix only allows communication between users defined in mynetworks. This prevents an open relay that spammers can abuse to send out spam. This however, also prevents users of your domain to send out email to domains outside your box. So you have to authenticate your users with a username and password to allow them to be part of your circle of trust. Most email cilents such as Thunderbird have this feature included.


Use the following from Christoph Haas’s tutorial located in http://workaround.org/articles/ispmail-sarge/ to setup authentication:

As written earlier Postfix uses the Cyrus SASL library for authenticated SMTP. So you need to tell Postfix how to access the data storage that keeps the usernames and passwords. This is easy. Create a directory /etc/postfix/sasl to put the SASL config file there:
mkdir /etc/postfix/sasl

Now you need to create a file smtpd.conf in that directory like this:

Read more

Share

Integrating Apache 2.0.x and Tomcat 5.0.x with mod_jk on Mandrake Linux 10

When it comes to Linux, nothing comes easy for me, but I get it done. Tonight I tried to install the connector between Apache and Tomcat. I had basic instructions for that, and he even e-mailed me the required mod_jk.so file. Unfortunately, Mandrake 10 didn’t like it and I had to manually build it from the source. What a pain, but the instruction below helped quite a bit, I just had to install “autoconf” to get it to work. Mandrake asked for the installation CD to be inserted. I already had the other components (gcc).

Read more

Share