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.
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
Import Database schema manually to RDS.
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).
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.
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
1
2
3
Operating system:CentOS7Minimal
IP Address:192.168.1.10
HostName:server.lintel.local
Filebeat
1
2
3
Operating System:CentOS7Minimal
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.
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.
1
yum install-yelasticsearch
Configure Elasticsearch to start during system startup.
1
2
3
systemctl daemon-reload
systemctl enable elasticsearch
systemctl start elasticsearch
Use CURL to check whether the Elasticsearch is responding to the queries or not.
1
curl-XGET http://localhost:9200
Output:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
{
"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.
1
yum-yinstall 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.
1
cd/etc/pki/tls/
Now, create the SSL certificate. Replace green one with the hostname of your real Logstash server.
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.
1
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.
1
2
3
4
5
6
7
8
9
10
11
12
input{
beats{
port=>5044
# Set to False if you do not use SSL
ssl=>true
<strong># Delete below linesif you do not use SSL</strong>
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.
1
2
3
4
5
6
7
8
9
10
11
12
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.
1
2
3
4
5
6
7
8
9
output{
elasticsearch{
hosts=>localhost
index=>"%{[@metadata][beat]}-%{+YYYY.MM.dd}"
}
stdout{
codec=>rubydebug
}
}
Now start and enable the Logstash service.
1
2
systemctl start logstash
systemctl enable logstash
You can troubleshoot any issues by looking at Logstash logs.
1
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.
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.
1
server.host:"<strong>192.168.1.10</strong>"
Uncomment the following line and update it with the Elasticsearch instance URL. In my case, it is localhost.
Now, install Filebeat using the following command.
1
yum-yinstall filebeat
Set up a host entry on the client machine in case your environment does not have DNS server.
1
vi/etc/hosts
Make an host entry like below on the client machine.
1
192.168.1.10server.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.
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.
1
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
filebeat.inputs:
-type:<strong>log</strong>
<strong># Change to true to enable this input configuration.</strong>
enabled:<strong>true</strong>
<strong># Paths that should be crawled and fetched.</strong>
paths:
<strong>-/var/log/messages
</strong># - /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.
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
1
2
3
Server1(Redis Master)
Redis Service at host192.168.0.1andport tcp:6379
Sentinel Service at host192.168.0.1andport tcp:16379
Server 2
1
2
3
Server2(Redis Slave1)
Redis Service at host192.168.0.2andport tcp:6380
Sentinel at host192.168.0.2andport tcp:16380
Server 3
1
2
3
Server3(Redis Slave2)
Redis Service at host192.168.0.3andport tcp:6381
Sentinel Service at host192.168.0.3andport 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.
# 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 ..."
sleep1
done
echo"Redis Sentinel stopped"
fi
;;
restart)
stop
sleep3
start
;;
*)
echo"Usage: $PROG_NAME {start|stop|restart}"
exit1
;;
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.
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
dBase
Microsoft Access
Microsoft Excel
Visual Foxpro
Apache OpenOffice
dbfview
dbf Viewer Plus
Linux
Apache OpenOffice
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.
Shell
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[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.
Shell
1
pip install dbfread
The below code snippet can read dbf file and retrieve data as python dictionary.
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.
create a connection with db.
1
db=dbmodule.connect('dbname','user','password')
2) create a cursor.
create a cursor.
1
cursor=db.cursor()
3) do a query.
query.
1
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
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 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.
data=urllib2.urlopen(uri%urllib.quote('SELECT * FROM market')).read()
print json.loads(data)
dbex()
Start dbslayer :
1
dbslayer-c/path/dbslayer.conf-sservername
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 :
1
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]
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
Shell
1
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
Shell
1
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.
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
1
mysql>help<COMMANDorSTATEMENT>
The above statement in the mysql console will give you the whole syntax and it’s usage.
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,
1
2
3
4
mysql>\Pless
PAGER set to'less'
mysql>\Pmore
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.
1
2
mysql>\Pless
mysql>show status;
Reset pager:
you can reset your pager using \P with arguments;
MySQL
1
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,
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.
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
1
2
3
4
5
6
mysql>begin;
QueryOK,0rowsaffected(0.00sec)
mysql>-- do you work like deleting records and other stuff
mysql>deletefromuserswhereidin(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.
MySQL
1
2
3
4
5
6
ysql>begin;
QueryOK,0rowsaffected(0.00sec)
mysql>-- do you work like deleting records and other stuff
mysql>deletefromusers;-- lets say you forgot <strong>where</strong> in delete query
mysql>rollback;-- You can rollback your accidental mistake
