MySQL Support Blogs

In this post I will show how to program a C++ client for MySQL NDB Cluster. I have already presented MySQL Cluster, the distributed database using the in-memory storage engine, in several occasions. You may have learnt how to configure and start MySQL Cluster, so I will assume that a cluster is up and running. If you want to develop a simple C++ client, just run the cluster in a single host, which may be your very laptop. In order to compile an NDB API client, you will need.

1. MySQL NDB Cluster installed. Community distributions are here (I like to work with the self contained TAR).
2. The cluster …

[Read More]

Here’s a cheat sheet to run MySQL in a Docker container in your Windows or Mac laptop in a few minutes. In this brief how-to you will:

1. Install Docker Desktop in your laptop
2. Download official Oracle MySQL image or Docker Inc. image
3. Start the container
4. Administer it
5. Start a MySQL session and start a Linux session

Download and install Docker

You can download Docker Desktop for Windows or Mac from https://docs.docker.com/desktop/. Install like any other application and you’re ready to move to the next step. As you can read from the docs, Docker Desktop will install several things like Docker Compose or Kubernetes. We’ll use both in advanced examples in future posts.

Pull MySQL Server image

Now you can pull the MySQL Server image. You have two options. You can download the image maintained by …

[Read More]

MySQL Server can rely on OCI services to:

• Secure sensitive information in the OCI Vault
• Store backups safely and conveniently in OCI Object Storage
• Migrate data from OCI Object Storage to a MySQL Database Service DB System

Let’s examine the options to administer MySQL the easy way using OCI services.

Secure encryption keys in the OCI vault

It is possible to encrypt virtually anything in MySQL 8: redo logs, undo logs, binary logs, backups (…) and, obviously, data. See the list of features to make your data safer than ever. MySQL Server relies on a set of keyring plugins to store …

[Read More]

Just a quick how-to, to download, compile and use Sysbench on your RHEL/OL Linux box to test MySQL Server (note: compiling and using Sysbench is no mistery, the README file has all the instructions clearly explained, but I write this little guide to setup and use this tool in minutes).

Download and compile

The repository is hosted at: https://github.com/akopytov/sysbench. You can choose to download the latest master branch as usual or the latest stable. I have picked the latest stable. In order to compile and use certain MySQL library version, make sure mysql_config (used by sysbench auto configure to determine what libraries must be used) and a whole MySQL distribution is installed and/ or in the PATH.

If you have MySQL headers and libraries in non-standard …

[Read More]

Time zone handling can sometimes generate confusion, especially when dealing with data migrations to different host running on a different time zone, or when switching to a Daylight Saving Time (DST) time zone or when leap seconds are introduced. Will the stored date still make sense after changing a system-wide configuration? What happens when you migrate a server to another host? What is actually stored in the database?

There’s plenty of literature around about MySQL and time zone management, but there’s also missing information, because new features and fixes are constantly introduced into MySQL Server in this area. So I thought that spending a few words here to summarize how to best deal with time zones, and keeping this information up to date to reflect the …

[Read More]

MySQL Server offers, among the different security features, encryption at rest (or Transparent Data Encryption, TDE) in the commercial release (find the differences with Community here).

While testing TDE with MySQL InnoDB Cluster, I was wondering what inner mechanism was implemented to deal with TDE, master keys and keyrings, and the clone plugin. If I need to clone an instance, how would everything work so to guarantee my InnoDB Cluster instances will still be encrypted, and the keyring chosen would still use a good master key to decrypt tablespace keys and finally tablespace pages?

The answer is obviously that this is …

[Read More]

While testing a XA transactions based workload, I noticed in the output of SHOW ENGINE INNODB STATUS;

---TRANSACTION 108674, ACTIVE (PREPARED) 35 sec
4 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 2

So far, so good. A XA transaction has been PREPARED, and reported as such. But after a while (and I was not really looking at the session), I repeated the command and the output turned to:

---TRANSACTION 108674, ACTIVE (PREPARED) 91 sec recovered trx
4 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 2

I went to check logs, because XA transactions are …

[Read More]

You only have to choose your favorite flavour to setup, administer, configure, backup or attach an analytics cluster to your MDS DB System, as Oracle maintains SDKs in several languages to integrate your software with Oracle Cloud Infrastructure.

And in particular today I’d like to test the SDK for Python, you can find an introduction here (but you have also SDKs for Java, Go, Ruby, Javascript, .NET). Read an introduction to SDKs.

First of all let’s create a virtual environment (venv) to work in a containerized environment.

…

[Read More]

Running a MySQL Database Service (MDS) DB System, the MySQL managed solution by Oracle MySQL on Oracle Cloud Infrastructure, means forgetting about time-consuming duties such as upgrades, backups and configuration, among others. It is so possible to focus on the service, on data and at the same time take advantage of the usual monitoring assets to configure and scale the service optimally. That’s what I want to talk about in this post: how to monitor a DB System in a few steps.

OCI MDS OS Metrics Charts

An MDS DB System compute instance file system is protected from any access, but you don’t need to access it to create system metrics, as these are already exposed on your DB System administration page in OCI dashboard.

…

[Read More]

If you started looking at running your MySQL database on the cloud and thought that taking your data to Oracle Cloud Infrastructure (OCI) is an option (and wondering about integrating to the new MySQL Acnalytics service), well that is probably the natural choice, because MySQL can integrate easily with OCI services like cloud backups in OCI Object Storage service and OCI keyring for transparent data encryption. I will share a few options with advantages of every solution, so to put the idea into practice.

• MySQL Database Service (MDS)
• MySQL on the OCI Marketplace
• MySQL on OCI do-it-yourself

MySQL Database Service (MDS)

The brand new MySQL Database Service is the easiest way to run your Server in OCI, as it offers …

[Read More]

OCI CLI is a powerful tool that does it all for OCI administration: from creating a compute instance to configuring a Virtual Cloud Network (VCN), from setting up Identity Management Service (IAM) to managing all the different storage flavours. Consult the OCI CLI command reference.

MySQL Database Service (MDS) is not an exception, everything related to the new MySQL Server service running over OCI can be easily managed with a set of commands using OCI CLI, check it here. …

[Read More]

MySQL highly available solutions, InnoDB Cluster (it uses InnoDB storage engine and is based on Group Replication plugin) and NDB Cluster (NDB storage engine), offer high scalability and redundant topologies.

• InnoDB Cluster can be configured with up to 9 replicas, in single primary configuration or multi-primary.
• NDB …

[Read More]

It’s now more than two months since MDS, the managed MySQL Server solution by Oracle MySQL team, hosted on Oracle Cloud Infrastructure (OCI), became available in several regions on the 1st of September. You can find online several resources online, such as:

• MDS documentation
• Oracle MySQL blog
• MySQL Database Service Release …

[Read More]

Given a JSON object:
SET @j := '{"BOOSTER": 1, "RETRO": 2, "GUIDANCE": 0, "SURGEON": 1, "RECOVERY": 0}';

How do you find the keys which do NOT contain a value of 0?

A second approach to finding the non-0 values from a JSON object is to turn the keys and values into separate columns of a table. This is cleaner, but a little wordier.

We’ll get the keys in one table:

SELECT ord, keyname 
FROM JSON_TABLE(JSON_KEYS(@j), '$[*]' COLUMNS (
    ord FOR ORDINALITY,
    keyname VARCHAR(100) PATH '$')
) AS keyTable;
 +------+----------+
 | ord  | keyname  |
 +------+----------+
 |    1 | RETRO    |
 |    2 | BOOSTER  |
 |    3 | SURGEON  |
 |    4 | GUIDANCE |
 |    5 | RECOVERY |
 +------+----------+

And the values in a second table:

…

[Read More]

Given a JSON object:
SET @j := '{"BOOSTER": 1, "RETRO": 2, "GUIDANCE": 0, "SURGEON": 1, "RECOVERY": 0}';

How do you find the keys which do NOT contain a value of 0?

One approach is to find the keys which do match 0, and then remove them.

Sounds like JSON_SEARCH and JSON_REMOVE? But there’s a gotcha: JSON_SEARCH works only with strings. Ok, so we REPLACE(@j, ‘0’, ‘”0″‘) – but that doesn’t help, because JSON_REMOVE can’t accept an array of paths like JSON_SEARCH would return; it requires each path as a separate parameter.

Instead, JSON_MERGE_PATCH will suffice. It has the feature that values from the second parameter are preserved only if they are not NULL. So, the approach becomes turning the 0s into NULLs, and then merging that document into an empty one.

SELECT REPLACE(@j, 0, 'null') AS j; -- case matters! null, not NULL …

[Read More]

Monitoring a database is a critical mission: collecting information over time allows tracking how's behaving certain instance in response to a determined workload. There are several solution on the market, and MySQL offers its own solution in the Enterprise Edition, MySQL Enterprise Monitor (MEM). Today I'd like to remember that there's also another nice solution that is free, and designed by MySQL performance architect, Dimitri Kravtchuk (hence not to be considered as an Oracle MySQL solution, not supported and "as is"). I'm talking about dim_STAT. dim_STAT has a slightly different purpose than MEM as it is specialized to make some low level analysis of the impact of the workload on our MySQL Server instance (e.g. can profile mysqld process), and above all, permits offline metrics captures.

Extensive instructions to setup, …

[Read More]

Monitoring a database is a critical mission: collecting information over time allows tracking how’s behaving certain instance in response to a determined workload. There are several solutions on the market, and MySQL offers its own in the Enterprise Edition, MySQL Enterprise Monitor (MEM). Today I’d like to remember that there’s also another nice solution that is free, and designed by MySQL performance architect, Dimitri Kravtchuk (hence not to be considered as an Oracle MySQL solution, not supported and “as is”). I’m talking about dim_STAT. dim_STAT has a slightly different purpose than MEM as it is specialized to make some low level analysis of the impact of the workload on our MySQL Server instance (e.g. can profile mysqld process), and above all, permits offline …

[Read More]

Given: a table with JSON arrays

CREATE TABLE t (id int auto_increment primary key, d json);
INSERT INTO t VALUES (1, '["apple", "apple", "orange"]');
INSERT INTO t VALUES (2, '["apple", "banana", "orange", "orange", "orange"]');

The desired output is each row with a count of the unique objects:

+------+----------------------------------------+
| id   | fruitCount                             |
+------+----------------------------------------+
|    1 | {"apple": 2, "orange": 1}              |
|    2 | {"apple": 1, "banana": 1, "orange": 3} |
+------+----------------------------------------+

JSON_TABLE() can transform the array into rows.

SELECT id, fruit
FROM t,
JSON_TABLE(d,
    "$[*]" COLUMNS (
        fruit VARCHAR(100) PATH "$"
    )
) AS dt;

+----+--------+
| id | fruit  |
+----+--------+
|  1 | apple  |
|  1 | apple  |
|  1 | orange |
|  2 | apple  |
|  2 | banana |
|  2 | orange |
|  2 | orange |
|  2 | orange | …

[Read More]

At times, when playing with different InnoDB Clusters for testing (I usually deploy all Group Replication instances on the same host on different ports) I find myself stopping the group and doing operations on every instance (e.g. a static reconfiguration). Or I may need to shutdown all instances at once. Scripting is the usual approach, but in addition, MySQL Shell offers a very nice (and powerful) way to integrate custom scripts into the Shell itself to manage an InnoDB Cluster. This is the purpose of MySQL Shell extensions, to create new custom reports and functions and have the flexibility to manage one or more instances at once. I found particularly practical the new plugin feature, introduced in MySQL Shell 8.0.17, that can aggregate …

[Read More]

At times, when playing with different InnoDB Clusters for testing (I usually deploy all Group Replication instances on the same host on different ports) I find myself stopping the group and doing operations on every instance (e.g. a static reconfiguration). Or I may need to shutdown all instances at once. Scripting is the usual approach, but in addition, MySQL Shell offers a very nice (and powerful) way to integrate custom scripts into the Shell itself to manage an InnoDB Cluster. This is the purpose of MySQL Shell extensions, to create new custom reports and functions and have the flexibility to manage one or more instances at once. I found particularly practical the new plugin feature, introduced in MySQL Shell 8.0.17, that can aggregate reports and functions under the same umbrella: the …

[Read More]