目录
本文是通过docker-compose在linux配置mysql一主二从高可用，也可以通过该下面的脚本直接创建。前提条件是系统中需要安装docker和docker-compose。
配置配置信息
配置配置文件、数据和日志文件路径
配置master目录：
mkdir -p master/conf;
mkdir -p master/data;
mkdir -p master/log;
mkdir -p master/mysql-files;
配置slave1目录：
mkdir -p slave1/conf;
mkdir -p slave1/data;
mkdir -p slave1/log;
mkdir -p slave1/mysql-files;
配置slave2目录：
mkdir -p slave2/conf;
mkdir -p slave2/data;
mkdir -p slave2/log;
mkdir -p slave2/mysql-files;
mkdir -p /conf.d/stream
配置my.cnf
在master/conf/配置master的my.cnf：
[mysqld]
## 设置server_id，同一局域网中需要唯一
server_id=101
## 指定不需要同步的数据库名称
binlog-ignore-db=mysql
## 开启二进制日志功能，以备Slave作为其它数据库实例的Master时使用
log-bin=mall-mysql-master-bin
## 设置二进制日志使用内存大小（事务）
binlog_cache_size=1M
## 设置使用的二进制日志格式（mixed,statement,row）
binlog_format=mixed
## 二进制日志过期清理时间。默认值为0，表示不自动清理。
binlog_expire_logs_seconds=2592000
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
replica_skip_errors=1062
在slave1/conf/配置slave1的my.cnf：
[mysqld]
## 设置server_id，同一局域网中需要唯一
server_id=102
## 指定不需要同步的数据库名称
binlog-ignore-db=mysql
## 开启二进制日志功能，以备Slave作为其它数据库实例的Master时使用
log-bin=mall-mysql-slave1-bin
## 设置二进制日志使用内存大小（事务）
binlog_cache_size=1M
## 设置使用的二进制日志格式（mixed,statement,row）
binlog_format=mixed
## 二进制日志过期清理时间。默认值为0，表示不自动清理。
binlog_expire_logs_seconds=2592000
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
replica_skip_errors=1062
## relay_log配置中继日志
relay_log=mall-mysql-relay-bin
## log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
## slave设置为只读（具有super权限的用户除外）
read_only=1
在slave2/data/配置slave2的my.cnf：
[mysqld]
## 设置server_id，同一局域网中需要唯一
server_id=103
## 指定不需要同步的数据库名称
binlog-ignore-db=mysql
## 开启二进制日志功能，以备Slave作为其它数据库实例的Master时使用
log-bin=mall-mysql-slave2-bin
## 设置二进制日志使用内存大小（事务）
binlog_cache_size=1M
## 设置使用的二进制日志格式（mixed,statement,row）
binlog_format=mixed
## 二进制日志过期清理时间。默认值为0，表示不自动清理。
binlog_expire_logs_seconds=2592000
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
replica_skip_errors=1062
## relay_log配置中继日志
relay_log=mall-mysql-relay-bin
## log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
## slave设置为只读（具有super权限的用户除外）
read_only=1
配置nginx.conf
user nginx;
worker_processes auto;
error_log /var/log/nginx/error.log warn;
pid /var/run/nginx.pid;
events {
worker_connections 1024;
}
# 添加stream模块，实现tcp反向代理
stream {
include /opt/nginx/stream/conf.d/*.conf; #加载 /opt/nginx/stream/conf.d目录下面的所有配置文件
}
创建mysql-net网络
docker network create mysql-net
配置docker-compose.yml
MASTER_HOST修改为当前主机对应的ip。
version: '3'
services:
mysql-slave-lb:
restart: always
image: nginx:alpine
container_name: mysql-slave-lb
ports:
- 3307:3307
volumes:
- ./conf.d/stream:/opt/nginx/stream/conf.d
- ./nginx.conf:/etc/nginx/nginx.conf
networks:
- alex_net
depends_on:
mysql-master:
condition: service_healthy
mysql-slave1:
condition: service_healthy
mysql-slave2:
condition: service_healthy
mysql-master:
restart: always
image: mysql:latest
container_name: mysql-master
environment:
MYSQL_ROOT_PASSWORD: "123456"
MASTER_SYNC_USER: "sync_admin" #设置脚本中定义的用于同步的账号
MASTER_SYNC_PASSWORD: "123456" #设置脚本中定义的用于同步的密码
ADMIN_USER: "root" #当前容器用于拥有创建账号功能的数据库账号
ADMIN_PASSWORD: "123456"
TZ: "Asia/Shanghai" #解决时区问题
ports:
- 3336:3306
healthcheck:
test: [ "CMD", "mysqladmin" ,"ping", "-h", "localhost" ]
interval: 10s
timeout: 10s
retries: 5
start_period: 10s
deploy:
resources:
limits:
memory: 512M
cpus: 50m
networks:
- alex_net
volumes:
- ./master/data:/var/lib/mysql:rw
- ./master/conf/my.cnf:/etc/mysql/my.cnf:rw
- ./master/log:/var/log/mysql:rw
- ./master/mysql-files:/var/lib/mysql-files:rw
mysql-slave1:
restart: always
image: mysql:latest
container_name: mysql-slave1
environment:
MYSQL_ROOT_PASSWORD: "123456"
SLAVE_SYNC_USER: "sync_admin" #用于同步的账号，由master创建
SLAVE_SYNC_PASSWORD: "123456"
ADMIN_USER: "root"
ADMIN_PASSWORD: "123456"
MASTER_HOST: "mysql-master" #master地址，开启主从同步需要连接master
TZ: "Asia/Shanghai" #设置时区
ports:
- 3316:3306
healthcheck:
test: [ "CMD", "mysqladmin" ,"ping", "-h", "localhost" ]
interval: 10s
timeout: 10s
retries: 5
start_period: 10s
deploy:
resources:
limits:
memory: 512M
cpus: 50m
networks:
- alex_net
depends_on:
mysql-master:
condition: service_healthy
volumes:
- ./slave1/data:/var/lib/mysql:rw
- ./slave1/conf/my.cnf:/etc/mysql/my.cnf:rw
- ./slave1/log:/var/log/mysql:rw
- ./slave1/mysql-files:/var/lib/mysql-files:rw
mysql-slave2:
restart: always
image: mysql:latest
container_name: mysql-slave2
environment:
MYSQL_ROOT_PASSWORD: "123456"
SLAVE_SYNC_USER: "sync_admin"
SLAVE_SYNC_PASSWORD: "123456"
ADMIN_USER: "root"
ADMIN_PASSWORD: "123456"
MASTER_HOST: "mysql-master"
TZ: "Asia/Shanghai"
ports:
- 3326:3306
healthcheck:
test: [ "CMD", "mysqladmin" ,"ping", "-h", "localhost" ]
interval: 10s
timeout: 10s
retries: 5
start_period: 10s
deploy:
resources:
limits:
memory: 512M
cpus: 50m
networks:
- alex_net
depends_on:
mysql-master:
condition: service_healthy
volumes:
- ./slave2/data:/var/lib/mysql:rw
- ./slave2/conf/my.cnf:/etc/mysql/my.cnf:rw
- ./slave2/log:/var/log/mysql:rw
- ./slave2/mysql-files:/var/lib/mysql-files:rw
networks:
alex_net:
external: true
最后的效果图
tree
最后配置的目录如下。
.
├── conf.d
│ └── stream
├── docker-compose.yml
├── master
│ ├── conf
│ │ └── my.cnf
│ ├── data
│ ├── log
│ └── mysql-files
├── nginx.conf
├── slave1
│ ├── conf
│ │ └── my.cnf
│ ├── data
│ ├── log
│ └── mysql-files
└── slave2
├── conf
│ └── my.cnf
├── data
├── log
└── mysql-files
启动docker-compose
docker-compose up -d
配置mysql主从
master中添加slave1、slave2用户
运行如下代码，然后之前设置的密码：123456，进入mysql master。
docker exec -it mysql-master mysql -u root -p
执行如下代码添加slave1、slave2用户。
#在主机MySQL里执行授权命令
CREATE USER 'slave1'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%';
#此语句必须执行。否则见下面。
ALTER USER 'slave1'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
#在主机MySQL里执行授权命令
CREATE USER 'slave2'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'slave2'@'%';
#此语句必须执行。否则见下面。
ALTER USER 'slave2'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;
#查询master的状态,记住file和position对应的信息，为后来添加从到主做准备。
show master status;
+------------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------------------+----------+--------------+------------------+-------------------+
| mall-mysql-master-bin.000003 | 40130 | | mysql | |
+------------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
slave1中配置主从
运行如下代码，然后之前设置的密码：123456，进入mysql master。
docker exec -it mysql-slave1 mysql -u root -p
$host替换为配置master的ip，master_log_file就是之前master对应的file信息，master_log_pos就是之前position对应的信息。
change master to master_host='mysql-master', master_user='slave1', master_password='123456', master_port=3306, master_log_file='mall-mysql-master-bin.000003', master_log_pos=1965, master_connect_retry=30;
启动slave。
start slave;
查看slave信息，如果Slave_IO_Running、Slave_SQL_Running两个信息是yes代表搭建从库成功。
show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: mysql-master
Master_User: slave1
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mall-mysql-master-bin.000003
Read_Master_Log_Pos: 40130
Relay_Log_File: bc55a10d23de-relay-bin.000002
Relay_Log_Pos: 38503
Relay_Master_Log_File: mall-mysql-master-bin.000003
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: 40130
Relay_Log_Space: 38720
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
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: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 101
Master_UUID: 0d212aaf-665e-11ed-968d-0242ac120003
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
slave2中配置主从
运行如下代码，然后之前设置的密码：123456，进入mysql master。
docker exec -it mysql-slave2 mysql -u root -p
$host替换为配置master的ip，master_log_file就是之前master对应的file信息，master_log_pos就是之前position对应的信息。
change master to master_host='mysql-master', master_user='slave2', master_password='123456', master_port=3306, master_log_file='mall-mysql-master-bin.000003', master_log_pos=1965, master_connect_retry=30;
启动slave。
start slave;
查看slave信息，如果Slave_IO_Running、Slave_SQL_Running两个信息是yes代表搭建从库成功。
show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: mysql-master
Master_User: slave2
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mall-mysql-master-bin.000003
Read_Master_Log_Pos: 40130
Relay_Log_File: bc55a10d23de-relay-bin.000002
Relay_Log_Pos: 38503
Relay_Master_Log_File: mall-mysql-master-bin.000003
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: 40130
Relay_Log_Space: 38720
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
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: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 101
Master_UUID: 0d212aaf-665e-11ed-968d-0242ac120003
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
通过脚本直接创建mysql主从
#!/bin/bash
pd=123456
echo "删除原来的文件"
rm -rf master/data/*
rm -rf slave1/data/*
rm -rf slave2/data/*
echo "删除之前的mysql容器"
docker rm -f mysql-master
docker rm -f mysql-slave1
docker rm -f mysql-slave2
docker rm -f mysql-slave-lb
docker-compose up -d
echo "master添加用户"
docker exec -it mysql-master mysql -uroot -p$pd -S /var/lib/mysql/mysql.sock -e \
"CREATE USER 'slave1'@'%' IDENTIFIED BY '$pd'; \
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%'; \
ALTER USER 'slave1'@'%' IDENTIFIED WITH mysql_native_password BY '$pd'; "
docker exec -it mysql-master mysql -uroot -p$pd -S /var/lib/mysql/mysql.sock -e \
"CREATE USER 'slave2'@'%' IDENTIFIED BY '$pd'; \
GRANT REPLICATION SLAVE ON *.* TO 'slave2'@'%'; \
ALTER USER 'slave2'@'%' IDENTIFIED WITH mysql_native_password BY '$pd'; "
echo "获取master position"
master_status=`docker exec -it mysql-master mysql -uroot -p$pd -S /var/lib/mysql/mysql.sock -e "show master status\G"`
echo "master_status: $master_status"
master_log_file=`echo "$master_status" | awk 'NR==3{print substr($2,1,length($2)-1)}'`
master_log_pos=`echo "$master_status" | awk 'NR==4{print $2}'`
master_log_file="'""$master_log_file""'"
echo "配置slave1"
docker exec -it mysql-slave1 mysql -uroot -p$pd -S /var/lib/mysql/mysql.sock -e \
"CHANGE MASTER TO MASTER_HOST='mysql-master',MASTER_PORT=3306,MASTER_USER='slave1',MASTER_PASSWORD='$pd',MASTER_LOG_FILE=$master_log_file,MASTER_LOG_POS=$master_log_pos;"
docker exec -it mysql-slave1 mysql -uroot -p$pd -S /var/lib/mysql/mysql.sock -e "start slave;"
docker exec -it mysql-slave1 mysql -uroot -p$pd -S /var/lib/mysql/mysql.sock -e "show slave status\G;"
echo "配置slave2"
## Setting Up Replication Slaves
docker exec -it mysql-slave2 mysql -uroot -p$pd -S /var/lib/mysql/mysql.sock -e \
"CHANGE MASTER TO MASTER_HOST='mysql-master',MASTER_PORT=3306,MASTER_USER='slave2',MASTER_PASSWORD='$pd',MASTER_LOG_FILE=$master_log_file,MASTER_LOG_POS=$master_log_pos;"
docker exec -it mysql-slave2 mysql -uroot -p$pd -S /var/lib/mysql/mysql.sock -e "start slave;"
docker exec -it mysql-slave2 mysql -uroot -p$pd -S /var/lib/mysql/mysql.sock -e "show slave status\G;"
mysql定时备份
因为我这面的数据库由于之前没有重视安全性，被黑客黑了，数据都丢失了。所以在这里添加数据库数据定时配置，备份数据。在当前目录创建mysql.sh文件，然后添加如下信息$ip修改为mysql数据库对应的ip地址、端口号。
#!/bin/bash
#备份路径
BACKUP=/usr/local/soft/mysql/backup
#当前时间
DATETIME=$(date +%Y-%m-%d)
echo "===备份开始==="
#数据库名称
finance=alex_finance
user=alex_user
nacos=nacos
miaosha=alex_miaosha
oss=alex_oss
#数据库地址
HOST=$ip
PORT=3306
#数据库用户名
DB_USER=root
#数据库密码
DB_PW=123456
#创建备份目录
[ ! -d "${BACKUP}" ] && mkdir -p "${BACKUP}"
echo "开始备份 ${finance}"
docker exec -i mysql-master sh -c "exec mysqldump -uroot -p$DB_PW -q -R --databases ${finance}" > $BACKUP/${finance}_`date +%F`.sql
echo "结束备份 ${finance}"
echo "开始备份 ${user}"
docker exec -i mysql-master sh -c "exec mysqldump -uroot -p$DB_PW --databases ${user}" > $BACKUP/${user}_`date +%F`.sql
echo "结束备份 ${user}"
echo "开始备份${nacos}"
docker exec -i mysql-master sh -c "exec mysqldump -uroot -p$DB_PW --databases ${nacos}" > $BACKUP/${nacos}_`date +%F`.sql
echo "结束备份${nacos}"
echo "开始备份${miaosha}"
docker exec -i mysql-master sh -c "exec mysqldump -uroot -p$DB_PW --databases ${miaosha}" > $BACKUP/${miaosha}_`date +%F`.sql
echo "结束备份${miaosha}"
echo "开始备份${oss}"
docker exec -i mysql-master sh -c "exec mysqldump -uroot -p$DB_PW --databases ${oss}" > $BACKUP/${oss}_`date +%F`.sql
echo "结束备份${oss}"
#删除十天前的备份文件
find $BACKUP -mtime +9 -name "*.sql" -exec rm -rf {} \;
echo "===导出成功==="
执行如下代码，添加备份定时。
crontab -e
设置每天01:01执行定时。
01 01 * * * /usr/local/soft/mysql/mysql.sh
$root修改为mysql对应的root， $password修改为mysql对应的数据库密码。然后执行如下命令补数据。
docker exec -i mysql-master mysql -u$root -p$password < /usr/local/soft/mysql/backup/alex_finance_2022-12-30.sql
总结
至此，通过docker-compose搭建mysql一主二从完成。
Q&A
当执行开始slave，报如下错误时，运行重新创建slave即可。
mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
重新设置slave
reset slave;
start slave;
251
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
