MySQL Storage Engine

MySQL supported storage engines
===========================
>> InnoDB: A transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints. InnoDB is the default storage engine as of MySQL 5.5.5.

>> MyISAM: The MySQL storage engine that is used the most in Web, data warehousing, and other application environments. MyISAM is supported in all MySQL configurations, and is the default storage engine prior to MySQL 5.5.5.

>> Memory: Stores all data in RAM for extremely fast access in environments that require quick lookups of reference and other like data. This engine was formerly known as the HEAP engine.

>> Merge: Enables a MySQL DBA or developer to logically group a series of identical MyISAM tables and reference them as one object. Good for VLDB environments such as data warehousing.

>> Archive: Provides the perfect solution for storing and retrieving large amounts of seldom-referenced historical, archived, or security audit information.

>> Federated: Offers the ability to link separate MySQL servers to create one logical database from many physical servers. Very good for distributed or data mart environments.

>> NDB (also known as NDBCLUSTER)—This clustered database engine is particularly suited for applications that require the highest possible degree of uptime and availability.
Note: The NDB storage engine is not supported in standard MySQL 5.5 releases. Currently supported MySQL Cluster releases include MySQL Cluster NDB 7.0 and MySQL Cluster NDB 7.1, which are based on MySQL 5.1, and MySQL Cluster NDB 7.2, which is based on MySQL 5.5. While based on MySQL Server, these releases also contain support for NDB.

>> CSV: The CSV storage engine stores data in text files using comma-separated values format. You can use the CSV engine to easily exchange data between other software and applications that can import and export in CSV format.

>> Blackhole: The Blackhole storage engine accepts but does not store data and retrievals always return an empty set. The functionality can be used in distributed database design where data is automatically replicated, but not stored locally.
 - Example: The Example storage engine is “stub” engine that does nothing. You can create tables with this engine, but no data can be stored in them or retrieved from them. The purpose of this engine is to serve as an example in the MySQL source code that illustrates how to begin writing new storage engines. As such, it is primarily of interest to developers.

MRG_MYISAM - Collection of identical MyISAM tables                        
CSV - CSV storage engine                                                  
MyISAM - MyISAM storage engine                                            
BLACKHOLE - /dev/null storage engine (anything you write to it disappears)
MEMORY - Hash based, stored in memory, useful for temporary tables        
InnoDB - Supports transactions, row-level locking, and foreign keys       
ARCHIVE - Archive storage engine                                          
PERFORMANCE_SCHEMA - Performance Schema                                   
FEDERATED - Federated MySQL storage engine                                

Database charset and collation

A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set. Let's make the distinction clear with an example of an imaginary character set.

Suppose that we have an alphabet with four letters: 'A', 'B', 'a', 'b'. We give each letter a number: 'A' = 0, 'B' = 1, 'a' = 2, 'c' = 3. The letter 'A' is a symbol, the number 0 is the encoding for 'A', and the combination of all four letters and their encodings is a character set.

Now, suppose that we want to compare two string values, 'A' and 'B'. The simplest way to do this is to look at the encodings: 0 for 'A' and 1 for 'B'. Because 0 is less than 1, we say 'A' is less than 'B'. Now, what we've just done is apply a collation to our character set. The collation is a set of rules (only one rule in this case): "compare the encodings." We call this simplest of all possible collations a binary collation.

But what if we want to say that the lowercase and uppercase letters are equivalent? Then we would have at least two rules: (1) treat the lowercase letters 'a' and 'b' as equivalent to 'A' and 'B'; (2) then compare the encodings. We call this a case-insensitive collation. It's a little more complex than a binary collation.

In real life, most character sets have many characters: not just 'A' and 'B' but whole alphabets, sometimes multiple alphabets or eastern writing systems with thousands of characters, along with many special symbols and punctuation marks. Also in real life, most collations have many rules: not just case insensitivity but also accent insensitivity (an "accent" is a mark attached to a character as in German 'รถ') and multiple-character mappings (such as the rule that 'รถ' = 'OE' in one of the two German collations).

=========================================================================================
=========================================================================================

mysql> SELECT COLLATION_NAME,CHARACTER_SET_NAME FROM information_schema.COLLATIONS WHERE COLLATION_NAME like '%_cs';
+--------------------+--------------------+
| COLLATION_NAME     | CHARACTER_SET_NAME |
+--------------------+--------------------+
| latin1_general_cs  | latin1             |
| latin2_czech_cs    | latin2             |
| cp1250_czech_cs    | cp1250             |
| latin7_estonian_cs | latin7             |
| latin7_general_cs  | latin7             |
| cp1251_general_cs  | cp1251             |
+--------------------+--------------------+
6 rows in set (0.00 sec)

mysql> show variables like '%char%';
+--------------------------+-----------------------------------------------+
| Variable_name            | Value                                         |
+--------------------------+-----------------------------------------------+
| character_set_client     | utf8                                          |
| character_set_connection | utf8                                          |
| character_set_database   | utf8                                          |
| character_set_filesystem | binary                                        |
| character_set_results    | utf8                                          |
| character_set_server     | utf8                                          |
| character_set_system     | utf8                                          |
| character_sets_dir       | /u01/app/mysql/product/5.7.10/share/charsets/ |
+--------------------------+-----------------------------------------------+
8 rows in set (0.00 sec)

mysql> show variables like '%collat%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

mysql> SELECT COLLATION_NAME,CHARACTER_SET_NAME FROM information_schema.COLLATIONS WHERE COLLATION_NAME like 'utf_%_c%';
+--------------------------+--------------------+
| COLLATION_NAME           | CHARACTER_SET_NAME |
+--------------------------+--------------------+
| utf8_general_ci          | utf8               |
| utf8_unicode_ci          | utf8               |
...
...
...
| utf32_unicode_520_ci     | utf32              |
| utf32_vietnamese_ci      | utf32              |
+--------------------------+--------------------+
102 rows in set (0.00 sec)

mysql> SELECT COLLATION_NAME,CHARACTER_SET_NAME FROM information_schema.COLLATIONS WHERE COLLATION_NAME like 'utf_%_cs';
Empty set (0.00 sec)

mysql> SELECT COLLATION_NAME,CHARACTER_SET_NAME FROM information_schema.COLLATIONS WHERE COLLATION_NAME like 'utf8mb4_%';                                                                    +------------------------+--------------------+
| COLLATION_NAME         | CHARACTER_SET_NAME |
+------------------------+--------------------+
| utf8mb4_general_ci     | utf8mb4            |
| utf8mb4_bin            | utf8mb4            |
| utf8mb4_unicode_ci     | utf8mb4            |
..
..
| utf8mb4_vietnamese_ci  | utf8mb4            |
+------------------------+--------------------+
26 rows in set (0.00 sec)

=========================================================================================
=========================================================================================


Example:
--------
mysql> create table test1 (i varchar(30)) engine=innodb DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;
Query OK, 0 rows affected (0.19 sec)
mysql> insert into test1 values ('Gaurav');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1 values ('GAURAV');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1 values ('gaurav');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table test1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                     |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| test1 | CREATE TABLE `test1` (
  `i` varchar(30) COLLATE latin1_general_cs DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test1;
+--------+
| i      |
+--------+
| Gaurav |
| GAURAV |
| gaurav |
+--------+
3 rows in set (0.00 sec)

mysql> select * from test1 where i='Gaurav';
+--------+
| i      |
+--------+
| Gaurav |
+--------+
1 row in set (0.00 sec)

=========================================================================================
=========================================================================================

mysql> create table test2 (i varchar(30)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)
mysql> show create table test2;

+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                               |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| test2 | CREATE TABLE `test2` (
  `i` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into test2 values ('Gaurav');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test2 values ('GAURAV');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test2 values ('gaurav');
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test2;
+--------+
| i      |
+--------+
| Gaurav |
| GAURAV |
| gaurav |
+--------+
3 rows in set (0.00 sec)

mysql> select * from test2 where i='Gaurav';
+--------+
| i      |
+--------+
| Gaurav |
| GAURAV |
| gaurav |
+--------+
3 rows in set (0.00 sec)

=========================================================================================
=========================================================================================

mysql> ALTER TABLE test2 CONVERT TO CHARACTER SET latin1 COLLATE latin1_general_cs;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table test2;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                     |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| test2 | CREATE TABLE `test2` (
  `i` varchar(30) COLLATE latin1_general_cs DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test2 where i='Gaurav';
+--------+
| i      |
+--------+
| Gaurav |
+--------+
1 row in set (0.00 sec)

=========================================================================================
=========================================================================================

mysql> ALTER TABLE test2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test2 where i='Gaurav';
+--------+
| i      |
+--------+
| Gaurav |
+--------+
1 row in set (0.00 sec)

mysql> ALTER TABLE test2 CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test2 where i='Gaurav';
+--------+
| i      |
+--------+
| Gaurav |
| GAURAV |
| gaurav |
+--------+
3 rows in set (0.00 sec)

=========================================================================================
=========================================================================================

At last, i want to conclude session - before we start with new DB, make sure about its charset/collate.
Does it comes under - %cs (case-sensitive) OR %ci (case-in-sensitive)?
which charset need to use - latin1 or utf8?

References:
http://dev.mysql.com/doc/refman/5.7/en/charset-general.html

Storing and Retrieving epoch/Unix time in MySQL DB

Epoch time - The Unix epoch (or Unix time or POSIX time or Unix timestamp) is the number of seconds that have elapsed since January 1, 1970 (midnight UTC/GMT), not counting leap seconds (in ISO 8601: 1970-01-01T00:00:00Z). Literally speaking the epoch is Unix time 0 (midnight 1/1/1970), but 'epoch' is often used as a synonym for 'Unix time'.

Select Unix time from DATE column
---------------------------------------------
mysql> SELECT unix_timestamp('2012-12-28 19:09:03');
+---------------------------------------+
| unix_timestamp('2012-12-28 19:09:03') |
+---------------------------------------+
|                            1356721743 |
+---------------------------------------+
1 row in set (0.10 sec)

Select date from Unix time
----------------------------------
mysql> SELECT from_unixtime(1356721743);
+---------------------------+
| from_unixtime(1356721743) |
+---------------------------+
| 2012-12-28 19:09:03       |
+---------------------------+
1 row in set (0.11 sec)

References:
http://www.epochconverter.com/

Journey of DBA

DBA - Database Administrator

Its one of the most sought after job of IT industry in terms of challenges and remunerations.
As perks are on bit high side, the challenges also follow same trend.

Being a DBA, one should technically learn:
=> Database - Be it Oracle/MySQL/SQL Server/DB2. Become Jack of 2 or more DB's and master of one.
=> OS - One should be well versed with OS level components. Make yourself well knowledgeable with Linux/Solaris.
=> Scripting - The next thing which comes under Compulsory module. Be it Shell/Perl, but one should have hands-on on any one scripting language.
=> Big Data - With growing trend of IOT and smart devices, huge scope of it.
=> PL/SQL Programming - It will definitely be an added advantage and pretty useful too.
=> Tools - It is the one which saves lot of time and effort. Lot of DBAs try to avoid using any tool but it may come handy.

Now, other than above technical aspects there are other traits which are being expected from DBA:

=> Good Communication skills - Most of IT projects in fact some of IT companies have 1 or 2 DBAs for whole organization.
   In this aspect you become SPOC (Single Point of Contact) and you should be well able to communicate with other people.
=> Strong Problem Solving Skills - As most of time, you would be sailing in problem boat alone. So, you must have problem solving capability.
=> Flexible with timings - Production Outage never comes in your comfortable time zone.
=> Cool attitude in Panic situation - Trust me, Production outage can make you sweat in Strong AC too.
=> Never say Die Attitude - Some of the issues may take lot of time (more than expected), so don't leave issues in between.

Last but not the least, remember it is pure technical line and should be followed by those who want to be Technical Architect.