607.273.3828

Dokku and MySQL Root Control

For hosting web applications, we have embraced Dokku, a wrapper around docker that we find to be so much easier to implement and manage. We probably manage about 70 virtual servers, and most use Dokku. Many of our applications run MySQL or PostgreSQL instances, and the selection depends on whether we need some DB-specific features like GIS.

Recently I imported a MySQL dump from my development computer into a Dokku MySQL instance. This is an easy process, and went something like this:

$ mysqldump -u root -p MY_DATABASE > database.sql
$ scp database.sql REMOTE_SERVER:
$ ssh USER@REMOTE_SERVER 
# dokku mysql:import MY_DATABASE < database.sql

Seems straightforward and the site worked, at least until the database needed to be revised. I received a nasty warning when trying to import another database dump: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation.

This problem was traced to the dump file specifying a DEFINER for a view. When the database was imported, it set the development computer user as the DEFINER for views. Unfortunately the imported views could not be deleted by the Dokku MySQL user!
# dokku mysql:connect MY_DATABASE
mysql> drop view MY_VIEW;
Access denied; you need (at least one of) the SYSTEM_USER
privilege(s) for this operation.

So I needed to get into the MySQL database with a user with SYSTEM_USER privileges, which to me meant the root user. I could run this command to get the folder that has a file named ROOT_PASSWORD, but I don’t have a MySQL client on our remote server. I tried exposing a MySQL port and then creating a local port-forwarding tunnel with SSH, but my development computer kept adding “@localhost” to the user.

# dokku mysql:info MY_DATABASE

In hindsight the answer was straightforward. First get the root password from the ROOT_PASSWORD folder that is in the information returned from the ‘dokku mysql:info MY_DATABASE’ command. Now enter the Dokku MySQL container:

# dokku mysql:enter MY_DATABASE
$ mysql -u root -p MY_DATABASE
  (enter password from ROOT_PASSWORD file)
mysql> drop view MY_VIEW;
mysql> exit
$ exit
Problem solved! Next time I will edit the dump file and remove all the “DEFINER=” statements before I import a new database into a Dokku MySQL instance.
Written by Matt Clark

RECENT ARTICLES:

GORGES Web Development