The latest from the MySQL community

 Launching Your First MySQL DB System

Step 1 - Pre-requisites

1.    1.     Create a compartment and user group to organize the resources and access. In this example, we create a compartment named as MySQL_Sandbox, and a group MySQL_Test_Group. Log-in the Console as an Administrator, under the Identity menu, create a MySQL_Sandbox compartment, and a …

 Using MySQL Enterprise Workbench to write SQL statements against MDS

Assume that you wanted to access/write sql statements on MDS from MySQL Workbench Running on On-premises (Windows OS)

How? Below are steps you needed to follow.

To connect with MySQL Workbench ,you require below details handy :-

Prerequisite:-

Guide to Migrate Production MySQL Database running on On-premises to Oracle Cloud MySQL Database Service(MDS) ?

I have one production database which i would like to migrate into Oracle Cloud PaaS model i.e MySQL Database Service , we will walk through on how to migrate customerDB database from On-premises to Oracle Cloud MySQL Database Service(MDS).

Note:- Recommended is to have MySQL DB On-Premises and MDS  both must have same version i.e 8.0 and Utility shell MySQL shell must be 8.0.21 onwards.

make sure app is disconnected and not allowing any traffic during the migration process.

We will use two new features introduced with latest release of MySQL 8.0.21.

1.       Dump Schema Utility

a.       This will help us to take the backup from On-promises database and export  to Oracle Cloud Object …

Why Does InnoDB Need A Dictionary

An InnoDB dictionary is a set of internal tables InnoDB uses to maintain various information about user tables. It serves as API between a human and the database. While the humans refer to tables by their names, InnoDB works with integer identifiers. The dictionary stores correspondence between table name and index_id.

The dictionary tables are normal InnoDB tables, but they’re not visible to a user. However, some versions of MySQL provide read-only access to the dictionary in information_schema database.

The dictionary is stored in ibdata1. Root page of SYS_TABLES, for example, has id 8, so it’s the eighth page from the beginning of ibdata1.

The dictionary pages are in REDUNDANT format even if you use MySQL 5.6. I hope to write more about record formats in future posts. For now, it’s enough to mention that REDUNDANT is the oldest record format. It was available since 4.0 …

MySQL for RDS and DBaaS, in general, are very controlled environments by the vendors, meaning that there are missing things like a SUPER grant for the root user (and any user in general). This has some implications on operations, one of them being the impossibility of running pt-table-checksum to verify data consistency between a primary and its replicas.

However, there’s a workaround that might overcome this situation and involves three things:

• The pt-table-checksum itself
• A way to collect executed queries
• And the last one, which can be controversial, is to remove the read-only from the replica and use a maintenance window to stop traffic to the database while pt-table-checksum runs.

The problem with RDS is that you cannot change binlog_format to STATEMENT, which is one of the requirements for pt-table-checksum to run.

The workaround consists of capturing the executed …

We have had a look at how MySQL 8 handles JSON recently, but with all those JSON functions and expressions it is clear that many JSON accesses cannot be fast. To grab data from a JSON column, you will use a lot of $->>field expressions and similar, and without indexes nothing of this will be fast.

JSON cannot be indexed.

But MySQL 8 offers another feature that comes in handy: Generated columns and indexes on those. Let’s look at the parts, step by step, and how to make them work, because they are useful even outside of the context of JSON.

An example table

For the following example we are going to define a table t1 with an integer id and two integer data fields, a and b. We will be filling it with random integers up to 999 for the data values:

MySQL 8 provides solid support for the JSON data type. The manual has an overview of the data type, a JSON function reference, an an overview on generated column indexes, and explains multi-values indexes.

Creating JSON columns

Creating JSON columns is easy: Make the column of the JSON data type, fill in valid JSON data.

mysql> create table t ( id integer not null primary key auto_increment, j json);
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t (j) values 
-> ('null'),
-> ('true'),
-> …

Over the last few months, I have written numerous blog posts on different features of the MySQL Shell ranging from basic CRUD to aggregate functions and DDL. As a part of the MySQL version 8 release, MySQL Shell is a powerful and alternative environment that you can manage and work with your data in using a choice of 3 languages: Python, Javascript, or SQL. So this blog post is a simple compilation of all the Python mode related posts, in one easy-to-access location…

Photo by Tamara Gore on Unsplash

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit …

    One of the problems with Structured Query Language as opposed to languages like C++, Java, Python, or PHP is that it is hard to tell if a query is good or bad just by looking at it.  A programmer with a few months of coding experience can be shown a snippet of code from a project and have a pretty good chance of telling if that code is good or not.  Some call this a 'code smell'.

    A programmer can tell if the code is legible, understandable, and many times if it performs well often times at just a glance.  Want to do an experiment on this?  Go out to Github and check out a handful of random projects.  Good code often just looks good.

    But SQL is different.  It is mostly a declarative language, telling the computer what it wants rather than how to make what it wants. You can random pull SQL queries off Gitthub or Stackoverflow or Reddit …

InnoDB ReplicaSet was introduced from MySQL 8.0.19. It works based on the MySQL asynchronous replication. Generally, InnoDB ReplicaSet does not provide high availability on its own like InnoDB Cluster, because with InnoDB ReplicaSet we need to perform the manual failover. AdminAPI includes the support for the InnoDB ReplicaSet. We can operate the InnoDB ReplicaSet using the MySQL shell. 

• InnoDB cluster is the combination of MySQL shell and Group replication and MySQL router
• InnoDB ReplicaSet is the combination of MySQL shell and MySQL traditional async replication and MySQL router

Why InnoDB ReplicaSet?

• You can manually perform the switchover and failover with InnoDB ReplicaSet
• You can easily add the new node to your replication environment. InnoDB ReplicaSet helps with data provisioning (using MySQL clone plugin) and setting up the replication.

In this …