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.

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!

Beta Test MySQL SWAG

September 11, 2015

The latest version of MySQL Sakila SWAG is being beta tested this week at the Pacific Northwest PHP Conference.

Plush Sakila on left, Stress Relief Sakila on righ

Plush Sakila on left, Stress Relief Sakila on righ

The plush toy version of Sakila is very popular but the MySQL Community Team is exploring an alternative stress relief version.

Why a new Sakila when the old one is so popular? We on the MySQL Community Team are always looking for ways to extend and enhance our outreach. Plush toys often encounter a harsh environment such a drool from small children or pets, tearing from rough handling, and finally they get dirty from repeated handling. The new Sakilas have a plastic coating proven to be largely drool resistant and washable. Both are about the same size and weight (important when you bring a hundred to a show and have to carry them around!) and have inherent Dolphin cuteness.

Comparison of the two Sakilas

Comparison of the two Sakilas

So please, if you get the change, test the new Sakila and send any bug reports to me.

Texas Linuxfest and MySQL’s No SQL

August 20, 2015

The Texas Linxufest is Friday and Saturday in San Marcos, Texas. Come hear me speak about MySQL’s NoSQL on Saturday and get a MySQL plush dolphin . Drop by the booth for MySQL SWAG and to see MySQL 5.7.8-rc!

Sakila Toy Dolphin

Plush Sakila MySQL mascot toy

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": "",
        "SecondaryIPAddresses": null,

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

mysql -h -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

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

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


Get every new post delivered to your Inbox.

Join 829 other followers