A long-time missing (and missed) functionality in MySQL, is sequences/ranges.

As of MySQL 8.0, this functionality is still not supported in a general sense, however, it’s now possible to generate a sequence to be used within a single query.

In this article, I’ll give a brief introduction to CTEs, and explain how to build different sequence generators; additionally, I’ll introduce the new (cool) MySQL 8.0 query hint SET_VAR, and a pinch of virtual columns and functional indexes (“functional key parts”, another MySQL 8.0 feature).

Contents:

• A brief introduction to Common Table Expressions (CTEs)
• …

Monitoring a database is a critical mission: collecting information over time allows tracking how's behaving certain instance in response to a determined workload. There are several solution on the market, and MySQL offers its own solution in the Enterprise Edition, MySQL Enterprise Monitor (MEM). Today I'd like to remember that there's also another nice solution that is free, and designed by MySQL performance architect, Dimitri Kravtchuk (hence not to be considered as an Oracle MySQL solution, not supported and "as is"). I'm talking about dim_STAT. dim_STAT has a slightly different purpose than MEM as it is specialized to make some low level analysis of the impact of the workload on our MySQL Server instance (e.g. can profile mysqld process), and above all, permits offline metrics captures.
…

Monitoring a database is a critical mission: collecting information over time allows tracking how’s behaving certain instance in response to a determined workload. There are several solutions on the market, and MySQL offers its own in the Enterprise Edition, MySQL Enterprise Monitor (MEM). Today I’d like to remember that there’s also another nice solution that is free, and designed by MySQL performance architect, Dimitri Kravtchuk (hence not to be considered as an Oracle MySQL solution, not supported and “as is”). I’m talking about dim_STAT. dim_STAT has a slightly different purpose than MEM as it is specialized to make some low level analysis of the impact of the workload on our MySQL Server instance (e.g. can profile mysqld process), and above all, permits offline metrics captures.

This blog post illustrates ” How to setup SSL enabled replication”

By default, mysql package installation creates SSL file in the data directory at the time of installation. If you would like to use different self-signed certificates then create them as described here.

Add SSL setting to my.cnf on all servers.

ssl=on
ssl-ca=/etc/sslcerts/ca.pem
ssl-cert=/etc/sslcerts/server-cert.pem
ssl-key=/etc/sslcerts/server-key.pem

Restart mysql server and verify the settings.

Example: client connections using SSL

#mysql -urpluser -p -P22403 --host 127.0.0.1 --ssl-cert=/etc/sslcerts/client-cert.pem --ssl-key=/etc/sslcerts/client-key.pem -e '\s'
Enter password:
--------------

Connection id: 5
Current database:
Current user: rpluser@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current …

In the MySQL ecosystem, orchestrator is the most popular and well-respected high availability and topology management tool, integrating well with other solutions such as ProxySQL. It facilitates automatic (or manual) discovery, refactoring and recovery of a replicated MySQL environment, and comes complete with both command-line (CLI) and web interfaces for both humans and machines to interact with.

As we all know, humans are prone to errors and as such accidents can happen, particularly when humans and computers interact with each other! Recently, one of these situations related to the web interface of orchestrator during topology refactoring with its drag-and-drop capabilities, where a drop occurred unintentionally and thus had an impact on replication.

When …

As you know, Oracle Cloud Infrastructure (aka OCI) becomes more and more popular. You can find OCI data centers almost everywhere and the price is very attractive.

Something very interesting is the possibility to have different availability domains but also different fault-domains in the same availability one !

Oracle provides a lot of modules to deploy your architecture on OCI using Terraform. Those are called oracle-quickstart repos. You can find them on Github.

There is an official Reference Architecture for MySQL InnoDB Cluster in OCI that I encourage you to read before deploying your MySQL HA solution …

If you’re a MySQL user trying ClickHouse, one thing which is likely to surprise – and annoy you – is the handling of Double Quotes. In MySQL, you can use both double quotes and single quotes to quote strings, and as an example, these two queries are equivalent:

mysql> select * from performance_schema.global_variables where variable_name='max_connections';
+-----------------+----------------+
| VARIABLE_NAME   | VARIABLE_VALUE |
+-----------------+----------------+
| max_connections | 151            |
+-----------------+----------------+
1 row in set (0.01 sec)

mysql> select * from performance_schema.global_variables where variable_name="max_connections";
+-----------------+----------------+
| VARIABLE_NAME   | VARIABLE_VALUE |
+-----------------+----------------+
| max_connections | 151            |
+-----------------+----------------+
1 row in set (0.00 sec)

This means that many of us tend to use single quotes and double quotes …

As a MySQL DBA, the databases powering your applications need to handle changing traffic workloads while remaining responsive and stable so you can deliver an excellent user experience. Optimizing MySQL performance and troubleshooting MySQL problems cost-efficiently are some of the most critical and challenging tasks for MySQL DBAs. In this presentation, we will look at specific, common MySQL problems and demonstrate how Percona Monitoring and Management (PMM), built on free and open-source software, enables you to solve these challenges.

Please join Percona CEO Peter Zaitsev on Wednesday, March 18, 2020, at 1:30 pm EDT for his webinar “Optimize and Troubleshoot MySQL Using Percona Monitoring and Management”.

…

Database schema change is becoming more frequent than before, Four out of five application updates(Releases) requires a corresponding database change, For a DBA schema change is a more often a repetitive task, it might be a request from the application team for adding or modifying columns in a table and many more cases.

MySQL supports online DDL from 5.6 and the latest MySQL 8.0 supports instant columns addition.

This blog post will look at the online DDL algorithms inbuilt which can be used to perform schema changes in MySQL.

DDL Algorithms supported by InnoDB is,

• COPY
• INPLACE
• INSTANT ( from 8.0 versions)

INPLACE Algorithm:

INPLACE algorithm performs operations in-place to the original table and avoids the table copy and rebuild, whenever possible.

If the INPLACE algorithm is specified with the …

We are happy to invite you to the next MysQL User Camp India event which is hold on March 5th, 2020 in the Oracle office in Bangalore, India. Please find details below:

• Date: March 5, 2020
• Time: 3-5:30pm
• Place: 0C001, Block 1, B wing, Kalyani Magnum IT park, JP Nagar, 7th phase, Bengaluru, India
• Agenda: 
  • MySQL Analytics Cloud Service by Nipun Agarwal, the Vice President, Research & Advanced Development 
  • Metadata Synchronization in NDB Cluster 8.0 by Arnab Ray, the Lead Member Technical Staff
  • Creating multiple DB accounts for an App by Rahul Sisondia, the Principal Member Technical Staff
• Registration: send an email to: <tinku.ajit@oracle.com>
• Follow us on:
  • …