Docker MariaDB配置主从复制

编写主节点配置文件master.cnf:

[client]

# 默认字符集

default-character-set=utf8mb4

[mysqld]

# 字符集

character-set-server=utf8mb4

collation-server=utf8mb4_general_ci

# 允许最大链接数

max_connections=4096

# 服务器引擎

default-storage-engine=InnoDB

# 表名小写

# lower_case_table_names=1

# 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存

binlog_cache_size=1M

# binlog 格式 row statement mixed

binlog_format=mixed

# 忽略的数据库binlog

binlog-ignore-db=mysql,information_schema

# 到期自动删除

expire_logs_day=7

# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。

# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致

slave_skip_errors=1062

# 设置server_id，一般设置为IP,注意要唯一

server-id=1

# bin-log

log_bin=mariadb-bin

从节点配置文件slave.cnf:

[client]

# 默认字符集

default-character-set=utf8mb4

[mysqld]

# 字符集

character-set-server=utf8mb4

collation-server=utf8mb4_general_ci

# 服务器引擎

default-storage-engine=InnoDB

# 表名小写

# lower_case_table_names=1

# 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存

binlog_cache_size=1M

# binlog 格式 row statement mixed

binlog_format=mixed

# 忽略的数据库binlog

binlog-ignore-db=mysql,information_schema

# 到期自动删除

expire_logs_day=7

# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。

# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致

slave_skip_errors=1062

# 设置server_id，一般设置为IP,注意要唯一

server-id=10

# bin-log

log_bin=slave-mariadb-bin

# 表示slave将复制事件写进自己的二进制日志

log_slave_updates=1

# 记录同步信息

relay-log=slave-relay-bin

# 防止改变数据(除了特殊的线程)

read_only=1

# 同步刷新binlog，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，

# 而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。

# sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘

# 1 最安全，但是最慢。 0 最快，但是风险最大

sync_binlog=0

docker-compose文件docker-compose.yml:

version: "3"

services:

  mariadb-master:

    image: mariadb:10.7.1

    container_name: mariadb-master

    restart: always

    ports:

      - "3307:3306"

    environment:

      - MARIADB_ROOT_PASSWORD=123

    volumes:

      - ./master/data:/var/lib/mysql:cached

      - ./master.cnf:/etc/mysql/my.cnf:cached

    security_opt:

      - seccomp:unconfined

    logging:

      driver: "json-file"

      options:

        max-size: "500m"

    networks:

      - dev_network

  mariadb-slave:

    image: mariadb:10.7.1

    container_name: mariadb-slave

    restart: always

    ports:

      - "3308:3306"

    environment:

      - MARIADB_ROOT_PASSWORD=123

    volumes:

      - ./slave/data:/var/lib/mysql:cached

      - ./slave.cnf:/etc/mysql/my.cnf:cached

    security_opt:

      - seccomp:unconfined

    logging:

      driver: "json-file"

      options:

        max-size: "500m"

    networks:

      - dev_network

networks:

  dev_network:

    external:

      name: dev_network

启动:

$ sudo docker-compose up -d

Creating mariadb-master ... done

Creating mariadb-slave  ... done

查看列表信息:

$ sudo docker ps

CONTAINER ID   IMAGE            COMMAND                  CREATED          STATUS         PORTS                                       NAMES

452272cd0ee8   mariadb:10.7.1   "docker-entrypoint.s…"   10 seconds ago   Up 9 seconds   0.0.0.0:3308->3306/tcp, :::3308->3306/tcp   mariadb-slave

750c98f28b24   mariadb:10.7.1   "docker-entrypoint.s…"   10 seconds ago   Up 9 seconds   0.0.0.0:3307->3306/tcp, :::3307->3306/tcp   mariadb-master

查看主节点IP:

$ sudo docker inspect 750 | grep IPAddress

            "SecondaryIPAddresses": null,

            "IPAddress": "",

                    "IPAddress": "172.18.0.2",

同理可得从节点IP为172.18.0.2

连接主节点:

$ mysql -u root -h 172.18.0.2 -p

Enter password:

执行:

mysql> create user 'slave'@'%' identified by '123';

Query OK, 0 rows affected (0.04 sec)

mysql> grant replication slave on *.* to 'slave'@'%';

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> show master status;

+--------------------+----------+--------------+--------------------------+

| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB         |

+--------------------+----------+--------------+--------------------------+

| mariadb-bin.000003 |      771 |              | mysql,information_schema |

+--------------------+----------+--------------+--------------------------+

连接从节点执行:

mysql> change master to master_host='mariadb-master',master_port=3306,master_user='slave',master_password='123',master_log_file='mariadb-bin.000003',master_log_pos=771;

Query OK, 0 rows affected (0.02 sec)

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

mysql> show slave status \G;

*************************** 1. row ***************************

                Slave_IO_State: Waiting for master to send event

                   Master_Host: mariadb-master

                   Master_User: slave

                   Master_Port: 3306

                 Connect_Retry: 60

               Master_Log_File: mariadb-bin.000003

           Read_Master_Log_Pos: 771

                Relay_Log_File: slave-relay-bin.000002

                 Relay_Log_Pos: 557

         Relay_Master_Log_File: mariadb-bin.000003

              Slave_IO_Running: Yes

             Slave_SQL_Running: Yes

完成

Docker MariaDB配置主从复制的相关教程结束。