In my projecto i am using docker compose to run an api (using tomcat). That api connect to a maxscale server that "connects" to other 3 databases (1 master and 2 slaves).
I've enabled replication on the mariadb side and it is recognized on the maxscale UI.
If i shut down my master one of the slaves gets promoted to a new master (the expected result). The problem comes when i turn my original master back on i get the following error:
db | 2023-07-07 6:58:11 5 [ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MariaDB server ids;
these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
Internal MariaDB error code: 1593
The strange part is the fact that if I turn of the slave that was promoted to master and turn him back on he joins as a slave again no problem at all.
Docker compose file:
#Use docker compose up --build to run. Without the build we might have problems connecting to the db
version: '3'
#use this volumes if i want to use the same volume in multiple services
#then we call data:path/ in the services to use this volume
volumes:
data:
services:
#Database container
db:
container_name: db
image: mariadb
environment:
MYSQL_USER: root
#Password for my root user
MYSQL_ROOT_PASSWORD: 123456
#Copies the init.sql file that has all the initializations for the docker entry point to create the tables
volumes:
- ./Database/init.sql:/docker-entrypoint-initdb.d/init.sql
- ./Database/initSlaves.sql:/docker-entrypoint-initdb.d/initSlaves.sql
#Maps my computers 50000 port to the 3306 on the container. Now i can access the database using localhost:50000
#Port 50000 gets exposed to my machine but not other images
ports:
- "50000:3306"
healthcheck:
test: ["CMD", "/usr/local/bin/healthcheck.sh", "--connect", "--innodb_initialized"]
interval: 10s
timeout: 5s
retries: 3
command:
- '--log-bin'
- '--server-id=3001'
- '--log-basename=my_logs'
- '--log-slave-updates'
db2:
container_name: db2
image: mariadb
environment:
MYSQL_USER: root
#Password for my root user
MYSQL_ROOT_PASSWORD: 123456
#Copies the init.sql file that has all the initializations for the docker entry point to create the tables
volumes:
- ./Database/initSlaves.sql:/docker-entrypoint-initdb.d/initSlaves.sql
#Maps my computers 50000 port to the 3306 on the container. Now i can access the database using localhost:50000
#Port 50000 gets exposed to my machine but not other images
ports:
- "50010:3306"
healthcheck:
test: ["CMD", "/usr/local/bin/healthcheck.sh", "--connect", "--innodb_initialized"]
interval: 10s
timeout: 5s
retries: 3
depends_on:
db:
condition: service_healthy
restart: false
command:
- '--log-bin'
- '--server-id=3002'
- '--log-basename=my_logs'
- '--log-slave-updates'
db3:
container_name: db3
image: mariadb
environment:
MYSQL_USER: root
#Password for my root user
MYSQL_ROOT_PASSWORD: 123456
#Copies the init.sql file that has all the initializations for the docker entry point to create the tables
volumes:
- ./Database/initSlaves.sql:/docker-entrypoint-initdb.d/initSlaves.sql
#Maps my computers 50000 port to the 3306 on the container. Now i can access the database using localhost:50000
#Port 50000 gets exposed to my machine but not other images
ports:
- "50020:3306"
healthcheck:
test: ["CMD", "/usr/local/bin/healthcheck.sh", "--connect", "--innodb_initialized"]
interval: 10s
timeout: 5s
retries: 3
depends_on:
db:
condition: service_healthy
restart: false
command:
- '--log-bin'
- '--server-id=3003'
- '--log-basename=my_logs'
- '--log-slave-updates'
#Need tomcat to host the api
api:
container_name: api
image: tomcat
#will place my jar/war file inside the webapps folder
volumes:
- ./api/target/api.war:/usr/local/tomcat/webapps/api.war
#use localhost:50001 to access tomcat
#but in the url for the database i have to use port 3306 (since that one is visible to my docker image)
ports:
- "50001:8080"
depends_on:
db:
condition: service_healthy
restart: false
db2:
condition: service_healthy
restart: false
db3:
condition: service_healthy
restart: false
maxscale:
image: mariadb/maxscale
container_name: max
ports:
- "4006:4006"
- "4008:4008"
- "50002:8989"
environment:
MYSQL_USER: maxscale
#Password for my root user
MYSQL_ROOT_PASSWORD: maxscale
depends_on:
db:
condition: service_healthy
restart: false
db2:
condition: service_healthy
restart: false
db3:
condition: service_healthy
restart: false
volumes:
- ./maxscale/maxscale.cnf:/etc/maxscale.cnf.d/my-maxscale.cnf
init.sql
CREATE USER 'maxscale'@'%' IDENTIFIED BY 'maxscale';
GRANT ALL PRIVILEGES ON *.* TO 'maxscale'@'%' IDENTIFIED BY 'maxscale' WITH GRANT OPTION;
GRANT PROXY ON ''@'%' TO 'maxscale'@'%' WITH GRANT OPTION;
CREATE USER 'repluser'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%';
FLUSH PRIVILEGES;
CREATE SCHEMA mydb;
use mydb;
CREATE TABLE user (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(45) NOT NULL ,
password VARCHAR(512) NOT NULL ,
name VARCHAR(45) NOT NULL ,
age INT NOT NULL ,
UNIQUE (email)
) ENGINE = InnoDB;
CREATE TABLE friends (
userId INT NOT NULL ,
friendId INT NOT NULL ,
date DATETIME NOT NULL ,
CONSTRAINT pk_friends PRIMARY KEY ( UserId, FriendId )
) ENGINE = InnoDB;
CREATE INDEX friendId_idx ON friends ( userId ASC, friendId ASC ) VISIBLE;
CREATE TABLE posts (
userId INT NOT NULL ,
date DATETIME NOT NULL ,
text VARCHAR(512) ,
image MEDIUMBLOB ,
CONSTRAINT pk_posts PRIMARY KEY ( UserId, Date )
) ENGINE = InnoDB;
ALTER TABLE posts ADD CONSTRAINT FK_UserId FOREIGN KEY ( userId ) REFERENCES user( id ) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE friends ADD CONSTRAINT FK_FriendId_User FOREIGN KEY ( userId ) REFERENCES user( id ) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE friends ADD CONSTRAINT FK_FriendId_Friend FOREIGN KEY ( friendId ) REFERENCES user( id ) ON DELETE NO ACTION ON UPDATE NO ACTION;
INSERT INTO user (email,password,name,age) VALUES ("admin@admin.com","123456","Admin",31);
INSERT INTO user (email,password,name,age) VALUES ("user1@user.com","123456","user1",31);
INSERT INTO user (email,password,name,age) VALUES ("user2@user.com","123456","user2",31);
INSERT INTO user (email,password,name,age) VALUES ("user3@user.com","123456","user3",31);
INSERT INTO posts (userId,date,text,image) VALUES (2,now(),"Hello world",NULL);
DO SLEEP(3);
INSERT INTO posts (userId,date,text,image) VALUES (2,now(),"Hello world 2",NULL);
INSERT INTO posts (userId,date,text,image) VALUES (3,now(),"Hello world 3",NULL);
INSERT INTO friends (userId,friendId,date) VALUES (2,3,now());
INSERT INTO friends (userId,friendId,date) VALUES (2,4,now());
initSlaves.sql
CHANGE MASTER TO
MASTER_HOST='db',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_CONNECT_RETRY=10;
FLUSH PRIVILEGES;
Ps: I've tried checking the server id on the main master server and he has 3001 while the slaves have 3002 and 3003. Any help would be appreciated.
