MySQL基础

目录

概要

主要探索一下几个问题：

  • MySQL 主从复制 (MySQL Replication) 是什么？
  • MySQL主从架构
  • 用docker搭建一个简单的主从库
  • 读写分离
  • 主从延时分析

主从复制原理

Mysql主从基本原理，主要形式以及主从同步延迟原理 (读写分离)导致主库从库数据不一致问题的及解决方案

深度探索MySQL主从复制原理

MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。在Master与Slave之间的实现整个复制过程主要由三个线程来完成，其中两个线程（SQL线程和I/O线程）在Slave端，另外一个线程（I/O线程）在Master端。

image-20201105101228873

从上面架构图看，可以知道主从复制的大概步骤：

  1. 主库master开启binary log，将数据更新操作记录到binlog中；
  2. 从库slave启动一个I/O thread 连接并监听master的binlog，以拉的方式读取；如果没有它会睡眠等待Master产生新的日志事件。
  3. 如果从库监听到主库有新的日志事件(Log Events)，则会将其拷贝至Slave服务器中的中继日志(Relay Log)。
  4. 从库Slave重做中继日志(Relay Log)中的事件，将Master上的改变反映到它自己的数据库中。

MySQL 主从形式

From 深度探索MySQL主从复制原理

一主一从

img

一主多从

img

一主一从和一主多从是最常见的主从架构，实施起来简单并且有效，不仅可以实现HA，而且还能读写分离，进而提升集群的并发能力。

多主一从

img

多主一从（从5.7开始支持）可以将多个mysql数据库备份到一台存储性能比较好的服务器上。

双主复制

img

双主复制，也就是互做主从复制，每个master既是master，又是另外一台服务器的slave。这样任何一方所做的变更，都会通过复制应用到另外一方的数据库中。

级联复制

img

级联复制模式下，部分slave的数据同步不连接主节点，而是连接从节点。因为如果主节点有太多的从节点，就会损耗一部分性能用于replication，那么我们可以让3~5个从节点连接主节点，其它从节点作为二级或者三级与从节点连接，这样不仅可以缓解主节点的压力，并且对数据一致性没有负面影响。

主要用途

  • 读写分离
  • 数据实时备份
  • 高可用HA（High Available）,实时灾备，用于故障切换。
  • 架构扩展

随着系统中业务访问量的增大，如果是单机部署数据库，就会导致I/O访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘I/O访问的频率，提高单个机器的I/O性能。数据实时备份，当系统中某个节点发生故障时，可以方便的故障切换。

搭建一主一从

docker基本操作

Docker 安装 MySQL

要想查看镜像的版本号TAG，可以在 docker hub 查看，进入之后，在页面左上角搜索框搜索。

image-20201104115338298

进入dockerhub 的mysql介绍页面，里面有介绍docker启动mysql的基本操作。

image-20201104115657216

  • 拉取镜像：选择了8.0版本。

    $ docker pull mysql:8.0

  • 查看镜像

    $ docker images
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
mysql               8.0                 db2b37ec6181        12 days ago         545MB

  • 启动镜像

    $ docker run -itd --name mysql -p 53306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:8.0

    参数说明：

    • -p 53306:3306 ：映射容器服务的 53306 端口到宿主机的 3306 端口，外部主机可以直接通过 宿主机ip:53306 访问到 MySQL 的服务。
    • MYSQL_ROOT_PASSWORD=123456：设置 MySQL 服务 root 用户的密码。
    • mysql:8.0：镜像版本

  • 查看容器进程

    $ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                                NAMES
961e398feca0        mysql:8.0           "docker-entrypoint.s…"   3 minutes ago       Up 3 minutes        33060/tcp, 0.0.0.0:53306->3306/tcp   mysql

  • 连接mysql

    $ mysql -h localhost -P 53306 -u root -p

  • 进入容器

    可以进入mysql-master容器中，查看或修改一些配置

    $ docker exec -it mysql-master bash

创建主从库

https://www.jianshu.com/p/0439206e1f28

创建主库

  • 创建一个master目录，然后创建my.cnf 和 dockerfile文件。

  • 创建 my.cnf 文件：

    [mysqld]
#[必须]服务器唯一ID，默认是1，一般取IP最后一段，这里看情况分配
server_id = 1
#[必须]启用二进制日志
log-bin = mysql-bin
# 使用mysql_native_password加密规则
default_authentication_plugin = mysql_native_password

  • 创建 Dockerfile 文件：

    FROM mysql:8.0
COPY my.cnf /etc/mysql/
RUN mkdir /var/lib/mysql-files
EXPOSE 3306
CMD ["mysqld"]

  • 在当前master目录下构建镜像：

    #构建镜像
$ docker build -t master/mysql .

  • 启动镜像

    $ docker run -itd --name mysql-master -p 33306:3306 -e MYSQL_ROOT_PASSWORD=123456 master/mysql

  • 主库授权

    本地连接主库：

    $ mysql -u root -P 33306 -p

    授权：

    # 创建角色
# create user ‘username’@‘host’ identified by ‘password’;
mysql> CREATE USER 'slave'@'%' IDENTIFIED BY 'slv123456';
# 授权
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' WITH GRANT OPTION;
#刷新权限
mysql> flush privileges;

  • 查看主容器数据库状态

    mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |     1178 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

    主库的 File 和 Position 参数在配置从库时候会用到。 binlog.000002 是binlog文件名。

创建从库

  • 创建一个slave目录，然后创建my.cnf 和 dockerfile 文件。

  • 创建 my.cnf 文件：

    [mysqld]
#[必须]服务器唯一ID，默认是1，一般取IP最后一段，这里看情况分配
server_id = 2
#[必须]启用二进制日志
log-bin = mysql-bin
# 使用mysql_native_password加密规则
default_authentication_plugin = mysql_native_password

  • 创建 Dockerfile 文件：

    FROM mysql:8.0
COPY my.cnf /etc/mysql/
RUN mkdir /var/lib/mysql-files
EXPOSE 3306
CMD ["mysqld"]

  • 在slave目录下构建镜像：

    #构建镜像
$ docker build -t master/mysql .

  • 启动容器

    $ docker run -itd --name mysql-slave -p 43306:3306 -e MYSQL_ROOT_PASSWORD=123456 slave/mysql

    如果是本地起的两个容器，需要使用--link 主库容器名:容器别名 命令，让从库容器访问到主库容器。

    $ docker run -itd --name mysql-slave --link mysql-master:mysql-master -p 43306:3306 -e MYSQL_ROOT_PASSWORD=123456 slave/mysql

  • 配置maser节点

    连接mysql-slave

    $ mysql -u root -P 43306 -p

    配置master节点

    mysql> change master to
    -> master_host='mysql-master',
    -> master_user='slave',
    -> master_log_file='mysql-bin.000002',
    -> master_log_pos=1178,
    -> master_port=3306,
    -> master_password='slv123456';
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> start slave;

    master_host=’x.x.x.x’ // 这里填 master 主机 ip，如果master是本地的容器，使用了--link命令后，可以用别名连接

    master_log_file=’mysql-bin.000002’, // 这里填写主库的 File 的值

    master_log_pos=1178, // 这里填写主库的 Position 的值。

    mysql> start slave; // 启动从服务器复制功能

    如果不小心配置错, 输入 mysql> stop slave; 然后重新录入一遍

  • 查看从库连接情况

    mysql> show slave status G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql-master
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 838
               Relay_Log_File: 219ff327d373-relay-bin.000002
                Relay_Log_Pos: 324
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

问题

本地容器网络互访

因为两个容器的网络是独立的，所以在容器中localhost只能访问到本容器中的。为了从库容器能访问到主库容器，可以在启动从库的容器时，使用--link命令。

$ docker run -itd --name mysql-slave --link mysql-master:mysql-master -p 43306:3306 -e MYSQL_ROOT_PASSWORD=123456 slave/mysql

进入容器，可以ping下

$ docker exec -it mysql-slave bash

root@920659abcaec:/# ping mysql-master
PING mysql-master (172.17.0.2) 56(84) bytes of data.
64 bytes from mysql-master (172.17.0.2): icmp_seq=1 ttl=64 time=0.076 ms
64 bytes from mysql-master (172.17.0.2): icmp_seq=2 ttl=64 time=0.238 ms
64 bytes from mysql-master (172.17.0.2): icmp_seq=3 ttl=64 time=0.130 ms

msyql 8 认证方式

报 caching_sha2_password 认证错误：

2020-11-04T10:01:00.787811Z 9 [ERROR] [MY-010584] [Repl] Slave I/O for channel '': error connecting to master 'slave@mysql-master:3306' - retry-time: 60 retries: 4 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061

2020-11-04T10:02:00.788882Z 9 [ERROR] [MY-010584] [Repl] Slave I/O for channel '': error connecting to master 'slave@mysql-master:3306' - retry-time: 60 retries: 5 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061

2020-11-04T10:03:00.789919Z 9 [ERROR] [MY-010584] [Repl] Slave I/O for channel '': error connecting to master 'slave@mysql-master:3306' - retry-time: 60 retries: 6 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061

因为我们这里测试使用的是MySQL 8.0，在mysql8 之前的版本中加密规则是mysql_native_password,而在mysql8之后,加密规则是caching_sha2_password, 解决问题方法有两种,一种是升级navicat驱动,一种是把mysql用户登录密码加密规则还原成mysql_native_password. 我们这里使用旧的加密规则，在主库修改：

mysql> alter user 'slave'@'%' identified by 'slv123456' password expire never;
Query OK, 0 rows affected (0.01 sec)

mysql> alter user 'slave'@'%' identified with mysql_native_password by 'slv123456';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

或者，在主库的my.cnf中添加下面命令后重启：

default_authentication_plugin=mysql_native_password

再查看从库的状态：

mysql> show slave status G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql-master
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 838
               Relay_Log_File: 219ff327d373-relay-bin.000002
                Relay_Log_Pos: 324
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

读写分离

28 | 读写分离有哪些坑？

MySQL 技术内幕：主从同步和主从延时

https://www.cnblogs.com/a-phper/p/10313967.html

读写分离一般分两种方式：直连方式和代理方式。

  • 直连方式：客户端（client）主动做负载均衡，这种模式下一般会把数据库的连接信息放在客户端的连接层。

    image-20201105104751544

  • 代理方式：在MySQL和客户端之间有一个中间代理层proxy，客户端只连接proxy， 由proxy根据请求类型和上下文决定请求的分发路由。常见的MySQL中间件有：mysql-proxy（官方）、MyCat、atlas、Sharding-JDBC......

    image-20201105104935280

两种方案的有确定：

  • 直连方式：性能较好，因为直连数据库。但是会增加客户端数据库读写代码复杂性和代码量，以及出现主备切换、库迁移等操作的时候，可能需要客户端调整数据库连接信息、重启。
  • 代理方式：对客户端比较友好。客户端不需要关注后端细节，连接维护、后端信息维护等工作，都是由proxy完成的。但引入了proxy架构，性能较直连方式差，同时需要单独对proxy服务/集群进行维护。

但是，不论使用哪种架构，都存在主从延迟问题。

延迟分析

延迟的原因一般有：

  • 主从服务器处于不同的网络之中，由于网络延迟导致；
  • 主从服务器的硬件配置不同，从服务器的硬件配置（包括内存，CPU，网卡等）远低于主服务器；
  • 主库上有大量的写入操作，导致从库无法实时重放主库上的binlog；
  • 主库上存在着大事务操作或者慢SQL，导致从库在应用主库binlog的过程过慢，形成延迟；
  • 数据库实例的参数配置问题导致，如：从库开启了binlog，或者配置了每次事务都去做刷盘操作；

分析过程

分析binlog文件大小、时间戳

  • 连接master，查看master binlog位置
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+

  • 查看master binlog 文件列表

    root@641a6f1ade6a:/var/lib/mysql# cd /var/lib/mysql/
root@641a6f1ade6a:/var/lib/mysql# 
root@641a6f1ade6a:/var/lib/mysql# ls -l | grep mysql-bin
-rw-r----- 1 mysql mysql      179 Nov  4 10:45 mysql-bin.000001
-rw-r----- 1 mysql mysql  3104223 Nov  4 10:46 mysql-bin.000002
-rw-r----- 1 mysql mysql     1029 Nov  5 03:45 mysql-bin.000003
-rw-r----- 1 mysql mysql      397 Nov  5 03:47 mysql-bin.000004
-rw-r----- 1 mysql mysql       76 Nov  5 03:45 mysql-bin.index

    如果master的binlog文件突然变得很大，比如平常的是1G，最近的binlog文件飙升到10G，那么要排查业务是否有大量的操作，如批量插入等业务。

  • 连接slave，查看slave 的状态，查看当前处理master的哪个binlog文件，查看参数：Relay_Master_Log_File。如果当前slave消费的binlog文件和master的最新binlog文件不是同一个，那说明从库消费慢导致延误。

    mysql> show slave status G

    image-20201105122559500

总结：

  1. 如果master的binlog文件突然变得很大，比如平常的是1G，最近的binlog文件飙升到10G，那么要排查业务是否有大量的操作，如批量插入等业务。
  2. 如果当前slave消费的binlog文件落后于master的最新binlog文件，那说明从库消费慢。

分析 slave 执行情况

一般因为机器性能、从库参数配置从而导致从库延迟情况，可以查看从库的执行，从Read_Master_Log_Pos 、Exec_Master_Log_Pos、Seconds_Behind_Master参数中看到延迟情况

mysql> show slave status G

image-20201105115149660

Seconds_Behind_Master参数，这个参数表示的是从库上的IO线程和SQL线程相差的时间，然后根据该参数值判断，这个值只是初步判断，不能由这个值来下结论，有如下几种情况：

  • 0：表示无延迟，理想状态；
  • NULL：表示从库上的IO线程和SQL线程中，有某一个线程出现问题，可以再次查看Slave_IO_Running和Slave_SQL_Running的值是否都为Yes；
  • 大于0：表示主从已经出现延迟，这个值越大，表示从库和主库之间的延迟越严重；

总结：

从从库的slave status 中的Read_Master_Log_Pos 、Exec_Master_Log_Pos、Seconds_Behind_Master参数，可以分析从库的处理情况。如果处理慢，有可能是：

  1. binlog文件过大，导致消费不过来
  2. 从库有慢查询sql、锁、事务等耗时操作导致从库压力大
  3. 机器性能差，查看I/O情况
  4. 从库配置不合适

优化方案

  1. 优化表结构：
    • 建立/优化索引
    • 拆分大表
  2. 业务优化：
    • 优化慢查询、减少事务
    • 避免在业务繁忙期执行批量操作
    • 根据业务的实时性要求，改查主从库；实时性要求高的直接查主库。
  3. 机器性能优化：选择高性能机器，堆叠CPU、固态硬盘、内存
  4. 调整mysql集群架构和参数

资料

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/zhaooo/p/13973573.html