Category Archives: Database

Collection of articles about databases like Postgres, MySQL, Riak, Redis and technologies like RDBMS, NoSQL.

mysql

How To Import and Export Databases in MySQL

MySQL is an open-source relational database management system. Its name is a combination of “My”, the name of co-founder Michael Widenius’s daughter, and “SQL”, the abbreviation for Structured Query Language.

A relational database organizes data into one or more data tables in which data types may be related to each other; these relations help structure the data. SQL is a language programmers use to create, modify and extract data from the relational database, as well as control user access to the database. In addition to relational databases and SQL, an RDBMS like MySQL works with an operating system to implement a relational database in a computer’s storage system, manages users, allows for network access and facilitates testing database integrity and creation of backups.

What is mysql?

mysql is a simple SQL shell (with GNU readline capabilities). It supports interactive and
non-interactive use. When used interactively, query results are presented in an ASCII-table format.
When used non-interactively (for example, as a filter), the result is presented in tab-separated
format. The output format can be changed using command options.

What is mysqldump?

The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump
a database or a collection of databases for backup or transfer to another SQL server (not necessarily
a MariaDB server). The dump typically contains SQL statements to create the table, populate it, or
both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML
format.

Export a MySQL Database

Use mysqldump to export your database:

mysqldump -u username -p database_name > database_name-dump.sql

You can compress the data on the run using pipe and gzip.

mysqldump -u username -p database_name | gzip > database_name-dump.sql.gz

*Using GZIP will save a lot of space on disk for huge databases.

Import a MySQL Database

Use mysql to import your database:

Create the database first.

mysql > CREATE DATABASE database_name;

Import the database now.

mysql -u username -p database_name < database_name-dump.sql

If the file is compressed with gzip. use zcat to extract on the run.

zcat database_name-dump.sql.gz | mysql -u username -p database_name

Handy scripts for admins who do backups daily

bkpmysqlgz() {
    set -x
    DEST=${1:-/opt/backups}
    shift
    sudo mkdir -p $DEST
    DATE=$(DATE)
    if command -v pv > /dev/null 2>&1; then
        sudo mysqldump $@ | pv | gzip > $DEST/"${@: -1}"-$DATE.sql.gz
    else
        sudo mysqldump $@ | gzip > $DEST/"${@: -1}"-$DATE.sql.gz
    fi
    ls -lh $DEST/"${@: -1}"-$DATE.sql.gz
    set +x
}

using script.

$ bkpmysqlgz /opt/backups -u root -p secret dbname

How to fix missing foreign keys and/or indexes – AWS DMS

AWS Database Migration Service (DMS) helps you migrate databases to AWS quickly and securely. The source database remains fully operational during the migration, minimizing downtime to applications that rely on the database. The AWS Database Migration Service can migrate your data to and from most widely used commercial and open-source databases.

The Database Migration Service is a data mover. It creates only the structures required to migrate your data, (this is for performance reasons mainly.) Additionally, it doesn’t migrate secondary indexes, default values, procedures, triggers, auto increment columns etc. These objects/modifications need to be made after migrating the data, (and typically prior to switching the app.)

But it can be fixed by importing schema manually.

Problem

missing foreign keys and/or indexes

Solution

To fix foreign keys & indexes missing issue, follow this

  1. Import Database schema manually to RDS.
  2. Set “`Target table preparation mode“` to “`Truncate“`

Using JSON:

Using DMS GUI:

Now run the task.

You will see all foreign keys and indexes in target (RDS).

Installing ELK Stack(Elasticsearch,Logstash,Kibana) on CentOS with Sentinl plugin

ELK stack is also known as the Elastic stack, consists of Elasticsearch, Logstash, and Kibana. It helps you to have all of your logs stored in one place and analyze the issues by correlating the events at a particular time.

This guide helps you to install ELK stack on CentOS 7 / RHEL 7.

Components

Logstash – It does the processing (Collect, enrich and send it to Elasticsearch) of incoming logs sent by beats (forwarder).

Elasticsearch – It stores incoming logs from Logstash and provides an ability to search the logs/data in a real-time

Kibana – Provides visualization of logs.

Sentinl –  Sentinl extends Siren Investigate and Kibana with Alerting and Reporting functionality to monitor, notify and report on data series changes using standard queries, programmable validators and a variety of configurable actions – Think of it as a free an independent “Watcher” which also has scheduled “Reporting” capabilities (PNG/PDFs snapshots).

SENTINL is also designed to simplify the process of creating and managing alerts and reports in Siren Investigate/Kibana 6.xvia its native App Interface, or by using native watcher tools in Kibana 6.x+.

 

Beats – Installed on client machines, send logs to Logstash through beats protocol.

Environment

To have a full-featured ELK stack, we would need two machines to test the collection of logs.

ELK Stack

Operating system : CentOS 7 Minimal
IP Address       : 192.168.1.10
HostName         : server.lintel.local

Filebeat

Operating System : CentOS 7 Minimal
IP Address       : 192.168.1.20
HostName         : client.lintel.local

Prerequisites

Install Java

Since Elasticsearch is based on Java, make sure you have either OpenJDK or Oracle JDK is installed on your machine.

Here, I am using OpenJDK 1.8.

yum -y install java-1.8.0 wget

Verify the Java version.

java -version

Output:

java version "1.8.0_181"
Java(TM) SE Runtime Environment (build 1.8.0_181-b13)
Java HotSpot(TM) 64-Bit Server VM (build 25.181-b13, mixed mode)

Configure ELK repository

Import the Elastic signing key.

rpm --import https://artifacts.elastic.co/GPG-KEY-elasticsearch

Setup the Elasticsearch repository and install it.

vi /etc/yum.repos.d/elk.repo

Add the below content to the elk.repo file.

[elasticsearch-6.x]
name=Elasticsearch repository for 6.x packages
baseurl=https://artifacts.elastic.co/packages/6.x/yum
gpgcheck=1
gpgkey=https://artifacts.elastic.co/GPG-KEY-elasticsearch
enabled=1
autorefresh=1
type=rpm-md

Install Elasticsearch

Elasticsearch is an open source search engine, offers a real-time distributed search and analytics with the RESTful web interface. Elasticsearch stores all the data are sent by the Logstash and displays through the web interface (Kibana) on users request.

Install Elasticsearch.

yum install -y elasticsearch

Configure Elasticsearch to start during system startup.

systemctl daemon-reload
systemctl enable elasticsearch
systemctl start elasticsearch

Use CURL to check whether the Elasticsearch is responding to the queries or not.

curl -X GET http://localhost:9200

Output:

{
  "name" : "1DwGO86",
  "cluster_name" : "elasticsearch",
  "cluster_uuid" : "xboS_6K5Q2OO1XA-QJ9GIQ",
  "version" : {
    "number" : "6.4.0",
    "build_flavor" : "default",
    "build_type" : "rpm",
    "build_hash" : "595516e",
    "build_date" : "2018-08-17T23:18:47.308994Z",
    "build_snapshot" : false,
    "lucene_version" : "7.4.0",
    "minimum_wire_compatibility_version" : "5.6.0",
    "minimum_index_compatibility_version" : "5.0.0"
  },
  "tagline" : "You Know, for Search"
}

Install Logstash

Logstash is an open source tool for managing events and logs, it collects the logs, parse them and store them on Elasticsearch for searching. Over 160+ plugins are available for Logstash which provides the capability of processing the different type of events with no extra work.

Install the Logstash package.

yum -y install logstash

Create SSL certificate (Optional)

Filebeat (Logstash Forwarder) are normally installed on client servers, and they use SSL certificate to validate the identity of Logstash server for secure communication.

Create SSL certificate either with the hostname or IP SAN.

(Hostname FQDN)

If you use the Logstash server hostname in the beats (forwarder) configuration, make sure you have A record for Logstash server and also ensure that client machine can resolve the hostname of the Logstash server.

Go to the OpenSSL directory.

cd /etc/pki/tls/

Now, create the SSL certificate. Replace green one with the hostname of your real Logstash server.

openssl req -x509 -nodes -newkey rsa:2048 -days 365 -keyout private/logstash-forwarder.key -out certs/logstash-forwarder.crt -subj /CN=server.lintel.local

Configure Logstash

Logstash configuration can be found in /etc/logstash/conf.d/. Logstash configuration file consists of three sections input, filter, and the output. All three sections can be found either in a single file or separate files end with .conf.

I recommend you to use a single file for placing input, filter and output sections.

vi /etc/logstash/conf.d/logstash.conf

In the first section, we will put an entry for input configuration. The following configuration sets Logstash to listen on port 5044 for incoming logs from the beats (forwarder) that sit on client machines.

Also, add the SSL certificate details in the input section for secure communication – Optional.

input {
 beats {
   port => 5044
   
   # Set to False if you do not use SSL 
   ssl => true

   # Delete below linesif you do not use SSL
   ssl_certificate => "/etc/pki/tls/certs/logstash-forwarder.crt"
   ssl_key => "/etc/pki/tls/private/logstash-forwarder.key"
   }
}

In the filter section. We will use Grok to parse the logs ahead of sending it to Elasticsearch. The following grok filter will look for the syslog labeled logs and tries to parse them to make a structured index.

filter {
if [type] == "syslog" {
    grok {
      match => { "message" => "%{SYSLOGLINE}" }
    }

    date {
match => [ "timestamp", "MMM  d HH:mm:ss", "MMM dd HH:mm:ss" ]
}
  }

}

For more filter patterns, take a look at grokdebugger page.

In the output section, we will define the location where the logs to get stored; obviously, it should be Elasticsearch.

output {
 elasticsearch {
  hosts => localhost
    index => "%{[@metadata][beat]}-%{+YYYY.MM.dd}"
       }
stdout {
    codec => rubydebug
       }
}

Now start and enable the Logstash service.

systemctl start logstash
systemctl enable logstash

You can troubleshoot any issues by looking at Logstash logs.

cat /var/log/logstash/logstash-plain.log

Install & Configure Kibana

Kibana provides visualization of logs stored on the Elasticsearch. Install the Kibana using the following command.

cd /opt
wget https://artifacts.elastic.co/downloads/kibana/kibana-6.2.4-x86_64.rpm
rpm -i kibana-6.2.4-x86_64.rpm

Edit the kibana.yml file.

vi /etc/kibana/kibana.yml

By default, Kibana listens on localhost which means you can not access Kibana interface from external machines. To allow it, edit the below line with your machine IP.

server.host: "192.168.1.10"

Uncomment the following line and update it with the Elasticsearch instance URL. In my case, it is localhost.

elasticsearch.url: "http://localhost:9200"

Start and enable kibana on system startup.

systemctl start kibana
systemctl enable kibana

Install Sentinl plugin:

/usr/share/kibana/bin/kibana-plugin install https://github.com/sirensolutions/sentinl/releases/download/tag-6.2.4/sentinl-v6.2.4.zip
systemctl restart kibana

Install and Configure Filebeat

There are four beats clients available

  1. Packetbeat – Analyze network packet data.
  2. Filebeat – Real-time insight into log data.
  3. Topbeat – Get insights from infrastructure data.
  4. Metricbeat – Ship metrics to Elasticsearch.

To analyze the system logs of the client machine (Ex. client.lintel.local), we need to install filebeat. Create beats.repo file.

vi /etc/yum.repos.d/beats.repo

Add the below content to the above repo file.

[elasticsearch-6.x]
name=Elasticsearch repository for 6.x packages
baseurl=https://artifacts.elastic.co/packages/6.x/yum
gpgcheck=1
gpgkey=https://artifacts.elastic.co/GPG-KEY-elasticsearch
enabled=1
autorefresh=1
type=rpm-md

Now, install Filebeat using the following command.

yum -y install filebeat

Set up a host entry on the client machine in case your environment does not have DNS server.

vi /etc/hosts

Make an host entry like below on the client machine.

 192.168.1.10 server.lintel.local server

Filebeat (beats) uses SSL certificate for validating Logstash server identity, so copy the logstash-forwarder.crt from the Logstash server to the client.

Skip this step, in case you are not using SSL in Logstash.

scp -pr root@server.lintel.local:/etc/pki/tls/certs/logstash-forwarder.crt /etc/ssl/certs/

Filebeat configuration file is in YAML format, which means indentation is very important. Make sure you use the same number of spaces used in the guide.

Open up the filebeat configuration file.

vi /etc/filebeat/filebeat.yml

On top, you would see the prospectors section. Here, you need to specify which logs should be sent to Logstash and how they should be handled. Each prospector starts with – character.

For testing purpose, we will configure filebeat to send /var/log/messages to Logstash server. To do that, modify the existing prospector under paths section.

Comment out the – /var/log/*.log to avoid sending all .log files present in that directory to Logstash.

filebeat.inputs:
- type: log
 
      # Change to true to enable this input configuration. 
      enabled: true

      # Paths that should be crawled and fetched.
      paths:

        - /var/log/messages
        # - /var/log/*.log

.  .  .

Comment out the section output.elasticsearch: as we are not going to store logs directly to Elasticsearch.

Now, find the line output.logstash and modify the entries like below. This section defines filebeat to send logs to Logstash server server.lintel.local on port 5044 and mention the path where the copied SSL certificate is placed

Replace server.lintel.local with IP address in case if you are using IP SAN.

.   .   .

output.logstash:
  # The Logstash Server
  hosts: ["server.lintel.local:5044"]


  # Comment out this line if Logstash is not using SSL
  ssl.certificate_authorities: ["/etc/ssl/certs/logstash-forwarder.crt"]

.   .   .

Restart the service.

systemctl restart filebeat

Beats logs are typically found syslog file.

cat /var/log/filebeat/filebeat

Access Kibana

Access the Kibana using the following URL.

http://your-ip-address:5601/

You would get the Kibana’s home page.

Install Elasticsearch, Logstash, and Kibana (ELK Stack) on CentOS 7 – Kibana Starting Page

On your first login, you have to map the filebeat index. Go to Management >> Index Patterns.

Install Elasticsearch, Logstash, and Kibana (ELK Stack) on CentOS 7 – Management

Type the following in the Index pattern box.

filebeat-*
Install Elasticsearch, Logstash, and Kibana (ELK Stack) on CentOS 7 – Create Index Pattern

You should see at least one filebeat index something like above. Click Next step.

Select @timestamp and then click on Create.

@timestamp
Install Elasticsearch, Logstash, and Kibana (ELK Stack) on CentOS 7 – Configure Timestamp

Verify your index patterns and its mappings.

Install Elasticsearch, Logstash, and Kibana (ELK Stack) on CentOS 7 – Index Mappings

Now, click Discover to view the incoming logs and perform search queries.

Install Elasticsearch, Logstash, and Kibana (ELK Stack) on CentOS 7 – Discover Logs

You can see sentinl plugin here

That’s All.

 

Reference list:

https://github.com/sirensolutions/sentinl

https://www.itzgeek.com

How to Setup Redis Cluster from Source

What is redis

Redis is an open source in-memory database. It stores data in key-value format. Because of residing in memory, redis is an excellent tool for caching. Redis provides a rich set of data types. This gives redis upper hand over Memcached. Apart from caching, redis can be used as distributed message broker.

Redis Cluster and Sentinels

To achieve high availability, redis can be deployed in cluster along with Sentinels. Sentinel is a feature of redis. Multiple sentinels are deployed across redis clusters for monitoring purpose. When redis master goes down, sentinels elect a new master from slaves. When old master comes up again, it is added as slave.

Another use case of clustering is a distribution of load. In high load environment, we can send write requests to master and read request to slaves.

This tutorial is specifically focused on Redis Cluster Master Slave model. We will not cover data sharding across cluster here. In data sharding, keys are distributed across multiple redis nodes.

Setup for tutorial

For this tutorial, we will use 3 (virtual) servers. On one server Redis master will reside while other two servers will be used for slaves. Standard redis port is 6379. To differentiate easily, we will run master on 6379 port and slaves on
6380 and 6381 ports. Same will be applied for sentinel services. Master sentinel will listen on 16379 port while slave sentinels will be on 16380 and 16381.

Lets put this easy way.

    Server 1 (Redis Master)
    Redis Service at host 192.168.0.1 and port tcp:6379
    Sentinel Service at host 192.168.0.1 and port tcp:16379
    Server 2 (Redis Slave 1)
    Redis Service at host 192.168.0.2 and port tcp:6380
    Sentinel at host 192.168.0.2 and port tcp:16380
    Server 3 (Redis Slave 2)
    Redis Service at host 192.168.0.3 and port tcp:6381
    Sentinel Service at host 192.168.0.3 and port tcp:16381

This tutorial is tested on CentOS 6.9. For CentOS 7.X, check below Notes Section.

Installation

We will follow same installation steps for setting up of all servers. Only difference will be in configurations.

  • Step 1: Grab redis source, make and install
  • Step 2: Setup required directories
  • Step 3: Configure redis master
  • Step 4: Configure redis master sentinel
  • Step 5: Add low privileged user to run redis
  • Step 6: Setup init scripts
  • Step 7: Start service

Server 1 (Redis Master)


Install Redis

    cd /opt
    wget download.redis.io/releases/redis-4.0.9.tar.gz
    tar -xf redis-4.0.9.tar.gz
    cd redis-4.0.9
    make
    make install

Setup required directories

    mkdir -p /etc/redis /var/run/redis /var/log/redis /var/redis/6379
    cp redis.conf redis.conf.bak
    cp redis.conf /etc/redis/6379.conf

Configure redis master

Edit config file `/etc/redis/6379.conf` in your favorite editor and change below options.

    bind 192.168.0.1
    port 6379
    daemonize yes
    pidfile "/var/run/redis/redis_6379.pid"
    logfile "/var/log/redis/redis_6379.log"
    dir "/var/redis/6379"

Configure redis master sentinel

Add config file for sentinel at `/etc/redis/sentinel_6379.conf`. Open a file and add below content

    bind 192.168.0.1
    port 16379
    sentinel monitor redis-cluster 192.167.0.1 6379 2
    sentinel down-after-milliseconds redis-cluster 5000
    sentinel parallel-syncs redis-cluster 1
    sentinel failover-timeout redis-cluster 10000
    daemonize yes
    pidfile "/var/run/redis/sentinel_6379.pid"
    dir "/var/redis/6379"

Add non-privileged user

    adduser redis -M -g daemon
    passwd -l redis
    chown -R redis:daemon /opt/redis-4.0.9
    chown -R redis:daemon /var/run/redis
    chown -R redis:daemon /var/log/redis
    chown -R redis:daemon /var/redis/

Setup init scripts

You can find sample init scripts in Notes section below.

    cp redis-6379-init-script /etc/init.d/redis_6379
    chmod 750 /etc/init.d/redis_6379
    chkconfig redis_6379 on
    cp sentinel-6379-init-script /etc/init.d/sentinel_6379
    chmod 750 /etc/init.d/sentinel_6379
    chkconfig sentinel_6379 on

Start service

    service redis_6379 start
    service sentinel_6379 start

Server 2 (Redis Slave 1)


Install Redis

    cd /opt
    wget download.redis.io/releases/redis-4.0.9.tar.gz
    tar -xf redis-4.0.9.tar.gz
    cd redis-4.0.9
    make
    make install

Setup required directories

    mkdir -p /etc/redis /var/run/redis /var/log/redis /var/redis/6380
    cp redis.conf redis.conf.bak
    cp redis.conf /etc/redis/6380.conf

Configure redis slave 1

Edit config file `/etc/redis/6380.conf` in your favorite editor and change below options.

    bind 192.168.0.2
    port 6380
    daemonize yes
    pidfile "/var/run/redis/redis_6380.pid"
    logfile "/var/log/redis/redis_6380.log"
    dir "/var/redis/6380"
    slaveof 192.168.0.1 6379

Configure redis slave 1 sentinel

Add config file for sentinel at `/etc/redis/sentinel_6380.conf`. Open a file and add below content

    bind 192.168.0.2
    port 16380
    sentinel monitor redis-cluster 192.167.0.1 6379 2
    sentinel down-after-milliseconds redis-cluster 5000
    sentinel parallel-syncs redis-cluster 1
    sentinel failover-timeout redis-cluster 10000
    daemonize yes
    pidfile "/var/run/redis/sentinel_6380.pid"
    dir "/var/redis/6380"

Add non-privileged user

    adduser redis -M -g daemon
    passwd -l redis
    chown -R redis:daemon /opt/redis-4.0.9
    chown -R redis:daemon /var/run/redis
    chown -R redis:daemon /var/log/redis
    chown -R redis:daemon /var/redis/

Setup init scripts

You can find sample init scripts in Notes section below. Change `$HOST` and `$PORT` values accordingly

cp redis-6380-init-script /etc/init.d/redis_6380
    chmod 750 /etc/init.d/redis_6380
    chkconfig redis_6380 on
cp sentinel-6380-init-script /etc/init.d/sentinel_6380
    chmod 750 /etc/init.d/sentinel_6380
    chkconfig sentinel_6380 on

Start service

    service redis_6380 start
    service sentinel_6380 start

Server 3 (Redis Slave 2)


Install Redis

    cd /opt
    wget download.redis.io/releases/redis-4.0.9.tar.gz
    tar -xf redis-4.0.9.tar.gz
    cd redis-4.0.9
    make
    make install

Setup required directories

    mkdir -p /etc/redis /var/run/redis /var/log/redis /var/redis/6381
    cp redis.conf redis.conf.bak
    cp redis.conf /etc/redis/6381.conf

Configure redis slave 2

Edit config file `/etc/redis/6381.conf` in your favorite editor and change below options.

    bind 192.168.0.3
    port 6381
    daemonize yes
    pidfile "/var/run/redis/redis_6381.pid"
    logfile "/var/log/redis/redis_6381.log"
    dir "/var/redis/6381"
    slaveof 192.168.0.1 6379

Configure redis slave 2 sentinel

Add config file for sentinel at `/etc/redis/sentinel_6381.conf`. Open a file and add below content

    bind 192.168.0.3
    port 16381
    sentinel monitor redis-cluster 192.167.0.1 6379 2
    sentinel down-after-milliseconds redis-cluster 5000
    sentinel parallel-syncs redis-cluster 1
    sentinel failover-timeout redis-cluster 10000
    daemonize yes
    pidfile "/var/run/redis/sentinel_6381.pid"
    dir "/var/redis/6381"

Add non-privileged user

    adduser redis -M -g daemon
    passwd -l redis
    chown -R redis:daemon /opt/redis-4.0.9
    chown -R redis:daemon /var/run/redis
    chown -R redis:daemon /var/log/redis
    chown -R redis:daemon /var/redis/

Setup init scripts

You can find sample init scripts in Notes section below. Change `$HOST` and `$PORT` values accordingly

    cp redis-6381-init-script /etc/init.d/redis_6381
    chmod 750 /etc/init.d/redis_6381
    chkconfig redis_6381 on
    cp sentinel-6381-init-script /etc/init.d/sentinel_6381
    chmod 750 /etc/init.d/sentinel_6381
    chkconfig sentinel_6381 on

Start service

    service redis_6381 start
    service sentinel_6381 start

Sentinel Testing

    # Connect to any redis sentinel
    /usr/local/bin/redis-cli -h 192.168.0.3 -p 16381

    # To see current masters
    192.168.0.3:16381> SENTINEL masters

    # To see slaves for given cluster
    192.168.0.3:16381> SENTINEL slaves redis-cluster

Redis Fail-over Testing

For fail-over testing, we can take down redis-master either using init script or below command.

    # Connect to redis master and execute below command
    /usr/local/bin/redis-cli -h 192.168.0.1 -p 6379
    192.168.0.1:6379> DEBUG SEGFAULT

Also we can force sentinel to run fail over using below command

    # Forced failure
    192.168.0.1:6379> SENTINEL failover redis-cluster
    # Check after few seconds. You should get new master
    192.168.0.1:6379> SENTINEL masters

Sample init scripts

Redis Init Script

#!/bin/sh
#
# Simple Redis init.d script conceived to work on Linux systems
# as it does use of the /proc filesystem.
#
# chkconfig: 345 85 15
# description:  Redis is persistent key-value database
# processname: redis_6379

# Source function library
. /etc/init.d/functions

REDISHOST=192.168.0.1
REDISPORT=6379
EXEC=/usr/local/bin/redis-server
USER=redis
CLIEXEC=/usr/local/bin/redis-cli
PIDFILE=/var/run/redis/redis_${REDISPORT}.pid
CONF="/etc/redis/${REDISPORT}.conf"

case "$1" in
    start)
        if [ -f $PIDFILE ]
        then
                echo "$PIDFILE exists, process is already running or crashed"
        else
                echo "Starting Redis server..."
                daemon --user $USER $EXEC $CONF
        fi
        ;;
    stop)
        if [ ! -f $PIDFILE ]
        then
                echo "$PIDFILE does not exist, process is not running"
        else
                PID=$(cat $PIDFILE)
                echo "Stopping ..."
                daemon --user $USER $CLIEXEC -h $REDISHOST -p $REDISPORT shutdown
                while [ -x /proc/${PID} ]
                do
                    echo "Waiting for Redis to shutdown ..."
                    sleep 1
                done
                echo "Redis stopped"
        fi
        ;;
    restart)
        stop
        sleep 3
        start
        ;;
    *)
        echo "Usage: $PROG_NAME {start|stop|restart}"
		exit 1
		;;
esac

Sentinel Init Script

#!/bin/sh
#
# Simple Redis Sentinel init.d script conceived to work on Linux systems
# as it does use of the /proc filesystem.
#
# chkconfig: 345 86 15
# description:  Redis Sentinel to monitor redis cluster
# processname: sentinel_6379

# Source function library
. /etc/init.d/functions

REDIS_PORT=6379
SENTINEL_PORT=16379
EXEC=/usr/local/bin/redis-server
CLIEXEC=/usr/local/bin/redis-cli
USER=redis

PIDFILE=/var/run/redis/sentinel_${REDIS_PORT}.pid
CONF="/etc/redis/sentinel_${REDIS_PORT}.conf"

case "$1" in
    start)
        if [ -f $PIDFILE ]
        then
                echo "$PIDFILE exists, process is already running or crashed"
        else
                echo "Starting Redis Sentinel server..."
                daemon --user $USER $EXEC $CONF --sentinel
        fi
        ;;
    stop)
        if [ ! -f $PIDFILE ]
        then
                echo "$PIDFILE does not exist, process is not running"
        else
                PID=$(cat $PIDFILE)
                echo "Stopping ..."
                kill -9 $PID
                rm $PIDFILE
                while [ -x /proc/${PID} ]
                do
                    echo "Waiting for Redis Sentinel to shutdown ..."
                    sleep 1
                done
                echo "Redis Sentinel stopped"
        fi
        ;;
    restart)
        stop
        sleep 3
        start
        ;;
    *)
        echo "Usage: $PROG_NAME {start|stop|restart}"
		exit 1
		;;
esac

Notes

Security

  • NEVER EVER run redis on public interface
  • If redis is deployed in cloud environment like AWS, set up security groups/firewalls carefully. Most of times, cloud providers use ephemeral ips. Because of ephermal ips, even redis is bound to private ip, it can be accessed over public interface.
  • For more security, dangerous commands can be disabled(renamed). But be careful with them in cluster environment.
  • Redis also provides simple authentication mechanism. It is not covered here because of scope.

Sentinel management

  • During redis fail-over, config files are rewritten by sentinel program. So when restarting redis-cluster, be careful.

Sources

  • https://redis.io/topics/cluster-tutorial
  • https://redis.io/topics/security
  • https://redis.io/commands/debug-segfault

What is DBF file? How to read it in linux and python?

What is DBF files ?

A DBF file is a standard database file used by dBASE, a database management system application. It organises data into multiple records with fields stored in an array data type. DBF files are also compatible with other “xBase” database programs, which became an important feature because of the file format’s popularity.

Tools which can read or open DBF files

Below are list of program which can read and open dbf file.

  • Windows
    1. dBase
    2. Microsoft Access
    3. Microsoft Excel
    4. Visual Foxpro
    5. Apache OpenOffice
    6. dbfview
    7. dbf Viewer Plus
  • Linux
    1. Apache OpenOffice
    2. GTK DBF Editor

How to read file in linux ?

“dbview” command available in linux, which can read dbf files.

Below code snippet show how to use dbview command.

[lalit : temp]₹ dbview test.dbf 
Name       : John
Surname    : Miller
Initials   : JM
Birthdate  : 19800102

Name       : Andy
Surname    : Larkin
Initials   : AL
Birthdate  : 19810203

Name       : Bill
Surname    : Clinth
Initials   : 
Birthdate  : 19820304

Name       : Bobb
Surname    : McNail
Initials   : 
Birthdate  : 19830405

[lalit : temp]₹ 

 How to read it using python ?

dbfread” is the library available in python to read dbf files. This library reads DBF files and returns the data as native Python data types for further processing.

dbfread requires python 3.2 or 2.7.  dbfread is a pure python module, so doesn’t depend on any packages outside the standard library.

You can install library by the command below.

pip install dbfread

The below code snippet can read dbf file and retrieve data as python dictionary.

>>> from dbfread import DBF

>>> for record in DBF('people.dbf'):
...     print(record)

<strong>Out Put</strong>
OrderedDict([('NAME', 'Alice'), ('BIRTHDATE', datetime.date(1987, 3, 1))])
OrderedDict([('NAME', 'Bob'), ('BIRTHDATE', datetime.date(1980, 11, 12))])

You can also use the with statement:

with DBF('people.dbf') as table:
    ...

By default the records are streamed directly from the file.  If you have enough memory you can load them into a list instead. This allows random access

>>> table = DBF('people.dbf', load=True)
>>> print(table.records[1]['NAME'])
Bob
>>> print(table.records[0]['NAME'])
Alice

 How to Write content in DBF file using python ?

dbfpy is a python-only module for reading and writing DBF-files.  dbfpy can read and write simple DBF-files.

You can install it by using below command

pip install dbfpy

The below example shows how to create dbf files and write records in to it.

import datetime
from mx import DateTime
from dbfpy import dbf

## create empty DBF, set fields

db = dbf.Dbf("test.dbf", new=True)
db.addField(
    ("NAME", "C", 15),
    ("SURNAME", "C", 25),
    ("INITIALS", "C", 10),
    ("BIRTHDATE", "D"),
)

## fill DBF with some records

for name, surname, initials, birthdate in (
    ("John", "Miller", "JM", (1980, 1, 2)),
    ("Andy", "Larkin", "AL", datetime.date(1981, 2, 3)),
    ("Bill", "Clinth", "", DateTime.Date(1982, 3, 4)),
    ("Bobb", "McNail", "", "19830405"),
):
    
    rec = db.newRecord()
    rec["NAME"] = name
    rec["SURNAME"] = surname
    rec["INITIALS"] = initials
    rec["BIRTHDATE"] = birthdate
    rec.store()
db.close()

Also you can update a dbf file record using dbf module.

The below example shows how to update a record in a .dbf file.

db = dbf.Dbf("test.dbf")
rec = db[2]
rec["INITIALS"] = "BC"
rec.store()

 

Asynchronous DB Operations in Twisted

Twisted is an asynchronous networking framework. Other Database API Implementations have blocking interfaces.

For this reason, twisted.enterprise.adbapi was created. It is a non-blocking interface,which allows you to access a number of different RDBMSes.

General Method to access DB API.

1 ) Create a Connection with db.

 db = dbmodule.connect('dbname','user','password')

2) create a cursor.

 cursor = db.cursor()

3) do a query.

resultset = cursor.query('SELECT * FROM table WHERE condition=expression')

Cursor blocks to response in asynchronous framework. Those delays are unacceptable when using an asynchronous framework such as Twisted.
To Overcome blocking interface, twisted provides asynchronous wrapper for db module such as twisted.enterprise.adbapi

Database Connection using adbapi API.

To use adbapi, we import dependencies as below

 from twisted.enterprise import adbapi

1) Connect Database using adbapi.ConnectionPool

db = adbapi.ConnectionPool("MySQLdb",db="agentdata",user="root",passwd="<yourpassword>")

Here, We do not need to import dbmodule directly.
dbmodule.connect are passed as extra arguments to adbapi.ConnectionPool’s Constructor.

2) Run Database Query

query = ("SELECT * FROM agentdetails WHERE name = '%s'" % (name))
return dbpool.runQuery(query).addCallback(self.receiveResult).addErrback(self.errorquery)

Here, I used ‘%s’ paramstyle for mysql. if you use another database module, you need to use compatible paramstyle. for more, use DB-API specification.

Twisted doesn’t attempt to offer any sort of magic parameter munging – runQuery(query,params,…) maps directly onto cursor.execute(query,params,…).

This query returns Deferred, which allows arbitrary callbacks to be called upon completion (or failure).

Demo : Select, Insert and Update query in Database.

from twisted.enterprise import adbapi
import datetime,logging
from twisted.internet import reactor


"""
Test DB : This File do database connection and basic operation.
"""

log = logging.getLogger("Test DB")

dbpool = adbapi.ConnectionPool("MySQLdb",db="agentdata",user="root",passwd="<yourpassword>")

class AgentDB():

    def getTime(self):
        log.info("Get Current Time from System.")
        time = str(datetime.datetime.now()).split('.')[0]
        return time

    def doSelect(self,name):
        log.info("Select operation in Database.")
        query = ("SELECT * FROM agentdetails WHERE name = '%s'" % (name))
        return dbpool.runQuery(query).addCallback(self.receiveResult).addErrback(self.errorquery)

    def receiveResult(self,result):
        print "Receive Result"
        print result
        # general purpose method to receive result from defer.
        return result

    def errorquery(self,result):
        print "error received", result
        return result

    def doInsert(self,name,status,state):
        log.info("Insert operation in Database.")
        query = """INSERT INTO agentdetails (name,state,status,logtime) values('%s','%s','%s','%s')""" % (
        name,state,status,self.getTime())
        return dbpool.runQuery(query).addCallback(self.receiveResult)

    def doUpdate(self,name,status,state):
        log.info("Update operation in Database.")
        query = ("UPDATE agentdetails SET status = '%s', state = '%s',logtime = '%s' WHERE name = '%s'" % (
        status,state, self.getTime(),name))
        return dbpool.runQuery(query).addCallback(self.receiveResult)

    def checkDB(self):
        self.doSelect('1011')
        self.doInsert('Test','Test','Test')
        self.doUpdate('Test','SecondTest','SecondTest')



a= AgentDB()
a.checkDB()
reactor.run()

Here, I have used MySQLdb api, agentdata as a database name, root as a user, 123456 as a password.
Also, I have created select, insert and update query for select, insert and update operation respectively.
runQuery method returns deferred. For this, add callback and error back to handle success and failure respectively.

DBSlayer

DBSlayer is a simpler way to proxy mysql.

DBSlayer can be queried via JSON over HTTP, and the responses can be given in either one of the following supported languages : JSON, PHP and Python which makes processing the database results.

Multi-threaded server written in C.

Features :

  • Reduce configuration
  • Reduce dependencies
  • Handle failovers
  • Simple load balancing
  • Easy to monitor
  • Minimal performance overhead
  • Work in different configuration scenarios
  • Support different programming languages

Installing DBSlayer :

git clone https://github.com/derekg/dbslayer.git

./configure

make

make install

Database URI :

http://machine:port/db?URLENCODED(JSON OBJECT)

http://machine:port/dbform?URLENCODED(HTML FORM)

Parameters :

SQL – SQL to execute

Example Request :

http://localhost:9090/dbform?SQL=SELECT+name+FROM+emp

Example Response :

{'RESULT': {'HEADER': ['name'],

'ROWS' : [['name']],

'TYPES' : ['MYSQL_TYPE_VAR_STRING'],

'SERVER' : 'servername'

}}

Example for python :

import urllib2, urllib, json

def dbex():
    uri = 'http://localhost:9090/dbform?SQL=%s'
    data = urllib2.urlopen(uri%urllib.quote('SELECT * FROM market')).read()
    print json.loads(data)

dbex()

Start dbslayer :

dbslayer -c /path/dbslayer.conf -s servername

This starts up a DBSlayer daemon on 9090 (this default port can be changed which acts as a proxy for the backend mysql server. this proxy can be queried via JSON over HTTP).

Stop dbslayer :

pkill dbslayer

Other URI/API endpoints :

http://machine:port/stats [Queries per second]

http://machine:port/stats/log [Last 100 requests]

http://machine:port/stats/errors [Last 100 error]

http://machine:port/shutdown [Only from localhost]

How to delete records by disabling foreign key checks or constraints in mysql

If you have a foreign key constraints on your table, you can’t delete records from parent table, but you can delete from child table. The main reason we use the foreign key constraints is to maintain data integrity.

Some times you many need to disable this kind of enforcing against some operations like delete. To do so, you have to set mysql server system variable foreign_key_checks

 set foreign_key_checks=OFF

Now, you can delete or update with out any restriction because of foreign key constraint. But you will mess up the data integrity.

You can reset it back to enforce for integrity

 set foreign_key_checks=ON

There are global and local system variables. If you set the global variables it applies to all clients which are connected. If you change the local variable it applies to that current client session only. Here is how you can query local and global variable.

mysql> show global variables like "foreign_key_checks";
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | ON    |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> show local variables like "foreign_key_checks";
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | OFF   |
+--------------------+-------+
1 row in set (0.00 sec)

To sent global variable you just need to add global , where to set local you need to local. By default local or session variable will be changed

mysql> set local foreign_key_checks=OFF

To set global variable

mysql> set global foreign_key_checks=OFF

NOTE: Make sure you to revert the variable back to original state to maintain data integrity in your database.

 

MySQL tips for beginners and intermediate

This article will take you through few MySQL tips those are pretty helpful.

We will start with few useful statements,

Do you know, if you are working offline with mysql  and you need some help on syntax. You can get it right away on terminal  with simple statement  help followed by command or statement.

help

mysql> help  <COMMAND or STATEMENT>

The above statement in the mysql console will give you the whole syntax and it’s usage.

Here is  an example:

mysql> help create database
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.

URL: http://dev.mysql.com/doc/refman/5.6/en/create-database.html


For suppose you want know available list of date and time functions. Here is how you will get,

mysql> help  date and time functions;
You asked for help about help category: "Date and Time Functions"
For more information, type 'help <item>', where <item> is one of the following
topics:
   ADDDATE
   ADDTIME
   CONVERT_TZ
   CURDATE
   CURRENT_DATE
   CURRENT_TIME
   CURRENT_TIMESTAMP
   CURTIME
   DATE FUNCTION
   DATEDIFF
   DATE_ADD
   DATE_FORMAT
   DATE_SUB
   DAY
   DAYNAME
   DAYOFMONTH
   DAYOFWEEK
   DAYOFYEAR
   EXTRACT
   FROM_DAYS
   FROM_UNIXTIME
   GET_FORMAT
   HOUR
   LAST_DAY
   LOCALTIME
   LOCALTIMESTAMP
   MAKEDATE
   MAKETIME
   MICROSECOND
   MINUTE
   MONTH
   MONTHNAME
   NOW
   PERIOD_ADD
   PERIOD_DIFF
   QUARTER
   SECOND
   SEC_TO_TIME
   STR_TO_DATE
   SUBDATE
   SUBTIME
   SYSDATE
   TIME FUNCTION
   TIMEDIFF
   TIMESTAMP FUNCTION
   TIMESTAMPADD
   TIMESTAMPDIFF
   TIME_FORMAT
   TIME_TO_SEC
   TO_DAYS
   TO_SECONDS
   UNIX_TIMESTAMP
   UTC_DATE
   UTC_TIME
   UTC_TIMESTAMP
   WEEK
   WEEKDAY
   WEEKOFYEAR
   YEAR
   YEARWEEK

 

status

The  status statement in the mysql console will give you the quick overview about connection, mysql server and client details

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.6.27, for debian-linux-gnu (x86_64) using  EditLine wrapper

Connection id:		38
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.6.27-0ubuntu0.14.04.1 (Ubuntu)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/var/run/mysqld/mysqld.sock
Uptime:			6 hours 4 min 6 sec

Threads: 1  Questions: 112  Slow queries: 0  Opens: 87  Flush tables: 1  Open tables: 80  Queries per second avg: 0.005

Pager

Are you frustrated with querying lot of records to analyze in mysql conventional command line client console? No worries,  can set the pager in mysql client console. You can set it to either less or more. Here is the way to do it,

mysql>\P less
PAGER set to 'less'
mysql>\P more
PAGER set to 'more'

Let’s say if you set pager to less, you will see your query output like wise when you read file using less command. You try the following statements.

mysql>\P less
mysql>show status;
Reset pager:

you can reset your pager using \P with arguments;

mysql>\P

Processlist

The following statement show processlist will give you overview about all connected clients and their work, even queries they are being executed,

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 38 | root | localhost | NULL | Query   |    0 | init  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

explain

An other statement which very helpful in optimizing your queries and schema is explain . You can’t imagine how helpful this statement is, it will show how mysql is using indexes how many rows or records it’s searching for a result. Based on the report you can optimize the queries and even you can add indexes to table if required to speed up results.

Here is simple example,

mysql> EXPLAIN SELECT t.term_id, tt.parent, tt.count, tt.taxonomy FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('category') ORDER BY t.name ASC;
+----+-------------+-------+--------+---------------------------+------------------+---------+---------------------------+------+-----------------------+
| id | select_type | table | type   | possible_keys             | key              | key_len | ref                       | rows | Extra                 |
+----+-------------+-------+--------+---------------------------+------------------+---------+---------------------------+------+-----------------------+
|  1 | SIMPLE      | t     | ALL    | PRIMARY                   | NULL             | NULL    | NULL                      |    1 | Using filesort        |
|  1 | SIMPLE      | tt    | eq_ref | term_id_taxonomy,taxonomy | term_id_taxonomy | 138     | wordpress.t.term_id,const |    1 | Using index condition |
+----+-------------+-------+--------+---------------------------+------------------+---------+---------------------------+------+-----------------------+
2 rows in set (0.00 sec)

You will see a record for each table from which you are selecting or used in query. Find more about explain here

 

Other than few handy statements explained above, now we will go through few tips to avoid common pit falls in MySQL.

  • Always try to avoid creating the user with wildcard host . Doing so will mitigate the possible risks of getting hacked.
  • Only give required permissions to the user, so other databases or things will not get screwed up either intentionally or accidentally because of user or script.
  • When you are deleting something from production or from important place, use transactions. Here is how you will do it,
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> -- do you work like deleting records and other stuff
mysql> delete from users where id in (1, 3, 4);
mysql>commit;

This is useful to avoid common problems caused by either typos or accidental mistakes. Before you type commit, you can always review your changes and rollback if changes are unintended.

ysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> -- do you work like deleting records and other stuff
mysql> delete from users;  -- lets say you forgot where  in delete query
mysql>rollback;   -- You can rollback your accidental mistake