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 ("[email protected]","123456","Admin",31);
INSERT INTO user (email,password,name,age) VALUES ("[email protected]","123456","user1",31);
INSERT INTO user (email,password,name,age) VALUES ("[email protected]","123456","user2",31);
INSERT INTO user (email,password,name,age) VALUES ("[email protected]","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.
For failover in MaxScale to work, you should be using GTID replication: