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]).
Architecture
The …
[Read more]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@10.0.0.1
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:
$ …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]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:
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]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. MySQL …
[Read more]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.
1. Single table …
[Read more]Some times it’s important to know the size of a transaction, especially when you plan to migrate to a HA solution where by default transactions have a limited size to guarantee an optimal behavior of the cluster.
Today we will see the different possibilities to have an idea of the size of transactions.
First we need to split the transaction in two types:
To implement High Availability, only the first category is important.
Size of DML
To know the size of a DML transaction, the only possibility we have is to parse the binary log (or query the binlog event).
We need to check the binlog event from the binlog file and then calculate its size. To illustrate this, let’s try to find the transaction identified by a specific GTID: …
[Read more]In this new article about how to find the info when using MySQL Database Service on Oracle Cloud Infrastructure, we will learn about the query accelerator: HeatWave.
With HeatWave, you can boost the performance of your MySQL queries, providing your applications with faster, more reliable, and cost-effective access to data.
HeatWave is a high-performance in-memory query accelerator for MySQL Database Service on Oracle Cloud Infrastructure. It is designed to accelerate analytics workloads (OLAP) and increase the performance of your MySQL databases by orders of magnitude. This is achieved through the use of in-memory processing, advanced algorithms, and machine learning techniques to optimize query performance. If identified by the optimizer, OLTP requests can also be accelerated using HeatWave.
Today we will try to answer the following questions:
This is an update for MySQL 8 Stored PSM to add the ONLY_FULL_GROUP_BY mode to the global SQL_MODE variable when it’s not set during a session. Here’s the code:
/* Drop procedure conditionally on whether it exists already. */
DROP PROCEDURE IF EXISTS set_full_group_by;
/* Reset delimter to allow semicolons to terminate statements. */
DELIMITER $$
/* Create a procedure to verify and set connection parameter. */
CREATE PROCEDURE set_full_group_by()
LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'Set connection parameter when not set.'
BEGIN
/* Check whether full group by is set in the connection and
if unset, set it in the scope of the connection. */
IF EXISTS
(SELECT TRUE
WHERE NOT REGEXP_LIKE(@@SESSION.SQL_MODE,'ONLY_FULL_GROUP_BY'))
THEN
SET @@GLOBAL.SQL_MODE := CONCAT(@@SESSION.sql_mode,',ONLY_FULL_GROUP_BY');
END IF;
END;
$$
/* Reset the default delimiter. */
DELIMITER ;
You can call the …
[Read more]Recently, I wrote three articles on how to analyze queries and generate a slow query log for MySQL Database Service on OCI:
In these post, we were generating a slow query log in text or JSON directly in Object Storage.
Today, we will see how we can generate …
[Read more]