Mysql—主从复制

生如风2023年3月8日

Mysql

主从复制

大约 6 分钟

[TOC]

主从复制

创建mysql

基于docker创建2个mysql容器

1、拉取mysql镜像，这里拉取最新的8.0.20

docker pull mysql:latest

在这里插入图片描述

2、使用镜像运行两个mysql容器：mysql 和 mysql-2

docker run -itd --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql
docker run -itd --name mysql-2 -p 33062:3306 -e MYSQL_ROOT_PASSWORD=root mysql

mysql 使用容器服务的3306端口，映射到主机的3306端口，密码root mysql-2 使用容器服务的33062端口，映射到主机的3306端口，密码root

3、可以看到两个运行起来的mysql

docker ps

在这里插入图片描述

4、可以使用工具试着连接两个mysql

在这里插入图片描述

mysql主从配置

1、先在mysql上进行用户创建及授权，这样mysql-2可以通过该用户连接mysql

5.7版本的mysql授权命令为：

GRANT replication slave on *.* to 'repl'@'%' IDENTIFIED by 'root';

在这里插入图片描述

但是因为我们的mysql版本为8.0.20，用户创建和授权需要分开执行

# 创建用户
create user 'repl'@'%' IDENTIFIED by 'root';

在这里插入图片描述

创建好用户后，可以查询该用户默认的权限值

# 查询用户repl授权情况
show GRANTS FOR 'repl'@'%';

在这里插入图片描述

可以看到默认权限为USAGE ，该权限仅可连接数据库，无其他权限。具体权限值可以看官网：mysql权限open in new window

给创建好的用户repl 授权

# 授权replication slave 给用户
grant replication slave on *.* to 'repl'@'%';

# 刷新权限
FLUSH PRIVILEGES;

在这里插入图片描述

授权后查询权限，可以看到USAGE 变成了replication slave replication slave 主要是用于建立复制时的用户权限。主数据库mysql 授权具有该权限的用户，从数据库mysql-2 才能通过该用户复制。

用到的命令：

# 5.7 授权
GRANT replication slave on *.* to 'repl'@'%' IDENTIFIED by 'root';

# 创建用户
create user 'repl'@'%' IDENTIFIED by 'root';

# 授权replication slave 给用户
grant replication slave on *.* to 'repl'@'%';

# 回收授权
REVOKE replication slave on *.* from 'repl'@'%';

# 删除用户
DROP user 'repl'@'%';

# 查询用户repl授权情况
show GRANTS FOR 'repl'@'%';

# 刷新权限
FLUSH PRIVILEGES;

授权方面可参考：mysql授权open in new window

2、授权完成后，需要配置mysql 和mysql-2 的my.cnf docker 进入mysql配置

在这里插入图片描述

log-bin表示logbin的目录，该目录存放mysql执行的二进制文件，记录mysql除select外的操作记录，因为上面配置的repl用户可以连接msyql，也就是可以复制binlog文件，用于mysql-2 执行。 server-id 配置一个id标识 binlog-do-db 配置需要复制的是javaboy 数据库的binlog二进制文件

docker 进入mysql-2 配置

在这里插入图片描述

记得配置my.cnf 后都有执行mysql 容器的重启

docker restart mysql
docker restart mysql-2

了解mysql 主从复制的可以参考：mysql主从复制open in new window

3、授权完成，配置完my.cnf 后，还需要进入mysql-2 指定主master 为mysql

首先查询出mysql 的master信息

show master status;

在这里插入图片描述

进入mysql-2 容器，连接数据库，设置mysql-2 的master为上面的mysql

change master to master_host = '172.22.64.1' ,master_port=3306, master_user='repl', master_password='root',master_log_file='binlog.000472',master_log_pos=156;

master_port 为mysql的端口3306 master_user 为已经授权replication slave的repl 用户 master_log_file 为mysql 通过show master status 命令查出来的信息的file 值 master_log_pos 为mysql 通过show master status 命令查出来的信息的position 值

开启slave及查询slave 状态
看到Slave_IO_Running 和 Slave_SQL_Running 为 Yes 。并且提示Wating for master to send event 。就是配置成功了。下一步可以测试看看效果。
在mysql 中，新建数据库javaboy ，刷新mysql-2 看看是否同步

在这里插入图片描述

可以看到已经同步数据库javaboy，在试着建表，插入数据看看效果 mysql的javaboy 数据库建表user

在这里插入图片描述

查看mysql-2 数据库javaboy 也有user表和数据

在这里插入图片描述

这样就完成了mysql的主从配置

程序测试

这里使用springboot 和com.baomidou的dynamic datasource for springboot 动态数据源进行测试链接：https://github.com/baomidou/dynamic-datasource-spring-boot-starter

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.9</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.liwang</groupId>
    <artifactId>dynamicdata</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>dynamicdata</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>

        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>3.3.1</version>
        </dependency>
    </dependencies>

    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
            </resource>
        </resources>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

建立model.User、mapper.UserMapper、mapper.UserMapper.xml、service.UserService、service.UserServiceImpl

public class User {
    private Integer id;
    private String name;

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

@Mapper
public interface UserMapper {
    Integer add(User user);

    List<User> getAllUser();
}

<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.liwang.dynamicdata.mapper.UserMapper">

    <insert id="add">
        insert into user(name) value(#{name})
    </insert>

    <select id="getAllUser" resultType="com.liwang.dynamicdata.model.User">
        select * from user
    </select>

</mapper>

public interface UserService {

    Integer add(User user);

    List<User> getAllUser();
}

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;

    @Override
    @DS("master")
    public Integer add(User user) {
        return userMapper.add(user);
    }

    @Override
    @DS("slave")
    public List<User> getAllUser() {
        return userMapper.getAllUser();
    }
}

根据动态数据源的说明，在方法上使用@DS注解，标识某个方法使用主或从数据库。在上面的UserServiceImpl中，添加方法add使用master数据源，获取所有用户方法getAllUser使用slave数据源。

yml配置

spring:
  datasource:
    dynamic:
      primary: master
      strict: false
      datasource:
        master:
          url: jdbc:mysql://localhost:3306/javaboy
          username: root
          password: root
        slave:
          url: jdbc:mysql://localhost:33062/javaboy
          username: root
          password: root

测试

@SpringBootTest
class DynamicdataApplicationTests {

    @Autowired
    UserServiceImpl userService;

    @Test
    void contextLoads() {
        User user = new User();
        user.setName("钱五");
        Integer i = userService.add(user);
        System.out.println(i);
    }

    @Test
    public void test1() {
        List<User> list = userService.getAllUser();
        System.out.println("list = " + list);
    }

}

在添加方法中，可以看到mysql 数据库的javaboy user数据表中，正常添加数据，后刷新mysql-2 数据库的javaboy user ，也可以正常看到添加的数据。在查询方法中，可以在mysql-2 中，添加一条数据，然后查询结果是mysql-2 中的数据，证明使用的是slave

在这里插入图片描述

到这，测试mysql的主从复制就告一段落了。后续有需要在更新

可以看看我的个人博客：网站：https://www.fuzm.wangopen in new window / https://liwangc.gitee.ioopen in new window ————————————————————————— 作为初学者，很多知识都没有掌握，见谅，如有错误请指出，以期进步，感谢！。后续有新的学习，继续补充上来。

Mysql—主从复制

# 主从复制

# 创建mysql

# mysql主从配置

# 程序测试

主从复制

创建mysql

mysql主从配置

程序测试