The MySQL Release Notes should be part of any DBA’s regular reading list. The Changes in MySQL 5.6.20 came out last week and there are some interesting goodies.

• MySQL now includes DTrace support on Oracle Linux 6 or higher with UEK kernel.
• A new system variable binlog_impossible_mode controls what happens if the server cannot write to the binary log, for example, due to a file error.
• The mysqlhotcopy utility is now deprecated and will be removed in a future version of MySQL

5.6.20 has a slew of bug fixes, functionality changes, and notes.

So why should you be reading the changes on a regular basis? There isa goldmine of information in them. For instance, if you use blobs, consider this: …

THe MySQL APT Repository provides an easy and convenient way to get the latest MySQL software. My test server was need of a refresh so I put on a fresh install of Ubuntu 14.04 and downloaded mysql-apt-config_0.2.1-1ubuntu14.04_all.deb.

sudo dpkg -i mysql-apt-config_0.2.1-1ubuntu14.04_all.deb
[sudo] password for dstokes:
Selecting previously unselected package mysql-apt-config.
(snip)

You will get a choice to install MySQL 5.6 or the latest 5.7 DMR.

sudo apt-get update Pulls the latest information from the repository for the various packages.

sudo apt-get install mysql-server Installs the server and will start it running. And then a quick sudo apt-get install mysql-workbench to get me where i needed to be.

There is a detailed information at …

Foreign Keys are often a mystery to new DBAs in the MySQL world. Hopefully this blog will clear some of this up.

In this example, we will have a table for employee data and a table for the data on offices. First we need the two tables.
CREATE TABLE employee (
-> e_id INT NOT NULL,
-> name CHAR(20),
-> PRIMARY KEY (e_id)
-> );

CREATE TABLE building (
-> office_nbr INT NOT NULL,
-> description CHAR(20),
-> e_id INT NOT NULL,
-> PRIMARY KEY (office_nbr),
-> FOREIGN KEY (e_id)
-> REFERENCES employee (e_id)
-> ON UPDATE CASCADE
-> ON DELETE CASCADE);


Those who do not use Foreign Keys will not be familiar with the last four lines of the building table. The trick is that there are two e_id columns, one in each table. In the employee table …

The MySQL Utilities Team is pleased to announce the latest release candidate (RC) release of MySQL Utilities. This release includes a number of improvements for useabilty, stability, and a few enhancements. A complete list of all improvements can be found in our release_notes.

New Utilities!
We have also included two new utilities.

• The mysqlrplsync utility was added, which checks data consistency between servers in a replicated setup. 
• The mysqlrplms utility was added, which provides round-robin multi-source replication (a slave server continually cycles through multiple masters in order to store a consolidated data set).

How Can I Download MySQL Utilities?
You can download MySQL Utilities 1.4.2 from the following link using one of the pre-built …

The MySQL Workbench team just uploaded a new video to the MySQL channel at Youtube. This video is meant for beginners and describes the process of creating and troubleshooting connections in MySQL Workbench.

In MySQL each ‘user’ has its own password hash. To provide better security, pasword hashes were extended from 16 to 41 bytes in MySQL 4.1.

This change created a situation. If the user was created prior to version 4.1 and the server updated to a newer version, the password hash that was stored in the database is left in the old, deprecated format. This is because MySQL doesn’t store passwords in plain text so there’s no way to automatically regenerate a password hash. For this case, we consider two scenarios:
- If the secure_auth server option is disabled, you can login and update your password. You may also need to enable the allow old_password option in the Workbench advanced options tab for the connection.
- If secure_auth is enabled, you do not have possibility to log in to the database and the only thing you can do is to disable that option or log in as different user (such as root) to change the password. In this …

MySQL Workbench has an option to view MySQL server variables divided into groups [img. 1], for example: Binlog, General, Keycache, Performance, etc. This is okay if we just wanted to look around, but it can become overwhelming as sometimes we only want to monitor specific variables from different groups.

img.1. Server Variables main view

In MySQL Workbench 6.1, we solve this by implementing Custom Groups. It’s a special group that can be created by the user. At the end of the Category List, there is already one defined group, called Custom. When selected, you’ll find a description in the Variable List [img. 2].

img. 2. Server Variables custom group

Variable grouping …

The Performance Schema Reports feature from MySQL Workbench show summaries of the many statistics gathered by the MySQL performance_schema. By inspecting these reports, you can get some insight of what’s happening in a MySQL server in aspects such as:

• I/O by amount of data
• I/O by latency/time spent
• Index usage
• Performance critical operations performed by queries of the same type (table scans, index scans, temporary tables, sorts etc)

The MySQL SYS Schema

MySQL 5.5 introduced the performance_schema feature. performance_schema contains tables that log server performance and activity statistics. You can inspect it to have a clearer understanding about what kind of work is the server doing, how much time is spent doing that, resources used globally or by individual queries etc. MySQL 5.6 expanded on it and adds even more information, making it a lot more …

Beware of bugs in the above code; I have only proved it correct, not tried it.
Donald Knuth

Bugs in software are a fact of life. MySQL, as part of Oracle, issues of Critical Patch Updates and Security Alerts notices. You may have seen Daniel van Eeden‘s blog on the January announcement.

Daniel’s summary:

For MySQL 5.6 you should upgrade to 5.6.15
For MySQL 5.5 you should upgrade to 5.5.35
For MySQL 5.1 you should upgrade to 5.1.73

But you probably missed the executive summary.

But how do YOU get this information when it become available? …

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 …