docker 部署MariaDb、配置主从
主要记录一下操作过程，方便以后查阅
部署MariaDb
服务器Debian 12
安装路径 /opt/mariadb
主要流程就是准备好文件，然后docker run启动就OK了
准备
创建以下目录映射进去容器，分别用来持久化数据、日志、配置和初始化sql。
mkdir -p /opt/mariadb/{data,logs,conf.d,docker-entrypoint-initdb.d}环境变量
docker容器启动时会读取环境变量来进行一些操作。
相关文档 https://mariadb.com/kb/en/mariadb-server-docker-official-image-environment-variables
启动镜像时，您可以通过在docker run命令行上传递一个或多个环境变量来调整MariaDB Server实例的初始化。请注意，如果您使用已经包含数据库的数据目录启动容器，则以下所有变量（除MARIADB_ADB_UPGRADE之外）都将无效：任何预先存在的数据库将始终在容器启动时保持不变。
密码项是必须的，也可以随机生成密码，此处指定密码。为了防止看到明文密码，所以使用env文件vi /opt/mariadb/mariadb.env写入
MARIADB_ROOT_PASSWORD=some-password-string其他环境变量:比如直接创建一个数据库和所属用户就可以加上下面这个
MARIADB_DATABASE=my_db
MARIADB_USER=my_db_user
MARIADB_PASSWORD=my_db_user_password注意：root用户是默认创建的，这里不要再次创建root用户
配置文件
在/opt/mariadb/conf.d/目录下可以放一些cnf配置文件
此处放一个指定字符集的配置
cat > /opt/mariadb/conf.d/my.cnf <<EOF
[mariadb]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
[client]
default-character-set=utf8mb4
EOF初始化sql
在/opt/mariadb/docker-entrypoint-initdb.d/目录下可以放入一些sql文件，在容器初始化数据的时候进行操作
示例：比如创建一个init_db库并创建一个用户表，向里面写入3条数据
cat > /opt/mariadb/docker-entrypoint-initdb.d/init.sql <<EOF
CREATE DATABASE IF NOT EXISTS init_db;
USE init_db;
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
is_active BOOLEAN DEFAULT TRUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO users (username, email, password_hash) VALUES
('john_doe', 'john@example.com', '$2a$10$xJwL5v5zPqZrZ2NQYbWjU.9vTZ0jQ7Xc8uO1dKJmYnVJf6sLkHbW6'),
('jane_smith', 'jane@example.com', '$2a$10$yH8eL3vRnZp7MbVZ9sTQe.5rQwXc8uO1dKJmYnVJf6sLkHbW6xJw'),
('bob_johnson', 'bob@example.com', '$2a$10$zK9m4v2sRpXqWrNtYbWjU.7sTZ0jQ7Xc8uO1dKJmYnVJf6sLkHbW6');
EOF部署
docker 直接run就启动起来了
docker run -d \
--name mariadb \
-v /opt/mariadb/data:/var/lib/mysql \
-v /opt/mariadb/conf.d:/etc/mysql/conf.d \
-v /opt/mariadb/logs:/var/log/mysql \
-v /opt/mariadb/docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d \
--env-file /opt/mariadb/mariadb.env \
-p 3306:3306 \
--restart=unless-stopped \
mariadb:11.7.2一般此时就部署成功可以直接链接了
如果按上面举例放入了初始化sql，就能够看到数据库和表数据：
[root 17:01:24 /opt/mariadb]$ docker exec -it mariadb mariadb -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 11.7.2-MariaDB-ubu2404 mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| init_db |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.002 sec)
MariaDB [(none)]> use init_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [init_db]> show tables;
+-------------------+
| Tables_in_init_db |
+-------------------+
| users |
+-------------------+
1 row in set (0.001 sec)
MariaDB [init_db]> select * from users;
+----+-------------+------------------+------------------------------------+---------------------+-----------+
| id | username | email | password_hash | created_at | is_active |
+----+-------------+------------------+------------------------------------+---------------------+-----------+
| 1 | john_doe | john@example.com | a0.9vTZ0jQ7Xc8uO1dKJmYnVJf6sLkHbW6 | 2025-06-08 08:58:39 | 1 |
| 2 | jane_smith | jane@example.com | a0.5rQwXc8uO1dKJmYnVJf6sLkHbW6xJw | 2025-06-08 08:58:39 | 1 |
| 3 | bob_johnson | bob@example.com | a0.7sTZ0jQ7Xc8uO1dKJmYnVJf6sLkHbW6 | 2025-06-08 08:58:39 | 1 |
+----+-------------+------------------+------------------------------------+---------------------+-----------+
3 rows in set (0.006 sec)
MariaDB [init_db]> 配置主从复制
流程:
1.增加配置->备份数据->创建账号。
2.还原数据->配置从库->部署从库。
配置项相关文档https://mariadb.com/kb/en/replication-and-binary-log-system-variables
主节点配置
增加配置
可以直接在原有的/opt/mariadb/conf.d/my.cnf上修改或者直接新增
此处新增master.cnf配置文件
cat > /opt/mariadb/conf.d/master.cnf <<EOF
[mariadb]
server_id=1 #服务ID 不能冲突
log-bin=mysql-bin #开启并设置log-bin文件名
binlog-format=row #行格式
gtid_strict_mode=1
EOF重启生效配置文件docker restart mariadb
备份
使用mariadb-backup命令把文件备份到容器内的full_backup目录
docker exec -it mariadb mariadb-backup --backup --target-dir=full_backup --user=root --password在输出的时候能看到MySQL binlog position: filename 'mysql-bin.000005', position '342', GTID of the last change '0-1-32'这样的信息
把容器内目录cp出来
docker cp mariadb:full_backup .
docker exec -it mariadb rm -rf full_backup #删除容器内的full_backup文件夹此时cat full_backup/mariadb_backup_binlog_info也能看到mysql-bin.000005 342 0-1-32这样的信息
这个信息要记录下来，后面开启复制的时候就从这个值开始。
创建账号
账号用来进行主从复制
docker exec -it mariadb mariadb -uroot -p 进入容器执行
CREATE USER 'replication_user'@'%' IDENTIFIED BY 'replication_user_password';
GRANT REPLICATION REPLICA ON *.* TO 'replication_user'@'%';
FLUSH PRIVILEGES;从节点
部署节点
我的从节点在另外一个服务器。如果你是在同一台服务器，下面命令行的目录和端口要更改防止冲突
数据还原
mkdir -p /opt/mariadb/{data,logs,conf.d} #创建目录
scp -r node-1:~/full_backup /opt/mariadb/full_backup #把备份目录copy过来
docker run --rm -v /opt/mariadb/full_backup:/mariadb_backup_files mariadb mariadb-backup --prepare #准备处理一下(必须)
docker run --rm -v /opt/mariadb/full_backup:/mariadb_backup_files -v /opt/mariadb/data:/var/lib/mysql mariadb mariadb-backup --copy-back --target-dir=/mariadb_backup_files #还原
docker run --rm -v /opt/mariadb/data:/var/lib/mysql mariadb chown -R mysql:mysql /var/lib/mysql/ #授权配置
默认配置
cat > /opt/mariadb/conf.d/my.cnf <<EOF
[mariadb]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
[client]
default-character-set=utf8mb4
EOF新增从节点配置
cat > /opt/mariadb/conf.d/replica.cnf <<EOF
[mariadb]
server_id=2 # 确保与主库不同且唯一
log-bin=mysql-bin
binlog-format=row
read_only=ON # 从库设为只读 对root无效
gtid_strict_mode=1
EOF部署
docker run -d \
--name mariadb \
-v /opt/mariadb/data:/var/lib/mysql \
-v /opt/mariadb/conf.d:/etc/mysql/conf.d \
-v /opt/mariadb/logs:/var/log/mysql \
-p 3306:3306 \
--restart=unless-stopped \
mariadb:11.7.2部署完成后从节点的状态就和备份时候的主节点一模一样了，用户、数据库都还原了
开启复制
部署完进去从节点设置master docker exec -it mariadb mariadb -uroot -p
方式1
使用二进制日志文件和位置坐标
CHANGE MASTER TO
MASTER_HOST='主节点IP',
MASTER_PORT=3306,
MASTER_USER='replication_user',
MASTER_PASSWORD='replication_user_password',
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=342;MASTER_LOG_FILE和MASTER_LOG_POS的值上面备份的时候有记录，MASTER_USER这个就是前面创建的replication_user账号
方式2
使用GTID
SET GLOBAL gtid_slave_pos="0-1-32";
CHANGE MASTER TO
MASTER_HOST='主节点IP',
MASTER_PORT=3306,
MASTER_USER='replication_user',
MASTER_PASSWORD='replication_user_password',
MASTER_USE_GTID=slave_pos;gtid_slave_pos的值上面备份的时候有记录，MASTER_USER这个就是前面创建的replication_user账号
如果过程中设置错了，或者需要重新设置执行reset replica all;清除设置
最后启动复制
START REPLICA;使用SHOW REPLICA STATUS \G;命令看到这两行就OK了
Slave_IO_Running: Yes
Slave_SQL_Running: Yes执行效果如下
MariaDB [(none)]> SET GLOBAL gtid_slave_pos="0-1-3";
Query OK, 0 rows affected (0.011 sec)
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='1xx.xx.xx.xx',
-> MASTER_PORT=3306,
-> MASTER_USER='replication_user',
-> MASTER_PASSWORD='replication_user_password',
-> MASTER_USE_GTID=replica_pos;
Query OK, 0 rows affected (0.007 sec)
MariaDB [(none)]> start replica;
Query OK, 0 rows affected (0.009 sec)
MariaDB [(none)]> show replica status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 1xx.xx.xx.xx
Master_User: replication_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 2106
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 1509
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 2106
Relay_Log_Space: 1819
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: Slave_Pos
Gtid_IO_Pos: 0-1-8
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: optimistic
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Slave_DDL_Groups: 2
Slave_Non_Transactional_Groups: 1
Slave_Transactional_Groups: 3
Replicate_Rewrite_DB:
1 row in set (0.003 sec)
ERROR: No query specified半同步复制
半同步复制：就是主节点插入的数据在确认有任一个子节点同步到数据之后，主节点再提交成功。
如果在指定时间后还没有子节点回复同步状态，就降级为异步同步(直接入库)
主节点配置
cat > /opt/mariadb/conf.d/semi_sync_master.cnf <<EOF
[mariadb]
rpl_semi_sync_master_enabled=1 #开启
rpl_semi_sync_master_wait_point=AFTER_SYNC #等子节点写入磁盘之后再写入磁盘
rpl_semi_sync_master_timeout=5000 #超时时间
EOF注意：如果没有配置子节点就直接开启半同步，因为获取不到同步状态，那就会每个写入操作的sql都要等待rpl_semi_sync_master_timeout设置的时间超时之后才能操作成功，生产环境注意。
重启生效docker restart mariadb
从节点配置
cat > /opt/mariadb/conf.d/semi_sync_replica.cnf <<EOF
[mariadb]
rpl_semi_sync_slave_enabled=1
EOF重启生效docker restart mariadb