Help setting up database replication

[kostajh]

I work on MediaWiki, and having database replication in my local development environment is very useful for reproducing production issue.

Here's my .lando.yml file:

name: mw-lando
recipe: lamp
config:
  webroot: .
  xdebug: true
  php: 7.2
excludes:
  - vendor
  - node_modules
  - resources
  - tests
  - cache
services:
  redis:
    type: redis
    portforward: true
  appserver:
    build:
      - composer install
  database:
    overrides:
      environment:
        MYSQL_REPLICATION_MODE: master
        MYSQL_REPLICATION_USER: replicator
        MYSQL_REPLICATION_PASSWORD: replicator
        MYSQL_USER: lamp
        MYSQL_DATABASE: lamp
        ALLOW_EMPTY_PASSWORD: yes
    type: mysql:5.7
    portforward: true
  databasereplica:
    type: mysql:5.7
    portforward: true
    environment:
      MYSQL_REPLICATION_MODE: slave
      MYSQL_REPLICATION_USER: replicator
      MYSQL_REPLICATION_PASSWORD: replicator
      MYSQL_DATABASE: lamp
      MYSQL_MASTER_HOST: database
      ALLOW_EMPTY_PASSWORD: yes
tooling:
  mysql:
    service: masterdb
    cmd: mysql -uroot

A couple of notes/observations:

• I followed the env variables from https://github.com/bitnami/bitnami-docker-mysql/blob/master/docker-compose-replication.yml
• ALLOW_EMPTY_PASSWORD doesn't seem to work, since if I remove MYSQL_REPLICATION_PASSWORD then I see access is denied for the replicator user

Replication isn't happening. Maybe I need custom .cnf files? Although the Bitnami docker-compose file says using these variables should just work...

[pirog]

@kostajh i think you want

  databasereplica:
    type: mysql:5.7
    portforward: true
    overrides:
      environment:
        MYSQL_REPLICATION_MODE: slave
        MYSQL_REPLICATION_USER: replicator
        MYSQL_REPLICATION_PASSWORD: replicator
        MYSQL_DATABASE: lamp
        MYSQL_MASTER_HOST: database
        ALLOW_EMPTY_PASSWORD: yes

note the overrides key

[kostajh]

gah, I just discovered this myself :) thanks @pirog it's working now!

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions and please check out this if you are wondering why we auto close issues.

[claytron5000]

This is great and exactly what I've been looking for. However, I haven't been able to get it to work.

.lando.yml:

services:
  primary:
    type: mariadb:10.1
    portforward: 32789
    overrides:
      environment:
        MYSQL_REPLICATION_MODE: master
        MYSQL_REPLICATION_USER: replicator
        MYSQL_REPLICATION_PASSWORD: replicator
        MYSQL_DATABASE: qadb
        MYSQL_USER: database
        MYSQL_PASSWORD: database
        ALLOW_EMPTY_PASSWORD: yes
  primary_replica:
    type: mariadb:10.1
    portforward: 32794
    overrides:
      environment:
        MYSQL_REPLICATION_MODE: slave
        MYSQL_REPLICATION_USER: replicator
        MYSQL_REPLICATION_PASSWORD: replicator
        MYSQL_DATABASE: qadb
        MYSQL_MASTER_HOST: primary
        ALLOW_EMPTY_PASSWORD: yes
tooling:
  mysql:
    service: masterdb
    cmd: mysql -uroot

1. I set up a user in both database, replicator, with all the permissions.
2. Copied the master database into the slave.

Expectation: When I create a record in the master database, it is replicated into the slave database.

Am I missing a step, or config?

[kostajh]

Here's what I ended up with under services:

  database:
    overrides:
      environment:
        MYSQL_REPLICATION_MODE: master
        MYSQL_REPLICATION_USER: replicator
        MYSQL_REPLICATION_PASSWORD: replicator
    type: mysql:5.7
    portforward: true
  databasereplica:
    name: replica
    type: mysql:5.7
    portforward: true
    overrides:
      environment:
        MYSQL_REPLICATION_MODE: slave
        MYSQL_REPLICATION_USER: replicator
        MYSQL_REPLICATION_PASSWORD: replicator
        MYSQL_MASTER_HOST: database

You should be able to look at the MySQL container logs to see what's going wrong.

[claytron5000]

Hm. This looks similar to my setup; I copied it directly and it still doesn't work.

As for following the container logs, I get no action on the lando logs -f command. Adding rows, deleting, whatever, doesn't get logged in that view.

In the tooling section, is the masterdb service something that's documented anywhere? I've read some about master/slave database, and it seems more complicated, but maybe that complexity is hidden in the service?

[claytron5000]

I guess the big question is do you have a cusotm my.cnf file for the databases

[mstrelan]

I struggled with this for a bit but managed to get it working with a Drupal recipe by specifying the credentials for the replica db, it didn't work if I left them blank.

name: drupal9
recipe: drupal9
config:
  webroot: web
services:
  database:
    overrides:
      environment:
        MYSQL_REPLICATION_MODE: master
        MYSQL_REPLICATION_USER: replicator
        MYSQL_REPLICATION_PASSWORD: replicator
  replica:
    type: mysql:5.7
    portforward: true
    creds:
      user: drupal9
      password: drupal9
      database: drupal9
    overrides:
      environment:
        MYSQL_REPLICATION_MODE: slave
        MYSQL_REPLICATION_USER: replicator
        MYSQL_REPLICATION_PASSWORD: replicator
        MYSQL_MASTER_HOST: database