在我的项目中,我使用Docker compose来运行API(使用Tomcat)。该API连接到maxscale服务器,该服务器“连接”到其他3个数据库(1个主数据库和2个从数据库)。
我已经在mariadb端启用了复制,它在maxscale UI上被识别。
的数据
如果我关闭我的主服务器,其中一个从服务器将升级为新的主服务器(预期结果)。问题来了,当我把我原来的主人回来,我得到以下错误:
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
字符串
**奇怪的是,如果我把那个被提升为主人的奴隶转过来,再把他转回来,他又会成为奴隶,这一点都没有问题。
Docker编写文件:
#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;
型
附言:我试过检查主服务器上的服务器ID,他有3001,而从服务器有3002和3003。如果你能帮忙的话,我将不胜感激。
1条答案
按热度按时间sqxo8psd1#
要使MaxScale中的故障切换正常工作,您应该使用GTID复制:
后端必须全部使用基于GTID的复制,并且域ID在切换或故障转移期间不应更改。主服务器和副本服务器必须具有性能良好的GTID,并且副本服务器上没有额外的事件。