前段时间,刚好学了这个mysql主从搭建,为了记录自己学习成果,特以笔记形式记录下来。如有不足之处,谢谢指正!
一、mysql主从搭建,1主2从
docker-compose.yaml
version: '3'services: mysql-master: container_name: mysql-master hostname: mysql-master image: "mysql:5.7" ports: - "33060:3306" networks: ## 引入外部预先定义的网段 srm-mysql: ipv4_address: 173.200.6.110 #设置ip地址 volumes: - /home/order/mysql/master/etc:/etc/mysql - /home/order/mysql/master/var/lib:/var/lib/mysql - /home/order/mysql/master/my.cnf:/etc/my.cnf - ./master/init_db/:/docker-entrypoint-initdb.d/ environment: - TZ=Asia/Shanghai - MYSQL_ROOT_PASSWORD=123456 command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci restart: always mysql-slave-1: container_name: mysql-slave-1 hostname: mysql-slave-1 image: "mysql:5.7" ports: - "33070:3306" networks: ## 引入外部预先定义的网段 srm-mysql: ipv4_address: 173.200.6.120 #设置ip地址 volumes: - /home/order/mysql/slave1/etc:/etc/mysql - /home/order/mysql/slave1/var/lib:/var/lib/mysql - /home/order/mysql/slave1/my.cnf:/etc/my.cnf - ./slave-1/init_db/:/docker-entrypoint-initdb.d/ environment: - TZ=Asia/Shanghai - MYSQL_ROOT_PASSWORD=123456 command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci restart: always mysql-slave-2: container_name: mysql-slave-2 hostname: mysql-slave-2 image: "mysql:5.7" ports: - "33080:3306" networks: ## 引入外部预先定义的网段 srm-mysql: ipv4_address: 173.200.6.130 #设置ip地址 volumes: - /home/order/mysql/slave2/etc:/etc/mysql - /home/order/mysql/slave2/var/lib:/var/lib/mysql - /home/order/mysql/slave2/my.cnf:/etc/my.cnf - ./slave-2/init_db/:/docker-entrypoint-initdb.d/ environment: - TZ=Asia/Shanghai - MYSQL_ROOT_PASSWORD=123456 command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci restart: always# 设置网络模块networks: # 自定义网络 srm-mysql: driver: bridge ipam: #定义网段 config: - subnet: "173.200.6.0/24"
version: '3'
services:
mysql-master:
container_name: mysql-master
hostname: mysql-master
image: "mysql:5.7"
ports:
- "33060:3306"
networks: ## 引入外部预先定义的网段
srm-mysql:
ipv4_address: 173.200.6.110 #设置ip地址
volumes:
- /home/order/mysql/master/etc:/etc/mysql
- /home/order/mysql/master/var/lib:/var/lib/mysql
- /home/order/mysql/master/my.cnf:/etc/my.cnf
- ./master/init_db/:/docker-entrypoint-initdb.d/
environment:
- TZ=Asia/Shanghai
- MYSQL_ROOT_PASSWORD=123456
command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
restart: always
mysql-slave-1:
container_name: mysql-slave-1
hostname: mysql-slave-1
image: "mysql:5.7"
ports:
- "33070:3306"
networks: ## 引入外部预先定义的网段
srm-mysql:
ipv4_address: 173.200.6.120 #设置ip地址
volumes:
- /home/order/mysql/slave1/etc:/etc/mysql
- /home/order/mysql/slave1/var/lib:/var/lib/mysql
- /home/order/mysql/slave1/my.cnf:/etc/my.cnf
- ./slave-1/init_db/:/docker-entrypoint-initdb.d/
environment:
- TZ=Asia/Shanghai
- MYSQL_ROOT_PASSWORD=123456
command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
restart: always
mysql-slave-2:
container_name: mysql-slave-2
hostname: mysql-slave-2
image: "mysql:5.7"
ports:
- "33080:3306"
networks: ## 引入外部预先定义的网段
srm-mysql:
ipv4_address: 173.200.6.130 #设置ip地址
volumes:
- /home/order/mysql/slave2/etc:/etc/mysql
- /home/order/mysql/slave2/var/lib:/var/lib/mysql
- /home/order/mysql/slave2/my.cnf:/etc/my.cnf
- ./slave-2/init_db/:/docker-entrypoint-initdb.d/
environment:
- TZ=Asia/Shanghai
- MYSQL_ROOT_PASSWORD=123456
command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
restart: always
# 设置网络模块
networks:
# 自定义网络
srm-mysql:
driver: bridge
ipam: #定义网段
config:
- subnet: "173.200.6.0/24"
主节点的my.cnf
#my.conf[mysqld]server_id = 1# 开启GTID模式gtid-mode = ONenforce-gtid-consistency = 1# 复制过滤:也就是指定哪个数据库不用同步(mysql库一般不同步)binlog-ignore-db = mysql# 开启二进制日志功能,可以随便取,最好有含义(关键就是这里了)log-bin = mysql-bin# 为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存binlog_cache_size = 1M# 主从复制的格式(mixed,statement,row,默认格式是statement)binlog_format = mixed# 二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。expire_logs_days = 7# 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。# 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致slave_skip_errors = 1062# log_slave_updates表示slave将复制事件写进自己的二进制日志log_slave_updates = ON
#my.conf
[mysqld]
server_id = 1
# 开启GTID模式
gtid-mode = ON
enforce-gtid-consistency = 1
# 复制过滤:也就是指定哪个数据库不用同步(mysql库一般不同步)
binlog-ignore-db = mysql
# 开启二进制日志功能,可以随便取,最好有含义(关键就是这里了)
log-bin = mysql-bin
# 为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存
binlog_cache_size = 1M
# 主从复制的格式(mixed,statement,row,默认格式是statement)
binlog_format = mixed
# 二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。
expire_logs_days = 7
# 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。
# 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
slave_skip_errors = 1062
# log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates = ON
从节点的my.cnf(主要是修改一下server_id)
#my.conf[mysqld]server_id = 2gtid-mode = ONenforce-gtid-consistency = 1binlog-ignore-db = mysqllog-bin = mysql-slave1-binbinlog_cache_size = 1Mbinlog_format = mixedexpire_logs_days = 7slave_skip_errors = 1062relay_log = mysql-relay-binlog_slave_updates = ONread_only = 1
#my.conf
[mysqld]
server_id = 2
gtid-mode = ON
enforce-gtid-consistency = 1
binlog-ignore-db = mysql
log-bin = mysql-slave1-bin
binlog_cache_size = 1M
binlog_format = mixed
expire_logs_days = 7
slave_skip_errors = 1062
relay_log = mysql-relay-bin
log_slave_updates = ON
read_only = 1
主节点的执行的sql
create user 'repl'@'%' identified by 'repl';grant replication client,replication slave on *.* to 'repl'@'%';
create user 'repl'@'%' identified by 'repl';
grant replication client,replication slave on *.* to 'repl'@'%';
从节点执行的sql
reset master;select sleep(10); ## 第一次出现了mysql-slave先初始化好的情况CHANGE MASTER TO MASTER_HOST='mysql-master',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;start slave;
reset master;
select sleep(10); ## 第一次出现了mysql-slave先初始化好的情况
CHANGE MASTER TO MASTER_HOST='mysql-master',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;
start slave;
二、MySQL mha高可用构建方法
version: '3.6' # 确定docker-composer文件的版本services: # 代表就是一组服务 - 简单来说一组容器 # server mysql_mha_master_176_3: # 这个表示服务的名称,课自定义; 注意不是容器名称 build: # 根据 Dockerfile 编译镜像 context: /www/wwwroot/2007_SRM/docker/images/mysql_mha/node # 包含 Dockerfile 的目录 image: mysql_mha_node:latest # build 后的镜像 tag container_name: mysql_mha_master_176_3 # 这是容器的名称 expose: # 通过漂移 VIP 链接 mysql - 3306 privileged: true volumes: - /etc/localtime:/etc/localtime - "/www/wwwroot/2007_SRM/mysql_mha/mha_share/:/root/mha_share/" - "/www/wwwroot/2007_SRM/mysql_mha/master_3/lib/:/var/lib/mysql/" - "/www/wwwroot/2007_SRM/mysql_mha/master_3/conf/:/etc/mysql/conf.d/" env_file: # 共用的环境变量文件 - /www/wwwroot/2007_SRM/mysql_mha/parameters.env environment: - CONTAINER_NAME=master_3 hostname: master_3 networks: ## 引入外部预先定义的网段 mysql_mha: ipv4_address: 176.200.7.3 #设置ip地址 mysql_mha_slave_176_2: # 这个表示服务的名称,课自定义; 注意不是容器名称 build: # 根据 Dockerfile 编译镜像 context: /www/wwwroot/2007_SRM/docker/images/mysql_mha/node # 包含 Dockerfile 的目录 image: mysql_mha_node:latest # build 后的镜像 tag container_name: mysql_mha_slave_176_2 # 这是容器的名称 expose: # 通过漂移 VIP 链接 mysql - 3306 privileged: true volumes: - /etc/localtime:/etc/localtime - "/www/wwwroot/2007_SRM/mysql_mha/mha_share/:/root/mha_share/" - "/www/wwwroot/2007_SRM/mysql_mha/slave_2/lib/:/var/lib/mysql/" - "/www/wwwroot/2007_SRM/mysql_mha/slave_2/conf/:/etc/mysql/conf.d/" env_file: # 共用的环境变量文件 - /www/wwwroot/2007_SRM/mysql_mha/parameters.env environment: - CONTAINER_NAME=slave_2 hostname: slave_3 networks: ## 引入外部预先定义的网段 mysql_mha: ipv4_address: 176.200.7.2 #设置ip地址 mysql_mha_slave_176_4: # 这个表示服务的名称,课自定义; 注意不是容器名称 build: # 根据 Dockerfile 编译镜像 context: /www/wwwroot/2007_SRM/docker/images/mysql_mha/node # 包含 Dockerfile 的目录 image: mysql_mha_node:latest # build 后的镜像 tag container_name: mysql_mha_slave_176_4 # 这是容器的名称 expose: # 通过漂移 VIP 链接 mysql - 3306 privileged: true volumes: - /etc/localtime:/etc/localtime - "/www/wwwroot/2007_SRM/mysql_mha/mha_share/:/root/mha_share/" - "/www/wwwroot/2007_SRM/mysql_mha/slave_4/lib/:/var/lib/mysql/" - "/www/wwwroot/2007_SRM/mysql_mha/slave_4/conf/:/etc/mysql/conf.d/" env_file: # 共用的环境变量文件 - /www/wwwroot/2007_SRM/mysql_mha/parameters.env environment: - CONTAINER_NAME=slave_4 hostname: slave_4 networks: ## 引入外部预先定义的网段 mysql_mha: ipv4_address: 176.200.7.4 #设置ip地址 mysql_mha_slave_176_5: # 这个表示服务的名称,课自定义; 注意不是容器名称 build: # 根据 Dockerfile 编译镜像 context: /www/wwwroot/2007_SRM/docker/images/mysql_mha/node # 包含 Dockerfile 的目录 image: mysql_mha_node:latest # build 后的镜像 tag container_name: mysql_mha_slave_176_5 # 这是容器的名称 expose: # 通过漂移 VIP 链接 mysql - 3306 privileged: true volumes: - /etc/localtime:/etc/localtime - "/www/wwwroot/2007_SRM/mysql_mha/mha_share/:/root/mha_share/" - "/www/wwwroot/2007_SRM/mysql_mha/slave_5/lib/:/var/lib/mysql/" - "/www/wwwroot/2007_SRM/mysql_mha/slave_5/conf/:/etc/mysql/conf.d/" env_file: # 共用的环境变量文件 - /www/wwwroot/2007_SRM/mysql_mha/parameters.env environment: - CONTAINER_NAME=slave_5 hostname: slave_5 networks: ## 引入外部预先定义的网段 mysql_mha: ipv4_address: 176.200.7.5 #设置ip地址 mysql_mha_manager_176_100: # 这个表示服务的名称,课自定义; 注意不是容器名称 build: # 根据 Dockerfile 编译镜像 context: /www/wwwroot/2007_SRM/docker/images/mysql_mha/manager # 包含 Dockerfile 的目录 image: mysql_mha_manager:latest # build 后的镜像 tag container_name: mysql_mha_manager_176_100 # 这是容器的名称 depends_on: - mysql_mha_master_176_3 - mysql_mha_slave_176_2 - mysql_mha_slave_176_4 - mysql_mha_slave_176_5 privileged: true volumes: - /etc/localtime:/etc/localtime - "/www/wwwroot/2007_SRM/mysql_mha/mha_share/:/root/mha_share/" - "/www/wwwroot/2007_SRM/mysql_mha/manager_100/etc:/etc/mha" env_file: # 共用的环境变量文件 - /www/wwwroot/2007_SRM/mysql_mha/parameters.env environment: - CONTAINER_NAME=manager_100 hostname: manager_100 networks: ## 引入外部预先定义的网段 mysql_mha: ipv4_address: 176.200.7.100 #设置ip地址 entrypoint: "tail -f /dev/null"networks: mysql_mha: driver: bridge ipam: #定义网段 config: - subnet: "176.200.7.0/24"
END