docker run -it \
--name mysql_3306 \
--privileged \
-p 3306:3306 \
--network wn_docker_net \
--ip 172.18.12.2 \
-v /usr/local/software/mysql/3306/conf/my.cnf:/etc/mysql/my.cnf \
-v /usr/local/software/mysql/3306/data:/var/lib/mysql \
-v /usr/local/software/mysql/3306/mysql-files:/var/lib/mysql-files \
-e MYSQL_ROOT_PASSWORD=123 \
-d mysql
docker run -it \
--name mysql_3311 \
--privileged \
-p 3311:3306 \
--network wn_docker_net \
--ip 172.18.12.4 \
-v /usr/local/software/mysql/3311/conf/my.cnf:/etc/mysql/my.cnf \
-v /usr/local/software/mysql/3311/data:/var/lib/mysql \
-v /usr/local/software/mysql/3311/mysql-files:/var/lib/mysql-files \
-e MYSQL_ROOT_PASSWORD=123 \
-d mysql
change master to master_host='172.18.12.2', master_user='slave',master_password='123',MASTER_LOG_FILE='wnhz-master-bin.000001',MASTER_LOG_POS=156
MySQL搭建主从数据库
采用两个docker容器扮演主(master)从(slave)
1查看日志
查看日志是否启用
show variables like 'log_bin';
查看binlog的位置
show variables like '%datadir%';
显示所有的日志文件位置
show binary logs;
查询binlog日志
show binlog events in '日志名字'
2创建存储文件夹
[root@localhost local]# mkdir -p software/mysql/3306 software/mysql/3310 software/mysql/3311
[root@localhost local]# cd software/
[root@localhost software]# tree
.
└── mysql
├── 3306
├── 3310
└── 3311
例子
3从容器中拷贝原始配置文件my.cnf
先进入容器内部
docker run -it --name mytest -e MYSQL_ROOT_PASSWORD=123 -d mysql
docker cp mytest:/etc/mysql/my.cnf ./
4搭建主(master)服务器(docker容器)
docker run \
-it \
--name mysql_3306 \
--privileged \
--network wn_docker_net \
--ip 172.18.12.2 \
-p 3306:3306 \
-v /usr/local/software/mysql/3306/conf/my.cnf:/etc/mysql/my.cnf \
-v /usr/local/software/mysql/3306/data:/var/lib/mysql \
-v /usr/local/software/mysql/3306/mysql-files:/var/lib/mysql-files \
-e MYSQL_ROOT_PASSWORD=123 \
-d mysql
客户端测试
5搭建搭建mysql(master)服务器
编辑my.cnf文件
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
default_authentication_plugin=mysql_native_password
# Custom config should go here
!includedir /etc/mysql/conf.d/
server-id=200
log_bin=master-bin
binlog_format=row
重启docker
docker restart <当前容器的名称>
例子
6创建与从(slave)服务通信的用户
1. create user 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123';;
2. GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'slave'@'%';
3. flush privileges;
例子
查询master状态
进入到主容器
docker exec -it <主容器的名字> bash
7搭建mysql(slave)从服务器3310
修改3310内的配置文件
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
default_authentication_plugin=mysql_native_password
# Custom config should go here
!includedir /etc/mysql/conf.d/
server-id=201
log-bin=wnhz-slave-01-bin
relay_log=wnhz-mysql-relay-bin
read-only=1
例子
8创建运行slave01容器
docker run -it \
--name mysql_3310 \
--privileged \
-p 3310:3306 \
--network wn_docker_net \
--ip 172.18.12.3 \
-v /etc/localtime:/etc/localtime \
-v /usr/local/softwares/mysql/3310/conf/my.cnf:/etc/mysql/my.cnf \
-v /usr/local/softwares/mysql/3310/data:/var/lib/mysql \
-v /usr/local/softwares/mysql/3310/mysql-files:/var/lib/mysql-files \
-e MYSQL_ROOT_PASSWORD=123 \
-d mysql
navicat测试
9配置主从
开启slave
start slave
查询slave状态
show slave status \G;
两个yes是成功
10创建只读账号
create user 'rdb'@'%' IDENTIFIED WITH mysql_native_password BY '123';
GRANT SELECT ON *.* TO 'rdb'@'%';
flush privileges
例子
测试主从
在master创建库book_db,查看从(slave)是否也创建成功。
测试主从
在master创建库book_db,查看从(slave)是否也创建成功。
[外链图片转存中…(img-LKZc2HlD-1696854810191)]