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

Fabric First Steps, er, Threads

February 1, 2015

I will be presenting MySQL Fabric to the Triangle MySQL User Group in Raleigh on February 11th and then I will be speaking the next day at Percona University. Part of my first talk will have a live demo. Most presenters dread live demos as anything that can go wrong usually does go wrong and in very spectacular fashion to ensure humiliation, disgrace, and well deserved scorn.

To add to the pressure is a new laptop that is so far reluctant to perform well during the first two presentations of this year. Hopefully third presentation is a charm.

So how do you start with Fabric? First, download the MySQL Utilities. I am using the 1.5.2 version (1.6 Alpha is also available) and installed it on my Ubuntu 14.04 with dpkg.

For the impatient, there is a Fabric Quick Start for those who do not want to follow my plodding.

I setup a Fabric user with MySQL Workbench un-creatively named ‘Fabric’.

The heart of Fabric is the mysqlfabric command.
$ mysqlfabric help
Usage: mysqlfabric [--param, --config] [arg, ...].

MySQL Fabric 1.5.2 - MySQL server farm management framework

--version show program's version number and exit
-h, --help show this help message and exit
Override a configuration parameter.
--config=FILE Read configuration from FILE.

Basic commands:
help Show help for command
help commands List all commands
help groups List all groups

Now I need to investigate setting up some servers to manage. With a new laptop I need to see how it handles Virtual Box, Vagrant or maybe Giuseppe’s MySQL Sandbox for this purpose. Part of the dread of a live demo is the failure of the building blocks and i want this as simple as possible. But that will be in the next post.

Dallas PHP Users Group Meeting Rescheduled to Jan 21st

January 14, 2015

Dave Stokes: SQL for PHP Programmers

Wednesday, Jan 21, 2015, 7:00 PM

SoftLayer Offices
4849 Alpha Rd Dallas, TX

50 PHP Developers Went

This month we welcome back Dave Stokes to share some excellent SQL knowledge. He’ll be presenting a regular session length of his tutorial talk “SQL for PHP Developers”:————-PHP Programmers know the latest and greatest on their preferred language, are excellent at Javascript but very few have had any training in SQL.  This presentation co…

Check out this Meetup →

Wednesday, January 21, 2015
7:00 PM

SoftLayer Offices
4849 Alpha Rd, Dallas, TX (map)

Come in the doors by the flagpoles, check in with security then it’s upstairs to the left
This month we welcome back MySQL Community Manager Dave Stokes from Oracle to share some excellent SQL knowledge. He’ll be presenting a regular session length of his tutorial talk “SQL for PHP Developers”:


PHP Programmers know the latest and greatest on their preferred language, are excellent at Javascript but very few have had any training in SQL. This presentation covers the differences in a declarative language (SQL) and an Object-Orientated/Procedural language (PHP), how relational theory is supposed to work, and how to let the database do the heavy lifting to make your life easier.
Come out on Tuesday the 20th at 7pm at the SoftLayer offices on Alpha to hang out with fellow developers and hear about this great topic!

Where: SoftLayer Offices on Alpha Rd

Who: Dave Stokes

When: Wednesday, January 21st @ 7pm

Color Your MySQL Console

January 13, 2015

I started programming in the era of punch cards and learned to love monochrome screens quickly. The standard MySQL client console is a echo of those days. But we can get color.

One of the really interesting parts of my job is looking at the new submissions to Planet.MySQL.Com and seeing what interesting things people are doing with MySQL. Today I found Nicola Strappzaaon’s in the approval queue and his top article at the time was Give a little color to the MySQL Console. Actually the tile read Darle un poco de color a la consola de MySQL but Google translate overcame my insufficient skills.

Basically you install grcat ‘universal coulouriser’, install a configuration file, and tweak the .my.cnf file to use grcat as the pager. Nicola’s blog goes into more detail but here is the gist.

apt-get install grc wget -O ~ / .grcat

And the .my.cnf file

pager = grcat ~/.grcat | less -RSFXin

Colorized MySQL Console

Color Added MySQL Console is quick and easy to do do.

I had change the .grcat configuration file as there was a complaint about a bad color ‘whit’ but replacing the first ‘white’ in the file with red solved that quickly. So change white to red under the section #data in ( ) and ‘ ‘.

So hats off to Nicola Strappzaaon and all the other new bloggers on Planet! And I encourage everyone to check out the different language blogs as you never know what gems you will find there.


Get every new post delivered to your Inbox.

Join 829 other followers