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.

…

Okay, so you’ve read the first post on enabling MariaDB’s data at rest encryption, and now you are ready to create an encrypted table.

And just to get it out of the way for those interested, you can always check your encrypted (and non-encrypted) table stats via:

SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION;

ENCRYPTION_SCHEME=1 means the table is encrypted and ENCRYPTION_SCHEME=0 means they are not.

But let’s get into some specific examples.

I find the following 4 tables interesting, as the first 3 essentially all create the same table, and the 4th shows how to create a non-encrypted table once you have encryption enabled.

CREATE TABLE t10 (id int) ENGINE=INNODB;
CREATE TABLE t11 (id int) ENGINE=INNODB ENCRYPTED=YES;
CREATE TABLE t12 (id int) ENGINE=INNODB …

Encryption is becoming more and more prevalent and increasingly necessary in today’s world, so I wanted to provide a good overall “getting started” article on using MariaDB’s data at rest encryption (DARE) for anyone out there interested in setting this up in their environment.

MariaDB’s data encryption at rest manual page covers a lot of the specifics, but I wanted to create a quick start guide and also note a few items that might not be immediately obvious.

And due to the number of my examples, I’m splitting this into two posts. The first will focus solely on setting up encryption so you can use it. The second will focus on using it with a number of examples and common use cases.

Also, I feel that I should mention from the outset that, currently, this data at rest encryption only applies to InnoDB/XtraDB tables and Aria …

In my post yesterday, I shared a little known trick for sorting NULLs last when using ORDER BY ASC.

To summarize briefly, NULLs are treated as less than 0 when used in ORDER BY, However, sometimes you do not want that behavior, and you need the NULLs listed last, even though you want your numbers in ascending order.

So a query like the following returns the NULLs first (expected behavior):

SELECT * FROM t1 ORDER BY col1 ASC, col2 ASC;
+--------+------+
| col1   | col2 |
+--------+------+
| apple  | NULL |
| apple  |    5 |
| apple  |   10 |
| banana | NULL |
| banana |    5 |
| banana |   10 |
+--------+------+

The trick I mentioned in my post is to rewrite the query like:

SELECT * FROM t1 ORDER BY col1 ASC, -col2 DESC;

The difference is that we added a minus sign (-) in front of the column …

Take Percona’s blog poll on what database engine you are using to store time series data.

Time series data is some of the most actionable data available when it comes to analyzing trends and making predictions. Simply put, time series data is data that is indexed not just by value, but by time as well – allowing you to view value changes over time as they occur. Obvious uses include the stock market, web traffic, user behavior, etc.

With the increasing number of smart devices in the Internet of Things (IoT), being able to track data over time is more and more important. With time series data, you can measure and make predictions on things like energy consumption, pH values, water consumption, data from environment-aware machines like smart cars, etc. The sensors used in IoT devices and systems generate huge …

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, …

Backups and Recovery

This is the most important task of an database administrator, you must protect your data at all costs, this means regular backups and regular restores even to another system just to check the integrity of those backups. There is no point in putting yourself in a position where you are holding your breathe when a restore is happening only to find out that the backup is corrupt, try if possible to perform regular restores if not then at least you should be performing a disaster recovery test once per year. Not being able to restore could be a disaster for your company and your job.

To check your backups you can use one or more of the below which I have used in the past

• use a reporting database if the customers don't need real time data and you have the money and time, Production data could be restored every day to this system which is a very good test
• use a performance …

We all  know MySQL has different types of locking for different storage engines.It will vary depends upon the storage engines which you are using.Here we have explained how the locking mechanism will work.

There are three types of locking in MySQL,

1.Table level locking,

2.Row level locking and 

3.Page level locking.

 Table level locking:

Example1:Assume that there is a house with 5 rooms(1,2,3,4,5) and it is having 3 floors(x,y,z) and each houses are having only one  main door and each floor is having separate door to go inside the floor.And all the doors are automated when one person goes inside it will lock until the person is leaving from the house,floor and room.Here house is a table and rooms are rows and floors are pages.when a person is going inside the house name A and it will locked until the person open …

     It is important to back up your databases so that you can recover your data and be up and running again in case problems occur, such as system crashes, hardware failures, or users deleting data by mistake.

Types of Backup: