How can I run a SQL command when using a Docker install?

While troubleshooting an issue you may be asked to run a SQL statement on your database Docker container, or you may want to run a statement yourself for another purpose.

To do either of these things there are two options.

1. Using the command line

2. Using the GUI called PhpMyAdmin

Command Line

Before you can run a statement inside the docker container you will need to find the container name. This can be done using the docker ps command.

The resulting output will show a list of running docker containers, one of these will be the web container. Depending on your folder name, that will look something like this:

CONTAINER ID        IMAGE                             COMMAND                  CREATED             STATUS              PORTS                               NAMES
28a6c69ec7be        xibosignage/xibo-cms:latest       "/entrypoint.sh"         2 weeks ago         Up 16 seconds       0.0.0.0:80->80/tcp                  xibocms_web_1
a8fdca5d77dd        xibosignage/xibo-xmr:latest       "/entrypoint.sh"         2 weeks ago         Up 17 seconds       0.0.0.0:9505->9505/tcp, 50001/tcp   xibocms_xmr_1
a21e1b4c6558        mysql:5.6                         "docker-entrypoint..."   6 weeks ago         Up 17 seconds       0.0.0.0:3315->3306/tcp              xibocms_db_1

You’re looking for the container name with web in the name - in the example above that is xibocms_web_1. Then, substitute name in the following:

docker exec -ti name bash

That will get you a shell inside the web container. To connect to MySQL, you’d then run

mysql -u cms -h mysql -p cms

The password will be the one you set in config.env when you installed Xibo.

Please avoid making direct changes to your data as we can’t offer support once you’ve done so. Many of the tables reference each other so it’s not safe to delete or modify things directly, unless you have been specifically asked to do so.

PhpMyAdmin

We can add PhpMyAdmin as an extra Docker container which can be started and stopped when you need to access the database.

You need two things. First, name of your MySQL container, which you can get by running docker ps as per the Command Line instructions above. You need the one with cms-db in the name. Second, you need the name of the network the MySQL container is running in, which you can get by running docker network ls

For example:

• Container name: xibodocker_cms-db_1

• Network name: xibodocker_default

You also need to pick a port for PHPMyAdmin to be available on, you can choose any available port number, I chose 8080 which would be suitable on most systems that are dedicated to Xibo.

You would then run a statement to create and run a docker container for PhpMyAdmin:

docker run --name phpmyadmin -d --network=xibodocker_default --link xibodocker_cms-db_1:db -p 8080:80 phpmyadmin/phpmyadmin

PHPMyAdmin will then be running on port 8080, and you would log in with the username CMS and the password you picked in config.env.

When you’re done with PHPMyAdmin, you can simply run docker stop phpmyadmin to stop it temporarily (and then docker start phpmyadmin to start it again if needed later), or docker rm phpmyadmin to remove it completely.