Author: Surya

How to get the all the database and table sizes of MySQL or MariaDB?
If you are working with MySQL or MariaDB web server, sooner or later you will be asked what is the size of database or tell me the size of all the database and table sizes.

You can get the a particular databases tables and indexes size using below mentioned SQL query.
```
SELECT 
    table_name AS `Table`, 
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = "$DB_NAME"
    AND table_name = "$TABLE_NAME";
```
To get the size of all the databases and tables in MySQL or MariaDB use below query.
```
SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;
```
This is how you can get all the tables and databases sizes in MySQL. You can later pivot the records to get the cumulative sizes of all the databases in MS Excel or Libreoffice.
November 1, 2017
How to set or change default text editor for crontab in Linux?
To edit the crontab entries you use crontab -e. The command will check for the environment variables $EDITOR or $VISUAL.

So you need to set the $EDITOR or $VISUAL variable. Let’s learn how to do that.

To Set the default editor to (Vim) for crontab editing
```
$ export VISUAL=vim

or

$ export EDITOR=vim

Then
$ crontab -e
```
To set the default editor to (nano) for crontab editing
```
$ export VISUAL=nano
```
To set the default editor to (ed) for crontab editing
```
$ export VISUAL=ed
```
to exit from ed you need to type q and press enter.

This is how you can set the default editor for crontab in linux.
November 1, 2017

How to install Comodo SSL certificate with NGNIX web server?

Installation method for COMODO SSL Certificate

If you have generated the CSR and purchased or renewed the SSL with Comodo, you might have noticed that, the comodo has sent you 4 files instead of 1 one file.

AddTrustExternalCARoot.crt - This is root CA Certificate

COMODORSAAddTrustCA.crt - Intermediate CA Certificate

COMODORSAOrganizationValidationSecureServerCA.crt - This is also intermediate 
CA certificate

domain_name_com.crt - The domain/ Sub Domain name you have provided while generating CSR>

You need to concat the content of those file in specific order. The order is

domain_name.crt
COMODORSAOrganizationValidationSecureServerCA.crt
COMODORSAAddTrustCA.crt
AddTrustExternalCARoot.crt

You can simply open your favourite text editor to do that or if you are working with linux, you can simply use linux cat command to do that.

surya >>/$ cat domain_name.crt COMODORSAOrganizationValidationSecureServerCA.crt  COMODORSAAddTrustCA.crt AddTrustExternalCARoot.crt > domain_name_combined.crt

Now upload the combined or bundled ssl certificate to the location where nginx can find that.

mkdir -p /etc/nginx/ssl/domain_name_com/
scp domain_name_combined.crt user@host:/etc/nginx/ssl/domain_name_com/

Move the private key to the same folder which you have generated while generating CSR.

mv domain_name_com.key /etc/nginx/ssl/domain_name_com/

Now edit the nginx conf and make an entry for SSL certificate

server {
    listen 443;

    ssl on;
    ssl_certificate /etc/nginx/ssl/domain_name_com/domain_name_combined.crt;
    ssl_certificate_key /etc/nginx/ssl/domain_name_com/domain_name_com.key;

    ssl_protocols TLSv1 TLSv1.1 TLSv1.2;

    # Rest of the conf ...

}

Now check for the nginx configuration is valid or not? if valid then reload the nginx conf. Make a practice of validating nginx conf before reloading or restarting nginx.

surya@x ~/$ sudo nginx -t
nginx: the configuration file /etc/nginx/nginx.conf syntax is ok
nginx: configuration file /etc/nginx/nginx.conf test is successful

surya@x ~/$ sudo service nginx reload

Now validate the SSL is installed correctly or not by online tools. like below

https://sslanalyzer.comodoca.com/

This is how you can install the Comodo SSL on nginx.

October 31, 2017

How to give access to specific users to specific buckets on AWS S3
In AWS S3, you might want to provide the access to selected users to selected buckets. To provide the specific permissions you need to add a custom policy in IAM.

Let’s learn,

How to give permission to specific users to specific bucket?

After bucket creation in S3, Navigate to IAM management console and click on “Policies > Create Policy > then select “Create Your Own Policy"

Fill the Policy Name and Description

Fill below JSON to the Policy Document
```
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "s3:ListAllMyBuckets",
            "Resource": "arn:aws:s3:::*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:*"
            ],
            "Resource": [
                "arn:aws:s3:::your-bucket-name",
                "arn:aws:s3:::your-bucket-name/*"
            ]
        }
    ]
}
```
In the above document, you can see that I have given access to list all the buckets – this is necessary, however I have given the full access on “your-bucket-name“.

After this, click on the “Validate Policy“. After successful validation click on “Create Policy“. After successful creation of Policy, attach the policy to specific users.

Tji

This is how you can grant access of S3 bucket to specific users.
October 23, 2017

How to add or change the password (passphrase) of OpenSSH key?

It’s possible you have earlier generated a ssh key without password/ passphrase. Later you found that for security reasons you wish to add a password to the key file.

Let’s learn how to add password or passphrase to existing SSH Key. The method is also valid for changing the passphrase to the key. Use below command to do that.

ssh-keygen -p -f /path/to/keyfile

ssh-keygen man page

-p Requests changing the passphrase of a private key file instead of
 creating a new private key. The program will prompt for the file
 containing the private key, for the old passphrase, and twice for
 the new passphrase.
 
-f filename
 Specifies the filename of the key file.
 Example: ssh-keygen -p -f ~/.ssh/id_rsa

Example:

surya@x:~$ ssh-keygen -p -f /path/to/key
Key has comment 'key'
Enter new passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved with the new passphrase.

This is how you can change or add the password to pre generated OpenSSH Keyfile.

October 23, 2017

How to find files on linux OS ( distributions )
If you are working on Linux OS, finding files effectively always a tricky part.

Like find files greater than 500 MB, find files which are created 2 days ago, find and delete all files recursively which are greater than specific date.

Lets learn these simple linux tricks to work with files.

How to find files or directory in Linux?

For Files command:
```
find /path/to/directory -name "*.txt" -type f
```
For Directory
```
find /path/to/directory -name "nameOfDirectory" -type d
```
In the above commands only type argument is changed from f to d. f represents for file and d represents for directory.

How to find files which are greater than specific size?
```
find /path/to/directory -type f -size +1024M
```
How to find files which are created after number of days?
```
find /path/to/directory -mindepth 1 -mtime +5 -size +700M
```
How to delete files after finding files?

Method 1:
```
find /path/to/directory -mindepth 1 -mtime +5 -size +700M -delete
```
–mindepth is for level

–mtime for number of days

–size is for file size greater than or equal to.

Method 2:
```
find . -name "*.done" -type f -print0 | xargs -0 rm -f
```
How to find files and set permission to 644 or 664?
```
find . -type f -print0 | xargs -0 chmod 0644
```
How to find all directory and set permission to 755 or 775?
```
find . -type d -print0 | xargs -0 chmod 0755
```
This is how you can work with find command. You can always check for man for more arguments.
October 20, 2017
How to create CSR ( Certificate Signing Request ) for new SSL or to renew SSL?
What is SSL?

SSL (Secure Sockets Layer) is a standard security protocol for establishing encrypted connection between a web server and client browser in communication.

The usage of SSL technology ensures that all data transmitted between the web server and clients browser remains encrypted.
To get the SSL from Certificate Authorities, you must need to provide the CSR to them and Certificate Authorities will sign the certificate provided by you. CSR contains the information related to Country, State, Locality, Organisation, Organisation Unit, Common Name – Domain Name and email address as well as public key.

Let’s learn how to generate the CSR?

Step 1: Generate the private key first. If you are renewing the SSL this step is not required. To generate the private key use below mentioned command.
```
surya@x ~/ » openssl genrsa -out blog.suryaelite.com.key 4096

 1 ↵
Generating RSA private key, 4096 bit long modulus
................................++
.........++
e is 65537 (0x010001)
```
2048 or higher bit private key recommended. I have used 4096 bit.

Step 2: Now generate the CSR by using below command.
```
surya@x ~/ » openssl req -new -key blog.suryaelite.com.key -out blog.suryaelite.com.csr 


You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:IN
State or Province Name (full name) [Some-State]:HR
Locality Name (eg, city) []:Gurgaon
Organization Name (eg, company) [Internet Widgits Pty Ltd]:SuryaElite
Organizational Unit Name (eg, section) []:blog
Common Name (e.g. server FQDN or YOUR name) []:blog.suryaelite.com 
Email Address []:surya@suryaelite.com

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:Surya Elite Pvt. Ltd.
```
Now you will have two files, one is private key and second one is CSR. Not you need to send the CSR file to Certificate Authority and then Certificate Authority will sign the Certificate and send you the Certificate.

Now you need to apply the SSL to the web server you are using NGINX or Apache. So this is how you can generate the CSR File.
October 12, 2017