Connect to wp-env DB host from external tooling

[danieliser]

This may already exist and I'm not finding it which means we should work on updating the docs, but if that is not the case then this may be a feature request for the @wordpress/env package.

Specifically looking to use MySQL Workbench or Navicat MySQL tools to work on custom db tables in a plugin would be my particular use case.

I have 2 parts I'm stuck on:

1. I need to be able to connect externally. I managed to get this working once by luck, but after the env was ips, ports all seem to have changed. I can't find a reliable way to map a connection.

2. We have a large sample set of data needed for our testing currently, not sure if wp-env was really meant for that or not but trying to make it work.

I can of course rerun my internal tool to generate mock data but this can take hours depending on the time ranges being generated.

Preferably I'd love a way to run extra mysql imports from a custom file on creation of an environment for a specific project.

Might be nice to add a custom_sql_import option of sorts to the .wp-env.json file

Thanks.

[danieliser]

Should add the way I have gotten a connection working so far is to open WSL terminal and run docker ps which shows the running containers and their IP:PORT.

Using localhost and the corresponding port does seem to work, but changes with every restart.

[noahtallen]

I need to be able to connect externally

It sounds like we need to expose the mysql port for wp-env so that it is accessible like this 🤔

Interestingly, we do set a port for the mysql service, not sure if that works:

gutenberg/packages/env/lib/build-docker-compose-config.js

Line 127 in 97dc776

ports: [ '3306' ],

We have a large sample set of data needed for our testing currently

I'm not sure what format this is in, but you have total access to wp-cli tools which might be able to help! You can run wp-cli commands with wp-env run cli "wp $command" where $command is the command to pass to wp-cli. You can use tests-cli instead of cli if you need to target the tests database.

For example, there is a wp-cli import command documented here: https://developer.wordpress.org/cli/commands/import/. You'd run that like wp-env run cli "wp import ...options"

[timnolte]

I too have been able to connect to the Docker DB instance, using the port identified when looking at the results of docker ps. I'm wondering if a random port is being set via environment variables during setup to avoid port collisions.

[danieliser]

It is definitely random, changes each time I boot the env.

[timnolte]

@danieliser yes, so the port is random due to how Docker works, and the PR that exposed an external port talks about this explicitly and there was discussion about making it configurable but it was left being random for the time being. #21545 (comment)

[lgersman]

It's not a solution for this issue, but accessing the wp-env Database using common mysql tools like

• mysqldump
• mysql client

can easily done using docker-compose.

Since the wp-env generated docker-compose file already provides the database password and database name, you only need to
use docker(-compose) exec functionality.

Some examples (directly from my Linux terminal) :

• dump wordpress database : docker-compose -f wp-env-home/*/docker-compose.yml exec -T mysql sh -c 'mysqldump --skip-comments --skip-extended-insert --password="$MYSQL_ROOT_PASSWORD" $MYSQL_DATABASE'

  • You could use this statement to export the wordpress database in a file on your host (docker-compose -f wp-env-home/*/docker-compose.yml exec -T mysql sh -c 'mysqldump --skip-comments --skip-extended-insert --password="$MYSQL_ROOT_PASSWORD" $MYSQL_DATABASE' > wordpress_db.sql and inspect this dump in your host system using arbitrary mysql tools)

• open mysql client in terminal : docker-compose -f wp-env-home/*/docker-compose.yml exec mysql sh -c 'mysql --password="$MYSQL_ROOT_PASSWORD" "$MYSQL_DATABASE"'

Hope this helps a bit until this issue gets resolved.

[noahtallen]

Might be nice to add a custom_sql_import option of sorts to the .wp-env.json file

Related is #29776 which discusses adding a WordPress import option.

[noahtallen]

Closing this, as I think this is documented now (https://github.com/WordPress/gutenberg/blob/trunk/docs/contributors/code/getting-started-with-code-contribution.md#accessing-the-mysql-database). We're also working towards a custom startup script which would allow imports (#49996)