About NoSQL and its Databases

If you have heard "NoSQL" word from your friends or colleague and curious to know more about "NoSQL". Then, you came at the right place. In this article, we will talk about NoSQL and its databases and how they are different from most commonly used relational databases like Oracle, MySQL, SQL Server and etc..

About NoSQL

A NoSQL sometimes refer as "Not Only SQL" or "Non Relational", which means it can use SQL as well but it will be using non relational structure to store data. 

About NoSQL Databases

Most of NoSQL database are primarily non-relational database or distributed database, which stores data in form of key-value, wide column, graph, or document, which are different from those used by default in relational databases, which makes some operations faster in NoSQL. There are more than 255 NoSQL databases currently available to use on which MongoDB, CouchDB, HBase and Cassandra are widely used databases. 

NoSQL Database Categories

The NoSQL databases can be categorized into 5 types :

1. Column : In this category of databases, data gets stored in column base structure in distributed data stores environment, where information is stored on more than one node. To store data, it uses tuple (key-value pair), which consists 3 elements Unique Name (to reference the column), Value (actual data) and Timestamp (to determine updated value). The well known databases in this category are Cassandra and HBase.



Unique Name	Unique Name	Unique Name
Value	Value	Value
Timestamp	Timestamp	Timestamp





2. Document : In this category of databases are designed for storing, retrieving, and managing document-oriented information. The central concept of a document-oriented database is the notion of a document. While each document-oriented database implementation differs on the details of this definition, in general, they all assume documents encapsulate and encode data (or information) in some standard formats or encodings. Encodings in use include XML, YAML, JSON, and BSON, as well as binary forms like PDF and Microsoft Office documents (MS Word, Excel, and so on). The well known databases in this category are CouchDB and MongoDB.



{
    Website: “http://apptech-solution.blogspot.in”, 
    Title: “About NoSQL and it’s Databases”, 
    Tag: “Database, NoSQL"
}





3. Key-Value : They are designed for storing, retrieving, and managing associative arrays, a data structure more commonly known today as a dictionary or hash. Dictionaries contain a collection of objects, or records, which in turn have many different fields within them, each containing data. These records are stored and retrieved using a key that uniquely identifies the record, and is used to quickly find the data within the database. The well known databases in this category are Dynamo and Redis.





Key 1	Value 1
Key 2	Value 2
Key 3	Value 3
Key 4	Value 4
Key 5	Value 5





4. Graph : Graph databases are based on graph theory, which will have nodes, edges and properties.
• Nodes represent entities such as people, businesses, accounts, or any other item you might want to keep track of. 
• Edges, also known as graphs or relationships, are the lines that connect nodes to other nodes; they represent the relationship between them. 
• Properties are pertinent information that relate to nodes.
The well known databases in this category are Neo4J and OrientDB.






5. Multi-Model :  It is designed to support multiple data models against a single, integrated backend. Document, graph, relational, and key-value models are examples of data models that may be supported by a multi-model database. The well known databases in this category are OrientDB and ArangoDB.

Schedule SQL Query to execute on specific time/interval

Imagine, you have created some stuff and to demo it, you have created a page where you accept dummy data from your guest users and you are populating them into your database. At some point of time, your database would be flooded with records that would be useless and this will definitely slow down the performance of your database. Now, in this case, you need to manually clean up your database in order to keep the performance of your database at it best.

But, instead of doing it manually you would think about something, which should automate cleanup work on regular interval. At this point of time, you probably think about CRON job. But, instead, we will use MySQL database inbuilt option called EVENTS, which we can use to execute certain task in database like executing SQL script, calling stored procedure etc.. 

Also, please note that, this is not like triggers. Triggers are fired on data or structure change but MySQL events are scheduled based on time/interval.

First of all Enable EVENT Scheduler

SET GLOBAL event_scheduler = ON;

Then, validate current status of Event Scheduler

SELECT @@event_scheduler;

Now, create EVENT to execute your SQL query

CREATE EVENT e_store_ts 
ON SCHEDULE
EVERY 1 HOUR
DO
UPDATE myschema.yourtable set mycolumn='N' -- update this table

List all EVENT created

SHOW EVENTS;

That's it. !!

Schedule MySQL Database backup on CPanel or Linux

If you running a business online and you are using MySQL database to keep your customer and product related information. Then, you always try to keep database backed up, just in case, if your database got crashed, you will have a copy of data. 

Taking manual backup will never be a good idea as it takes time and resource whereas you will find many paid services which will help you to keep your database backed up on regular interval. But, if you are looking for something, which can do same job without paying single penny to any third party. Then, this might be useful article for you

First of all, we create a backup folder, where we will keep our database backup dump file. And then, we will write the shell script, which will create database backup file and place it to our backup folder. 

#!/bin/sh
now="$(date +'%d_%m_%Y_%H_%M_%S')"
filename="db_backup_$now".gz
backupfolder=“"
fullpathbackupfile="$backupfolder/$filename"
logfile="$backupfolder/"backup_log_"$(date +'%Y_%m')".txt
echo "mysqldump started at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"
mysqldump —user= —password= --default-character-set=utf8  | gzip > "$fullpathbackupfile"
echo "mysqldump finished at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"
chown  "$fullpathbackupfile"
chown  "$logfile"
echo "file permission changed" >> "$logfile"
find "$backupfolder" -name db_backup_* -mtime +2 -exec rm {} \;
echo "old files deleted" >> "$logfile"
echo "operation finished at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"
echo "*****************" >> "$logfile"
exit 0

Above script will create backup file and place it to your backup folder and also it will remove dump file, which is older than 2 days. You can keep this shell script file to any location (but I would recommend to keep this file in same backup folder)

Now, depending on your requirement, you should schedule this script to run on specific time/interval by adding it to CRON job. And to add new CRON job, you need to provide following details, path of shell script file (i.e. command) and email id where you want to receive notification for any failure/success.

Once, you have successfully created CRON job, then you will see a CRON job entry created, which will be executed only specified time/interval 




Upon your specified time/interval, your backup folder will start getting populated with your database backup and log files.

That's it. !!

Let me know, if you got into any issue while configuring it.