Remove Unwanted Mails from mailq in postfix Server

We had an issue in a development server which sends thousands of mail messages to the system administrator. Those mails were queued in the out going mail server which affected the normal mail delivery process in the out going mail server.

I used following command to remove unwanted mails from the mailq.

mailq |fgrep apache@testdomain.nic.lk|sed 's/\*.*//'| postsuper -d -

Here apache@testdomain.nic.lk is the mail sending user and I did not use the recipient address to filter the mails as he might delete required mails also.

When I wanted to delete the mails received to a mail user (i.e. rcpt to:someone@yourdomain.lk) I used the following command to delete them:

mailq | awk 'BEGIN { RS = "" } / someone@userdomain\.lk$/ { print $1 }' | postsuper -d -

Clarification 

fgrep will display the detailed line of the mails sent by 'apache@testdomain.nic.lk' which are queued in the mailq and using sed command, just the mailIDs can be listed.
awk is also such tool which can be used for text formatting. When it used correctly with relevant switches. it will generate a list which can be used as an input to postsuper process to delete the given mailID.

Normally, postsuper -d <MailID> will delete the mail in the queue with given mail ID. So the above command will list all mailIDs in the mailq send by a particular user and forward to delete it with postsuper user command.

Good To Read:

https://www.howtoforge.com/delete-mails-to-or-from-a-specific-email-address-from-postfix-mail-queue

MySQL Error - Use mysql_upgrade

Mysql database has issues and the log file contained following error codes

150924 14:11:17 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it
150924 14:11:17 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
150924 14:11:17 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
150924 14:11:17 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure
150924 14:11:17 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure
150924 14:11:17 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure
150924 14:11:17 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure
150924 14:11:17 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure

Even though the service shows up and running no database interactions were possible. After searching for 'mysql_upgrade' I was able to find out that this is due to MySQL schema storage is broken. These reasons may be:

• You have broken the database information_schema
• File system corrupted or some bugs in the file system damaged the database.
• MySQL internals broke the schema database due to a bug in MySQL (maybe nobody encountered it before).

[Ref: http://stackoverflow.com/questions/6288103/native-table-performance-schema-has-the-wrong-structure ]

All references were guided to run mysql_upgrade as the root.

mysql_upgrade -u root -p

*Please note that the root user here is the mysql root user and the password, not the system root








Then the system will prompt for the database root user's password.

When the mysql_upgrate executed, it will check for the compatibility of the databases and tables with the current MySQL version and repair if inconsistency is available. After successful command run,  you need to restart the mysql service. Also check the log file for any errors (/var/log/mysqld.log)

Good References:

* https://dev.mysql.com/doc/refman/5.5/en/mysql-upgrade.html

*http://serverfault.com/questions/527422/mysql-upgrade-is-failing-with-no-real-reason-given

NSUPDATE Issues with TSIG

We planned to enable auto-dnssec maintain in the DNS server to allow dynamic updates with DNSSEC. In the process, it is required to implement dns update with TSIG key. We implemented the key pair with following command.

 #dnssec-keygen -r /dev/urandom -a HMAC-MD5 -b 512 -n HOST updatekey.mydomian.lk

It generated following key pair.
Kupdatekey.mydomain.lk.+157+26933.key
Kupdatekey.mydomain.lk.+157+26933.private

and the key pair was included in /var/named as the files can be accessed by named user. The permissions were set to rw by the user named.

The pre-shared key were included in the name.conf (locate in /etc in our case) as follows;

key "updatekey.mydomian.lk." {
        algorithm       hmac-md5;
        secret  "********************************************";
};
where the hidden part is taken from the Kupdatekey.mydomian.private


In the zone section in the named.conf file, the key name was configured as the allows key for zone update;

zone mydomain.lk{
         type master;
         file "mydomain.file";
         .... . . . .
         allow-update {  key updatekey.mydomain.lk.; };
};


Once we need to update the DNS entries in the zone 'mydomain.lk' we tried the following command from the localhost.

nsupdate -k <key_file.private>
>server <serve_ip>
>zone mydomain.lk
>update add/delete <update query>
>send

If there is an issue, the error will be prompted else DNS entries will be updated and if  the zone if configured with
        auto-dnssec maintain;
        dnssec-secure-to-insecure yes;
        key-directory "/var/named/keys"; the new updates will be propagated with dnssec signatures and as IXFR updates to secondaries.

Issues We Found and Solutions
In the initial stage, when generating the key pair we used the domain as the name for the shared key file and the key name given in the named.conf was different to the key file name.
i.e. file generated was Kmydomian.lk.key and Kmydomain.lk.private
and in the named.conf the shared key name was  updatekey.mydomian.lk

We followed the same steps as above the only differences were, the share key name given in the named.conf is different to the Key name;

e.g.
Named conf parameters
key "updatekey.mydomian.lk." {
        algorithm       hmac-md5;
        secret  "**********************************************";
};
Command given for nsupdate
nsupdate -k Kmydomain.lk.private 
>


Once we entered the data for nsupdate, once we confirm the changes with send, it gave the error
TSIG error with server: tsig indicates error

But most interestingly when we used the nsupdate with -y and the key name and pre-shared  string in the named.conf it worked fine.

**Later we understood that the key name should be same as the string that we provided in generating the key with dnssec-keygen -H ..... etc. The key name and the file id should be identical.

Other than that we did not encountered any other issues and we are ready with DNSSEC auto-dnssec maintain with IXFR.

Good reference for TSIG and nsupdate
http://www.crypt.gen.nz/papers/dns_security_1.html

Upgrade PHP 5.3 to 5.4 in CentOS

I wanted to upgrade php version of one of the server. After referring the following web page it was easily done without any issue.

http://www.servermom.org/upgrade-php-53-54-55-centos/1534/

MySQL Sync with one Master and 2 Slaves

We had a requirement to run mysql replication with one master and 2 slaves. By following normal procedure this is doable. Interestingly we got few points which may be useful to others when working with mysql replication. Following given the points that we had to deal with;

1. A slave with mysql higher version: One slave had mysql ver 5.5 while others are with mysql 5.1. In this case the mysql server 5.5 does not allow to configure mysql masters details in my.cnf where we need to give some important parameters of master server. Following blog page describes the way to give those parameters in the shell and configure the server to connect with the master server.

https://tapasmishra.wordpress.com/2012/06/11/how-to-configure-mysql-5-5-server-as-replication-slave/

Following setting was taken from the above post

mysql> CHANGE MASTER TO MASTER_HOST=’192.168.2.101′,
MASTER_PORT=3306,
MASTER_USER=’replication_user’,
MASTER_PASSWORD=’PASSWORD';

 2. After configuring the master parameters in the slave server as above, we encountered an interesting issue in the synchronization. The issue was identified referring /var/log/mysqld.log and also from 'show slave status' in the shell. The issue was the slave replication SQL thread aborted due to non executable sql commands in master log. Referring below post;

http://stackoverflow.com/questions/2366018/how-to-re-sync-the-mysql-db-if-master-and-slave-have-different-database-incase-o

According to the above post, when defining the master parameters, we need to give MASTER_LOG_FILE and MASTER_LOG_POS. These parameters can be observed by executing 'show master status' in the master server. After getting the log file name and the log position of the master server, the database in the master file should be copied to the slave server. Please note that it is very important to keep the same data in the databases in both master and slave servers when defining the master log positions. mysqldump command can be used to copy the master server's database to slave server.

We gave the master_log_file and master_log_pos parameters along with the master_host, master_port parameters with the CHANGE MASTER TO .. command.

After rectifying the above described issues, the replications were started correctly.