Recently I was doing some small testing by using EC2 instances on AWS and I noticed the execution time and performance highly depend on which time of the day I am running my scripts. I was using t3.xlarge instance type as I didn’t need many CPUs and memory for my tests, but from time to time I planned to use all the resources for a short time (few minutes), and this is when I noticed the difference.

First, let’s see what AWS says about T3 instances:

T3 instances start in Unlimited mode by default, giving users the ability to sustain high CPU performance over any desired time frame while keeping cost as low as possible.

In theory, I should not have any issues or performance differences. I have also monitored the CPU credit balance and there was no correlation between the balance and the performance at all, and because these were unlimited instances the balance should not have any impact.

At times, when playing with different InnoDB Clusters for testing (I usually deploy all Group Replication instances on the same host on different ports) I find myself stopping the group and doing operations on every instance (e.g. a static reconfiguration). Or I may need to shutdown all instances at once. Scripting is the usual approach, but in addition, MySQL Shell offers a very nice (and powerful) way to integrate custom scripts into the Shell itself to manage an InnoDB Cluster. This is the purpose of MySQL Shell extensions, to create new custom reports and functions and have the flexibility to manage one or more instances at once. I found particularly practical the new plugin feature, introduced in MySQL Shell 8.0.17, that can aggregate reports and …

At times, when playing with different InnoDB Clusters for testing (I usually deploy all Group Replication instances on the same host on different ports) I find myself stopping the group and doing operations on every instance (e.g. a static reconfiguration). Or I may need to shutdown all instances at once. Scripting is the usual approach, but in addition, MySQL Shell offers a very nice (and powerful) way to integrate custom scripts into the Shell itself to manage an InnoDB Cluster. This is the purpose of MySQL Shell extensions, to create new custom reports and functions and have the flexibility to manage one or more instances at once. I found particularly practical the new plugin feature, introduced in MySQL Shell 8.0.17, that can aggregate reports and …

MySQL 8.0 is now part of RedHat Enterprise 8 and other distros based on it like CentOS and Oracle Linux.. This is a very good thing !

However if for any reason you want to use the latest version of MySQL from the Community Repository, you may encounter some frustration if you are not familiar with the new way the package manager works.

Let’s start by verifying our system:

LSB Version:    :core-4.1-amd64:core-4.1-noarch
Distributor ID:    OracleServer
Description:    Oracle Linux Server release 8.0
Release:    8.0
Codename:    n/a

We can see that we are on Oracle Linux 8.0. So now let’s try to install MySQL Server:

[root@localhost ~]# dnf install mysql-server
Last metadata expiration check: 0:08:15 ago on Sat 02 Nov 2019 09:54:07 AM UTC.
Dependencies resolved.
============================================================================================
  Package                 Arch   Version …

Some say that the best password is the one you don’t have to remember. That’s possible with MySQL, thanks to the auth_socket plugin and its MariaDB version unix_socket.

Neither of these plugins is new, and some words have been written about the auth_socket on this blog before, for example: how to change passwords in MySQL 5.7 when using plugin: auth_socket. But while reviewing what’s new with MariaDB 10.4, I saw that the unix_socket now comes installed by default and is one of the authentication methods (one of them because in MariaDB 10.4 a single user can have more than one authentication plugin, …

Many years ago I was working at a university and had to create accounts for students every semester.  Each account needed a random password and there were several hacks used to do that.  One of the new features in MySQL 8.0.18 is the ability to have the system generate a random password.

Example

  SQL > create user 'Foo'@'%' IDENTIFIED BY RANDOM PASSWORD;
+------+------+----------------------+
| user | host | generated password   |
+------+------+----------------------+
| Foo  | %    | Ld]5/Fkn[Kk29/g/M;>n |
+------+------+----------------------+
1 row in set (0.0090 sec)

Another Example

SQL > ALTER USER 'Foo'@'%' IDENTIFIED BY RANDOM PASSWORD;
+------+------+----------------------+
| user | host | generated password   |
…

Dear MySQL users,

MySQL Connector/ODBC 5.3.14, a new version of the ODBC driver for the
MySQL database management system, has been released.

The available downloads include both a Unicode driver and an ANSI
driver based on the same modern codebase. Please select the driver
type you need based on the type of your application – Unicode or ANSI.
Server-side prepared statements are enabled by default. It is suitable
for use with any MySQL version from 5.6.

This is the sixth release of the MySQL ODBC driver conforming to the
ODBC 3.8 specification. It contains implementations of key 3.8
features, including self-identification as a ODBC 3.8 driver,
streaming of output parameters (supported for binary types only), and
support of the SQL_ATTR_RESET_CONNECTION connection attribute (for the
Unicode driver only).

The release is now available in …

In MySQL 8.0.18 there is a new feature called Hash Joins, and I wanted to see how it works and in which situations it can help us. Here you can find a nice detailed explanation about how it works under the hood.

The high-level basics are the following: if there is a join, it will create an in-memory hash table based on one of the tables and will read the other table row by row, calculate a hash, and do a lookup on the in-memory hash table.

Great, but does this give us any performance benefits?

First of all, this only works on fields that are not indexed, so that is an immediate table scan and we usually do not recommend doing joins without indexes because it is slow. Here is …

Maintenance of databases or servers is quite often performed by database administrators at night. But these routines sometimes get blocked by long-running queries or applications that hang onto locks much longer than expected. Regularly, priority is given to the application and maintenance routines are often canceled in order not to interfere with the application. But […]

The post How to kill certain connections to a MySQL database appeared first on blog.

We are fueled and energized by the inquisitiveness and eagerness of Database Administrators as a community, reminding us of conducting the next edition of Mydbops Database Meetup. The regular attendees are now looking forward to this quarterly meetup organized by Mydbops, for the benefit of its participants, with the latest hands-on knowledge being shared by the practitioners themselves.

This time we are back in to Diamond District as the venue for this edition of 5th Mydbops Database Meetup, even though, the place of the meeting will be in a different Tower within the same campus at Gojek Tech, Diamond District, 4th Floor, Tower ‘B’, HAL Road, Bangalore – 560 008.

Mydbops Database Meetup

Bangalore, IN
175 Members

Let us meet together at Mydbops Database Conference. This …