MySQL 5.7.13 Hidden Gems

June 28, 2016

The Release Notes for MySQL 5.7.13 has some interesting notices about account length, JSON short cuts, and more.

A Rose By Any Other Name

Way back in 5.7.8 the account username was increased from sixteen to thirty two characters. Yeah! For many years this has been heavily requested. Now this has been incorporated in the definer fields of event and proc tables plus the grantor field of procs_priv and tables_priv tables. To do this these columns where bumped up from CHAR(77)) to CHAR(93) to hold the longer username@hostname data.

JSON Inline Path

An unquoting extraction operator ->>, called an inline path operator, is for use with JSON documents stored in MySQL. The new operator is similar to the -> operator which is a shotcut for JSON_EXTRACT. But it goes a step further in that it performs JSON unquoting of the value as well. For a JSON column mycol and JSON path expression mypath, the following three expressions are equivalent:


JSON_UNQUOTE( JSON_EXTRACT(jsoncolumn, "$.jsondata") )
JSON_UNQUOTE(jsoncol->"$.jsondata")
mycol->>"$.jsondata"

This new ->> operator can be used in SQL statements where JSON_UNQUOTE(JSON_EXTRACT()) would be allowed. This includes SELECT lists, WHERE and HAVING clauses, and ORDER BY and GROUP BY clauses.

Systemd and Multiple Severs

Those of you running Systemd equipped distros need to read Configuring Multiple MySQL Instances Using systemd if you desire to run multiple servers on a single host.

And there is more

The release notes for ’13 cover bugs fixed, audit log granularity, and more. Please take a few minutes to read this.

Advertisements

Mysqlsh on Windows

April 20, 2016

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

Welcome to MySQL Shell 1.0.3.5

Installation is a snap.

You can also determine where to keep your new mysql

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.

new shell

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


Need a quick MySQL 5.7 PHP Vagrant environment?

November 20, 2015

The Laravel folks are not letting the grass grow under their feet. The popular PHP Framework (motto ‘The PHP Framework For Web Artisans’) wraps their framework and the recently released MySQL 5.7 server software in a Vagrant image called Homestead ready for you to use.

I created a new directory for my new Laravel work, cd-ed into it, and then issued vagrant init laravel/homestead. As quick as my hotel wi-fi could support, I had 5.7, PHP 5.6.15 plus more on an Ubuntu 14.04 LTS virtual machine.

So if you want to easily try MySQL 5.7 or Laravel please Laravel Homestead a try


JSON and MySQL 5.7

October 8, 2015

In the past few months I have been inundated with questions about the new JSON data type. The man page for JSON is fascinating reading. So grab your favorite beverage, the JSON manual page, and take a comfy seat for reading. There are many highlights but be sure to check out some of these highlights.

  • Automatic validation of JSON documents stored in JSON columns. Invalid documents produce an error and this is a big advantage over putting JSON data in a string column.
  • JSON columns can not have a DEFAULT value, can not be indexed (virtual columns for secondary indexes are okay)
  • MySQL handles strings used in a JSON columnusing the utf8mb4 character set and utf8mb4_bin collation.

One thing to note is that strings are normalized. If you duplicate a key the new value is discarded.
SELECT JSON_OBJECT('first', 1, 'second', 2, 'first', 3, 'third', 4);
Will return
'{\"first\": 1, \"third\": 4, \"second\": 2}'

The second ‘first’ goes away. So not only does your JSON need to be well formed but you can not reuse keys in a sting.

If you use JSON_MERGE the values for that key are merged into an array.
SELECT JSON_MERGE('{"first": 1}','{"first": 2}')
Here First-1 and First-2 are merged into a single array.
'{\"first\": [1, 2]}'

A little more complex example for illustration.
SELECT JSON_MERGE('{"first": 1}','{"second": 3}', '{"first": 2}')
will produce
'{\"first\": [1, 2], \"second\": 3}'

And how big ban a JSON doc be? IT can not be stored longer than the max_allowed_packet system variable setting but it can be longer in memory for manipulation. And the protocol limit for max_allowed_packet is one gigabyte. So docs longer than 1GB will need to be saved in 1GB pieces.


Webinar: The Proper Care and Feeding Of MySQL Databases for Busy Linux Administrators with David Stokes

September 15, 2015

Wednesday, September 16, 2015 12 PM EST / 4 PM GMT

Are you a Linux Admin who ‘also has’ MySQL DBA responsibilities but no DBA training? Do you wonder why databases are the problem children of all the software services you are responsible for? Then this webinar is for you. You’ll learn all about:

  • Why databases do not play well with others
  • How (and how often) you should be doing backups
  • How MySQL use authentication is ‘too permissive’
  • Which configuration hints keep the server happy
  • Other best practices to keep your MySQL instances happy and shiny for years to come

Join us for this exciting live webinar on what you should know about the proper care and feeding of MySQL databases, with Dave Stokes, MySQL Community Manager at Oracle, and Janis Griffin, Database Performance Evangelist at SolarWinds. Register here!


Use Docker To Explore MySQL 5.7.8-rc

August 10, 2015

Recently I have been using Ansible and Vagrant to test the MySQL 5.7 release candidates but several of you asked about using Docker. The hardest part of this process will be installing Docker on your operating system of choice and that is fairly easy. I am using Ubuntu 14.04 LTS and the installation was a wget command.

Next comes the magic. Docker will download the MySQL 5.7.8-rc image if it is not already loaded locally and then start it.
docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORRD=secret -d mysql:5.7.8-rc
The quick translation of the above is that we are telling Docker to set up a container named mysql on port 3306 using a password of secret, run all this as a daemon using MySQL version 5.7.8-rc.

And MySQL 5.7.8-rc is running. But to find it you will have to ask Docker where the server is running.

docker inspect mysql | grep -i ipad
        "IPAddress": "172.17.0.12",
        "SecondaryIPAddresses": null,

Using the local MySQL client, it is easy to connect to the 5.7.8-rc server.

mysql -h 172.17.0.12 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.8-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.6.26, for Linux (x86_64) using  EditLine wrapper

Connection id:          2
Current database:       
Current user:           root@172.17.42.1
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ; 
Server version:         5.7.8-rc MySQL Community Server (GPL)
Protocol version:       10
Connection:             172.17.0.12 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 12 sec

Threads: 1  Questions: 5  Slow queries: 0  Opens: 105  Flush tables: 1  Open tables: 98  Queries per second avg: 0.416
--------------

And now there is a instance of 5.7.8-rc to use. Just add in your schemas!

Note that by default 5.7.8 would rather assign a random password and the above ‘force’ of a password is an insecure install (–initialize-insecure).


Ansible and Loading MySQL Databases Part II

July 29, 2015

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