Updated 2/10/2017

If you've been watching the evolution of database technologies over the past few years, you've seen how quickly JSON has quickly cemented its position in major database servers. Due to its use in the web front-end, JSON has overtaken XML in APIs, and it’s spread through all the layers in the stack one step at a time.

Most major databases supported XML in some fashion for a while, too, but developer uptake wasn’t universal. JSON adoption among developers is nearly universal today, however. (The king is dead, long live the king!) But how good is JSON support in the databases we know and love? We’ll do a comparison in this blog post.

…

MySQL Extensions and Deviations

This section I am going to cover material relating to the extensions and deviations of MySQL, an administrator should have the basic knowledge of the following even if he is not going to be developing

• How MySQL extends the SQL language
• Data Types
• Indexes
• Stored routines, triggers and events
• Views
• Transactions

MySQL does not completely follow the SQL standard (ANSI/ISO SQL:2003 standard) and has it's own extensions and deviations. The reason for the extensions is to make the system easy to use.

We start with some of the rules of the SQL language which relate to the following

• Comments and portability
• Case-sensitivity
• Escape characters
• Naming limitations
• Quoting
• Time zones
• Character sets …

MySQL Caching

To make queries faster to respond is to use caching, a cache stores frequently used data in a place that is local where is can be accessed faster. Cached data can be stored either on a computers local hard disk or in the computers internal memory. For example a web browser stores text, images and other objects of recently viewed web pages locally on your hard drive, thus the next time you visit the web and the information is still the same, the browser will retrieve the text, image, etc from the local hard disk instead of having to go out on the internet to get them which is slower. This means that the first time you retrieve data it will always be slower than when you retrieve it the second time as the data will be cached.

• getting the text, picture, etc from the internet is the slowest way to get that information and takes the longest for the web page to load
• getting the text, …

What is binlog?

In MySQL the binary log record the events that, when ever the changes happens in the databases. (eg.create,delete,update,insert)

   What is binlog formats?

The format that has been used to record the changes of data in binlog.

What are the their types?

There are three types of binlog formats.

1) Statement

2) Row

3) Mixed

1) Statement 

It is the default binlog format for MySQL5.6. It records the events in SQL statement in binlog to read easily with mysqlbinlog. The binlog does not grow so fast than row format.
Faster to recover from a backup.  how it works?
· set the binlog format to statement if you use older version of MySQL 5.6 in my.cnf file.

 · restart MySQL   · create a database example india. …

MySQL STORAGE ENGINES

What is storage engine?

       In MySQL the datas are stored as files in any one of the types in storage engines. MySQL supports pluggable storage engines that we can use any types of engine belongs to your data.There are two types of storage engines in MySQL  Transactional(The data can be modified in engines) and non-transactional(It can only fetch the data from engines). The default storage engine for MySQL prior to version 5.5 was MyISAM. For MySQL 5.5 and later, the default storage engine is InnoDB.

Types of Storage Engine

1.MyISAM

2.InnoDB

3.Merge

4.Memory

5.Blackhole

6.Archive

7.CSV

8.Federated

1.MyISAM

       MyISAM is the oldest storage …

Locking User Accounts in MySQL

For SOX Compliance when an employee, contractor, or vendor leaves your company their account needs to be locked, expired, or removed.  Some of these people may return, and their Account may have many complicated permissions.  In these cases, it would be better to “Lock” their Account while they are no longer doing business with your company. 
However, MySQL does not have the ability to “Lock” or “Expire” a User Account.   The following is a simple procedure to “Lock” and “Unlock” a MySQL Account.

Typically, the one-way encrypted hash value of a users password is stored in a password field  in an user account table.  MySQL supports three builtin one-way hashed functions, PASSWORD(), MD5() and SHA1().  It is recommended not to directly use the PASSWORD() function. Reason, is that prior to PHP 4.1 version, the …

MySQL – When an Optimize Fails
When using the MyISAM engine, you may need to run an “optimize” to regain space and increase performance.  What do you need to know BEFORE you start the “optimize” command.

The first thing to know is the “optimize” creates a Temporary file in the database datadir, (see mr.TMD).  The problem is there is only 16GB of free space with the mr.MYD and mr.MYI are almost 40GB.  There is one more thing you need to know.  MySQL uses tmpdir at the end of the “optimize” which can be a problem if there is not enough space.

SERVER1:/db/mysql/data/stats>ls -lt kh*                                                                   -rw-rw---- 1 pm pmapp 18163175424 Mar 23 …

As a part database administration, DBA has to take care of sub-components of database like server logs and has to plan for maintenance activity for these components regularly.

MySQL has various types of log i.e binary log, error log, slow query log, general log for different purposes. And after certain time these logs will grow and you will start seeing issues like low disk space, a large number of logs etc.

MySQL allows you to flush logs using flush logs command, next “How to rotate and clean up old MySQL logs? ”

Linux has a utility called “logrotate” , using logrotate we can implement log rotation for MySQL server logs.

Binary logs: This one is critical if you have replication setup, By enabling  expire_logs_days mysql variable you can manage …

Be involved with MySQL Performance in some way is to ask them what should be tuned in MySQL Server straight after installation, assuming it was installed with default settings.

Even though you can tune quite a lot of variables in MySQL Servers only few of them are really important for most common workload. After you get these settings right other changes will most commonly offer only incremental performance improvements.

key_buffer_size – Very important if you use MyISAM tables. Set up to 30-40% of available memory if you use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload – remember MyISAM uses OS cache to cache the data so you need to leave memory for it as well, and data can be much larger than indexes in many cases. Check however if all of key_buffer is used over time – it is not rare to see key_buffer being set to 4G while combined size of …

Valeriy Kravchuk’s great recent post on using oprofile to profile MySQL mentioned how pt-stalk, the script from Percona Toolkit to capture diagnostics data based on a detected condition, currently does not support the new operf comand, and relies instead on the deprecated and soon to be removed, opcontrol.

Fortunately, in the Open Source world, we deal with these situations by contributing, and this seemed a simple enough change that I could get a PR ready quickly and reply …