roll no rocks

Ansible, python and mysql… untangling the mess

Nearly every “how to” article or tutorial on the web describes one way of using Ansible, python, and connecting to MySQL as if that was the only solution.  Many don’t note code versions used, or even the pub date, and the Internet is rife with simply bad advice.  I finally gave up researching all this and ran a few quick tests  using VirtualBox/Vagrant to see what is really necessary to do a few things we need.  Our situation is:

  • Ubuntu 18.04, building instances using Ansible 2.7
  • We must manage remote mysql users
  • A target machine must be able to
    • run mysql commands from the command line (via a python script)
    • run mysql queries from inside a python script

Here’s what I found:

Python 2 vs python 3: just stop using python(2)

Unless you’re invested in a bunch of skanky old python(2) code, there is no reason to NOT use python3.  Python3 is the default version, and comes pre-installed on Ubuntu 18.04 (at least all of the images we are using) and it is accessible as “python3”.  Install it like this on your local machine too.  (Python 2 and python 3 are not compatible, so they are reasonably installed using different executable names. Don’t fight this.  The Homebrew folks added some confusion at first by installing python 3 as “python” in some cases, but that’s fixed now.)

To use python3 with Ansible, you must set the variable ansible_python_interpreter: “python3” , and then Ansible will just use python3 and you won’t need to mess around installing python(2) at all.  For anything.

What’s needed to use the Ansible mysql_user module: the PyMySQL pip package

Ansible runs on python (specifically, python3, if you are following along.)  If you try and run a mysql_user task without installing the necessary pip packages, you’ll get a surprisingly helpful error message: “The PyMySQL (Python 2.7 and Python 3.X) or MySQL-python (Python 2.X) module is required.”  So to make this work in Ansible, just install the PyMySQL pip package:

# this package is required to use the pip module
    - apt:
        name: python3-pip
        state: present

    # this is the required pip package
    - pip:
        name: PyMySQL

    # and finally, now you can use mysql_user module
    - mysql_user:
        login_host: 127.0.0.1
        login_user: fake_master_user
        login_password: fake_master_password
        login_port: 3306
        user: fake_user
        password: fake_password
        host: 127.0.0.1

What’s needed to run mysql from the command line: the mysql-client linux package

Finally with Ubuntu 18.04 and current versions of mysql packages, we have success by installing a single linux package.  This package makes the “mysql” command available from the command line for all users (and from python scripts.)

      - apt:
          name: mysql-client
          state: present

What’s needed access mysql databases from within a python script: just use the PyMySQL pip package

Our batch jobs do most of the mysql work using the mysql command line (using python’s subcommand with the shell=True option,) but there are times when we need to read some data in the script to determine what tasks to perform, etc.  The two most common pip packages are PyMySQL and mysql-connector-python.  After digging into the code examples, it turns out that there are only a few differences in the interface, and my conclusion is that for most purposes there is no real difference.

I recommend using PyMySQL, because it’s already required for the Ansible mysql_user module, and you can install it on the target host using the same Ansible tasks show above.

Note: WordPress’ unrelenting march to make editing easier totally wrecked this post, due to what I consider blatant disregard for backward compatibility with respect to existing posts. I’ve had to completely reassemble this. My apologies if it’s a bit out of order.

Tom

, , ,