The Primary key is like the hero of a row, which has more beneficial features in the table while performing any task on the table.

The DBA knows the importance of the primary key in the table and how to handle it.

1. Notable features of having a primary key:
2. Requirements:
3. Enabling GIPK:
4. Handling GIPK:
…

We all know that data is important, and some businesses need historical data to be available all the time. The problem is that queries on large tables perform poorly if they are not properly optimized. We get many customer requests in Managed Services to purge/archive large tables, and to achieve it, we use pt-archiver.

Recently, we received a request to archive a large table, and the customer was worried about the downtime and performance issues during the archival.

We proposed a solution to the customer to archive the table using pt-archive. The idea is to archive old data to other tables and keep the latest data on the current table with minimal performance issues. All of the data will remain available and can be queried anytime.

In the blog, I will …

Earlier in the year, I was working on an issue where one of my clients had reported a massive influx in connection on their hosts after enabling Percona Monitoring and Management (PMM). This was something I had not seen before and after researching for a couple of days I discovered that if you monitor a MySQL instance with PMM configured to collect table statistics, and if the tables that it’s gathering statistics from are Federated, it will generate a connection on the remote host for the Federated tables, one for each Federated table in the instance. Let’s go over the details and provide some examples so we can understand this a bit better.

First, I’ll offer a reminder that a Federated table is simply a table that you can put in your MySQL instance that is empty locally and uses a network connection to get the data from …

In this article you will see how you can replace the user's password by an SSL X509 certificate to connect to MySQL.

More and more people are requesting how they could connect to MySQL without using a password but using a SSL certificate. Known as X509.

CA Certificate

A CA certificate is a digital certificate issued by a certificate authority (CA). It’s used by clients to verify the SSL certificates sign by this CA.

Such certificates is usually paid and needs to be manually installed with MySQL Server. But by default, MySQL generates a self-signed certificate and provides its own CA.

For obvious reason, I will use the certificates that have been auto-generated by MySQL on my system. However, for production, I encourage you to have a real CA signed certificate.

The CA certificate is called ca.pem and is located in MySQL’s datadir (/var/lib/mysql/ca.pem on Oracle Linux, RHEL, Fedora, CentOS, …).

In case you don’t know where your ca.pem is located, you can …

MySQL HeatWave is the only MySQL based service on AWS that combines transaction processing, real-time analytics, and machine learning within one single database. It eliminates the need for complex, time-consuming ETL operations between separate databases and tools for OLTP, analytics, and machine learning use cases. MySQL HeatWave also includes MySQL Autopilot, providing workload-aware, machine learning-powered automation of various aspects of the database system lifecycle.

Codership is pleased to announce a new Generally Available (GA) release of the multi-master Galera Cluster for MySQL 8.0, consisting of MySQL-wsrep 8.0.30 (release notes, download) with Galera replication library 4.13 (release notes, download) implementing wsrep API version 26. This release incorporates all changes to MySQL 8.0.30, adding a synchronous option for your MySQL High Availability solutions.

For the Galera replication library 4.13, we now complete the I/O for the client handshake before starting an asynchronous read to fix …

We have another packed issue of OpenLampTech for you this week. And, we have our first sponsor with a smashing deal to help you configure your MySQL servers with ease. Thank you Releem for partnering with OpenLampTech this week!

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

In OpenLampTech issue #54, we have content covering:

• SQL Wildcards
• PHP Magic Methods Explained
• Managing CSS in WordPress block themes
• WordPress as a Twitter alternative?
• And much much more

…

In this blog post, I will try to cover a few dump and data-loading utilities offered by MySQL Shell.

What is MySQL Shell?

It is a robust client and code editor for MySQL. In addition to having APIs for dealing with MySQL, MySQL Shell offers scripting capabilities for JavaScript and Python. It provides an AdminAPI to manage MySQL servers and create InnoDB Cluster and ReplicaSet deployments. It supports an X DevAPI to work on both SQL and NoSQL interfaces via the X Protocol. Finally, it provides utilities to make working with MySQL in Python and Javascript mode easier.

Now, let’s jump into some practical stuff and see what these tools offer us.

Dump Utility

In the dump utility, there are three tools: util.dumpInstance(), util.dumpSchemas(), and util.dumpTables () so let’s discuss them one by one. 

1.  “util.dumpInstance()” – This was introduced in MySQL Shell 8.0.21 and …

MySQL variables store data, label data, and let developers create more accurate and efficient code by turning long and complicated strings of characters into one simple variable. This article will explore user-defined variables. User-defined variables let us execute various data sets with one command and use this data whenever needed. Mastering variables in MySQL is […]

The post MySQL Variables – Definition and Examples appeared first on Devart Blog.