Showing entries 1 to 10 of 128
10 Older Entries »
Displaying posts with tag: mysql shell (reset)
Making my MySQL InnoDB Cluster safe from naughtiness

TL;DR: Make sure to run “SET persist_only disabled_storage_engines=’MyISAM’, persist sql_generate_invisible_primary_key=ON;” on all instances and restart each one in your MySQL InnoDB Cluster.

Ok, what does “safe from naughtiness” mean?:
– Anyone creating tables that aren’t InnoDB, as this doesn’t make sense, after all, it is an “InnoDB” cluster.
– Making sure all tables have a Primary Key (invisible or not).
– Making sure that my (invisible) primary keys are visible to the cluster as it will rightfully complain if they aren’t!

This basically means that once you’ve got it all up and running you won’t run into those horrible situations whereby someone, somewhere, creates a MyISAM table that didn’t have a Primary Key and thus leave you with a broken cluster.


MySQL rtnode-01:3306 ssl JS > vlc.status()
 "clusterName": "VLC",
 "clusterRole": "PRIMARY", …
[Read more]
Observing InnoDB Cluster: A different approach for specific info extraction

Now this is far from being any observability manual for your InnoDB Cluster and let alone go into everything MySQL Shell API Admin, or the collectDiagnostics utility. You can also use the default javascript commands that we all know and love via dba.getCluster() and so on, but here’s a different take.

I just want to share something I’ve been playing with to pull out some key info from mycluster. Hope it helps someone else out there.

General setup:

select cluster_id, cluster_name, description, cluster_type, primary_mode, clusterset_id
from mysql_innodb_cluster_metadata.clusters;

Members of our cluster:

select * from …
[Read more]
MySQL install ‘n’ config one-liners

Back again, now with MySQL installs. And this means using the MySQL repository this time around.

I’ve been installing and configuring InnoDB Clusters and ClusterSets and thinking about the Ansible and Terraform users amongst us, maybe one-liners might help someone out there.

So, what about if I share how to install the MySQL repo, install the MySQL instance, create an InnoDB Cluster, add a MySQL Router, create a ClusterSet, make sure the Router is ClusterSet-aware, and then test it out. And all via one-liners.

First up, obrigado Miguel for

To simplify the command execution sequence, these sections aim to help summarize the technical commands required to create the whole platform. And on a default path & port configuration, to ease operational deployments for all those 000’s of …

[Read more]
MySQL Shell for VS Code – Bastion Host & Invalid fingerprint detected

If you use MySQL Shell for Visual Studio Code, using a bastion host is the easiest method to connect to a MySQL HeatWave DB Instance on OCI.

If you already have a connection setup using a bastion to host, you may experience the same problem as me, MySQL Shell complains about an invalid fingerprint detected:

This error has nothing to do with the fingerprint of your user OCI Key. The problem is related to the key of your bastion host as you can see in the output window:

This happens if you have changed your bastion host for example.

To resolve the problem, remove the current ssh host key for the bastion host stored in your know_hosts:

$ ssh-keygen -R ""

Use the name of your bastion host of course.

When done, it’s already fixed, you can connect back to your MySQL HeatWave DB Instance using MySQL Shell …

[Read more]
WordPress in OCI with MySQL HeatWave Read Replicas and MySQL Router R/W Splitting

Some time ago, we saw how we could deploy WordPress on OCI using MySQL HeatWave Database Service with Read Replicas. We had to modify WordPress to use a specific plugin that configures the Read/Write Splitting on the application (WordPress): LudicrousDB.

Today, we will not modify WordPress to split the Read and Write operations, but we will use MySQL Router 8.2.0 (see [1], [2], [3]).


The …

[Read more]
MySQL Shell AdminAPI: different ways to call the commands… same effect?

During last Ubuntu Summit in Riga, I got an interesting question from Alex Lutay, engineering manager at Canonical regarding the different ways to perform some Admin API commands in MySQL Shell.

He wanted to know why, in the manual and blogs, we always use separate commands instead of combining them, as he would expect this to have an impact on the connections established with the server.

Let’s illustrate this by looking at the different ways of obtaining the state of a cluster. Here are the four different methods:

Method 1

This is the method most frequently used in the documentation:

$ mysqlsh admin@
JS> cluster=dba.getCluster()
JS> cluster.status() 

Method 2

This is Alex’s preferred method as he expects to have less round trips with the network:

$ …
[Read more]
Backup your MySQL instance to the Cloud

Since MySQL Shell 8.1, it’s even easier to create a logical backup of your MySQL instance and store it directly in Object Storage, an internet-scale, high-performance storage platform in Oracle Cloud Infrastructure (OCI).

MySQL Shell now offers the option of dumping to Object Storage Bucket using PAR (Pre-Authenticated Request).

Bucket Creation

The first step is to create an Object Storage Bucket in the OCI Console:

Let’s call it lefred-mysql-backups:

When created, we can click on the three-dots and create a new PAR:

[Read more]
How to copy a MySQL instance ?

To copy a MySQL server to another server or to the cloud, there are several ways.

We can distinguish between two different types of copy:

  • physical copy
  • logical copy

The physical copy is often the fastest. However, it requires some tools to ensure that you have a consistent online backup. For example, you can use MySQL Enterprise Backup (MEB).

Alternatively, it’s possible to use the CLONE plug-in to provision a new instance with existing data from a source server. This is my preferred approach.

Finally, the last physical solution is the use of a file system snapshot, but this requires the right infrastructure and even more care to have a consistent …

[Read more]
Cut & Paste a User Creation Statement with MySQL 8

Sometimes it’s convenient to retrieve the user creation statement and to copy it to another server.

However, with the new authentication method used as default since MySQL 8.0, caching_sha2_password, this can become a nightmare as the output is binary and some bytes can be hidden or decoded differently depending of the terminal and font used.

Let’s have a look:

If we cut the create user statement and paste it into another server what will happen ?

We can see that we get the following error:

ERROR: 1827 (HY000): The password hash doesn't have the expected format.

How could we deal with that ?

The solution to be able to cut & paste the authentication string without having any issue, is to change it as a binary representation (hexadecimal) like this:

And then replace the value in the user create statement:

But there is an easier way. …

[Read more]
Backup and Restore Using MySQL Shell

MySQL Shell is an advanced client and code editor for MySQL. In addition to the provided SQL functionality, similar to MySQL, MySQL Shell provides scripting capabilities for JavaScript and Python and includes APIs for working with MySQL. The X DevAPI enables you to work with both relational and document data, and MySQL Shell 8.0 is highly recommended for use with MySQL Server 8.0 and 5.7.

MySQL Shell includes utilities for working with MySQL. To access the utilities from within MySQL Shell, use the util global object, which is available in JavaScript and Python modes, but not SQL mode. These are the utilities to take a backup; let’s see some basic commands.

  • util.dumpTables – Dump one or more tables from single database
  • util.dumpSchemas – Dump one or more databases
  • util.dumpInstance – Dump full instance
  • util.loadDump – Restore dump

1. Single table …

[Read more]
Showing entries 1 to 10 of 128
10 Older Entries »