Connecting your Lando DB with an external MySQL app (Sequel Ace) on an local environment

This article assumes that you are running Docker and Lando already.  Don't meet this... have a read through the following page "Building a Drupal or Laravel instance on OSX using Lando and Docker" for more.

I'll be using the following: .lando.yml file:

name: alpha
recipe: drupal9
config:
  webroot: web

Lando info

Lando provides a suite of useful tools, but for now I will focus on one command info

lando info

This command exposes information about our running containers or “services” in Lando terminology.  The following is output from this command on a test directory I created.  The service to look for is database:

[ { service: 'appserver',
    urls:
     [ 'https://localhost:51070',
       'http://localhost:51071',
       'http://alpha.lndo.site:8000/',
       'https://alpha.lndo.site/' ],
    type: 'php',
    healthy: true,
    via: 'apache',
    webroot: 'web',
    config: { php: '/Users/{your-name}/.lando/config/drupal9/php.ini' },
    version: '8.0',
    meUser: 'www-data',
    hasCerts: true,
    hostnames: [ 'appserver.alpha.internal' ] },
  { service: 'database',
    urls: [],
    type: 'mysql',
    healthy: true,
    internal_connection: { host: 'database', port: '3306' },
    external_connection: { host: '127.0.0.1', port: '51072' },
    healthcheck: 'bash -c "[ -f /bitnami/mysql/.mysql_initialized ]"',
    creds: { database: 'drupal9', password: 'drupal9', user: 'drupal9' },
    config: { database: '/Users/{your-name}/.lando/config/drupal9/mysql.cnf' },
    version: '5.7',
    meUser: 'www-data',
    hasCerts: false,
    hostnames: [ 'database.alpha.internal' ] } ]

The external_connection key shows 127.0.0.1 with the port 51072.  

external_connection: { host: '127.0.0.1', port: '51072' },

However, being clear upfront this port number changes from time to time embedding much frustration.  Specifically around the rebuild command:

lando rebuild

But not all is lost as it can be persisted by setting a static port in the .lando.yml file.

You are going to need to select a port that is actually available on your host machine. If you don't, then this will fail. It's a good idea to avoid common ports like 3306 which likely are in use already. To that end it's good practice to use a port CLOSE TO the default eg 3307 for 3306 or 5433 for 5432.

I updated my .lando.yml file to include the following changes:

name: alpha
recipe: drupal9
config:
  webroot: web
services:
  database:
    portforward: 3307

Run the rebuild command

# rebuild from app directory
lando rebuild --yes

Note, if you leave out the --yes, you will be prompted 

Are you sure you want to rebuild? (y,N)

# Check to see the locked down port
lando info

Now the output of the lando info command shows:

{ service: 'database',
    urls: [],
    type: 'mysql',
    healthy: true,
    internal_connection: { host: 'database', port: '3306' },
    external_connection: { host: '127.0.0.1', port: '3307' },
    healthcheck: 'bash -c "[ -f /bitnami/mysql/.mysql_initialized ]"',
    creds: { database: 'drupal9', password: 'drupal9', user: 'drupal9' },

The port is static.

Sequel Ace

When opening a new window, you will be prompted for the following:

• TCP/IP
• Socket
• SSH

In this situation we'll focus on TCP/IP.  Using the data above complete:

Name: Lando DB

Host: 127.0.0.1

Username: drupal9

Password: drupal9

Database: 

Port: 3307

I intentionally left Database blank.  I prefer to select in the app.  However, you can easily add so your page opens in the corresponding DB.  Using the data above the Database would be

Database: drupal9