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
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
.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.
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
-- 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:
I set it up with
replicate_do_db = mydatabaseon 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.
I've tried deleting the offending row from the
mysql.usertable (both with a
DELETEstatement and, when that didn't with, with
TRUNCATE) on the slave before the
CHANGE MASTERstatement, 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?