Enable slow query log in MySQL

# touch /var/log/mysql/mysql-slow.log

# chown mysql:mysql /var/log/mysql/mysql-slow.log

If you want to leave slow query logging on, you will need to add 3 lines to /etc/mysql/my.cnf under the [mysqld] section:

slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=2

Restart your mysql :- 

#/etc/init.d/mysqld restart

To test if it is working correctly, simply run a SELECT SLEEP(x) query where "x" is longer than the long_query_time

root@anneke:~# mysql -e 'SELECT SLEEP(5);'

Then you can look at the logfile and check id your "slow" query is there:

less /var/log/mysql/mysql-slow.log

                        Backup all your MySQL databases

#!/bin/bash
# set -x
start_time=`date +%k" Hour "-" "%M" Min "-" "%S" Sec."`
MyUSER="root"
MyPASS="admin"
MyHOST="localhost"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
MYSQLADMIN="$(which mysqladmin)"
DEST="/mysqlbackup"
ARCHIVE="/mysqlbackup"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
GZIP="$(which gzip)"
MAIL="$(which mail)"
SUBJECT1="Some thing goes wrong with Mysql Database. Backup operation not Performed"
EMAIL="raj13aug@gmail.com"
EMAILMESSAGE="/tmp/emailmessage.txt"
#HOST="$(hostname)"
DEST="/mysqlbackup"
MBD="$DEST/mysql-`(date +"%d-%m-%Y")`"

# Get data in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y")"

# File to store current backup file
FILE=""

# Store list of databases
DBS=""

# DO NOT BACKUP these databases
#IGGY="test"
#IGGY="information_schema"

EXCLUDE="test information_schema"


# Get all database list first
mysql_alive=`"$MYSQLADMIN" -uroot -padmin -s ping|grep alive|wc -l`

if [ "$mysql_alive" == 1 ];then
{
if [ -d "$DEST" ] && [ -w "$DEST" ];then
    {
            echo "Ready to Perform Backup"
    }
    else
    {
            echo "Backup Directory either does not exists or not Writeable by User: `echo $USER|tr [a-z] [A-Z]`" > $EMAILMESSAGE
            /bin/mail -s "$SUBJECT1" "$EMAIL" < $EMAILMESSAGE
            exit 0
    }
     fi
[ ! -d $MBD ] && mkdir -p $MBD || :

# Only root can access it!
$CHOWN 0.0 -R $DEST
$CHMOD 0600 $DEST
DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"
for db in $DBS
do
    skipdb=-1
    if [ "$EXCLUDE" != "" ];
    then
        for i in $EXCLUDE
        do
            [ "$db" == "$i" ] && skipdb=1 || :
        done
    fi

    if [ "$skipdb" == "-1" ] ; then
        FILE="$MBD/$db.$NOW.sql.gz"

        $MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS --routines $db |  $GZIP -9 > $FILE
    fi
done

end_time=`date +%k" Hour "-" "%M" Min "-" "%S" Sec."`
echo "Backup Operation Started at $start_time  and Ended at $end_time" > $EMAILMESSAGE
/bin/mail -s "Mysql Database Backup Process Detail on 10.10.3.53(Testing Server App1) " "$EMAIL" < $EMAILMESSAGE
}
    else
    {
                echo "Oops Can't Connect to Database, may be your database server is DOWN" > $EMAILMESSAGE
                /bin/mail -s "$SUBJECT1" "$EMAIL" < $EMAILMESSAGE
                exit 0;
        echo
    }
fi

dim_m=`date +%m`
dim_y=`date +%Y`
days=`cal $dim_m $dim_y | egrep -v '[A-Za-z]' | wc -w`
date_d=`date +%d`

if [ "$date_d" == "$days" ];
then
echo "Archiving Data"
#cd $DEST
#find .  -type d -mtime -1 -exec ls  -d {} \; | cut -d/ -f2| grep -vE '\.' | xargs mv `awk '{print $1}'` "$ARCHIVE/"
#rm -rf $DEST/*
#else
#echo "Today is not the Last day of the month, so nothing to archve";
fi


# To delete files older than 8 days
find /mysqlbackup -mtime +7 -type d | xargs rm -rf

Create an FTP Server With VSFTPD Centos 6.4

                                             

What is FTP ?
FTP stands for File Transfer Protocol. An FTP server allows clients to connect to it either
anonymously or with a username and password combination.

which port VSFTP server works ?
FTP uses two ports, 20 and 21. By default, the FTP server listens for requests on port 21.After a connection is established, the client sends commands to the server on port 21. However, port 20 is used when the server sends data back to the client.

Install the Vsftpd service.

#yum install vsftpd

Default ftp port : 21

Default configuration file : /etc/vsftpd.conf

Edit the Configuration file and UN-comment it .

#vim /etc/vsftpd/vsftpd.conf

anon_upload_enable=NO --> anonymous disable

xferlog_enable=YES -->create logrotate log file

xferlog_file=/var/log/xferlog -->file contains logging information from the FTP server

Note :- User restricted to access other directory apart from user ftp directory.

chroot_local_user=NO

chroot_list_enable=YES

chroot_list_file=/etc/vsftpd/chroot_list

Note :- Save and exit from the file

create file under /etc/vsftpd directory

#touch /etc/vsftpd/chroot_list

Start Vsftpd Service :

# service vsftpd start

Make Vsftpd Service auto start on boot up :

# chkconfig vsftpd on

Port listen command :

#netstat -tulpn | grep vsftpd ---> port listen or not

Configure Selinux :

# getsebool -a | grep ftp

allow_ftpd_anon_write --> off

allow_ftpd_full_access --> off

allow_ftpd_use_cifs --> off

allow_ftpd_use_nfs --> off

ftp_home_dir --> off

ftpd_disable_trans --> off

ftpd_is_daemon --> on

httpd_enable_ftp_server --> off

tftpd_disable_trans --> off

Selinux status : 

# getenforce

Enforcing

For normal users set Boolean

# setsebool -P ftp_home_dir on

Add these rules to your iptables configuration:

#iptables -A INPUT -p tcp --dport 21 -j ACCEPT

#iptables -A OUTPUT -p tcp --sport 20 -j ACCEPT

Ensure that the space-separated list of modules contains the FTP connection-tracking module

# vim /etc/sysconfig/iptables-config

IPTABLES_MODULES="ip_conntrack_ftp"

Note : Save and close the file 

Save iptables services :

# service iptables save

iptables: Saving firewall rules to /etc/sysconfig/iptables:    [  OK  ]

Restart iptables services :

# service iptables restart

Restart vsftpd services and Testing it :

# service vsftpd restart

Shutting down vsftpd:                                          [  OK  ]

Starting vsftpd for vsftpd:                                    [  OK  ]

vsftpd status 

# service vsftpd status

vsftpd (pid 5531) is running...

create users for ftp access (eg: vino)

Note :- use the below command to create the user in nologin shell(recommended)

#mkdir /var/ftp_package

#useradd -s /sbin/nologin -d /var/ftp_package vino

#passwd vino

Note :- In order to enable the ftpuser read and write the data in your home dir, change the permission and take ownership:

#chown -R vino:vino /var/ftp_package

#chmod 775 /var/ftp_package

Add user vino to file to allow ftp access

#vim /etc/vsftpd/chroot_list
#add the user ‘vino’ to the file
vino

Note :- Save and exit from the file

connect to VSFTPD  Server using command line.

1.Type ftp and enter to continue.

2.Type open and enter to continue.

3.Type ftp server ip address and enter to connect to ftp server.

4.If connected, type login name and enter.

5.Type user password and enter. If the username and password are valid, then you'll be in.

#ftp

ftp> open

(to) 192.168.4.25

Connected to 192.168.4.25

---------- Welcome to Pure-FTPd [privsep] [TLS] ----------

220-You are user number 1 of 50 allowed.

220-Local time is now 10:42.

Server port: 21.

220-This is a private system - No anonymous login

220-IPv6 connections are also welcome on this server.

220 You will be disconnected after 15 minutes of inactivity.

Name : vino

Password required

Password: enter password of vino

list a directory content in ftp server:

ftp> ls

200 PORT command successful

150 Connecting to port 39545

-rwxrwxrwx 1 vino vino 16 Nov 4 06:36 File.txt

FTP command :-

The ftp command used to download a single file from a server is get.

The ftp command used to download a multiple files from a server is mget.

The ftp command used to upload a single file to a server is put.

The ftp command used to upload a multiple files to a server is mput.

If you want to exit frm ftp, use ftp 

#bye 

Monitor clients connected to vsftpd :-
Add the configure file vim /etc/vsftpd/vsftpd.conf

Make sure you have enabled the config option, "setproctitle_enable=YES" and restart your vsftpd server

#ps -Cvsftpd -o user, pid, stime, cmd

For Windows 7 :-

Use FileZilla - An FTP Client to Upload, Download and Manage files

Download URL :-

https://filezilla-project.org/download.php