1.0. Introduction:

MySQL 8.0 was released with awesome features. One of its most prominent features is CTE (Common Table Expression).

The Common Table Expression can be used to construct complex queries in a more readable manner. In this blog, I have described how the CTE works with some useful examples.

1.1 What is CTE?

A Common Table Expression (CTE) is the result set of the query, which exists temporarily and uses only within the context of a larger query.

The CTE provides better readability and performance in comparison with a derived table.

In a normal query the temporary result set that exists only within the execution scope of a single SQL statement.

Example:

select …

Image Source: DigitalOcean

This is my first post in 2019, and Im starting with a MySQL solution. In MySQL world, implementing a better backup strategy to meet all of your requirement is still a challenging thing. The complexity depends on your RPO and RTO. Percona has many tools to help DBAs in many scenarios. [Xtrabackup](https://www.percona.com/software/mysql-database/percona-xtrabackup) is one of the best backup tools to perform a better backup on TeraBytes size of databases. Also, another great feature is it supports Incremental and Differential backup.

There are couple of tools which is available in MySQL world, But due to some restrictions we can’t achieve what we are expecting.

#1 mysqldump

This is a widely used backup tool and most of the DBAs are trust this. This comes with …

In this blog we are going to see how to implement Proxysql for Amazon Aurora RDS, this would be next in series of ProxySQL blogs. Below is the list of our previous blogs on ProxySQL which provides deeper insights based on different use cases and different architecture.

• MySQL replication Read-Write Split up
• Replication Switchover using Proxysql And MHA
• HA for proxysql with keepalived
• Mirroring …

With most, if not all of 2018 behind us (depending on when you are reading this post), there is no doubt that it was a fantastic year for open-source SQL databases.

PostgreSQL 11 and MySQL 8 were both released, providing both communities with plenty to 'talk about'. Truth be told, both vendors have introduced many significant changes and additions in their respective releases and deserve their praise and accolades.

I normally guest post about the former here on the Severalnines blog (Many thanks to a great organization!) but I also have an interest in the latter. With many blog posts on my own website (link in my bio section), mostly targeting MySQL version 5.7, it (MySQL) is always in my peripherals.

So what does MySQL 8 have that version 5.7 does not have? What are the improvements? Well, there are many. In fact, too many to …

Percona announces the GA release of Percona Server for MySQL 8.0.13-3 on December 21, 2018 (downloads are available here and from the Percona Software Repositories). This release merges changes of MySQL 8.0.13, including all the bug fixes in it. Percona Server for MySQL 8.0.13-3 is now the current GA release in the 8.0 series. All of Percona’s software is open-source and free.

Percona Server for MySQL 8.0 includes all the features available in MySQL 8.0 Community Edition in addition to enterprise-grade features developed by Percona. For a list of …

In the “State of the Open-Source DBMS Market, 2018”, Gartner predicts that by 2022, 70 percent of new in-house applications will be developed on an open-source database. And 50% of existing commercial databases will have converted. So, Oracle DBAs, get ready to start deploying and managing new open source databases - along with your legacy Oracle instances. Unless you’re already doing it.

So how does MySQL or MariaDB replication stack up against Oracle Data Guard? In this blog, we’ll compare the two …

Codership is pleased to announce the release of Galera Replication library 3.25, implementing wsrep API version 25. The new release includes many bug fixes and fixes to all security issues mentioned in the Oracle Critical Patch Update Advisory – October 2018

As always, Galera Cluster is now available as targeted packages and package repositories for a number of Linux distributions, including Ubuntu, Red Hat, Debian, CentOS, OpenSUSE and SLES, as well as FreeBSD. Obtaining packages using a package repository removes the need to download individual files and facilitates the easy deployment and upgrade of Galera Cluster nodes.

This release incorporates all changes up to MySQL 5.7.24, MySQL 5.6.42 and MySQL 5.5.62.

Galera Replication …

This tutorial aims to give you an in-depth introduction to PHP (PHP 7 version) by building a simple CRUD REST API.

Also read: How to implement JWT authentication and access the Authorization header in PHP

You'll also learn about important concepts such as CRUD operations and PHP PDOs etc.

In this PHP 7 tutorial, we're going to learn by example how to create a REST API with PHP 7 and MySQL. We'll see example implementations for HTTP GET and HTTP POST methods and we'll use json_encode() to return data in JSON format.

The REST API, we'll be creating in this tutorial, will be the basis of the next tutorials for adding JWT-based authentication and building your front-ends with modern JavaScript/TypeScript frameworks and libraries such as Angular, React.js and Vue.js etc.

In this tutorial we'll create an example CRUD (Create, Read, Update and Delete) PHP application that implements the equivalent HTTP API …

Clickhouse is a relatively new analytics and datawarehouse engine that provides for very quick insertion and analysing of data. Like most analytics platforms it’s built on a column-oriented storage basis and unlike many alternatives is completely open source. It’s also exceedingly fast, even on relatively modest platforms.

Clickhouse does have some differences from some other environments, for example, data inserted cannot easily be updated, and it supports a number of different storage and table engine formats that are used to store and index the information. So how do we get into that from our MySQL transactional store?

Well, you can do dumps and loads, or you could use Tungsten Replicator to do that for you. The techniques I’m going to describe here are not in an active release, but use the same principles as other part of our data loading.

We’re going to use the CSV-based batch loading system that is …

As mentioned in a previous post, I gave a talk at Percona Live Europe 2018 about system-versioned tables. This is a new MariaDB 10.3 feature, which consists of preserving old versions of a table rows. Each version has two timestamps that indicate the start (INSERT,UPDATE) of the validity of that version, and its end (DELETE, UPDATE). As a result, the user is able to query these tables as they appear at a point in the past, or how data evolved in a certain time range. An alternative name for this feature is temporal table, and I will use it in the rest of this text.

In this post, I want to talk a bit about temporal tables best practices. Some of the information that I will provide is not …