mariadb 原来的主人未能加入maxscale作为奴隶一旦他是备份

wwtsj6pe  于 2023-08-05  发布在  其他
关注(0)|答案(1)|浏览(138)

在我的项目中,我使用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。如果你能帮忙的话,我将不胜感激。

sqxo8psd

sqxo8psd1#

要使MaxScale中的故障切换正常工作,您应该使用GTID复制:
后端必须全部使用基于GTID的复制,并且域ID在切换或故障转移期间不应更改。主服务器和副本服务器必须具有性能良好的GTID,并且副本服务器上没有额外的事件。

相关问题