Setting up replication with MariaDB 10.3.4 docker images

I'm attempting to set up replication between two docker containers, both running the stock MariaDB 10.3.4 images (which are the latest versions as of right now). When I start up the containers, I get error code 1062 (Duplicate key) on table mysql.user for key localhost-root. The slave is clearly trying to replicate the mysql.user table from the master and failing because they both have root@localhost users. This does not seem to be Docker-related - I would imagine the same issue will arise when setting up any master/slave pair from scratch.

How can I set up a slave to replicate everything? I'm starting from scratch, so I want the slave to be a (more-or-less) perfect copy of the master.

Here is the set up:

I'm running the containers from a docker-compose.yml file:

```
version: '2'

volumes:
dbdata:
external: false

services:

# the MariaDB database MASTER container
#
database:
image: mariadb:10.3.4
env_file:
- ./env/.env.database
volumes:
- dbdata:/data/db
- /etc/localtime:/etc/localtime:ro
# mount the configuration files in the approriate place
#
- ./database/master/etc/mysql/conf.d:/etc/mysql/conf.d:ro
# mount the SQL files for initialization in a place where the
# database container will look for it on initialization; see
# "Initializing a fresh instance" at
# https://hub.docker.com/_/mariadb/ for details
#
- ./database/master/docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d:ro
ports:
- "3306:3306"

# the MariaDB database SLAVE container
#
slave:
image: mariadb:10.3.4
# env_file:
# - ./env/.env.database
environment:
- MYSQL_ALLOW_EMPTY_PASSWORD=yes
volumes:
- /etc/localtime:/etc/localtime:ro
# mount the configuration files in the approriate place

I've come up with an answer for this - it seems like a bit of a hack, but it seems to work.

To recap: the goal was to set up a master with a single slave using MariaDB 10.3.4 (in Docker containers, although this isn't necessarily a requirement) from scratch (ie. newly-installed, empty databases), so that the system will simply start up and work without any errors. I wanted to use MariaDB's global transaction IDs.

The issue I was encountering was that some of the queries were failing because users already existed in the database (having been set up as part of the Docker container initialization). When the master sent the queries for creating these users, the slave failed since these rows already exist in the mysql.users table.

The solution was to simply drop those users before executing the CHANGE MASTER statement. Here's the SQL which the slave runs at startup:

-- drop the application user
DROP USER IF EXISTS 'myuser'@'%';
-- drop the root user
DROP USER IF EXISTS 'root'@'localhost';

CHANGE MASTER TO
    MASTER_HOST='database',
    MASTER_USER='replicant',
    MASTER_PASSWORD='password',
    MASTER_PORT=3306,
    MASTER_USE_GTID=slave_pos,
    MASTER_CONNECT_RETRY=10;

Trial and error revealed that those were the two users that were failing.

One issue that comes up with this configuration: once the slave finishes its initial sync, you can no longer log in as root@localhost. There are a couple of solutions to this:

• log in as root@slave_hostname, or

• create a new user which is unique to the slave which has root privileges.

StackExchange Database Administrators Q#198840, answer score: 2