Tweet

Maybe the biggest new feature in MySQL 8 is the new transaction data dictionary that improves the consistency of schema objects among other things. To further protect the data in the data dictionary, the data dictionary tables are hidden and their content only exposed through the Information Schema. (One exception is when you use the debug binary, then it is possible to get direct access to the data dictionary tables. This is not recommended at all on production systems!)

A side effect of the data dictionary tables being hidden is that those that have had a habit of manipulating the tables directly in MySQL 5.7 and earlier (I will not recommend doing that) will no longer be able to do so. Examples of manipulating the tables include …

Thank you for nice information.

I am observing High Memory Usage every time I query below command from MySQL 5.7.22 version. I am not sure if hitting with any bug. Any Idea or hint please?

SELECT table_name, avg_row_length, data_length, max_data_length, index_length, data_free, table_schema FROM information_schema.tables WHERE TABLE_TYPE = ‘BASE TABLE’ AND max_data_length > 0 ORDER BY table_schema ASC;

LikeLike

Percona announces the release of Percona Server 5.6.42-84.2 on November 29, 2018 (Downloads are available here and from the Percona Software Repositories).

Based on MySQL 5.6.42, including all the bug fixes in it, Percona Server 5.6.42-84.2 is the current GA release in the Percona Server 5.6 series. All of Percona‘s software is open-source and free.

Improvements

• …

In this three-part blog series, we will explain the details and functionality of a High Availability (HA) framework for MySQL hosting using MySQL semisynchronous replication and the Corosync plus Pacemaker stack. In Part I, we’ll walk you through the basics of High Availability, the components of an HA framework, and then introduce you to the HA framework for MySQL.

What is High Availability?

The availability of a computer system is the percentage of time its services are up during a period of time. It’s generally expressed as a series of 9′s. For example, the table below shows availability and the corresponding downtime measured over one year.

The principal objective of backing up your data is, of course, the ability to roll back and access your archives in case of hardware failure. To do business today, you need the certainty of knowing that in the case of disaster, your data will be protected and accessible. You would need to store your backups offsite, in case your datacenter goes down in flames.

Data protection remains a challenge for small and medium-sized businesses. Small-to-medium sized businesses prefer to archive their company’s data using direct-attached storage, with the majority of firms having plans to do offsite backup copies. Local storage approach can lead to one of the most severe dilemmas the modern company can face - loss of data in case of disaster.

Related resources

…

Continuing on the series of blog posts about MySQL High Availability, today we will talk about stale reads and how to overcome this issue.

The Problem

Stale reads is a read operation that fetches an incorrect value from a source that has not synchronized an update operation to the value (source Wiktionary).

A practical scenario is when your application applies INSERT or UPDATE data to your master/writer node, and has to read it immediately after. If this particular read is served from another server in the replication/cluster topology, the data is either not there yet (in case of an INSERT) or it still provides the old value (in case of an UPDATE).

If your application or part of your application …

In the first part of this howto, I illustrated how to setup two MySQL InnoDB Cluster linked by an asynchronous replication.

In that solution, I didn’t use any replication filters to ignore the replication of the InnoDB Cluster’s metadata (mysql_innodb_cluster_metadata), but I used the same metadata tables with two different clusters in it.

The benefit is that this allows to backup everything from any node in any of the data center, it works also in MySQL 5.7, and there is not risk to mess up with the replication filters.

In this blog I will show how to use replication filters to link two different clusters. This doesn’t work on …

The Symfony Frame work is very popular with PHP developers and it has a very nice Demo Application to help novices learn the intricacies involved. But the demo does not use MySQL. So this blog entry is about re configuring the demo so it works with MySQL 8. And I am using Ubuntu 18.04 LTS to you may have to adjust the following commands to work with your operating system.

This is not difficult but there are some steps that are not exactly self evident that this blog will show you how to get the demo working.  


Preliminaries
The first thing to do is to make sure you have PHP 7.2 or better installed including the php7.2-intl (sudo apt-get install php7.2-intl) package as well as the PDO connector. I will admit I have been using PHP since it appeared and this …

Partitioning is a way of splitting the actual data down into separate .ibd files (data compartments) in the file system based on the defined ranges using the partitioning key. It can help us with maintaining the enormous amount of data in different partitions without much hassle.

In this blog post, we are going to see how to manage table partitioning using yahoo partition manager.

Needs for partitioning:

• Enhanced data retrieval ( reduced IO ) with smaller B+Tree.
• Easy Archival or Purge by dropping or truncate  of partition
• Lesser fragmentation, hence avoiding frequent table optimization.

Partitions management activity like adding/Dropping partition is made easy and automated by …

The settings of MySQL configuration variables have a fundamental impact on the performance of your database system. Sometimes it can be a little tricky to predict how changing one variable can affect others, and especially when dealing with cases like the one I’ll describe in this post, where the outcome is not very intuitive. So here, we’ll look at what happens when you set innodb_open_files higher than the open_files_limit.

We can set the maximum number of open files in our MySQL configuration file using:

open_files_limit=10000

If this isn’t set, then the default – which is 5,000 in MySQL 5.7 – should be used.

See Sveta’s excellent blog post for an explanation …