MySQL Group Replication July 7th at DFW Unix User Group

I will be speaking at the Dallas Fort Worth Unix User Group meeting July 7th MySQL Group Replication. Some folks asked for a reminder well beforehand especially with the upcoming US Holiday weekend, so here it is.

The meeting location is the IBM Innovation Center at 1177 South Beltline Road, in Coppell, just south of the Airline Drive traffic light. And just west of North Lake. Pizza at 6:45 and me at 7:00PM.

Mysqlsh on Windows

Been seeing references to the new document store features of MySQL 5.7 and want to try it on Windows? The new features of MySQL require a download of the new mysqlsh msi or zip.

Welcome to MySQL Shell 1.0.3.5

Installation is a snap.

You can also determine where to keep your new mysql

The installation msi quickly guides you through the choices and your best bet for now is to pick the defaults. If you have used the 5.7 msi server install before there are no surprises. But you are offered the opportunity to change the options for such things such as location of the binaries. Again the defaults worked well for me. In a very short time you can launch the new shell.

And then you are ready to use mysqlsh.

And then you have the new shell with the new protocol!

mycli — New command line interface for MySQL

mycli is a new command line interface tool for MySQL. It features syntax highlighting and auto-completion of commands plus the ability edit commands with Emacs or VI.

So who could benefit from mycli? If you need prompting to complete commands, fat finger as you type, or just want to add some color highlights as you work with your MySQL server then try mycli.

You can even save save favorite queries. So if you have Python on your system and are looking for a new command line tool please take a look at mycli.

MySQL 5.6 to 5.7 Upgrade Warning

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 the changes that could bite you. Ten minutes here can save you hours later.

Ansible and Loading MySQL Databases Part II

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 passwords for MySQL server
sudo debconf-set-selections --verbose <<< 'mysql-server-5.5 mysql-server/root_password password wordpass'
sudo debconf-set-selections  --verbose <<< 'mysql-server-5.5 mysql-server/root_password_again password wordpass'
sudo apt-get -y install mysql-server php5-mysql php5

This approach works great and would be fairly simple to maintain. Secure? No. Having a password in plain text is a major no-no.

With Ansible
The playbook.yml has evolved since the last blog entry. This example is a bit pedantic but was written that way for clarity. This playbook will set up a LAMP server. Note that Step 3 where the MySQL server is installed will need modification for the upcoming MySQL 5.7 release to force a password as it will require an insecure install (mysql_install_db –insecure) see here for details, as 5.7 will assign a random root password by default.

# Playbook.yml
---
- hosts: all
  remote_user: vagrant
  sudo: yes
  vars:
   - docroot: /var/www/html
  tasks:
    - name: 1. Install Apache
      apt: name=apache2 state=present

    - name: 2. Install PHP Module for Apache
      apt: name=libapache2-mod-php5 state=present

    - name: 3. Set MySQL root password before installing
      debconf: name='mysql-server' question='mysql-server/root_password' value='wordpass
      debconf: name='mysql-server' question='mysql-server/root_password_again' value='wordpass' vtype='password'

    - name: 4. Install Mysql
      apt: name={{ item }} state=latest
      with_items:
        - php5-mysql
        - mysql-server
        - python-mysqldb

    - name: 5. Copy Homepage
      copy: src=index.php dest=/var/www/html/index.php mode=0644

    - name: 6. Start Apache
      service: name=apache2 state=running enabled=yes

    - name: 7. Copy database over
      copy: src=../Downloads/world_innodb.sql dest=/tmp/world.sql

    - name: 8. Create database
      mysql_db: name=world state=present login_user='root' login_password='wordpass'

    - name: 9. Import data
      mysql_db: name=world state=import target=/tmp/world.sql login_user='root' login_password='wordpass'

In Step 7 the World database is copied over to the new server. This file was created bu running mysqldump on the database.

Step 8 is where Ansible helps us a great deal. There are core modules in Ansible just for dealing with MySQL. Here mysql_db is used to create the database. The same command is used in the next step to import the SQL file (moved in step 7) into the database. See the Database Modules docs for details.

Problems? We still have the root password in plain text but hopefully stored in a directory with strong enough permissions to keep most at bay. Ansible does have a vault to hold sensitive information like password.

Reading databases (and probably several at the same time) from a SQL file generated by mysqldump is not going to be optimal. Transportable table spaces or using a LVM dump may be a better solution. Or using mysqldbcopy from the MySQL Utilities for a hot copy from a currently running server.

But those problems can wait for another installment

DFW Unix User Group – MySQL Workbench January 8th

MySQL Workbench will be the subject of the January 8th presentation of the DFW Unix User Group. Pizza before the meeting at the IBM Innovation Center at 1177 South Beltline Road, in Coppell, just south of the Airline Drive traffic light, and just west of North Lake.

MySQL Workbench Performance Dashboard

Workbench 6.2, CakePHP’s new ORM, and OKC Meetup on Wednesday

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 stats like the number of rows processed, joins per table, sorting information, and even how many temporary tables were created. And it is easier to toggle between the explain panel and the results grid.

Now that I am back from Europe, I will write a little more on this in the following days.

CakePHP’s new ORM

I attended Cakefest in Madrid to hear about the latest in CakePHP. Version 3.0 is almost ready to launch and has many new features that will help speed its adoption. Chief among them is a new ORM’s query builder that is well designed, intuitive, and easy to use. Under the covers is PDO prepared statements to help protect against SQL injection.

Most Object Relational Modelers remind me of a juggler balanced on a large ball while simultaneously swinging a Hulla-hoop around his middle while trying to order coffee in a crowded Starbucks. Too much action for something that can be done much simpler. Object Oriented programmers want everything as an object but the rigors most ORMs demand if much more complicated than writing good SQL in the first place.

The new CakePHP ORM is based on a data mapper pattern and easily interfaces with MySQL, Postgres and SQLserver. The query builder is designed to take advantage of MySQL 5.6 features like improved sub queries. They hope soon to offer Oracle database support. The design is light, easy to read, and can take advantage of sub queries. My preference is still for well written SQL but this query builder is not bad. I am sure it will catch the attention of those who fight other ORMs in their projects.

Next years is CakePHP’s fifteenth anniversary and their spirited crew is doing great things. If you have not investigated them in a while, you really need to spend some time with their new 3.0.

Oklahoma MySQL Meetup

And I will be heading up I35 to talk to the OKC MySQL Meetup tomorrow. Hope to see you if you are in the area!

Log Changes with MySQL 5.7

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 flexibility from log_error_verbosity.

And with MySQL 5.7.2, the log_timestamps system variable lets you set the timestamp time zone of messages written to the error log, the general query log, and slow query log files. The choices are UTC (the default) and SYSTEM (local system time zone). Previously messages use the local system time zone.

Please note that as of MySQL 5.7.2, the ID included in error log messages is that of the thread within mysqld responsible for writing the message. This indicates which part of the server produced the message, and is consistent with general query log and slow query log messages, which include the connection thread ID. Earlier releases use the ID of the mysqld process in error log message.

Triggers — MySQL 5.6 and 5.7

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.
CREATE TABLE products (id INT NOT NULL auto_increment,
price DECIMAL(5,2) NOT NULL,
PRIMARY KEY (id));

The table for price changes on the product table.
CREATE TABLE products_log (id INT NOT NULL,
price DECIMAL(5,2) NOT NULL,
change_date timestamp);

Now to define a trigger that will log price changes. We do this when a price is updated. Now the use od OLD.price to avoid confusion between the old price or the new price being saved in the log.
DELIMITER |
CREATE TRIGGER product_price_logger
BEFORE UPDATE ON products
FOR EACH row
BEGIN
INSERT INTO products_log (id, price)
VALUES (id, OLD.PRICE);
END
|
DELIMITER ;

Add in some data.
INSERT INTO products (price) VALUES (1.10),(2.24),(.99),(.01),(.34);

So UPDATE a record.
UPDATE products SET price='1.11' WHERE ID = 1;

So did it work? Yes, and no. Running SELECT * FROM products_log; Provides us with a time stamp of the change and the OLD.price. But I forgot to also record the id!! Challenge: Correct my mistake and compare it to an update I will make in a few days.

Now 5.7 introduces multiple triggers for the same event. Lets add yet another log this time recording who made the change;

The ‘who made the change table’.
CREATE table who_changed (
id INT NOT NULL,
who_did_it CHAR(30) NOT NULL,
when_did_it TIMESTAMP);

And the second trigger.
DELIMITER |
CREATE TRIGGER product_price_whom
BEFORE UPDATE ON products
FOR EACH ROW
FOLLOWS product_price_logger
BEGIN
INSERT INTO who_changed (id, who_did_it)
VALUES (OLD.id, user());
END
|
DELIMITER ;

So UPDATE products SET price='19.99' WHERE id=4; is run and we see that both triggers execute. Note that SHOW TRIGGERS from schema; does not provide any information on trigger order. But you can find all that as action_order in PERFORMANCE_SCHEMA.TRIGGERS

Being able to order triggers makes it easy to make logical steps when processing data. Can you get into trouble with this? I am certain someone will manage to make a mess with this. But I think most of us will enjoy being able to use this great new functionality.

Expiring MySQL Passwords and Setting Password Strength

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 password.

You will not want to expire passwords for accounts used in applications automatically. It would be possible to write an application smart enough to handle sandbox mode and implement an acceptable password but my opinion (and probably mine alone) is that would be less hassle to plan manual updates on a regular basis.

There is a password validation plug-in plugin that lets you tune password complexity and provides for a password dictionary (a black list of words NOT to use).

An example of a MEDIUM strength password setting where one upper case, one lower case, one numeric, and one special character are in the pass phase (of a settable length).

There are three levels of password checking — LOW, MEDIUM, and STRONG with MEDIUM being the default. Change the value of validate_password_policy as required. These represent increasingly strict password tests. The following descriptions refer to default parameter values; these can be modified by changing the appropriate system variables.

LOW policy tests password length only and it must be at least 8 characters long.

MEDIUM policy adds to LOW with the additional conditions that passwords must contain at least 1 numeric character, 1 lowercase and uppercase character, and 1 special (nonalphanumeric) character.

STRONG policy compounds the MEDIUM setting with the condition that password substrings of length 4 or longer must not match words in the dictionary file, if one has been specified.

So what do you do if corporate rules require that interactive passwords are changed every XX days? Well, come back to the next entry of this blog.