搭建mysql主从数据库

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)]