Zimbra CLI commands

The documents of zimbra online is so messy with a lot of Zimbra versions and supported/unsupported articles. I write this for myself.

Show current config

 zmprov desc -a attribute
 zmlocalconfig

zimbra log directory
 /opt/zimbra/log

[ MySQL ] How to grab database and table size.

1. Grab all database size.

SELECT table_schema, SUM(data_length + index_length)/1024/1024 AS total_mb, SUM(data_length)/1024/1024 AS data_mb, SUM(index_length)/1024/1024 AS index_mb, COUNT(*) AS tables, CURDATE() AS today FROM information_schema.tables GROUP BY table_schema ORDER BY 2 DESC;


2. Grab all table size.

SELECT table_schema, table_name, (data_length + index_length)/1024/1024 AS total_mb, (data_length)/1024/1024 AS data_mb, (index_length)/1024/1024 AS index_mb, CURDATE() AS today FROM information_schema.tables ORDER BY 3 DESC;

ERROR: Cannot add watch /Dir (28:No space left on device)

Today I got the lsyncd problem and found the following error in /var/log/lsyncd.
 ===
Wed Sep 21 03:48:24 2016: ERROR: Cannot add watch /Dir_name/file_name (28:No space left on device)
 ===
I googled the error message and found the solution. The max_user_watches reached its limit. I need to increase the value of max_user_watches. 

At first, checked the current value.
 [root@host ~]# cat /proc/sys/fs/inotify/max_user_watches
 32768

To increase the max_user_watches, I edited the /etc/sysctl.cnf.
 [root@host ~]# vi /etc/sysctl.confadded the folowing line.
 fs.inotify.max_user_watches = 500000

To take a effect, I did the following command.
[root@host ~]# sysctl -p

That's it!

Temporary solution
  echo 65536 >/proc/sys/fs/inotify/max_user_watches

 [root@host ~]# cat /proc/sys/fs/inotify/max_user_watches
 65536

Finally I got around the error. :) 

How to configure your s3 bucket public.

To set the all files in bucket public, add the bucket policy to your s3 bucket.
1. Go to https://awspolicygen.s3.amazonaws.com/policygen.html
2.  Set the following parameters.
          Select Type of Policy with "S3 bucket policy".
          Effect: Allow
          Principal:   *
          AWS Service: S3
          Actions:  GetObject
          Amazon Resource Name (ARN):    arn:aws:s3:::bucket-name/*

 3. Then, click 'Add Statement'
 4.  Click the 'Generate Policy' button. You'll get the following information like below.

{
  "Id": "Policy1462558474573",
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "Stmt1462581569846",
      "Action": [
        "s3:GetObject"
      ],
      "Effect": "Allow",
      "Resource": "arn:aws:s3:::bucket-name/*",
      "Principal": "*"
    }
  ]
}
5. Add above information in your s3 bucket policy.
That's it!

[Linux] find old files and delete, or move

Example: delete files older than one year
find /path/to/source  -mtime +365 -exec rm {} \;

Example: move files older than one year.
find /path/to/source -mtime +365 -exec mv {} /path/to/destination/ \;

Example: find files which file name starts ABC and older than 30 days and move.

find . -name "ABC*.csv" -mtime +30 -exec mv {} /path/to/destination/  \;

Zimbra added the ***UNCHECKED*** in the mail subject. How to remove it.

This summary is not available. Please click here to view the post.

エラーメールの判別方法

エラーメールの判別方法

in reply to end of DATA command
<xxx_example@docomo.ne.jp>: host mfsmax.docomo.ne.jp[203.138.181.240]
    said: 550 Unknown user xxx_example@docomo.ne.jp (in reply to end of DATA command)

指定ドメイン拒否（デフォルト設定）
docomo端末の設定で、ドメイン許可リストに該当ドメインを指定する。
================
<xxx_doesnt_exist@gmail.com>: host aspmx.l.google.com[74.125.203.27] said: 550-5.1.1
    The email account that you tried to reach does not exist. Please try
    550-5.1.1 double-checking the recipient's email address for typos or
    550-5.1.1 unnecessary spaces. Learn more at 550 5.1.1
    https://support.google.com/mail/answer/6596 tn4si27093780pbc.45 - gsmtp (in reply to RCPT TO command)

宛先不明　-> メールアドレスに間違いがないか確認する。
================

Change timezone from UTC with RDS instance

Here is how to change timezone setting in your RDS instance

1, connect your RDS instance as root.
[user@ec2 ~]$ mysql -u root -p -h xxxxxx.yyyyyy.zzzzzz.rds.amazonaws.com

2. Change the timezone setting.
mysql> DELIMITER |
mysql> CREATE PROCEDURE mysql.`set_PST`()
    -> IF NOT (POSITION('rdsadmin@' IN CURRENT_USER()) = 1) THEN
    ->    SET SESSION time_zone = 'America/Los_Angeles';
    -> END IF |
mysql> DELIMITER ;

3. Create parameter group
find init_connect parameter and input 'CALL set_PST'. Then save changes.

4. Apply new parameter group to your RDS instance.

5. Log in to your RDS instance. Then submit the following command.

mysql> GRANT EXECUTE ON PROCEDURE mysql.set_PST TO 'username'@'hostname';

サイボウズガルーン v4.0.1、パッケージ版URLからクラウド版URLのリダイレクト

I figured out how to redirect web pages on Cybozu Garoon package edition to their cloud edition.
work as root user.

cd /var/www/cgi-bin/cbgrn/
mv grn.cgi grn.cgi.org
vi grn.cgi
write redirect script like following. then save it.

#!/usr/bin/perl

$uri = $ENV{'REQUEST_URI'};

my @param = split('grn.cgi',$uri);

$rdrURL =  "Location: https://xxxxxx.cybozu.com/g/$param[1]\n\n";

print $rdrURL;

exit;

chmod 755 grn.cgi
chown apache:root grn.cgi

That's it! Test the redirection.

How to make send only sendmail server.

I have a EC2 instance in AWS. I configured my sendmail to send only for some purpose. Here is how. Let's say the your servers are in the subnet of 10.0.1.0/24 and the IP address of the relay server is 10.0.1.25.

1. Allow relay from your VPC in /etc/mail/access

--access--
[root@host /]# cd /etc/mail
[root@host /]# cp access access.org
[root@host /]# cp access.db access.db.org
[root@host /]# vi access
===
Connect:localhost.localdomain           RELAY
Connect:localhost                       RELAY
Connect:127.0.0.1                       RELAY
Connect:10.0                            RELAY  <-- Add
===

[root@host /]# makemap -v hash access.db < access

--submit.mc--

[root@host /]# cp submit.mc submit.mc.org

[root@host /]# cp submit.cf submit.cf.org

[root@host /]# vi submit.mc

===

define(`confDOMAIN_NAME', `yourdomain.com')dnl  <-- Add

FEATURE(`msp', `[10.0.1.25]')dnl  <-- Change to your relay server.

===

[root@host /]# m4 submit.mc > submit.cf

--sendmail.mc--

[root@host /]# cp sendmail.mc sendmail.mc.org

[root@host /]# cp sendmail.cf sendmail.cf.org

[root@host /]# vi sendmail.mc

===

define(`SMART_HOST', `[10.0.1.25]')dnl  <-- Add

define(`MAIL_HUB', `yourdomain.com.')dnl  <-- Add

define(`LOCAL_RELAY', `yourdomain.com.')dnl  <-- Add

===

[root@host /]# make sendmail.cf

[root@host /]# service sendmail restart

That's it!

Then test it from your web server.

How to create mysql user on Amazon RDS

When I created a new mysql user on RDS, I got the following error.

===
ERROR 1184 (08S01): Aborted connection  to db: 'unconnected' user:  host:  (init_connect command failed)
===
After googling a while, the cause looks that I changed the timezone of the RDS from UTC to JST.

I got around the issue by the following commands. I need to grant execute command to mysql DB to a new mysql user.


mysql> grant execute on mysql.* to 'some_user'@'%';
mysql> grant select on some_db.some_table to 'some_user'@"%";
mysql> flush privileges;
mysql> set password for 'some_user'@'%' = password('some_pass');

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| some_db            |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)
Now it works, :) even though I don't like the new mysql user to access information schema and mysql db.

[OpenSSL] How to generate CSR and SSL Key

User must be root.
 
Generate Key
  # openssl genrsa -des3 -out server.key 2048
Generate CSR
  # openssl req -new -key server.key -out server.csr
Check CSR
  # openssl req -noout -text -in server.csr
Write password in the key
  # openssl rsa -in server.key -out servera.key

How to disable dhclient log and rsyncd.log in /var/log/messages on EC2

On Amazon EC2 instances, DHCP client logs are filled with /var/log/messages because EC2 uses DHCP service for getting their IP addresses and by default DHCP client activities are logged in /var/log/messages. I can't track other system activities than DHCP client log, then I disabled the dhcp client logs.


How to disable DHCP Client log

I tested this solution but the result is that no more system activities are logged in /var/log/messages.

So don't use this solution.

1,  Edit /etc/rsyslog.conf
2,  add ';dhclient.none' in the following line and save it.

Before
*.info;mail.none;authpriv.none;cron.none          /var/log/messages

After
*.info;mail.none;authpriv.none;cron.none;dhclient.none          /var/log/messages

3, restart rsyslog
# service rsyslog restart

Notes: I asked the AWS tech support about this, then they also don't know the solution. Their  workaround is to grep /var/log/messages to remove dhclient lines and redirect another text file.
#grep -v dhclient /var/log/messages > /var/log/messages-nodhclient.log

The syslog facility of dhclient is hard coded in the source to "LOG_DAEMON". You can change the setting with "LOG_DAEMON" not to log in /var/log/messages but you'll miss any other "LOG_DAEMON" activities in /var/log/messages. So it's hard to remove only dhclient logs in the /var/log/messages.


How to move rsync logs to xinetd.log

1, Edit /etc/xinetd.conf like following and save it.
Before

       log_type        = SYSLOG daemon info


After

#       log_type        = SYSLOG daemon info
        log_type        = FILE /var/log/xinetd.log


2, Reload xinetd service
#service xinetd reload

3, Add log rotation for xinetd.log
Create log rotate setting for xinetd.log like following
# vi /etc/logrotate.d/xinetd
#=====

/var/log/xinetd.log {
    rotate 10
    daily
    compress
    delaycompress
    missingok
    postrotate
        /bin/kill -HUP `cat /var/run/xinetd.pid 2> /dev/null` 2> /dev/null || true
    endscript
}

#=====

4, force log rotate by the following command
#logrotate -f /etc/logrotate.conf

How to fix OpenSSL vulnerability on EC2

How to fix the vulnerability of openssl issue on AWS.

EC2
1, Check openssl version
[root@web ~]# openssl version
OpenSSL 1.0.1e-fips 11 Feb 2013

[root@web ~]# yum info openssl

Loaded plugins: priorities, security, update-motd, upgrade-helper
amzn-main                                                                            | 2.1 kB     00:00  
amzn-updates                                                                         | 2.3 kB     00:00  
852 packages excluded due to repository priority protections
Installed Packages
Name        : openssl
Arch        : x86_64
Epoch       : 1
Version     : 1.0.1e
Release     : 4.53.amzn1
Size        : 3.8 M
Repo        : installed
From repo   : amzn-updates
Summary     : Utilities from the general purpose cryptography library with TLS implementation
URL         : http://www.openssl.org/
License     : OpenSSL
Description : The OpenSSL toolkit provides support for secure communications between
            : machines. OpenSSL includes a certificate management tool and shared
            : libraries which provide various cryptographic algorithms and
            : protocols.

If your openssl release is not 37.66.amzn1, You should update openssl by yum update.
[root@web ~]# yum update openssl


============================================================================================================
 Package                  Arch              Version                           Repository               Size
============================================================================================================
Updating:
 audit                    x86_64            2.3.2-3.19.amzn1                  amzn-main               258 k
 openssl                  x86_64            1:1.0.1e-37.66.amzn1              amzn-updates            1.7 M
Updating for dependencies:
 audit-libs               i686              2.3.2-3.19.amzn1                  amzn-main                84 k
 audit-libs               x86_64            2.3.2-3.19.amzn1                  amzn-main                87 k
 glibc                    i686              2.17-36.81.amzn1                  amzn-updates            6.0 M
 glibc                    x86_64            2.17-36.81.amzn1                  amzn-updates            5.6 M
 glibc-common             x86_64            2.17-36.81.amzn1                  amzn-updates             28 M
 glibc-devel              x86_64            2.17-36.81.amzn1                  amzn-updates            1.1 M
 glibc-headers            x86_64            2.17-36.81.amzn1                  amzn-updates            718 k
 openssl-devel            x86_64            1:1.0.1e-37.66.amzn1              amzn-updates            1.3 M

Transaction Summary
============================================================================================================
Upgrade      10 Package(s)



Total download size: 45 M
Is this ok [y/N]: y

Check the version of openssl again.

[root@web8 ~]# yum info openssl

Loaded plugins: priorities, security, update-motd, upgrade-helper

852 packages excluded due to repository priority protections

Installed Packages

Name        : openssl

Arch        : x86_64

Epoch       : 1

Version     : 1.0.1e

Release     : 37.66.amzn1

Size        : 4.0 M

Repo        : installed

From repo   : amzn-updates

Summary     : Utilities from the general purpose cryptography library with TLS implementation

URL         : http://www.openssl.org/

License     : OpenSSL

Description : The OpenSSL toolkit provides support for secure communications between

            : machines. OpenSSL includes a certificate management tool and shared

            : libraries which provide various cryptographic algorithms and

            : protocols.

Then restart the service using openssl such as apache.

[root@web ~]# /usr/sbin/apachectl stop

[root@web ~]# /usr/sbin/apachectl start

[root@web ~]# netstat -an|grep 80
tcp        0      0 10.0.1.175:57131            103.246.150.193:80          TIME_WAIT
tcp        0      0 :::80                       :::*                        LISTEN    
[root@web ~]# netstat -an|grep 443
tcp        0      0 :::443                      :::*                        LISTEN    

Reference:
https://aws.amazon.com/amazon-linux-ami/security-bulletins/ALAS-2014-320/

ELB
Generate SSL certificate with the latest openssl command.
1. Generate SSL key
# openssl genrsa -des3 -out server2048.key 2048

How to append the value in the field in MySQL

mysql> update table_name set field_name=concat(field_name, 'append_string');

example:
mysql> select * from table1;
+----+--------+
| id | field1 |
+----+--------+
|  1 | aaa    |
|  2 | bbb    |
|  3 | ccc    |
|  4 | ddd    |
|  5 | eee    |
+----+--------+
5 rows in set (0.00 sec)

mysql> update table1 set field1=concat(field1, '1');
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> select * from table1;
+----+--------+
| id | field1 |
+----+--------+
|  1 | aaa1   |
|  2 | bbb1   |
|  3 | ccc1   |
|  4 | ddd1   |
|  5 | eee1   |
+----+--------+
5 rows in set (0.00 sec)

Check version of Zimbra

#su - zimbra
$ zmcontrol -v

Zimbra high CPU usage with java process

I've been running Zimbra 8.0.2 and got a problem with almost 100% CPU usage with java processes.

I tried many solutions by applying the fix written on the forum but didn't work.... Finally found the solution for this.

http://www.zimbra.com/forums/known-issues/62819-excessive-nio-write-buffering-leads-mailboxd-service-outage.html#post271721

NIO imap, NIOSocketSession leaking. 

IMAP NIO is enabled by default with Zimbra 8.0.2. That's why Zimbra takes high CPU usage.

To disable IMAP NIO, excute the following command.

[root]# su - zimbra

[zimbra]$ zmlocalconfig -e nio_imap_enabled=false

[zimbra]$ zmmailboxdctl restart

How to send Ctrl + Alt + Del in Remote Desktop session

From remote Windows computer.

Ctrl + Alt + End

From remote Mac computer.

control + option + end

Cacti installation by yum

Cacti installation by yum.

1. yum install httpd httpd-devel

2. yum install mysql-server mysql-devel

3. yum install php php-mysql php-pdo php-mbstring php-devel

4. /usr/bin/mysqladmin -u root password 'some_password'

5. /etc/init.d/mysqld start

6. mysql -u root -p

7. delete unnecessary users

8. mysql> create database cacti;

9. mysql> exit

10. mysql -u root -p cacti <  /usr/share/doc/cacti-0.8.8b/cacti.sql

11. access the http://cacti_IP:/cacti with your web browser.

The following error was displayed in my web browser.

Cannot connect to MySQL server on 'localhost'. Please make sure you have specified a valid MySQL database name in 'include/config.php'

I checked the web for the solution. the httpd user can't access to the db.php

chown -R apache:root /etc/cacti/db.php

chown -R apache:root /var/lib/cacti

Then, I can display the initial setup screen for cacti.

continues with the default value. then finished setup wizard.

I saw the login screen. The username and password should be 'admin'. But I couldn't log in.

I searched the web again and got the solution.

Edit the php.ini file and changed the following settings.

mysql> update user_auth set password=md5('admin') where username='admin'; 

How to add spam filters on zimbra server.

# su - zimbra

Check current setting.
$ zmprov gacf | grep zimbraMtaRestriction
Output:
zimbraMtaRestriction: reject_non_fqdn_sender

Currently reject_non_fqdn_sender is set.
Adding sbl.spamhaus.org

$ zmprov mcf zimbraMtaRestriction reject_non_fqdn_sender zimbraMtaRestriction "reject_rbl_client sbl.spamhaus.org"

Check the result:
$ zmprov gacf | grep zimbraMtaRestriction
zimbraMtaRestriction: reject_non_fqdn_sender
zimbraMtaRestriction: reject_rbl_client sbl.spamhaus.org

Other options

zmprov mcf 

zimbraMtaRestriction reject_invalid_hostname 

zimbraMtaRestriction reject_non_fqdn_hostname 

zimbraMtaRestriction reject_non_fqdn_sender 

zimbraMtaRestriction "reject_rbl_client dnsbl.njabl.org" 

zimbraMtaRestriction "reject_rbl_client cbl.abuseat.org" 

zimbraMtaRestriction "reject_rbl_client bl.spamcop.net" 

zimbraMtaRestriction "reject_rbl_client dnsbl.sorbs.net" 

zimbraMtaRestriction "reject_rbl_client sbl.spamhaus.org" 

zimbraMtaRestriction "reject_rbl_client relays.mail-abuse.org"

According to zimbra wiki