Showing entries 1 to 10 of 121
10 Older Entries »
Displaying posts with tag: MySQL DBA (reset)
Improving Query Performance with Multi-Valued Indexing in MySQL 8.0

Learn how Multi-Valued Indexing in MySQL 8.0 can enhance query performance by efficiently indexing and querying JSON arrays. Discover the benefits, implementation steps, and considerations for optimizing your MySQL database.

  1. Multi-Valued Indexing in MySQL 8.0
  2. Understanding Multi-Valued Indexes and their benefits
  3. Creating Multi-Valued Indexes in MySQL …
[Read more]
Adding a New Node to MySQL Group Replication from a Backup: A Step-by-Step Guide

Learn how to seamlessly add a new node to MySQL Group Replication from a backup. Scale your cluster, save time, and efficiently manage data updates and recoveries.

  1. Hot Physical backup approach
  2. Clone plugin approach
  3. Logical backup approach

We highly recommend checking out our previous blog post on …

[Read more]
Enhancing Performance with Parallel Index Rebuild in MySQL 8.0.31

Learn how the parallel index rebuild feature in MySQL 8.0.31 improves performance by optimizing index and column additions. Explore the multithreaded insert phase, sort index build process, and system configurations for enhanced efficiency.

  1. Understanding Sorted Index Build
    1. When does InnoDB use a sort index build approach?
  2. Phases of Sort …
[Read more]
Exploring Aurora serverlessV2 for MySQL Part 3

Explore the powerful features of Aurora Serverless V2 for MySQL in this informative blog series. Learn about read-only scaling, parameter support, and cost performance. Compare costs between Provisioned Aurora and Aurora Serverless V2. Discover key takeaways for optimizing your MySQL deployment on the cloud. Read now!

  1. Read-only Scaling
    1. Failover replicas
[Read more]
How to Troubleshoot a MySQL Replica IO Thread that is Stuck in a Connecting State

Discover how to troubleshoot a MySQL replica IO thread stuck in a connecting state. Learn about the replication architecture, security group rules for AWS EC2 instances, and how to address common issues like network restrictions and bind address configuration.

MySQL is a powerful database management and a widely used cloud database service. One of its key features is the ability to create replicas of a master database to improve its availability and scalability. However, at times the IO thread in a MySQL replica may get stuck in a connecting state, which can cause replication issues and affect the overall data consistency …

[Read more]
Exploring Aurora serverlessV2 for MySQL Part 2 – Migration

Greetings everyone!!!!!

Let’s begin the phase II of our blog on Aurora serverless-V2.

Part 1 – https://mydbops.wordpress.com/2022/05/22/exploring-auAurora-serverless-v2-for-mysql

Here, I have focused primarily on the migration strategies, hence this blog will be helpful for those who are in a stance to migrate towards serverless V2.

Before entering into the migration strategies, let’s take a look at the feature called “Mixed-Configuration”. and then discuss about the migration strategies

  1. Mixed-Configuration: …
[Read more]
Prevent ProxySQL from directing traffic to broken MySQL replica

ProxySQL is an open-source MySQL proxy server, meaning it serves as an intermediary between a MySQL server and the applications that access its databases. ProxySQL can improve performance by distributing traffic among a pool of multiple database servers.

Consider 2 slaves are routed under Proxysql , In any one of the slave, if the replication is broken, we could still see the traffic routing to the broken replication slave. We can make Proxy to not send traffic to broken replication slave, by setting appropriate value to the variable mysql-monitor_slave_lag_when_null

[Read more]
MySQL @SQL_MODE

Installing MySQL Workbench 8 on Windows, we discovered that the default configuration no longer sets ONLY_FULL_GROUP_BY as part of the default SQL_MODE parameter value. While I’ve written a stored function to set the SQL_MODE parameter value for a session, some students didn’t understand that such a call is only valid in the scope of a connection to the database server. They felt the function didn’t work because they didn’t understand the difference between connecting to the MySQL CLI and clicking the lightening bolt in MySQL Workbench.

So, here are the instructions to reset the default SQL_MODE parameter value for Windows. You need to edit the setting in the my.ini file, which is in the C:\ProgramData\MySQL\MySQL Server 8.0 directory. The default installation will have the following:

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

You need to change it to the following in an …

[Read more]
Updating SQL_MODE

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]
MySQL PNG Files

LAMP (Linux, Apache, MySQL, Perl/PHP/Python) Architecture is very flexible. All the components can be positioned on the same server or different servers. The servers are divided into two types. The types are known as the Application or database tiers. Generally, the application tier holds the Apache Server, any Apache Modules, and local copies of Server Side Includes (SSI) programs.

In many development environments, you also deploy the client to the same machine. This means a single machine runs the database server, the application server, and the browser. The lab for this section assumes these configurations.

Before you test an installation, you should make sure that you’ve started the database and Apache server. In an Oracle LAMP configuration (known as an OLAP – Oracle, Linux, Apache, Perl/PHP/Python), you must start both the Oracle Listener and …

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