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