The latest from the MySQL community

We recently saw how the new Invisible Column feature works in MySQL since 8.0.23 and how we can use it as a Primary Key for InnoDB tables when no Primary Key was defined.

As I wrote earlier, a good Primary Key is important for InnoDB (storage, IOPS, secondary indexes, memory…) but there is another important domain where a Primary Key is important in MySQL: replication !

Asynchronous Replication

When using “traditional” replication, if you modify a record (UPDATE and DELETE), the record(s) to modify on the replica are identified using indexes, and of course the Primary Key if any. The hidden global 6-bytes auto generated by InnoDB primary key is never used as never exposed and as it’s global, …

This article is the second part of the series related to MySQL Invisible Column started here.

This post covers why Invisible Column is important for InnoDB Storage Engine.

To start, let me explain briefly how InnoDB deals with Primary Key and why an good primary key is important. And finally, why having a Primary Key is also important.

How does InnoDB Stores Data?

InnoDB stores data in table spaces. The records are stored and sorted using the clustered index (the primary key): they are called index-organized tables.

All secondary indexes also contain the primary key as the right-most column in the index (even if this is not exposed). That means when a secondary …

As a continue of the regular blog posts informing you about where you can find MySQL Community & MySQL team at, please find below a list of shows you can join and listen MySQL talks & sessions. 

• January 2021:

  • Coud Native Meetup/VIRTUAL, January 26, 2021 - URL to be provided later on.
    • Frederic Descamps, the MySQL User Group Manager will talk on: "MySQL 8.0: Why to use MySQL 8.0 and how to deploy a web application in OCI using MySQL Database Service" 
    • Scheduled for January 26th as EMEA & JAPAC session & January 27th, 2021.
  • OpenSource Conference Osaka/VIRTUAL, January 30, 2021
    • Yoshiaki Yamasaki, the MySQL …

Dear MySQL users,

MySQL Shell 8.0.23 is a maintenance release of MySQL Shell 8.0 Series (a
component of the MySQL Server). The MySQL Shell is provided under
Oracle’s dual-license.

MySQL Shell 8.0 is highly recommended for use with MySQL Server 8.0 and
5.7. Please upgrade to MySQL Shell 8.0.23.

MySQL Shell is an interactive JavaScript, Python and SQL console
interface, supporting development and administration for the MySQL
Server. It provides APIs implemented in JavaScript and Python that
enable you to work with MySQL InnoDB cluster and use MySQL as a document
store.

The AdminAPI enables you to work with MySQL InnoDB cluster and InnoDB
ReplicaSet, providing integrated solutions for high availability and scalability
using InnoDB based MySQL databases, without requiring advanced MySQL
expertise.  For more …

MyISAM is one of the most popular storage engines in MySQL. MyISAM frequently is the second choice after InnoDB - in this blog post we will try to find out how best to work with this engine.

What is MyISAM?

MyISAM is one of MySQL’s storage engines. MyISAM is based on ISAM (Indexed Sequential Access Method), an indexing algorithm developed by IBM that allows retrieving information from large sets of data in a fast way. However, it does not do very well when simultaneously reading from and writing to one table, due to its table locking. It also does not support transactions.

For some MySQL engineers, this engine is the most popular choice after InnoDB: the …

I was about to benchmark the various compression methods in MySQL ( Via Engines ). But i liked to simulate my own data set similar to a production work load. Well searched for a tool which should be flexible to enough make customised table structure and more.

Mysql_random_data_load is a tool from Percona labs used to manipulate random data based on flexible table structure. This seems like a right fit for our benchmarking needs.

Let’s explore this tool to work efficiently on it.

MySQL Random Data Load :

Mysql_random_data_load will load (insert) ‘n’ number of records to the source table and populate it with random data based on data type. So this tool won’t determine the predefined table column or data type like sysbench. It will insert data into the table based on column data type. Thus we …

I am super pleased to share that I have completed and uploaded my first (that I can share at least) personal portfolio piece written in PHP to a subdomain on my personal hosting server located at walk.openlamp.tech. Over the better part of the last year, I have developed a custom reporting dashboard written in PHP for my (current) employer, but do not share any of that work as it is proprietary and not owned by me. However, for a personal project, I can share far and wide. In this post, I provide a brief overview of my simple (in theory at least) application/site, built on the LAMP stack using the MVC (Model-View-Controller) design pattern in core PHP along with Bootstrap 4, jQuery, and MySQL.

Image by …

How scheduler and script stand in supporting failover (Percona and Marco example) 

In part one of this series,  I had illustrated how simple scenarios may fail or have problems when using Galera native support inside ProxySQL. In this post, I will repeat the same tests but using the scheduler option and the external script.

The Scheduler

First a brief explanation about the scheduler.

The scheduler inside ProxySQL was created to allow administrators to extend ProxySQL capabilities. The scheduler gives the option to add any kind of script or application and run it at the specified interval of time. The scheduler was also the initial first way we had to deal with Galera/Percona XtraDB Cluster (PXC) node …

How scheduler and script stand in supporting failover (Percona and Marco example) 

In part one of this series,  I had illustrated how simple scenarios may fail or have problems when using Galera native support inside ProxySQL. In this post, I will repeat the same tests but using the scheduler option and the external script.

The Scheduler

First a brief explanation about the scheduler.

The scheduler inside ProxySQL was created to allow administrators to extend ProxySQL capabilities. The scheduler gives the option to add any kind of script or application and run it at the specified interval of time. The scheduler was also the initial first way we had to deal with Galera/Percona XtraDB Cluster (PXC) node …

In recent times I have been designing several solutions focused on High Availability and Disaster Recovery. Some of them using Percona Server for MySQL with group replication, some using Percona XtraDB Cluster (PXC). What many of them had in common was the use of ProxySQL for the connection layer. This is because I consider the use of a layer 7 Proxy preferable, given the possible advantages provided in ReadWrite split and SQL filtering. 

The other positive aspect provided by ProxySQL, at least for Group Replication, is the native support which allows us to have a very quick resolution of possible node failures.

ProxySQL has Galera support as well, but in the past, that had shown to be pretty unstable, and the …