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
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)
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.
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.
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.
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.
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.
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
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
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.
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.
[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()
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 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
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]
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.
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