Thursday 18 October 2012

FTP Log file Format

The CentOS FTP log file format:

Fri May 14 05:16:12 2010 0 ::ffff:1.2.3.4 11974 /home/user/public_html/index.php a _ i r user ftp 0 * c

Fri May 14 05:16:12 2010 -- Date/time stamp, nothing complicated.

0 -- Transfer time, in whole seconds (this transfer took less than a second, so zero).

::ffff:1.2.3.4 -- Remote host where the user connected from.

11974 -- Size of the transferred file (in bytes).

/home/user/public_html/index.php --  Full path to the uploaded file.

a -- Transfer type, a = ASCII (plain-text files), b = binary (everything else)

_   --  Action flag, C = compressed, U = uncompressed; T = tar'ed; _ = no action was taken.

i -- Direction, i = incoming, o = outgoing, d = deleted.

r -- Access mode, a = anonymous user, r = real (normal) user.

user -- Local username authenticated with.

ftp -- The service being invoked (almost always FTP).

0 -- Authentication method, 0 = none, 1 = RFC931 authetication.

* --  User ID or * if not available (virtual user).

c -- Completion status, c = completed, i = incomplete.

Linux / UNIX setup SSH with DSA public key authentication (password less login)

Step #1: Generate DSA Key Pair

Use ssh-keygen command as follows:

$ ssh-keygen -t dsa

Output:

Enter file in which to save the key (/home/vivek/.ssh/id_dsa): Press [Enter] key
Enter passphrase (empty for no passphrase): myPassword
Enter same passphrase again: myPassword
Your identification has been saved in /home/vivek/.ssh/id_dsa.
Your public key has been saved in /home/vivek/.ssh/id_dsa.pub.
The key fingerprint is:
04:be:15:ca:1d:0a:1e:e2:a7:e5:de:98:4f:b1:a6:01 vivek@vivek-desktop
Caution: a) Please enter a passphrase different from your account password and confirm the same.
b) The public key is written to /home/you/.ssh/id_dsa.pub.
c) The private key is written to /home/you/.ssh/id_dsa.
d) It is important you never-ever give out your private key.


Step #2: Set directory permission

Next make sure you have correct permission on .ssh directory:

$ cd
$ chmod 755 .ssh


Step #3: Copy public key

Now copy file ~/.ssh/id_dsa.pub on Machine #1 (tom) to remote server jerry as ~/.ssh/authorized_keys:

$ scp ~/.ssh/id_dsa.pub user@jerry:.ssh/authorized_keys

Command to type on your remote server called jerry

Login to your remote server and make sure permissions are set correct:

$ chmod 600 ~/.ssh/authorized_keys

Task: How do I login from client to server with DSA key?

Use scp or ssh as follows from your local computer:
$ ssh user@jerry
$ ssh user@remote-server.com
$ scp file user@jerry:/tmp

You will still be asked for the passphrase for the DSA key file each time you connect to remote server called jerry, unless you either did not enter a passphrase when generating the DSA key pair.

Task: How do I login from client to server with DSA key but without typing a passhrase i.e. password-less login?

Type the following command at shell prompt:

$ exec /usr/bin/ssh-agent $SHELL
$ ssh-add


Output:

Enter passphrase for /home/vivek/.ssh/id_dsa: myPassword
Identity added: /home/vivek/.ssh/id_dsa (/home/vivek/.ssh/id_dsa)
Type your passhrase once. Now, you should not be prompted for a password whenever you use ssh, scp, or sftp command.

If you are using GUI such as Gnome use the command:

$ ssh-askpass

OR

$ /usr/lib/openssh/gnome-ssh-askpass

To save your passphrase during your GNOME session under Debian / Ubuntu, do as follows:
a) Click on System
b) Select Preferences
c) Select Session
d) Click on New
e) Enter "OpenSSH Password Management" in the Name text area
f) Enter /usr/lib/openssh/gnome-ssh-askpass in the command text area.

g) Click on close to save the changes
h) Log out and then log back into GNOME. After GNOME is started, a dialog box will appear prompting you for your passphrase. Enter the passphrase requested. From this point on, you should not be prompted for a password by ssh, scp, or sftp.

MySQL database server remote access

You need type the following commands which will allow remote connections.

Step # 1: Login Using SSH (if server is outside your data center)

First, login over ssh to remote MySQL database server:

ssh user@server1.cyberciti.biz


Step # 2: Edit my.cnf File

Once connected you need to edit the MySQL server configuration file my.cnf using a text editor such as vi.

If you are using Debian Linux file is located at /etc/mysql/my.cnf location
If you are using Red Hat Linux/Fedora/Centos Linux file is located at /etc/my.cnf location
If you are using FreeBSD you need to create a file /var/db/mysql/my.cnf
Edit /etc/my.cnf, run:

# vi /etc/my.cnf

Step # 3: Once file opened, locate line that read as follows

[mysqld]
Make sure line skip-networking is commented (or remove line) and add following line

bind-address=YOUR-SERVER-IP
For example, if your MySQL server IP is 65.55.55.2 then entire block should be look like as follows:

[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/English
bind-address = 65.55.55.2
# skip-networking

....
..
....
Where,

bind-address : IP address to bind to.
skip-networking : Don’t listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. Since you need to allow remote connection this line should be removed from my.cnf or put it in comment state.

Step# 4 Save and Close the file

If you are using Debian / Ubuntu Linux, type the following command to restart the mysql server:
# /etc/init.d/mysql restart

If you are using RHEL / CentOS / Fedora / Scientific Linux, type the following command to restart the mysql server:

# /etc/init.d/mysqld restart

If you are using FreeBSD, type the following command to restart the mysql server:

# /usr/local/etc/rc.d/mysql-server stop
# /usr/local/etc/rc.d/mysql-server start


OR
# /usr/local/etc/rc.d/mysql-server restart

Step # 5 Grant access to remote IP address

Connect to mysql server:

$ mysql -u root -p mysql

Grant access to a new database
If you want to add a new database called foo for user bar and remote IP 202.54.10.20 then you need to type the following commands at mysql> prompt:

mysql> CREATE DATABASE foo;
mysql> GRANT ALL ON foo.* TO bar@'202.54.10.20' IDENTIFIED BY 'PASSWORD';


How Do I Grant Access To An Existing Database?
Let us assume that you are always making connection from remote IP called 202.54.10.20 for database called webdb for user webadmin, To grant access to this IP address type the following command At mysql> prompt for existing database, enter:

mysql> update db set Host='202.54.10.20' where Db='webdb';
mysql> update user set Host='202.54.10.20' where user='webadmin';


Step # 6: Logout of MySQL

Type exit command to logout mysql:

mysql> exit

Step # 7: Open port 3306

You need to open TCP port 3306 using iptables or BSD pf firewall.

A sample iptables rule to open Linux iptables firewall

/sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT

OR only allow remote connection from your web server located at 10.5.1.3:

/sbin/iptables -A INPUT -i eth0 -s 10.5.1.3 -p tcp --destination-port 3306 -j ACCEPT

OR only allow remote connection from your lan subnet 192.168.1.0/24:

/sbin/iptables -A INPUT -i eth0 -s 192.168.1.0/24 -p tcp --destination-port 3306 -j ACCEPT

Finally save all rules (RHEL / CentOS specific command):

# service iptables save

A sample FreeBSD / OpenBSD pf rule ( /etc/pf.conf)
pass in on $ext_if proto tcp from any to any port 3306
OR allow only access from your web server located at 10.5.1.3:

pass in on $ext_if proto tcp from 10.5.1.3 to any port 3306 flags S/SA synproxy state

Step # 8: Test it

From your remote system or your desktop type the following command:

$ mysql -u webadmin –h 65.55.55.2 –p

Where,

-u webadmin: webadmin is MySQL username
-h IP or hostname: 65.55.55.2 is MySQL server IP address or hostname (FQDN)
-p : Prompt for password
You can also use the telnet or nc command to connect to port 3306 for testing purpose:
$ echo X | telnet -e X 65.55.55.2 3306

OR

$ nc -z -w1 65.55.55.2 3306

Sample outputs:

Connection to 65.55.55.2 3306 port [tcp/mysql] succeeded!

Monday 1 October 2012

MySQL Password Reset

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !


To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h svnser.ndotcbe.in password 'new-password'


Alternatively you can run:

/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

An Another way to Reset root password:


Stop MySQL:
root#  service mysqld stop

Start MySQL in safe mode:
root#  mysqld_safe --skip-grant-tables &

Log into MySQL as root:
root#  mysql -u root

Reset the password:
mysql>  update mysql.user set password=PASSWORD("YourNewPassW0RD") where User='root';
mysql>  flush privileges; exit;

Log out of MySQL and stop the Safe Mode:
root#  service mysqld stop

Start MySQL in the normal mode:
root#  service mysqld start

Log into MySQL with your new password:
root#  mysql -u root -p
Enter password:
mysql>