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") )

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.

Lucky Sevens — MySQL 5.7 and PHP 7

December 10, 2015

MySQL 5.7 and PHP 7 are the latest versions releases of two ofthe LAMP Stack pillars. In the past I have detailed how to use the MySQL apt-get repository to upgrade Ubuntu to the latest and greatest. But that about PHP 7? This is a fairly simple update for someone running a traditional LAMP (Linux Apache MySQL and PHP) server. There are also great directions out there on updating Nginx for those so inclined to be found with a quick search.

sudo apt-get install python-software-properties
sudo add-apt-repository ppa:ondrej/php-7.0
sudo apt-get update
sudo apt-get purge php5-fpm
sudo apt-get install php7.0-cli php7.0-common libapache2-mod-php7.0 php7.0 php7.0-mysql php7.0-fpm php7.0-curl php7.0-gd php7.0-mysql
cp /usr/local/php7/ /usr/lib/apache2/modules/

Then a quick test program:
$mysqli = new mysqli("localhost", "root", "hidave", "world");

/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());

/* print MySQL server version */

PHP version: %s

", phpversion());

MySQL version: %s

", $mysqli->server_info);
printf ("

System status: %s

", $mysqli->stat());


And the result:
PHP version:

MySQL version: 5.7.9

System status: Uptime: 2102 Threads: 1 Questions: 12 Slow queries: 0 Opens: 107 Flush tables: 1 Open tables: 26 Queries per second avg: 0.005

Please note that you will have to update your code if you use the old and no deprecated mysql calls instead of the supported mysqli calls.

Converting between GeoJSON documents and spatial values with MySQL 5.7

December 1, 2015

Need to convert between GeoJSON documents and spatial values? MySQL 5.7 has two functions that do just that. GeoJSON is an open standard for encoding geometric/geographical features. ST_AsGeoJSON generates a GeoJSON object from the geometry while ST_AsText parses a string str representing a GeoJSON object and returns a geometry.

Details at Spatial GeoJSON Functions

MySQL 5.6 to 5.7 Upgrade Warning

August 4, 2015

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

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: ""
and create the shell script.

# 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
   - docroot: /var/www/html
    - 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
        - 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

Ansible and Loading MySQL Databases Part I

July 14, 2015

Automation software like Puppet, Chef, and Ansible can quickly load software on virtual servers. But how do you get your MySQL database instances loaded on these new servers? And how do you do it securely? Lets start with a fairly common pairing.

Ansible and Vagrant work very well together and the documentation for getting both to work together is rather extensive. BTW the newest version VirtualBox is 5.0 was recently released with a large number of improvements. Follow the documentation at their respective sites to get VirtualBox, Vagrant, and Ansible installed.

The linchpin is the Vagrantfile which controls how Vagrant starts the server. Inside this file we pull in an Ansible playbook.

Vagrant.configure(2) do |config| = "tutorial"
  config.vm.hostname = "" :private_network, ip: ""
  config.hostmanager.enabled = true
  config.hostmanager.manage_host = true
  config.vm.provision "ansible" do |ansible|
    ansible.playbook = "playbook.yml"


The playbook is a list of actions to be undertaken to set up the desired software.

- hosts: all
  sudo: true
  gather_facts: false
   - name: Install Packages
     apt: name={{item}} state=installed
      - apache2
      - php5

   - name: Create Virtual Host
     copy: src=vhost.conf dest=/etc/apache2/sites-enabled/000-default.conf

   - name: Web site
     git: repo=/home/vagrant/our-site/ dest=/var/www/site

    - name: reload apache
      service: name=apache2 state=reloaded

The above playbook installs Apache and PHP, creates a virtual host by copying over configuration files to the new server, grabs the website files from git, and then restarts the Apache service so the new configuration & files take effect.

Shell>vagrant provision
[default] Running provisioner: ansible...

PLAY [all] ******************************************************************** 

TASK: [Install Packages] ****************************************************** 
ok: [default] => (item=apache2,php5)

TASK: [Create Vitual Host] **************************************************** 
ok: [default]

TASK: [Web site] ************************************************************** 
ok: [default]

PLAY RECAP ******************************************************************** 
default                    : ok=4    changed=0    unreachable=0    failed=0 

As you can imagine, it is easy to add another item for MySQL and have Ansible start up a fresh install of MySQL. That may be fine for a fresh install but what about cases where you want to use existing databases? Copying over static files may work in some cases but what if you want to copy a live database from another server? Or you need to setup a new replication slave? And how do you do this without exposing passwords??

Ansible offers four MySQL Database Modules:

  • mysql_db – Add or remove MySQL databases from a remote host.
  • mysql_replication (E) – Manage MySQL replication
  • mysql_user – Adds or removes a user from a MySQL database.
  • mysql_variables – Manage MySQL global variables

But these modules may not be what you want. For instance, the replication module does not use GTIDs which means you have to know where in the log file on the master that you need to start replication (and that offset may be a moving target). I prefer to use the MySQL Utilities for coping grants, databases, and setting up replication. So next time I will cover how to get the virtual system running, installing the desired LAMP stack software, and then copying over a complete database with users.

MySQL NYC Meetup Wednesday!

July 13, 2015

The MySQL NYC Meetup is this Wednesday, July 15th in the Oracle office at 120 Park Avenue. I will be talking about MySQL 5.7 and the event is free but you must RSVP by 6PM July 14 (and bring ID).


Get every new post delivered to your Inbox.

Join 988 other followers