MongoDB

I have worked on MongoDB for sometime and wanted to share some knowledge gathered during this time.

Shell script to execute mysql query

We can run mysql queries via shell scripts, since this is required at times .

Say  a query is to be scheduled via crontab and shell script.

Here is a simple shell script example, as an example:

vi testscript.sh

-----------------------------------------------------------------------
#!/usr/bin/bash

DBUSER='username'
DBPASSWD='Password'

Query='show databases'   -- example query

mysql -u $DBUSER -p $DBPASSWD  <


$Query

EOF

----------------------------------------------------------------------

The above is a simple script to show databases and is executed with shell script..

# sh testscript.sh

We can schedule the above in crontab to run at a designated time.

Many such scripts can be written to automate daily tasks - backups etc..

Executing mysql commands from unix command prompt

We can also run mysql queries from unix command prompt. This is useful in many cases, say i want to check status of db cluster or check running processes etc.

I am sharing a basic example here:

mysql -u username -p -e "show databases;"

The above command will ask for password and share output of current databases.

This is a quick way to get required information.

Maridb Galera Cluster

So after working on Oracle RAC, SQL Server Always-On and DB2 HADR now finally i have got a chance to get hands-on Mariadb Galera Cluster and i am amazed. The Galera cluster is an Active-Active cluster , what that means you can write on any node and data is available on all nodes, changes are getting replicated quickly across all nodes and it's just so simple to install while Oracle and other clusters take too much time..

Here i will list done a few commands to monitor Galera cluster status :

MariaDB > Show status like 'wsrep%';

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| wsrep_protocol_version |       |
| wsrep_last_committed   |       |
| ...                    | ...   |
| wsrep_thread_count   

You will see a list of variables related to galera in the output displaying about Number of Nodes in the cluster, their ip addresses, Current status..etc..

Will write more about these in my next post related to Galera.

Select Into OUTFILE Mariadb/MYSQL to export data in csv format

At  times where we need to export Data into csv format in mariadb/mysql,

SELECT INTO OUTFILE will write resulting rows from a query into a file - csv..

Below is a simple example on this :

select * from table INTO OUTFILE  '/path/test.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY ' " '
ESCAPED BY '\\'
LINES TERMINATED BY '\n';

Handling Null values -

Another thing we will notice is having /N in output where we have Null values in table, in few cases requirement is to have blank Fields instead of /N in output. in these cases we need to use IFNULL function in the query

example,

Select name, IFNULL (Date, ' ') from table INTO OUTFILE  '/path/test.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY ' " '
ESCAPED BY '\\'
LINES TERMINATED BY '\n';

With the above we replaced Null with blank in the csv output.

Scheduling Events in Mariadb/MYSQL , event scheduler/Job scheduler

Event schedulers are a way of scheduling Database jobs in Mariadb/MYSQL databases, it is similar to job schedulers used in Oracle and other databases.
There are times where we need to run certain reports or run queries at specified times, these can be accomplished using event schedulers in MariaDB database.
Before scheduling events we need to turn ON event_scheduler variable, we can check the current status using below command :

show Variables like "event_scheduler";

output :
---------------------------------
Variable_Name     |    Value
---------------------------------
event_scheduler    |      ON
---------------------------------

It is visible above that scheduler is turned ON, and hence we can proceed with scheduling jobs.

If the event scheduler is not running and event_scheduler has been set to OFF, use:

SET GLOBAL event_scheduler = ON;

Create event syntax can be studied in detail on Mariadb/MYSQL website, i will create a simple event to demonstrate here.

Create event 'test' On schedule every 24 hours starts 'Date' ENDS 'Date' On Completion Preserve Enable DO
Begin
 select * from test.test1;

We create an event named Test scheduled to run every 24 hours at a specified time with start and END date. If we don't specify On completion Preserve the event will be dropped from the database and need to recreate, hence it is important ot mention On completion Preserve.
Event is also enabled using the Enable clause.

To check event scheduled we can query information_schema.events table, that will provide information about currently scheduled events and their execution statistics.

More details regarding the syntax and scheduling can be found in mariadb website.