Showing entries 1 to 10 of 185
10 Older Entries »
Displaying posts with tag: MySQL 5.7 (reset)
Setting MySQL Configuration Variables – MySQL 5.7 vs MySQL 8.0

MySQL configuration variables are a set of server system variables used to configure the operation and behavior of the server. In this blog post, we will explain the differences in managing the configuration variables between MySQL 5.7 and MySQL 8.0. We will explain three different ways for setting the configuration variables based on your use-case. […]

An Unprivileged User can crash your MySQL Server

Yes, your read the title correctly: an unprivileged user can crash your MySQL Server.  This applies for the default configuration of MySQL 8.0.21 (and it is probably the case for all MySQL 8 GA versions).  Depending on your configuration, it might also be the case for MySQL 5.7.  This needs malicious intent and a lot of determination, so no need to panic as this will not happen by accident.  I am

MySQL File Privilege

While preparing a set of student instructions to create a MySQL 8 (8.0.21) Windows 10 instance I found an error with LOAD command and the --secure-file_priv variable set in the my.ini file. After granting the global FILE permission to the previously provisioned student user:

GRANT FILE ON *.* TO 'student'@'localhost';

Any attempt to run the following command failed:

LOAD DATA INFILE 'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\avenger.csv'
INTO TABLE avenger
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n';

and, raise this error message:

ERROR: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

The following covers my exploration to try and fix this error without removing a designated directory for secure file uploads. While MySQL 5.7 …

[Read more]
InnoDB Cluster with MySQL Server 5.7 & MySQL-Shell & Router 8.0

Below I will show you how to build the solution + manage your capabilities & compared against having MySQL 8.0 Server’s usage. This post will also demonstrate using MySQL’s Pluggable Password Store and the API-CLI for all password and cluster commands. These are MySQL 8.0 Features in the MySQL-Shell 8.0, of course. Understanding & Building the Solution The… Read More »

Row scanned equals to 1, Is the query is optimally tuned ?

A few days ago one of our intern @mydbops reached me with a SQL query. The query scans only a row according to the execution plan. But query does not seems optimally performing.

Below is the SQL query and its explain plan. ( MySQL 5.7 )

select username, role from user_roles where username= '9977223389' ORDER BY role_id DESC LIMIT 1;

Execution plan and table structure

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_roles
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

show create table user_roles\G
*************************** 1. row ***************************
       Table: …
[Read more]
MySQL Group Replication and its Memory consumption (troubleshooting).

This blog is about one of the issues encountered by our Remote DBA Team in one of the production servers. We have a setup of MySQL 5.7 Single Primary (Writer) GR with cluster size of  3 . Due to OOM, the MySQL process in the primary node got killed, this repeated over the course of the time.

We all know about the OOM (out of memory), theoretically, it is a process that the Linux kernel employs when the system is critically low on memory.

In a dedicated DB server, when the OOM triggers the direct impact will be on mysqld process since it will be the most memory consuming one.

Going forward will look into the detailed analysis made to tackle the issue of OOM.

DB Environment:-

  • Service – Group Replication Cluster
  • Cluster Nodes – 3
  • GR mode – Single Primary …
[Read more]
Troubleshooting an OLAP system on InnoDB

As a part of Mydbops Consulting we have a below problem statement from one of our client.

We have a high powered server for reporting which in turn powers our internal dashboard for viewing the logistics status.Even with a high end hardware, we had a heavy CPU usage and which in turn triggers spikes in replication lag and slowness. Below is the hardware configuration.

OS : Debian 9 (Stretch)
CPU : 40
RAM : 220G (Usable)
Disk : 3T SSD with 80K sustained IOPS.
MySQL : 5.6.43-84.3-log Percona Server (GPL)
Datasize : 2.2TB

Below is the graph on CPU utilisation from Grafana.

Since the work load is purely reporting(OLAP) we could observe a similar type of queries with different ranges. Below is the Execution plan of the query. It is a join query over 6 tables.

Explain Plan:

[Read more]
MySQL load data infile made faster .

Loading any large file into MySQL server using the LOAD DATA INFILE is a time consuming process , because it is single threaded and it is a single transaction too. But with modern hardwares system resource is not a bottle neck. At Mydbops we focus on improving the efficiency of process as we value performance more. MySQL introduced the parallel load data operations in its latest minor release MySQL 8.0.17 . I had the curiosity to test this feature and wanted to know, how it can improve the data loading comparing to the existing single threaded method  . Through this blog I am going to  compare the both methods .

Remember you can use the parallel data loading utility only via MySQL Shell .

Internal Work Flow :

This section describes the …

[Read more]
The dark side of super_read_only

There is an interesting feature in MySQL 5.7+: the global variable super_read_only.  MySQL 5.6 and before only had the read_only global variable which is not preventing a user with SUPER privilege to write to the database.  With super_read_only, those users cannot write anymore.  But this feature comes with a surprise.

TL&DR: changing super_read_only to ON also sets read_only to ON and setting

Save Space on MySQL data with Column Compression

                                   Recently, One of our client reached our Remote DBA team with a requirement to reduce the size of the table as it is having many text columns with huge number of records. At preliminary check , I have validated the table size and its row format, as it was  in compressed  format already. 

Later I checked on other possibilities to compress the text columns further, At that time, then I came across per-column compression feature in Percona MySQL server (From 5.7.17-11) which features individual column compression and we were using …

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