My-SQL: Deleting all duplicate rows but keeping one row for table

MySQL Query :

DELETE FROM `Tablename` WHERE id NOT IN ( SELECT MIN(id) FROM (SELECT * FROM Tablename ) as t1 GROUP BY field1, field2, field3, field4, field5 ) 

Here fields are column on which you want to group the duplicate rows.

Linux / ubuntu Server monitoring script and commands

Check server  Memory, CPU load, Storage with date time.

#! /bin/bash

printf "Memory\t\tOS_Disk\t\tHome_Disk\tCPU\t\tTime\n"

end=$((SECONDS+3600))

while [ $SECONDS -lt $end ]; do

MEMORY=$(free -m | awk 'NR==2{printf "%.2f%%\t\t", $3*100/$2 }')

DISK=$(df -h | awk '$NF=="/"{printf "%s\t\t", $5}')

DISKHOME=$(df -h | awk '$NF=="/home"{printf "%s\t\t", $5}')

CPU=$(top -bn1 | grep load | awk '{printf "%.2f%%\t\t\n", $(NF-2)}')

DATELOG=$(date "+%Y-%m-%d %T")

echo "$MEMORY$DISK$DISKHOME$CPU$DATELOG"

sleep 20

done

save this file as stats.sh

run file 

#./stats.sh

Tools

Basic listed below 

strace – discover system calls and signals to a process.

tcpdump – raw network traffic monitoring.

netstat – network connections monitoring.

htop – real time process monitoring.

iftop – real time network bandwidth monitoring.

lsof – view which files are opened by which process.

Sysdig - A Powerful System Monitoring and Troubleshooting Tool for Linux

Command for sysdig

$sudo sysdig

$csysdig

$sysdig -cl

$Sysdig -r trace.scap

$sysdig -c ps

$sysdig -c ps

$sysdig -c list_login_shells

$sysdig -c spy_users

For more detail click here

Basic Command for  Server monitoring

$last

$last reboot | shutdown

$htop

$top

$jobs

$who -b

$ps -aux| grep apache|wc -l  (check apache threads)

Setup Filesystem Quotas on Linux / Ubuntu Server

Installing the Quota Tools

sudo apt update

sudo apt install quota

Check Quota Version :

quota --version

Installing the Quota Kernel Module

First need  to check, we will use find to search for the quota_v1 and quota_v2 modules in the /lib/modules/... directory:

find /lib/modules/`uname -r` -type f -name '*quota_v*.ko*'

Output : 

/lib/modules/4.15.0-45-generic/kernel/fs/quota/quota_v1.ko

/lib/modules/4.15.0-45-generic/kernel/fs/quota/quota_v2.ko

If you get no output from the above command, install

sudo apt install linux-image-extra-virtual

OR 

Resolve this install kernel dependencies, like this:

apt-get -y install linux-image-generic

apt-get -y install linux-headers-generic

apt-get -y install linux-image-extra-`uname -r`

After that we need to add the quota modules to start with boot:

echo quota_v1 >> /etc/modules

echo quota_v2 >> /etc/modules

Updating Filesystem Mount Options

sudo nano /etc/fstab

LABEL=/home    /home   ext2   defaults,usrquota,grpquota  0 0

Remount the filesystem to make the new options take effect:

sudo mount -o remount /home

Enabling Quotas

sudo quotacheck -ugm /home

Now we’re ready to turn on the quota system:

sudo quotaon -v /home

Configuring Quotas for a User

Using edquota to Set a User Quota

sudo edquota -u  jagdeep

# edquota ramesh

Disk quotas for user jagdeep (uid 50001):
  Filesystem           blocks       soft       hard     inodes     soft     hard
  /dev/sda3           1419352          0          0       1686        0        0

Let’s update our sammy user to have a block quota with a 100MB soft limit, and a 110MB hard limit:

# edquota ramesh

Disk quotas for user jagdeep (uid 50001):
  Filesystem         blocks       soft       hard     inodes     soft     hard
  /dev/sda3        1419352       100M          110M   1686    0        0

Save and close the file

sudo quota -vs jagdeep

Using setquota to Set a User Quota

sudo setquota -u sammy 200M 220M 0 0 /home

Generating Quota Reports

sudo repquota -s /home

# repquota /home
*** Report for user quotas on device /dev/sda3
Block grace time: 7days; Inode grace time: 7days
                        Block limits                File limits
User            used    soft    hard  grace    used  soft  hard  grace
----------------------------------------------------------------------
root      --  566488       0       0           5401     0     0
nobody    --    1448       0       0             30     0     0
jagdeep    -- 1419352       0       0           1686     0     0
john      --   26604       0       0            172     0     0

Configuring a Grace Period for Overages

sudo setquota -t 864000 864000 /home

Output

Block grace time: 10days; Inode grace time: 10days

. . .

Disable quota for a Linux user or group on the shell
Example for disabling the quota for the user "testuser":

setquota -u testuser 0 0 0 0 -a /home

Example for disabling quota for the group "testgroup":

setquota -g testgroup 0 0 0 0 -a /home

Installing PHP and Configuring Nginx to Use the PHP Processor

Nginx does not contain native PHP processing like some other web servers, install php-fpm, which mean for "fastCGI process manager". Nginx to pass PHP requests to this software for processing.

Install the php-fpm module along with an additional helper package for mysql database server php-mysql, which will allow PHP to communicate with your database backend. Do this by typing:
sudo apt install php-fpm, php-mysql

Add the following content, which was taken and slightly modified from the default server block configuration file, to your new server block configuration file: /etc/nginx/sites-available/default

server {
        listen 80;
        root /var/www/html;
        index index.php index.html index.htm index.nginx-debian.html;
        server_name test.com;

        location / {
                try_files $uri $uri/ =404;
        }

        location ~ \.php$ {
                include snippets/fastcgi-php.conf;
                fastcgi_pass unix:/var/run/php/php7.2-fpm.sock;
        }

        location ~ /\.ht {
                deny all;
        }
}

Test your new configuration file for syntax errors by typing:
sudo nginx -t

Reload Nginx to make the necessary changes:
sudo systemctl reload nginx

Creating a PHP File to Test Configuration

To do this, use your text editor to create a test PHP file called info.php in your document root:

sudo nano /var/www/html/info.php

This is valid PHP code that will return information about your server:

<?php
phpinfo();

Open URL : http://your_server_domain_or_IP/info.php

Setup Userdir On Nginx

The commands below opens Nginx default site configuration file.

sudo nano /etc/nginx/sites-available/default

Then add the highlighted block of code the settings below:

# Default server configuration

server {
        listen 80 default_server;
        listen [::]:80 default_server;

        # SSL configuration
        # listen 443 ssl default_server;
        # listen [::]:443 ssl default_server;

        root /var/www/html;

        # Add index.php to the list if you are using PHP
        index index.html index.htm index.nginx-debian.html;
        server_name example.com www.example.com;

         location ~ ^/~(.+?)(/.*)?$ {
         alias /home/$1/public_html$2;
         index index.html index.htm;
         autoindex on;
           }

}

Creating User Directories

mkdir ~/public_html

In the ~/public_html folder, create html documents to be shared and accessed via the webserver.

Restart Nginx webserver to load the settings.

sudo systemctl restart nginx.service

Now test to open browsing followed by username :

example: http://example.com/~jagdeep