This post is the first of a series of articles dedicated to MySQL Database Service (MDS):

• Using MySQL Database Service in OCI – Part 1: creating a MySQL DB System
• Using MySQL Database Service in OCI – Part 2: importing Data to MySQL DB System
• Using MySQL Database Service in OCI – Part 3: faster with MySQL HeatWave !

The goal of this series if to get familiar with MDS, HeatWave and useful MySQL Shell utilities.

In this first article we will start by creating a MySQL DB System and a Compute instance in OCI. One of the goal is also to see the benefits of HeatWave, so we will start by creating this …

As you know, MySQL 8.0 can be used as JSON Document Store to store your documents without being linked to any schema. You can also use CRUD operations to deal with these documents using the MySQL X DevAPI.

Of course in documents, it’s possible to also store temporal attributes like date, time, datetime, …

Let’s see how we can deal with such attributes.

This is an example of a collection of documents with a datetime attribute createdOn:

As those attributes don’t have a real type, can we use the createdOn attribute as if it was a real datetime field ?

Let’s try to get all the documents have a created data > '2021-12-02':

We can see that the document “dave” has been filtered out. However, we can see that “kenny” and “miguel” are also present… and this is correct as …

Recently, I saw many interest in understanding and getting information about database locking.

MySQL InnoDB’s locking can be complex and having an overview not always simple.

For more information about how InnoDB locking is working, I can only recommend this excellent series of articles by Kuba:

• https://dev.mysql.com/blog-archive/innodb-data-locking-part-1-introduction/
• https://dev.mysql.com/blog-archive/innodb-data-locking-part-2-locks/
• https://dev.mysql.com/blog-archive/innodb-data-locking-part-2-5-locks-deeper-dive/
• …

MySQL InnoDB Cluster is the official High Availability solution for and from MySQL.

MySQL InnoDB Cluster is composed by MySQL Server, MySQL Group Replication, MySQL Router and MySQL Shell.

Before InnoDB Cluster there was no standard solution for MySQL Availability and many custom solutions were used (some better than the others). But there was a good solution using some similar principles of MySQL Group Replication: Galera.

Now that MySQL InnoDB Cluster is mature and easier to orchestrate than galera, I receive a lot of requests on how to migrate from Galera (or Percona XtraDB Cluster) to MySQL InnoDB Cluster.

I already wrote some time ago an article on this process: how to migrate from Galera to MySQL Group Replication.

In this article we will see how we can migrate from Percona XtraDB …

When you use MySQL Shell with extra Python plugins (like these available on GitHub), sometimes, you could need extra Python modules.

Some of my plugins require requests and prettytable for example.

MySQL 8.0.26 uses embedded Python 3.9 and if your system doesn’t have that version (like Oracle Linux 8), you won’t be able to install the missing module.

So how could we install the missing modules ? The easiest method is to use PIP, but if you don’t have PIP for Python 3.9 installed on the system, this will be more complicated…. not really !

This how to install PIP in MySQL Shell:

wget https://bootstrap.pypa.io/get-pip.py
mysqlsh --py -f get-pip.py 

And now you can use PIP to install extra modules:

mysqlsh --pym pip …

I’m active on multiple platforms (mail, slack, forums, …) and often, when people are looking for help, the first 5 or 10 questions are always the same:

• which version of MySQL are you running ?
• which OS ?
• is it in the cloud ?
• which provider ?
• are you using replication ?
• GTIDs ?
• ….

I’ve added to MySQL Shell Plugin repository and plugin called support which provides an output that user can share when looking for MySQL help.

Usually, I’m focusing only in MySQL 8.0, but this plugin works with older versions too (don’t try MySQL 3.23…. it should be compatible from 5.6).

Let’s see an output if I run it locally:

[fred@fedora ~] $ mysqlsh root@localhost -e "support.fetchInfo()" …

Tweet

When I wrote the book MySQL Concurrency I included a Python module for MySQL Shell that would help reproducing the examples in the book. Since things change, it has been necessary to update the code. In this blog I will explain what the changes are which also give me a chance to say thanks to those that have submitted pull requests.

MySQL Concurrency

Version v1.1 was mostly about correcting the directory structure of the repository which was not as it was meant to be – and different from the instructions in the book. Additionally some files with code listings and images were missing. …

Dear MySQL users,

MySQL Shell 8.0.25 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.25.

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 information …

From MySQL Shell 8.0.24, it’s possible to log all the SQL statements issued in MySQL Shell.

Everything is well documented in the dedicated manual section: System Logging for SQL Statements.

Let’s see it in action. We will first start MySQL Shell with --syslog option:

$ mysqlsh --syslog --sql root@localhost

We enter a statement:

MySQL  localhost:33060+   2021-04-30 09:18:39 
SQL  show databases;
+-------------------------------+
| Database                      |
+-------------------------------+
| bookstore                     |
| clusterdemo                   |
...

And we can verify in syslog if something has been logged. I use Systemd and Journald therefor I will …

Recently I posted an article on how to deal with arrays when trying to use MySQL Shell Dump Utility in command line.

Of course, this proposed solution was a workaround for MySQL Shell <= 8.0.23. The new version of MySQL Shell 8.0.24 supports now arrays \o/ !

So if we take the same example of the previous post, we can now use the following syntax:

$ mysqlsh root@localhost -- util dump-instance /tmp/dump \
    --excludeSchemas=["mysql_innodb_cluster_metadata","fred_test"] \
    --threads=8 --showProgress

Please mind that lack of <space> between the elements of the array. If you use …