Multiple Docker mysql instances on different ports

Found a tutorial gives the following example:

$ docker run -d –name=new-mysql -p 6604:3306 -v /storage/docker/mysql-datadir:/var/lib/mysql mysql

let’s dissect what we’re passing to docker

  • -d is short for –detach. This runs the container as a daemon in the background
  • –name is the name of the container
  • -p is short for –publish. This is a port mapping where the standard mysql port for the container (3306) appears as a different port (6604) on the local machine
  • -v is short for –volume. This tells the container to use a path outside the container for storing the mysql stuff, including data. This is needed so that the data persists if the container goes away.

Tweaking this to use my setup exited with an error because the mySQL root password was not set. After struggling with an error that came from invisible crud coming from copy paste (sigh), I got this to work, where mypassword is replaced by a real password.

docker run -d –name=mysql-rep01 -p 3307:3306 –env “MYSQL_ROOT_PASSWORD=mypassword” -v /var/lib/mysql-replica01:/var/lib/mysql mysql

To connect to this mySQL instance, I need to specify the host with -h and the port to use with -P.

mysql -u root -p -P 3307 -h 127.0.0.1

This doesn’t work if I use localhost instead of 127.0.0.1.

Connect it to a copy of phpMyAdmin

The Ubuntu apt installation arranges things a little differently from a manual installation. The configuration is inside /etc/phpmyadmin. In addition to the usual config.inc.php, there’s a directory called conf.d; anything with a .php extension is included. This allows you to create separate files for each database container.  I made one called config3307.inc.php. I took out a bunch of statements that rely on global variables $dbname and $dbserver. I think this is similar to the stuff that’s commented out in config.inc.php, but not that the port has to be set.

<?php
/**
 * Alternate configuration file
 * This file copies a central block from config.inc.php to manage connection 
 * to an alternative db server in a Docker container publishing on local port 3307
 */

/* Authentication type */
 $cfg['Servers'][$i]['auth_type'] = 'cookie';
 $cfg['Servers'][$i]['host'] = '127.0.0.1';

 $cfg['Servers'][$i]['connect_type'] = 'tcp';
 $cfg['Servers'][$i]['port'] = '3307';
 //$cfg['Servers'][$i]['compress'] = false;
 /* Select mysqli if your server has it */
 $cfg['Servers'][$i]['extension'] = 'mysqli';
 /* Optional: User for advanced features */
 $cfg['Servers'][$i]['controluser'] = $dbuser;
 $cfg['Servers'][$i]['controlpass'] = $dbpass;
 /* Optional: Advanced phpMyAdmin features */
 $cfg['Servers'][$i]['pmadb'] = $dbname;
 $cfg['Servers'][$i]['bookmarktable'] = 'pma__bookmark';
 $cfg['Servers'][$i]['relation'] = 'pma__relation';
 $cfg['Servers'][$i]['table_info'] = 'pma__table_info';
 $cfg['Servers'][$i]['table_coords'] = 'pma__table_coords';
 $cfg['Servers'][$i]['pdf_pages'] = 'pma__pdf_pages';
 $cfg['Servers'][$i]['column_info'] = 'pma__column_info';
 $cfg['Servers'][$i]['history'] = 'pma__history';
 $cfg['Servers'][$i]['table_uiprefs'] = 'pma__table_uiprefs';
 $cfg['Servers'][$i]['tracking'] = 'pma__tracking';
 $cfg['Servers'][$i]['userconfig'] = 'pma__userconfig';
 $cfg['Servers'][$i]['recent'] = 'pma__recent';
 $cfg['Servers'][$i]['favorite'] = 'pma__favorite';
 $cfg['Servers'][$i]['users'] = 'pma__users';
 $cfg['Servers'][$i]['usergroups'] = 'pma__usergroups';
 $cfg['Servers'][$i]['navigationhiding'] = 'pma__navigationhiding';
 $cfg['Servers'][$i]['savedsearches'] = 'pma__savedsearches';
 $cfg['Servers'][$i]['central_columns'] = 'pma__central_columns';
 $cfg['Servers'][$i]['designer_settings'] = 'pma__designer_settings';
 $cfg['Servers'][$i]['export_templates'] = 'pma__export_templates';

/* Uncomment the following to enable logging in to passwordless accounts,
 * after taking note of the associated security risks. */
 // $cfg['Servers'][$i]['AllowNoPassword'] = TRUE;

/* Advance to next server for rest of config */
 $i++;
Note the pulldown to go between the two servers

Then I had to create the phpmyadmin user and the create the tables for the advanced phpmyadmin features. I just imported the SQL file from phpmyadmin sql/create_tables.sql. Now it looks like this:

Connect it to a copy of MediaWiki

Created another MW directory on the host to use the containerized database. I just did the web install but use 127.0.0.1:3307 as the database location. The installer works and the correct tables are created. Copied LocalSettings to the correct location and it seems to work as a blank wiki. In LocalSettings, we see:

$wgDBserver = "127.0.0.1:3307";

For the port publishing done by the Docker container.

Make it restart automatically

add the somewhere before the image name.

--restart unless-stopped flag

Make it a service?

So far I’ve made a container that I can launch from the command line. The Docker docs talk about how:

Services are really just “containers in production.” A service only runs one image, but it codifies the way that image runs—what ports it should use, how many replicas of the container should run so the service has the capacity it needs, and so on.

Note that the rest of that tutorial goes beyond running a container in production. It sets up to run multiple instances that do load balancing between them, That would be nice but I’m going to save that for another time.

Data persistence!