Кластер MySQL InnoDB для высокой доступности в Docker Compose ⇐ MySql

[Anonymous]

Я пытаюсь настроить кластер MySQL InnoDB для обеспечения высокой доступности в Docker Compose. Ниже приведены файлы.
docker compose

Код: Выделить всё

version: '3'
services:
mysql-server-1:
container_name: mysql-server-1
env_file:
- mysql-server.env
image: mysql/mysql-server:8.0.13
healthcheck:
test: [ "CMD", "mysqladmin", "ping", "-h", "127.0.0.1", "-uroot", "-p${MYSQL_ROOT_PASSWORD}" ]
interval: 10s
timeout: 5s
retries: 20
start_period: 40s
ports:
- "3301:3306"
command: [ "mysqld",
"--server_id=1",
"--binlog_checksum=NONE",
"--gtid_mode=ON",
"--enforce_gtid_consistency=ON",
"--log_bin",
"--log_slave_updates=ON",
"--master_info_repository=TABLE",
"--relay_log_info_repository=TABLE",
"--transaction_write_set_extraction=XXHASH64",
"--user=mysql",
"--skip-host-cache",
"--skip-name-resolve",
"--default_authentication_plugin=mysql_native_password"
]
mysql-server-2:
container_name: mysql-server-2
healthcheck:
test: [ "CMD", "mysqladmin", "ping", "-h", "127.0.0.1", "-uroot", "-p${MYSQL_ROOT_PASSWORD}" ]
interval: 10s
timeout: 5s
retries: 20
start_period: 40s
env_file:
- mysql-server.env
image: mysql/mysql-server:8.0.13
command: [ "mysqld",
"--server_id=2",
"--binlog_checksum=NONE",
"--gtid_mode=ON",
"--enforce_gtid_consistency=ON",
"--log_bin",
"--log_slave_updates=ON",
"--master_info_repository=TABLE",
"--relay_log_info_repository=TABLE",
"--transaction_write_set_extraction=XXHASH64",
"--user=mysql",
"--skip-host-cache",
"--skip-name-resolve",
"--default_authentication_plugin=mysql_native_password" ]
ports:
- "3302:3306"
mysql-server-3:
container_name: mysql-server-3
healthcheck:
test: [ "CMD", "mysqladmin", "ping", "-h", "127.0.0.1", "-uroot", "-p${MYSQL_ROOT_PASSWORD}" ]
interval: 10s
timeout: 5s
retries: 20
start_period: 40s
env_file:
- mysql-server.env
image: mysql/mysql-server:8.0.13
command: [ "mysqld",
"--server_id=3",
"--binlog_checksum=NONE",
"--gtid_mode=ON",
"--enforce_gtid_consistency=ON",
"--log_bin",
"--log_slave_updates=ON",
"--master_info_repository=TABLE",
"--relay_log_info_repository=TABLE",
"--transaction_write_set_extraction=XXHASH64",
"--user=mysql",
"--skip-host-cache",
"--skip-name-resolve",
"--default_authentication_plugin=mysql_native_password"  ]
ports:
- "3303:3306"
mysql-shell:
container_name: mysql-shell
env_file:
- mysql-shell.env
image: bkandasa/mysql-shell-batch
volumes:
- ./scripts/:/scripts/
depends_on:
mysql-server-1:
condition: service_healthy
mysql-server-2:
condition: service_healthy
mysql-server-3:
condition: service_healthy
mysql-router:
container_name: mysql-router
env_file:
- mysql-router.env
image: mysql/mysql-router:8.0.13
ports:
- "6446:6446"
depends_on:
mysql-server-1:
condition: service_healthy
mysql-server-2:
condition: service_healthy
mysql-server-3:
condition: service_healthy
mysql-shell:
condition: service_started
restart: on-failure
wordpress:
container_name: wordpress
env_file:
- wordpress.env
image: wordpress:latest
ports:
- "8080:80"
depends_on:
- mys

/scripts/db.sql

Код: Выделить всё

CREATE DATABASE if not exists fossasiadb;
CREATE USER 'fossasia'@'%' IDENTIFIED BY 'fossasia';
GRANT ALL PRIVILEGES ON fossasiadb.* TO 'fossasia'@'%';

/scripts/setupCluster.js

Код: Выделить всё

var dbPass = "mysql"
var clusterName = "devCluster"

try {
println('Setting up InnoDB cluster...');
shell.connect('root@mysql-server-1:3306', dbPass)
var cluster = dba.createCluster(clusterName);
println('Adding mysql-server-2 to the cluster.');
cluster.addInstance({user: "root", host: "mysql-server-2", password: dbPass})
println('Adding mysql-server-3 to the cluster.');
cluster.addInstance({user: "root", host: "mysql-server-3", password: dbPass})
println('.Instances successfully added to the cluster.');
println('InnoDB cluster deployed successfully.');
println("Cluster status:", cluster.status())
cluster = dba.getCluster();
println("Cluster exists. Skipping bootstrap.", cluster);
} catch (e) {
println('The InnoDB cluster could not be created.Error: ', e.message);
}

mysql-server.env

Код: Выделить всё

MYSQL_ROOT_PASSWORD=mysql
MYSQL_ROOT_HOST=%

mysql-shell.env

Код: Выделить всё

MYSQL_USER=root
MYSQL_HOST=mysql-server-1
MYSQL_PORT=3306
MYSQL_PASSWORD=mysql
MYSQLSH_SCRIPT=/scripts/setupCluster.js
MYSQL_SCRIPT=/scripts/db.sql

mysql-router.env

Код: Выделить всё

MYSQL_USER=root
MYSQL_HOST=mysql-server-1
MYSQL_PORT=3306
MYSQL_PASSWORD=mysql
MYSQL_INNODB_NUM_MEMBERS=3

wordpress.env

Код: Выделить всё

WORDPRESS_DB_HOST: mysql-router:6446
WORDPRESS_DB_USER: fossasia
WORDPRESS_DB_PASSWORD: fossasia
WORDPRESS_DB_NAME: fossasiadb

команда для запуска композитора

Код: Выделить всё

docker-compose up --build --force-recreate

Я могу подключиться к mysql-маршрутизатору на 6446, и данные реплицируются на 3301, но не на других службах MySQL, и если mysql-server-1 не работает, маршрутизатор не выбирает другой основной сервер.

Код: Выделить всё

docker exec mysql-server-1 mysql -u fossasia -pfossasia fossasiadb -e "CREATE TABLE IF NOT EXISTS users ( user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL, email VARCHAR(100) NOT NULL ); INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');"
docker exec mysql-server-1 mysql -u fossasia -pfossasia fossasiadb -e "select * from fossasiadb.users"
docker exec mysql-server-2 mysql -u fossasia -pfossasia fossasiadb -e "select * from fossasiadb.users"
docker exec mysql-server-3 mysql -u fossasia -pfossasia fossasiadb -e "select * from fossasiadb.users"

Я проверил ChatGPT и Google, ни одно решение не работает.

Что я делаю не так?

Заранее спасибо

Подробнее здесь: https://stackoverflow.com/questions/798 ... er-compose