Surya's Blog!

Category: Linux

  • How to get the all the database and table sizes of MySQL or MariaDB?

    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.

  • How to set or change default text editor for crontab in Linux?

    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.

     

  • How to install Comodo SSL certificate with NGNIX web server?

    How to install Comodo SSL certificate with NGNIX web server?

    Installation method for COMODO SSL Certificate

    comodo_ssl_installation

    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.

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

    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.