Long months of the pandemic lockdown have brought to life many great online events enabling the MySQL community to get together and stay informed about the very recent developments and innovations available to MySQL users. It isn’t over yet! Next Thursday, June 4th, Percona & ProxySQL are co-hosting the ProxySQL Tech Talks with Percona virtual meetup covering ProxySQL, MySQL and Percona XtraDB Cluster.

The attendees are invited to participate in the two-hour deep-dive event with plenty of time for questions and answers (we will have two 40-minute sessions + 20 minutes allocated for Q&A). Get prepared, come with your burning questions and true war stories – we’ll have our speakers answer and comment on them! And here come the speakers:

• René Cannaò, ProxySQL author and CEO of ProxySQL …

In my roles as a DBA at various companies, I generally found the tooling to be quite lacking. Everything from metrics collection, alerting, backup management; they were either missing, incomplete or implemented poorly. DBA-Tools was born from a desire to build backup tools that supported my needs in smaller/non-cloud environments. As BASH is easily the most common shell available out there on systems running MySQL® or MariaDB®, it was an easy choice.

How DBA-Tools came to be

While rebuilding my home-lab two years ago, I decided I wanted some simple tools for my database environment. Being a fan of NOT re-inventing the wheel, I thought I would peruse GitHub and Gitlab to see what others have put together. Nothing I saw looked quite like what I wanted. They all hit one or more of the checkboxes I wanted, but never all of them.

My checklist when searching …

The MySQL 5.7.8 Release Candidate was released August 3rd. But before you upgrade, be sure to read how to upgrade from 5.6 to 5.7 PLEASE.

Yes, you need to make a backup (or three or four).

Be sure to run mysql_upgrade after starting the 5.7 binary. There are some changes to tables that must be made and this is the way to do it.

The upgrade docs offers several upgrade scenarios.

Also take time to read the MySQL 5.7 Release Notes! This is not only a list of new goodies but it warns you to …

Loading databases on virtual systems is not as straight forward as it should be. Last time the foundation for using Ansible was set down on our quest to be able to have fully functional database servers on virtual servers automatically. But that prompted a few of you to remind me that you do not always need Ansible.

Without Ansible
You can have Vagrant do the work. Modify the vagrant file to run a script to run a script when provisioned, such as
config.vm.provision :shell,path: "setup.sh"
and create the setup.sh shell script.

#!/bin/bash
#
# example setup script for LAMP stack on Vagant box
sudo apt-get -y update
sudo apt-get -y install apache2 php5 libapache2-mod-php5
## Set …

In the beginning was the MySQL command line and it was good. Verbose yes, error prone yes, and even tedious. But it was good. The UPDATE USER set SELECT_PRIV=’Y’, DROP_PRIV_’Y”, UPDATE_PRIV=’Y’,……,LAST_BLOODY_PRIV=’Y”; type errors have caught of us old timers. But it was still good.

Then came some GUI tools that were okay. Not great but they had their uses.

And then came MySQL Workbench. And it was very good.

Now there is MySQL Workbench 6.2 and it is excellent. It came out in Beta last week and has built on the proven success of its predecessors. All the usual stuff is there – Server Status, Users & Privileges, Startup/Shutdown, logs, Dashboard, Performance Reports and more. But there area whole bunch of subtle little tweaks that make it easier to use.

For instance, the VISUAL EXPLAIN allows toggling between the visual display and the tabular display. Plus you can now get query …

Most MySQL-ers quickly learn to move logs out of the data directory. Hopefully the logs are being written to a different disk, on a different controller than where the data is being kept. The horror of finding you database server dead to the world because the single partition used for everything was filled up by the error log should be a thing of the past. MySQL 5.7 will give DBAs better control of log files,

As of 5.7.2, we have gained the ability to control the verbosity of error messages with log_error_verbosity. This system variable controls verbosity in writing error, warning, and note messages to the error log. A value of 1 provides errors only, 2 adds warnings, and 3 adds notes. The default value is 3. And with that with level 3, aborted connections and access-denied errors for new connection attempts are written to the error log.

The good ol’ log_warnings is being deprecated in favor of the added …

MySQL Triggers are changing in 5.7 in a big way. Triggers have been around since 5.0 and have not changed much up to 5.6 but will gain the ability to have multiple triggers on the same event. Previously you had ONE trigger maximum on a BEFORE UPDATE, for example, and now you can have multiple triggers and set their order.

So what is a trigger? Triggers run either BEFORE or AFTER an UPDATE, DELETE, or INSERT is performed. You also get access to the OLD.col_name and NEW.col_name variables for the previous value and the newer value of the column.

So how do you use a trigger? Let say you are updating the price of an inventory item in a product database with a simple UPDATE statement. But you also want to track when the price change and the old price.

The table for products.
…

The MySQL Plug-In for Oracle Enterprise Manager 10g/11g Grid Control and Oracle Enterprise Manager 12c Cloud Control can help you monitor both your MySQL and Oracle databases with one solution. These plug-ins were developed by Pythian and, although we retain all rights to the intellectual property, we make them available to DBAs, at no cost, including ongoing updates.

Client User License Agreement
IMPORTANT: THIS SOFTWARE USER LICENSE AGREEMENT (“EULA”) IS A LEGAL AGREEMENT BETWEEN YOU (EITHER AN INDIVIDUAL OR, IF PURCHASED OR OTHERWISE ACQUIRED BY OR FOR AN ENTITY, AN ENTITY) AND THE PYTHIAN GROUP INC. YOU SHOULD READ AND MUST AGREE TO THESE TERMS BEFORE COMPLETING THE INSTALLATION PROCESS AND USING THE SOFTWARE. THIS AGREEMENT PROVIDES A LICENSE TO USE THE SOFTWARE AND CONTAINS WARRANTY INFORMATION AND LIABILITY DISCLAIMERS. BY SELECTING THE DOWNLOAD OR INSTALL BUTTON AND/OR USING THE SOFTWARE, YOU ARE CONFIRMING …

MySQL 5.6 introduced the ability to expire passwords. Many work environments have rules where it is mandatory to change passwords on a regular basis. It is easy to expire a single account with a ALTER USER 'dave'@'localhost' PASSWWORD EXPIRE; command.

The mysql.user table

The mysql.user table now has a PASSWORD_EXPIRED column. A user attempting to login with an expired account using a client that supports, they will be prompted to change their password.

An example of being forced into sandbox mode and changing the password. Note that user does not have SUPER or other admin level privs to change passwords.

SET PASSWORD does not check to see if you are reusing your old …

A colleague of mine has been benchmarking mysqldump data load vs. various versions of LOAD DATA INFILE. He created a sample data as a text file with either 100k or 20M rows of five integers each, the first column of which is the pk.
CODE: perl -MList::Util=shuffle -e '@k=shuffle(1..20e6);
  for (@k) {
    print $_, "    ", join("    ", map int(rand(1e9)), 0..3), "\n";
}' > loadme_nonpkorder.txt

perl -e 'print ++$i, "    ", join("    ", map int(rand(1e9)), 0..3), "\n" 
  for 1..20e6' > loadme_pkorder.txt
All insertion has been done on empty and new tables. The text files we read at least once before to warm up the OS disk cache. The tables have …