mysql8.4主从配置

mysql8.4主从配置

环境

docker + mysql8.4

查找mysql

docker search mysql

拉取mysql镜像

docker pull mysql

运行2台mysql(mysql1,mysql2)

docker run -itd --name=mysql1 -e MYSQL_ROOT_PASSWORD=123456 mysql

docker run -itd --name=mysql2 -e MYSQL_ROOT_PASSWORD=12345678 mysql

查看mysql1,mysql2的IP

docker inspect mysql1

docker inspect mysql2

mysql1IP:172.17.0.2 mysql2 IP:172.17.0.4 (后面需要用到!!!) mysql1 为主服务器，mysql2为从服务器

主从服务器配置 从mysql1复制配置文件my.cnf到主机

docker cp mysql1:/etc/my.cnf /home/my.cnf

主服务器mysql1 配置

vi /home/my.cnf

修改后如下

[mysqld]
server-id=1  
log-bin=mysql-bin
binlog-ignore-db=mysql,information_schema,performance_schema
#binlog-do-db=school-info


skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql

pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/

把修改好的主服务器配置文件my.cnf复制回mysql1

从服务器 mysql2 配置

docker cp /home/my.cnf mysql1:/etc/my.cnf

vi /home/ubuntu/my.cnf

修改后如下

[mysqld]
server-id=2  
log-bin=mysql-bin
replicate-ignore-db=mysql,information_schema,performance_schema

skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql

pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/

把修改好的从服务器配置文件my.cnf复制回mysql2

docker cp /home/my.cnf mysql2:/etc/my.cnf

重启mysql1 mysql2

docker restart mysql1

docker restart mysql2

主服务器mysql1配置 进入mysql1容器

docker exec -it mysql1 /bin/bash

登录mysql

mysql -uroot -p123456

修改mysql1从服务器连接账号密码

alter user 'root'@'%' identified by '123456';

给root授权

grant replication slave,replication client on *.* to 'root'@'%';

刷新权限

flush privileges;

查看master状态

show replica status;
show variables like 'log_bin';
SHOW BINARY LOGS;
SELECT user, host FROM mysql.user WHERE user='root' AND host='%';

从服务器mysql2配置 进入mysql2容器

docker exec -it mysql2 /bin/bash

登录mysql

mysql -uroot -p123456

停止slave

stop replica;

从服务器连接主服务器(用到前面的mysql1IP,root账号密码，file,position的值)

change replication source to
source_host='172.18.0.2',
source_user='root',
source_password='123456',
source_port=3306,
source_log_file='mysql-bin.000001',
source_log_pos=1;

启动slave

start replica;

查看slave状态

show replica status \G

测试(master添加数据库数据，slave查看是否同步) master(mysql1)下执行 创建数据库

create database school-info;

创建表

CREATE TABLE `school-info`.`school`  (
`id` bigint(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(200) NULL COMMENT '学校名称',
`create_at` bigint(11) NULL,
`update_at` bigint(11) NULL,
`status` tinyint(4) NULL DEFAULT 0 COMMENT '状态',
PRIMARY KEY (`id`)
) ENGINE = InnoDB COMMENT = '学校信息表';

表添加记录

insert into school values(1,"school1",1625568499,1625568499,1);
insert into school values(2,"school2",1625568499,1625568499,1);

slave(mysql2)下执行 查看数据库列表

show databases;

选择数据库

use school-info;

查看表记录

select * from school;

如果看见有school_info数据库,school表，记录跟master一致，则说明配置成功