Mysql – Setting up replication with MariaDB 10.3.4 docker images

dockermariadbMySQLreplication

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
            #
            - ./database/slave/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/slave/docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d:ro
        depends_on:
            - database

The .env/.env.database file simply exposes the environment variables that the MariaDB docker image requires:

# the root user password
#
MYSQL_ROOT_PASSWORD=password

# the database to use
#
MYSQL_DATABASE=mydatabase

Note that this is my development environment, so I'm using a dumb password.

The master & slave configuration files are mounted from my local host.

000-replication-master.sql:

GRANT REPLICATION SLAVE ON *.* TO 'replicant'@'%' IDENTIFIED BY 'password';

replication.cfg for the master:

[mariadb]
    log-bin
    server_id=1
    log-basename=master1

    # force binlog format to ROW to avoid issues with
    # replicate_do_db
    binlog_format=ROW

000-replication-slave.sql:

-- configure the connection to the master
--
CHANGE MASTER TO
    MASTER_HOST='database',
    MASTER_USER='replicant',
    MASTER_PASSWORD='password',
    MASTER_PORT=3306,
    MASTER_USE_GTID=slave_pos,
    MASTER_CONNECT_RETRY=10;

-- start the slave
--
START SLAVE;

replication.cnf for the slave:

[mariadb]
    server_id=1000
    relay-log = /var/log/mysql/mysql-relay-bin.log
    log_bin = /var/log/mysql/mysql-bin.log

The error I'm seeing on the slave is this:

Could not execute Write_rows_v1 event on table mysql.user; Duplicate entry 'localhost-root' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY;

The issue is similar to this question, but I'm attempting to use the stock MariaDB images (instead of a custom Docker image).

I've tried a number of different things:

  1. I set it up with replicate_do_db = mydatabase on the slave and it did work, but given the concerns with slave filtering, I'd prefer not to use it. I think it's set up correctly but I'd rather not take the chance.

  2. I've tried deleting the offending row from the mysql.user table (both with a DELETE statement and, when that didn't with, with TRUNCATE) on the slave before the CHANGE MASTER statement, but this does not work.

I should mention that I've searched for an answer to this problem, but all the tutorials online suggest getting the binary log position on the master and manually updating the slave position before starting replication. I'm looking for a solution that will allow me to set up the slave immediately after the master is created and start syncing from scratch.

So, in short, the question is how do I set up a master & slave to replicate everything, starting from a brand-new installation of MariaDB on both master and slave?

Best Answer

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:

  1. log in as root@slave_hostname, or
  2. create a new user which is unique to the slave which has root privileges.
Related Question