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/libphp7.so /usr/lib/apache2/modules/

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

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

/* print MySQL server version */
printf("

PHP version: %s

", phpversion());
printf("

MySQL version: %s

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

System status: %s

", $mysqli->stat());

$mysqli->close();
?>

And the result:
PHP version: 7.0.0-5+deb.sury.org~vivid+1

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


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|
  config.vm.box = "tutorial"
  config.vm.hostname = "phptek.dev"
  config.vm.network :private_network, ip: "192.168.33.10"
  config.hostmanager.enabled = true
  config.hostmanager.manage_host = true
  config.vm.provision "ansible" do |ansible|
    ansible.playbook = "playbook.yml"
  end

end

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

---
- hosts: all
  sudo: true
  gather_facts: false
  tasks:
   - name: Install Packages
     apt: name={{item}} state=installed
     with_items:
      - 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

  handlers:
    - 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

Options:
--version show program's version number and exit
-h, --help show this help message and exit
--param=CONFIG_PARAMS
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.


Follow

Get every new post delivered to your Inbox.

Join 882 other followers