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.
# 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
