Many of OpenStack’s containerised services do not include a MySQL client, despite heavily relying on the MariaDB/Galera cluster. Instead, the services use the PyMySQL and SQLAlchemy python modules for this interaction.

Given that peeking into the database is a pretty common operation when troubleshooting issues in OpenStack, the lack of a MySQL client is a bit of a pain point. Yes, you can always jump on to one of the controllers, grab a shell in the Galera container, and have at it, but this may not be beneficial if you suspect a network or connectivity related issue.

Thankfully it is quite simple to just utilise PyMySQL directly.

Firstly, you will want to fire up a shell in a relevant service container. I am going to use nova_compute for my example.

$ ssh heat-admin@compute1 
$ sudo docker exec -it nova_compute /bin/bash

Next, grab your account, password, and database connection details from the service configuration file.

$ egrep "^connection=" /etc/nova/nova.conf
connection=mysql+pymysql://nova_api:password1@os.internalapi.example.com/nova_api?read_default_group=tripleo&read_default_file=/etc/my.cnf.d/tripleo.cnf
connection=mysql+pymysql://nova:password2@os.internalapi.example.com/nova?read_default_group=tripleo&read_default_file=/etc/my.cnf.d/tripleo.cnf 
connection=mysql+pymysql://nova_placement:password3@os.internalapi.example.com/nova_placement?read_default_group=tripleo&read_default_file=/etc/my.cnf.d/tripleo.cnf

In this case I am interested in the connection details for the nova database. Note that you can also just use the root account and Galera service password if you have those details handy. Using root you will not need the additional default group/file details.

Now fire up a Python shell, import pymysql, and connect to the database (using the details above).

$ python 
>>> import pymysql 
>>> connection = pymysql.connect(host="os.internalapi.example.com",
        user="nova", password="password2",
        db="nova", read_default_group="tripleo",
        read_default_file="/etc/my.cnf.d/tripleo.cnf") 
>>> cursor = connection.cursor()

This cursor can now be used to execute SQL queries against the database. Here is a simple example:

>>> cursor.execute("select uuid,hostname from instances")
>>> for row in cursor.fetchall():
...     print("UUID: {} Name: {}".format(row[0], row[1]))
UUID: 63ec8044-9cd6-489e-9b58-26ccf67694fc Name: instance1
UUID: d4514c93-e4a1-4d11-bab5-b5a6d37bec8d Name: instance2
UUID: 8b4fa235-19e6-49a7-ac9f-3dc14da8eceb Name: instance3
UUID: cab40e8f-643b-45a3-b261-091db0e09941 Name: instance4

You’ll need to run a connection.commit() if you actually want to commit any changes to the database, but of course I am strongly recommending you don’t do this.

Finally, close your connection and be on your way.

>>> cursor.close() 
>>> connection.close()

OK – one last thing. If you would like to use tab completion in the python shell to easily show instance attributes and methods, run the following (this could also be added to your pythonrc file).

>>> import readline, rlcompleter
>>> readline.parse_and_bind("tab: complete")