As you know, the best way to perform logical dump for MySQL is to use MySQL Shell Dump & Load utilities. This is the most powerful option as it can dump and load in parallel (it also include many options to migrate to MDS very easily and supports OCI Object Store too).

One of the main question I receive related to MySQL Shell utility is related to the use of MySQL Shell in non-interactive mode with parameters requiring arrays.

What does that mean ?

For example if you want to dump a MySQL instance but you want to exclude some tables, you have an option called excludeSchemas and it expect an array of strings with the list of schemas you want to exclude in the dumb.

In interactive mode, this is how we use it:

JS> util.dumpInstance("/tmp/dump", {excludeSchemas: 
    ["mysql_innodb_cluster_metadata", "fred_test"], 
    threads: 8, showProgress: true})

However this notation is not …

The idea of ACID transactions are a basic feature of SQL’s individual Data Manipulation Language (DML) commands, like the INSERT, UPDATE, and DELETE statements. Transactions across two or more tables are a natural extension of ACID compliance features provided by DML commands. However, they require a structured programming approach, like a store procedure or like API implemented in an imperative language.

Surprisingly, transaction management wasn’t covered well in Alan Beaulieu’s Learning SQL because he only provided pseudo code logic. While I thought troubleshoot some broken MySQL SQL/PSM logic would be a good learning experience for students, it wasn’t. So, I wrote this sample code to show how to achieve an all or nothing transaction across four tables.

The code for this example on transaction management lets you perform the important tasks necessary to effect transaction …

When you migrate to MySQL Database Service on Oracle Cloud Infrastructure (MDS on OCI), the easiest, fastest and recommended way it to use MySQL Dump & Load Utility.

For more information check these different links:

• https://mysqlserverteam.com/mysql-shell-dump-load-part-1-demo/
• https://mysqlserverteam.com/mysql-shell-dump-load-part-2-benchmarks/
• https://mysqlserverteam.com/mysql-shell-dump-load-part-3-load-dump/
• …

I’ve already provided some solutions to connect to your MDS instance, using MySQL Router, SSH tunnel, … but one of the best way if you have multiple instance to manage, is to use a VPN.

This post summarize the steps on how to deploy Open VPN and configure your VCN to use it.

So, in OCI, we have already some MDS & Compute instances deployed, this is how the dashboard looks like:

OpenVPN Deployment

We can start by deploying our OpenVPN instance using OCI’s Marketplace:

And you follow the wizard by adding your administrator username and password:

An important step is to use the existing VCN and place the OpenVPN in the public subnet:

And you create the instance:

…

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 …

While I thought my instructions were clear, it appears there should have been more in my examples for using the MySQL MSI. A key thing that happened is that students opted not to install:

Samples and Examples 8.0.22

Unfortunately, they may not have read the Preface of Alan Beaulieu’s Learning SQL, 3rd Edition where he explains how to manually download the files from the MySQL web site. Here are those, very clear, instructions (pg. XV) with my additions in italics for the MySQL Shell:

First, you will need to launch the mysql command-line client or the mysqlsh command-line shell, and provide a password, and then perform the following steps:

1. Go to https://dev.mysql.com/doc/index-other.html and download the files for the …

In one of our previous articles - Setting up Replication with various methods for MySQL 8 - we reviewed how to create a replica with multiple tools.
Now, it is time to perform the same action but with MySQL Shell.

The post MySQL SHELL – The new era first appeared on dasini.net - Diary of a MySQL experts.

I recently received several messages from developers not being comfortable with command line when they need to create schemas, tables, … They were asking me for an easy alternative when testing MDS.

There is a very popular tool in the developer community to manage their databases in MySQL: PHPMyAdmin.

So if you are trying MDS (MySQL Database Service in Oracle Cloud Infrastructure), it’s possible to deploy such instance with a compute instance hosting PHPMyAdmin very easily using OCI’s Resource Manager and a stack (zip file) that you can find here: https://github.com/lefred/oci-phpmyadmin-mds/releases/tag/1.1.0

If you want to try MDS in OCI, you can get free credits using this url: …

Having recently installed a fresh copy of MySQL 8.0.21 on Windows, I took careful note of parsing changes in the MySQL Shell. It was tedious that we lost multiple statement processing, which is supported in the MySQL Client and MySQL Workbench because it uses MySQL Client.

It was frustrating when I subsequently discovered that the MySQL Shell took away the ability to write log files by removing the TEE and NOTEE commands. I suspected that since MySQL Workbench was still using the MySQL Client that it should be in the code tree. In fact, the mysql.exe client is in this directory:

C:\Program Files\MySQL\MySQL Server 8.0\bin

So, I immediately created a batch file to put the MySQL Client into my %PATH% environment variable when needed. I used this time tested DOS command: …

The amount of transactions committed is an important information, but how could you return an accurate value?

This is a question I got from my colleague Ivan, he was challenging with global status values like COM_COMMIT or HANDLER_COMMIT, then checking in innodb_metrics… but this was not accurate.

In fact depending which storage engine you are using, if binary logs are enabled, if you rollback transactions, if you are using auto_commit, etc… all those parameters influence those values.

So the first question was “What metrics or else should we use ?”. In my opinion, the most accurate “counter” for transactions are the GTIDs.

And this is why I created yet another MySQL Shell plugin that does that calculation:

This plugin is available on my GitHub repository …