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 to MySQL Shell

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!

mycli — New command line interface for MySQL

September 16, 2015

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. Screenshot of 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

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

DFW Unix User Group – MySQL Workbench January 8th

December 17, 2014

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

August 26, 2014

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 ORMCakePHP 2014 attendees in Madrid

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

June 30, 2014

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.