MySQL主从复制

HeJin大约 5 分钟

01.MySQL主从复制

MySQL主从复制是一个异步的复制过程，底层是基于MySQL数据库自带的二进制日志功能。就是一台或多台MySQL数据库（slave，即从库)从另一台MySQL数据库(master，即主库)进行日志的复制然后再解析日志并应用到自身，最终实现从库的数据和主库的数据保持一致。MySQL主从复制是ySQL数据库自带功能，无需借助第三方工具。

MySQL复制过程分成三步：

master将改变记录到二进制日志(binary log)。
slave:将master的pinary log拷贝到它的中继日志(relay log)。
slave重做中继日志中的事件，将改变应用到自己的数据库中。

02.安装docker-compose

curl -L "https://github.com/docker/compose/releases/download/v2.16.0/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose 

# 设置文件可执行权限 
chmod +x /usr/local/bin/docker-compose 

# 查看版本信息 
docker-compose -v

docker-compose.yml

master：

version: '3.1'
services:
  mysql-master:
    image: mysql:8.0.32
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    container_name: "mysql-master"
    ports: 
      - "3306:3306"
    volumes:
      - /home/mysql/conf/my.cnf:/etc/my.cnf
      - /home/mysql/data:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: "root"
    networks: 
      - dev
  redis:
    image: redis:7.0.8
    command: redis-server /etc/redis/redis.conf --appendonly yes
    restart: always
    container_name: "redis"
    ports: 
      - "6379:6379"
    volumes:
      - /home/redis/conf/redis.conf:/etc/redis/redis.conf
      - /home/redis/data:/data
    networks: 
      - dev    
networks:
  dev:
    driver: bridge
  pro:
    driver: bridge

slave：

version: '3.1'
services:
  mysql-slave:
    image: mysql:8.0.32
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    container_name: "mysql-slave"
    ports: 
      - "3306:3306"
    volumes:
      - /home/mysql/conf/my.cnf:/etc/my.cnf
      - /home/mysql/data:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: "root"
    networks: 
      - dev  
networks:
  dev:
    driver: bridge
  pro:
    driver: bridge

将写好的脚本（.sh文件）放到目录 /etc/profile.d/ 下，系统启动后就会自动执行该目录下的所有shell脚本。

docker-compose -f /home/mycompose/docker-compose.yml up -d

03.配置主从同步

分别在两台虚拟机上使用docker安装mysql，也可以使用上面的docker-compose安装。

docker pull mysql:8.0.32

docker run --name mysqltest \
-p 3306:3306 -e MYSQL_ROOT_PASSWORD=root \
-d mysql:8.0.32

# 拷贝配置文件
docker cp mysqltest:/etc/my.cnf /home/mysql/conf/

# 主库
docker run --name mysql-master \
-p 3306:3306 -e MYSQL_ROOT_PASSWORD=root \
--mount type=bind,src=/home/mysql/conf/my.cnf,dst=/etc/my.cnf \
--mount type=bind,src=/home/mysql/data,dst=/var/lib/mysql \
--restart=on-failure:3 \
-d mysql:8.0.32

# 从库
docker run --name mysql-slave \
-p 3306:3306 -e MYSQL_ROOT_PASSWORD=root \
--mount type=bind,src=/home/mysql/conf/my.cnf,dst=/etc/my.cnf \
--mount type=bind,src=/home/mysql/data,dst=/var/lib/mysql \
--restart=on-failure:3 \
-d mysql:8.0.32

主库master配置

修改配置文件my.cnf：

log_bin=mysql-bin
server-id=100

进入主库数据库配置：

docker exec -it mysql-master bash

mysql> create user 'repl'@'%' identified by 'Root12345_';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to 'repl'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)


FLUSH PRIVILEGES;

show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      898 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

从库slave配置

修改配置文件my.cnf：

server-id=101

进入从库slave执行sql：

docker exec -it mysql-slave bash

CHANGE MASTER TO 
MASTER_HOST = '192.168.1.222',  
MASTER_USER = 'repl', 
MASTER_PASSWORD = 'Root12345_',
MASTER_PORT = 3306,
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=1993,
MASTER_HEARTBEAT_PERIOD = 10000; 

# MASTER_LOG_FILE与主库File 保持一致
# MASTER_LOG_POS=120 , #与主库Position 保持一致

启动从库：

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.03 sec)

查看是否配置成功：

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.1.222
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 3107
               Relay_Log_File: c8942042d032-relay-bin.000002
                Relay_Log_Pos: 326
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3107
              Relay_Log_Space: 543
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 100
                  Master_UUID: 65519a8a-ab7a-11ed-8025-0242ac110002
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

Slave_IO_Running：从库的IO线程，用来接收master发送的binlog，并将其写入到中继日志relag log
Slave_SQL_Running：从库的SQL线程，用来从relay log中读取并执行binlog。
Slave_IO_Running、Slave_SQL_Running：这两个进程的状态需全部为 YES，只要有一个为 NO，则复制就会停止。
Master_Log_File：要同步的主库的binlog文件名。
Read_Master_Log_Pos：已同步的位置，即同步的 binlog 文件内的字节偏移量，该值会随着主从同步的进行而不断地增长。
Relay_Log_File：从库的中继日志文件，对接收到的主库的 binlog 进行缓冲。从库的SQL线程不断地从 relay log 中读取 binlog 并执行。
Relay_Log_Pos：relay log 中已读取的位置偏移量。
Seconds_Behind_Master: 主从同步延时, 值为 0 为正常情况，正值表示已经出现延迟，数字越大从库落后主库越多。

发现密码连接问题，修改主库：

mysql> SELECT plugin FROM `user` where user = 'repl';
+-----------------------+
| plugin                |
+-----------------------+
| caching_sha2_password |
+-----------------------+
1 row in set (0.01 sec)
mysql> ALTER USER 'repl'@'192.168.1.223' IDENTIFIED WITH mysql_native_password BY 'root';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

03.SpringBoot项目配置

导入依赖

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0-RC1</version>
</dependency>

配置文件

spring:
  main:
    # 允许bean定义覆盖
    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:
      names:
        master,slave
      # 主数据源
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.1.222:3306/rw?characterEncoding=utf-8
        username: root
        password: root
      # 从数据源
      slave:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.1.223:3306/rw?characterEncoding=utf-8
        username: root
        password: root
    masterslave:
      # 读写分离配置
      load-balance-algorithm-type: round_robin
      # 最终的数据源名称
      name: dataSource
      # 主库数据源名称
      master-data-source-name: master
      # 从库数据源名称列表，多个逗号分隔
      slave-data-source-names: slave
    props:
      sql:
        show: true #开启SQL显示，默认false

数据同步

把旧数据的数据同步到主库master的mysql中。

MySQL主从复制

# 01.MySQL主从复制

# 02.安装docker-compose

# 03.配置主从同步

# 主库master配置

# 从库slave配置

# 03.SpringBoot项目配置

# 导入依赖

# 配置文件

# 数据同步