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. […]
10 Older Entries »
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
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
variable set in the
my.ini file. After granting the
FILE permission to the previously provisioned
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]
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 »
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]
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.
- Service – Group Replication Cluster
- Cluster Nodes – 3
- GR mode – Single Primary …
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.
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]
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
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]
10 Older Entries »