一、准备3台物理机器master-1、master-2,
master-1:192.168.1.10
master-2:192.168.1.20
hproxy-master:192.168.1.30
二、然后分别在2台物理机器master-1、master-2上使用docker-compose安装mysql8,并配置互为主从。
1)配置master-1
cd /home
mkdir dockermysqldata
cd dockermysqldata
mkdir mysql8data
# master_1_init.sql见文件[master-1]
touch master_1_init.sql
# Dockerfile_master_1见文件[master-1]
touch Dockerfile_master_1
# docker-compose.yml见文件[master-1]
touch docker-compose.yml
[master-1]文件 master_1_init.sql
CREATE USER 'repl'@'%' IDENTIFIED WITH caching_sha2_password BY 'repl';
grant replication slave, replication client on *.* to 'repl'@'%';
flush privileges;
[master-1]文件 Dockerfile_master_1
FROM mysql:8.0.31
ENV TZ=Asia/Shanghai
RUN ln -snf /usr/share/zoneinfo/$TZ /etc/localtime && echo $TZ > /etc/timezone
COPY ./master_1_init.sql /docker-entrypoint-initdb.d
[master-1]文件 docker-compose.yml
version: "3.9"
services:
mysql8-master-1:
build:
context: ./
dockerfile: ./Dockerfile_master_1
image: mysql8-master-1
restart: always
container_name: mysql8-master-1
volumes:
- /home/dockermysqldata/mysql8data/conf/my.cnf:/etc/mysql/my.cnf
- /home/dockermysqldata/mysql8data/data:/var/lib/mysql
- /home/dockermysqldata/mysql8data/logs:/var/log/mysql
ports:
- 3306:3306
environment:
- MYSQL_ROOT_PASSWORD=root
- MYSQL_ROOT_HOST=%
privileged: true
extra_hosts:
# master_1容器可以使用的host访问master_2
- mysql8-master-2:192.168.1.20
command: ['--server-id=1',
'--sync_binlog=1',
'--log-bin=mysql8-master-1-bin',
'--binlog-ignore-db=mysql,information_schema,performance_schema,sys',
'--binlog_cache_size=256M',
'--binlog_format=mixed',
'--relay_log=mysql8-master-1-relay',
'--lower_case_table_names=1',
'--character-set-server=utf8mb4',
'--collation-server=utf8mb4_general_ci',
'--sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION']
2)配置master-2
cd /home
mkdir dockermysqldata
cd dockermysqldata
mkdir mysql8data
touch master_2_init.sql
touch Dockerfile_master_2
touch docker-compose.yml
[master-2]文件 master_2_init.sql
CREATE USER 'repl'@'%' IDENTIFIED WITH caching_sha2_password BY 'repl';
grant replication slave, replication client on *.* to 'repl'@'%';
flush privileges;
[master-2]文件 Dockerfile_master_2
FROM mysql:8.0.31
ENV TZ=Asia/Shanghai
RUN ln -snf /usr/share/zoneinfo/$TZ /etc/localtime && echo $TZ > /etc/timezone
COPY ./master_2_init.sql /docker-entrypoint-initdb.d
[master-2]文件 docker-compose.yml
version: "3.9"
services:
mysql8-master-2:
build:
context: ./
dockerfile: ./Dockerfile_master_2
image: mysql8-master-2
restart: always
container_name: mysql8-master-2
volumes:
- /home/dockermysqldata/mysql8data/conf/my.cnf:/etc/mysql/my.cnf
- /home/dockermysqldata/mysql8data/data:/var/lib/mysql
- /home/dockermysqldata/mysql8data/logs:/var/log/mysql
ports:
- 3306:3306
environment:
- MYSQL_ROOT_PASSWORD=omron5757124
- MYSQL_ROOT_HOST=%
privileged: true
extra_hosts:
# master_2容器可以使用的host访问master_1
- mysql8-master-1:192.168.1.10
command: ['--server-id=2',
'--sync_binlog=1',
'--log-bin=mysql8-master-2-bin',
'--binlog-ignore-db=mysql,information_schema,performance_schema,sys',
'--binlog_cache_size=256M',
'--binlog_format=mixed',
'--relay_log=mysql8-master-2-relay',
'--lower_case_table_names=1',
'--character-set-server=utf8mb4',
'--collation-server=utf8mb4_general_ci',
'--sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION']
3)启动master-1、master-2数据库服务
[master-1] 执行:
# mysql8真正启动需要一些时间,navicat链接成功说明启动成功
docker-compose up -d
[master-2] 执行:
# mysql8真正启动需要一些时间,navicat链接成功说明启动成功
docker-compose up -d
4)启动master-1、master-2互为主从
①查看[master-1]作为主节点 的bin-log-file、bin-log-pos:
show master status
配置及其查看[master-1]作为从节点的slave并启动(需要获取master-2主节点的日志)
(注意:master_log_file、master_log_pos为你自己的数据库实际值):
change master to master_host='192.168.1.20', master_user='repl',master_password='repl',master_port=3306,GET_MASTER_PUBLIC_KEY=1,master_log_file='mysql8-master-2-bin.000003', master_log_pos= 157, master_connect_retry=60;
start slave;
show slave status;
②查看[master-2] 作为主节点 的bin-log-file、bin-log-pos:
show master status
change master to master_host='192.168.1.10', master_user='repl',master_password='repl',master_port=3306,GET_MASTER_PUBLIC_KEY=1,master_log_file='mysql8-master-1-bin.000003', master_log_pos= 157, master_connect_retry=60;
start slave;
show slave status;
最后分别在两台数据库执行:show slave status;
两台都有 Slave_IO_Running 和 Slave_SQL_Running 都为YES就可以了
HAProxy-mysql负载均衡
1.拉取haproxy镜像
docker pull haproxy
2.配置目录
mkdir /docker/haproxy-master/
touch /docker/haproxy-master/haproxy.cfg
3.编辑haproxy.cfg
主与从都分别配置,mysql的用户尽量都一致
vim /docker/haproxy-master/haproxy.cfg
defaults
mode tcp
log global
option tcplog
option dontlognull
option http-server-close
option redispatch
retries 3
timeout http-request 10s
timeout queue 1m
timeout connect 10s
timeout client 1m
timeout server 1m
timeout http-keep-alive 10s
timeout check 10s
maxconn 3000#配置haproxy可连接的地址,与绑定固定的域名
frontend mysql
bind 0.0.0.0:13306
mode tcp
log global
default_backend mysql_server#负载均衡的真实数据库地址
backend mysql_server
balance roundrobin
server mysql1 192.168.1.10:3306 check inter 5s rise 2 fall 3
server mysql2 192.168.1.20:3306 check inter 5s rise 2 fall 3
#rise 2是2次正确认为服务器可用,fall 3是3次失败认为服务器不可用
#客户端配置
listen admin_status
mode http
bind 0.0.0.0:8899
option httplog
log global
stats enable
stats refresh 10s
stats hide-version
stats realm Haproxy\ Statistics
stats uri /admin-status
stats auth admin:123456
stats admin if TRUE
4.构建haproxy容器
docker run -d -p 8899:8899 -p 13306:13306 --name haproxy-master \
-v /docker/haproxy-master/haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg \
--privileged=true haproxy:latest
5.连接测试
mysql -uroot -proot -P13306 -h192.168.1.30 -e "show variables like 'server_id';"
页面详细参数解释
Queue
Cur: current queued requests //当前的队列请求数量
Max:max queued requests //最大的队列请求数量
Limit: //队列限制数量
Session rate(每秒的连接回话)列表:
scur: current sessions //每秒的当前回话的限制数量
smax: max sessions //每秒的新的最大的回话量
slim: sessions limit //每秒的新回话的限制数量
Sessions
Total: //总共回话量
Cur: //当前的回话
Max: //最大回话
Limit: //回话限制
Lbtot: total number of times a server was selected //选中一台服务器所用的总时间
Bytes
In: //网络的字节数输入总量
Out: //网络的字节数输出总量
Denied
Req: denied requests//拒绝请求量
Resp:denied responses //拒绝回应
Errors
Req:request errors //错误请求
Conn:connection errors //错误的连接
Resp: response errors (among which srv_abrt) ///错误的回应
Warnings
Retr: retries (warning) //重新尝试
Redis:redispatches (warning) //再次发送
Server列表:
Status:状态,包括up(后端机活动)和down(后端机挂掉)两种状态
LastChk: 持续检查后端服务器的时间
Wght: (weight) : 权重
Act: server is active (server), number of active servers (backend) //活动链接数量
Bck: server is backup (server), number of backup servers (backend) //backup:备份的服务器数量
Down: //后端服务器连接后都是down的数量
Downtime: downtime: total downtime (in seconds) //总的downtime 时间
Throttle: warm up status //设备变热状态