docker-compose下mariadb主从同步配置

目录

1、docker、docker-compose安装

2、mariaDB配置文件

3、docker-compose文件编写

4、主从同步配置

5、测试

---

1、docker、docker-compose安装

参考菜鸟教程，这里不做介绍

2、mariaDB配置文件

主节点：mariadb-master.cnf

[client]

port = 3306

socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]

socket = /var/run/mysqld/mysqld.sock

nice = 0

[mysqld]

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

port = 3306

basedir = /usr

datadir = /var/lib/mysql

tmpdir = /tmp

lc_messages_dir = /usr/share/mysql

lc_messages = en_US

skip-external-locking

default-time-zone = +00:00

max_connections = 100

connect_timeout = 5

wait_timeout = 600

max_allowed_packet = 16M

thread_cache_size = 128

sort_buffer_size = 4M

bulk_insert_buffer_size = 16M

tmp_table_size = 32M

max_heap_table_size = 32M

myisam_recover_options = BACKUP

key_buffer_size = 128M

table_open_cache = 400

myisam_sort_buffer_size = 512M

concurrent_insert = 2

read_buffer_size = 2M

read_rnd_buffer_size = 1M

query_cache_limit = 128K

query_cache_size = 64M

slow_query_log_file = /var/log/mysql/mariadb-slow.log

long_query_time = 10

# 主节点
server-id = 1

# 无需开启二进制日志文件的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
log_bin = /var/log/mysql/mariadb-master-bin

log_bin_index = /var/log/mysql/mariadb-bin.index
expire_logs_days = 10

max_binlog_size = 100M

default_storage_engine = InnoDB

innodb_buffer_pool_size = 256M

innodb_log_buffer_size = 8M

innodb_file_per_table = 1

innodb_open_files = 400

innodb_io_capacity = 400

innodb_flush_method = O_DIRECT

[galera]
binlog_format=mixed

# 当设置为0，该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
# 当设置为1，该模式是最安全的， 但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。。
# 当设置为2，该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。
innodb_flush_log_at_trx_commit=1

[mysqldump]

quick

quote-names

max_allowed_packet = 16M

[isamchk]

key_buffer = 16M

从节点：mariadb-slave.cnf

[client]

port = 3306

socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]

socket = /var/run/mysqld/mysqld.sock

nice = 0

[mysqld]

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

port = 3306

basedir = /usr

datadir = /var/lib/mysql

tmpdir = /tmp

lc_messages_dir = /usr/share/mysql

lc_messages = en_US

skip-external-locking

default-time-zone = +00:00

max_connections = 100

connect_timeout = 5

wait_timeout = 600

max_allowed_packet = 16M

thread_cache_size = 128

sort_buffer_size = 4M

bulk_insert_buffer_size = 16M

tmp_table_size = 32M

max_heap_table_size = 32M

myisam_recover_options = BACKUP

key_buffer_size = 128M

table_open_cache = 400

myisam_sort_buffer_size = 512M

concurrent_insert = 2

read_buffer_size = 2M

read_rnd_buffer_size = 1M

query_cache_limit = 128K

query_cache_size = 64M

slow_query_log_file = /var/log/mysql/mariadb-slow.log

long_query_time = 10

# 从节点

server-id = 11

# 无需开启二进制日志文件的数据库

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

# 指定需要开启二进制日志文件的数据库(备用机开启)

binlog-do-db=nest-interface

log_bin = /var/log/mysql/mariadb-slave-bin

log_bin_index = /var/log/mysql/mariadb-bin.index

expire_logs_days = 10

max_binlog_size = 100M

default_storage_engine = InnoDB

innodb_buffer_pool_size = 256M

innodb_log_buffer_size = 8M

innodb_file_per_table = 1

innodb_open_files = 400

innodb_io_capacity = 400

innodb_flush_method = O_DIRECT

[galera]

binlog_format=mixed

# 当设置为0，该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。

# 当设置为1，该模式是最安全的， 但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。。

# 当设置为2，该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。

innodb_flush_log_at_trx_commit=1

[mysqldump]

quick

quote-names

max_allowed_packet = 16M

[mysql]

[isamchk]

key_buffer = 16M

注意一下标注位置的配置

3、docker-compose文件编写

主节点3306端口，从节点3307端口

version: "3"
services: 
  mariaDB-master:
    image: mariadb:10.4.13
    container_name: mariadb-master
    # restart: always
    environment:
      - MYSQL_ROOT_PASSWORD=123456.!
    ports:
      - 3306:3306
    volumes:
      # 容器与宿主机时间同步
      - /etc/localtime:/etc/localtime
      - /data/docker/maria-data-master:/var/lib/mysql
      - ./mariadb-master.cnf:/etc/mysql/my.cnf # 上面主节点配置文件
  mariaDB-slave: 
    image: mariadb:10.4.13
    container_name: mariadb-slave
    # restart: always
    environment:
      - MYSQL_ROOT_PASSWORD=123456.!
    ports:
      - 3307:3306
    volumes:
      # 容器与宿主机时间同步
      - /etc/localtime:/etc/localtime
      - /data/docker/maria-data-slave:/var/lib/mysql
      - ./mariadb-slave.cnf:/etc/mysql/my.cnf # 上面从节点配置文件
    links: 
      - mariaDB-master # 这里需要链接master节点容器

配置文件准备好后，docker-compose up -d启动容器，通过docker ps命令查看启动情况

4、主从同步配置

• 配置远程登录账号,主从机器都需要执行一遍

docker exec mariadb-master -it /bin/bash # 进入主节点容器

docker exec mariadb-slave -it /bin/bash # 进入从节点容器

进入容器之后执行：mysql -u root -p123456.!    # 这里的密码要与上面docker-compose中配置的一致

进入mysql命令行后执行：grant replication slave on *.* to 'backup'@'%'identified by '123456.!';flush privileges;

• 查看主节点的binlog文件，binlog位置等信息

show master status;

• 从节点根据主节点的binlog文件，binlog位置信息，执行CHANGE MASTER TO操作，以确定需要同步的主机IP，用户名，密码，binlog文件，binlog位置等信息.

change master to MASTER_HOST='mariadb-master',master_user='backup',master_password='123456.!',MASTER_PORT=3306,master_log_file='mariadb-bin.000003',MASTER_LOG_POS=3340;

• 从节点启动主备，并查看主备状态

start slave;

show slave status\G;

5、测试

主节点新建数据库nest-interface，可以看到从节点也同步创建了nest-interface，大功告成。