To setup point-in-time recovery for MDS using Object Storage, these are the prerequisites:

• a MDS instance running
• a backup plan (default)
• a compute instance
• an Object Storage Bucket

In this article, I won’t focus on how to create a MDS instance, a compute instance, enable backups and Object Storage Bucket, this is easy and there is already a lot of literature about them (you can also check several of my previous articles).

This is part 1 of the Point-in-Time Recovery in OCI MDS series. It’s only about streaming the binary logs to Object Store. In part 2, we will see how to use them and perform PITR.

Saving the binary logs

In fact most of the work will be to setup the compute instance to store the binary logs coming from MDS (one single compute instance could stream those binlogs from multiple MDS source).

On the compute instance we need …

On MySQL Database Service (aka MDS) on Oracle Cloud Infrastructure (aka OCI), when you create a new instance, you also need to set the credentials for the administrator:

Let’s focus on that account…

Please don’t forget the password, it cannot be recovered !!

First of all that account’s username has some limitations. Indeed, some names are reserved as it’s mentioned in the manual. So you could not use the following usernames:

• administrator
• ociadmin
• ocirpl
• mysql.sys
• mysql.session
• mysql.infoschema

This list can evolve, you can get the list directly from the MySQL DB System creation’s screen:

In fact, those accounts are already existing in the MySQL DB …

We recently saw that .frm files have been somewhat of replaced for MyISAM tables in MySQL 8.0 (see this post).

However, what are those files ? Can they be used for something else than MyISAM tables ? … Let’s try to answer those questions and get more familiar with the .sdi files.

What does SDI stand for ?

SDI acronym stands for Serialized Dictionary Information.

As you may know already, MySQL 8.0 replaced the old way to store the metadata of tables, their structure, into the new transactional Data Dictionary (in InnoDB).

Additionally, that information is also part any InnoDB tabespace , its is appended to the tablespace, so the meta data and data are bundled …

On Oracle Cloud Infrastructure, aka OCI, MySQL Database Service, aka MDS uses the snapshot technology to backup your data.

This article is the second part of multiple part blog series. In part 1, we dealt with taking snapshots as backup. In this part, we deal with restoring the data.

Managed Snapshots

We saw in the post one how to create the snapshots automatically (Backup Plan) or Manually. Both type of backups are what we call Managed Snapshots. This means that as a user, we don’t have access to the Object Storage where the backup is stored.

So what’s the purpose of these snapshots ?

Let’s see them:

Data Restore

Restoring a MDS backup is recommended of course to save the data and in case of issue (hardware or software problem unlikely or human issue more …

On Oracle Cloud Infrastructure, aka OCI, MySQL Database Service, aka MDS uses the snapshot technology to backup your data.

I will explain how it works in the multiple part blog series. In part 1, we will deal with taking snapshots as backup. In part 2, we will see how to restore data and for which usage.

Snapshots

The Oracle Cloud Infrastructure Block Volume service provides you with the capability to group together multiple volumes in a volume group.

This is exactly what MDS uses to store the data.

We can easily see this from some global variables:

+-----------------------------+--------------------------------+ | Variable_name | Value | +-----------------------------+--------------------------------+ | datadir | /db/data/ | | innodb_data_home_dir | /db/data/ | | innodb_log_group_home_dir …

With MySQL 8.0, the version of MySQL Database Service aka MDS, the default character set has changed from latin1 to ut8mb4. The default collation is utf8mb4_0900_ai_ci but what does that mean ? and why are the utf8mb4_0900_* the recommended ones ?

Collations like utf8mb4_unicode_520_ci and utf8mb4_0900_ai_ci are based on Unicode Collation Algorithm (UCA). The number in the collation defines the UCA version:

• UCA 9.0.0 (recommended)
  - example: utf8mb4_0900_ai_ci
• UCA 5.2.0 (not recommended, see problems below)
  - example: utf8mb4_unicode_520_ci

The …

If like me you are using MySQL Shell on many different systems and new installations, there are some commands I often use to benefit from my environment.

What I really want to enable is the history of course, a nicer prompt and sometimes the default mode I want to use.

This blog post is just a little reminder with those commands I usually repeat in all my systems:

mysql-js> shell.options.setPersist('history.autoSave', 1) mysql-js> shell.options.setPersist('history.maxSize', 5000)

The option above is when using MySQL Shell in JS mode which is the default. To play with options using the same command in every mode (JS, Python, SQL), you can use /option like:

\option --persist history.autoSave 1

Then I set the prompt to have something like this:

$ cp /usr/share/mysqlsh/prompt/prompt_256pl+aw.json …

Yesterday my friend and ex-colleague Sveta published a blog post on how to migrate ownership of stored routines, views and triggers in MySQL.

I usually agree a lot with her and even if I agree with what she wrote, there is one single point I would like to comment: use MySQL Shell !

In the blog post, Sveta recommends the use of mysqldump which I don’t. For any logical dump & load operations, I really recommend the use of MySQL Shell’s Dump & Load Utility ! It’s much faster, it has many nice options like hidden Primary Key creation, and is compatible with OCI.

And of course, MySQL Shell also provides a …

Recently I blogged about how to easily deploy a LAMP application to MDS.

Using the Terraform module (OCI Resource Manager’s Stack) you also have the possibility to choose the PHP version to install:

But as you should already know, not all versions support the latest default authentication method in MySQL 8.0: caching_sha2_password [1], [2], [3].

Most of the PHP applications are using mysqli and …

As a continue of the previous announcement, please find below the list of shows where you can find MySQL Community and/or the MySQL team during the time of July - September 2021:

• July 2021:

  • MySQL meetup / Virtual, July 7, 2021

    • Carsten Thalheimer and Abdullah Zarour, the MySQL Principal Solution Engineers will be talking about Oracle Cloud - MySQL Database Service & HeatWave (for Real-time Analytics). 
    • Scheduled for 5:00 PM to 7:00 PM GMT+4. Please make sure you have zoom registration on the …