# 部署

MySQL 常见的部署方案:

  • 单实例
  • 主从集群
  • MGR 集群

# 版本

MySQL 存在多个分支:

  • MySQL

    • v1.0
      • 1995 年由 MySQL 公司发布。
      • 2008 年 MySQL 公司被 Sun 公司收购,2010 年 Sun 公司被 Oracle 公司收购。
    • v5.5
      • 2010 年发布。
      • 将默认的数据库引擎从 MyISAM 改为 InnoDB 。
      • 支持半同步复制。
      • 增加字符集 utf16、utf32、utf8mb4 。
    • v5.6
      • 2013 年发布。
      • 支持全文索引。
      • 增加 Online DDL 功能。
    • v5.7
      • 2015 年发布。
      • 增加 JSON 数据类型。
    • v8.0
      • 2018 年发布,跳过了 v6、v7 版本。
      • 默认字符集从 latin1 改为 utf8mb4 。utf8mb4 的默认字符序从 utf8mb4_general_ci 改为更新版本的 utf8mb4_0900_ai_ci 。
      • 实现 DDL 操作的原子性。
      • 密码的存储方式从 mysql_native_password 改为 caching_sha2_password 。老版本的客户端可能不兼容,导致不能登录。可以修改存储方式:
        ALTER USER test@'%' IDENTIFIED WITH mysql_native_password BY '******';
        
  • MariaDB

    • 2010 年,因为抗议 Oracle 公司的收购,MySQL 的创始人之一 Monty Widenius 从 MySQL v5.5 分叉出 MariaDB ,作为开源项目。
  • Percona

    • 与 MySQL 完全兼容,还提供了 XtraDB 引擎。
  • Drizzle

    • 与 MySQL 的差异较大。

# 单实例

  • 运行 mysqld 进程,即可作为 MySQL 服务器提供服务。
    • mysqld 默认监听 TCP 3306 端口,供 MySQL 客户端连接。
    • 默认是使用 mysqld_safe 脚本启动 mysqld 进程,当其异常退出时会自动重启。

# 部署

  • yum 默认源的 MySQL 版本很老,建议这样安装:

    wget https://www.percona.com/downloads/Percona-Server-5.7/Percona-Server-5.7.28-31/binary/redhat/7/x86_64/Percona-Server-5.7.28-31-rd14ef86-el7-x86_64-bundle.tar
    tar -xvf Percona-Server-5.7.26-29-r11ad961-el7-x86_64-bundle.tar
    yum install -y Percona-Server*.rpm
    rm -f Percona-Server*
    systemctl start mysqld
    systemctl enable mysqld
    

    启动之后要修改密码:

    cat /var/log/mysqld.log | grep password   # 查看初始密码
    mysql -u root -p                          # 登录,输入初始密码
    SET password for root@'localhost' = password('******');  # 设置新密码
    
  • 或者用 docker-compose 部署:

    version: "3"
    
    services:
      mysql:
        container_name: mysql
        image: percona:5.7.34
        restart: unless-stopped
        environment:
          MYSQL_ROOT_PASSWORD: ******     # root 用户的密码。必须设置该环境变量
          MYSQL_ROOT_HOST: localhost      # root 用户的登录地址
          # MYSQL_DATABASE: db1           # 创建一个数据库
          # MYSQL_USER: leo               # 创建一个用户,并自动授予该用户对上述数据库的全部权限
          # MYSQL_PASSWORD: ******
        ports:
          - 3306:3306
        volumes:
          - /etc/localtime:/etc/localtime:ro
          - ./my.cnf:/etc/my.cnf          # 挂载配置文件
          - ./data:/var/lib/mysql         # 挂载数据目录
    
    • 该容器内默认以 mysql 用户(uid 为 999 ,与宿主机的 polkitd 用户的 uid 相同)运行服务器,对于挂载目录可能没有访问权限(除非使用挂载卷),需要先在宿主机上修改文件权限:
      mkdir data
      touch my.cnf
      chown -R 999:999 .
      

# 客户端

# 安装

  • 安装的 MySQL 服务器会自带客户端,也可单独安装 MySQL 客户端:

    yum install mysql
    
  • 或者运行 Docker 镜像:

    docker run -it --rm percona:5.7.26-centos mysql -h 127.0.0.1 -u root -p
    

# mysql

:一个命令行工具,用于启动 MySQL 客户端,连接到 MySQL 服务器。

  • 命令:

    mysql                     # 启动 MySQL 客户端,登录成功之后会打开客户端的终端
          -h <host>           # 要连接的 MySQL 服务器的 IP 地址(默认是 localhost)
          -P <port>           # 连接的端口号(默认为 3306)
          -u <user>           # 连接的用户名(默认为 root)
          -p                  # 以带密码的方式连接(接下来会提示输入密码)
          --password=******   # 以带密码的方式连接,并传入密码
          -D <db>             # 切换到一个 database
    
          -e 'source 1.sql;'  # 不打开客户端的终端,只是执行 SQL 命令
    
    • 刚安装 mysql 服务器时,执行 mysql -u root -p 即可登录。
    • 如果不使用 -p 选项,则默认以免密的方式连接,就不能通过 MySQL 服务器的身份认证。
  • 进入 MySQL 客户端的终端之后,可以执行 SQL 命令,也可以执行内置命令。

    • 执行 SQL 命令时必须以分号 ; 结尾,执行内置命令时则不必。
    • 执行 SQL 命令时,有时显示结果是一个字段太多的表格,排版混乱、难以阅读。可以在执行的 SQL 命令的末尾加上 \G ,将显示结果从横向表格变成纵向列表,方便阅读。例如:SELECT * FROM mysql.user\G;
  • 常用的内置命令:

    connect [db]   # 重新连接到 MySQL 服务器
    status         # 显示 MySQL 服务器的状态
    exit           # 退出 MySQL 客户端(相当于 quit)
    

# mysqladmin

:一个命令行工具,用于管理 MySQL 服务器,使用时不需要进入 MySQL 客户端的终端。

  • 命令:
    mysqladmin [OPTIONS] [command]...   # 连接到 MySQL 服务器,执行某种操作
              -h <host>         # 输入主机名等信息,连接到 MySQL 服务器
              -P <port>
              -u <user>
              -p
              --password=******
    
              password ******   # 修改该用户的密码
              create <db>       # 创建一个 datebase
              drop <db>         # 删除一个 datebase
              processlist       # 列出 MySQL 服务器上的客户端线程
              kill <id>...      # 终止一个线程
              status            # 查看 MySQL 服务器的状态
              shutdown          # 关闭 MySQL 服务器
    
              -c 10 -i 1        # 重复执行 10 次操作,每次间隔 1s
    

# 备份数据

MySQL 备份数据的几种方式:

  • 拷贝 MySQL 的数据目录。
    • 优点:
      • 全量备份,冷备份。
      • 属于物理备份,速度快。
    • 缺点:
      • 数据文件比实际数据占用更多磁盘空间。
      • 备份时,需要停止 MySQL ,以免有文件正在被修改。
  • 用 XtraBackup 备份。
    • 优点:
      • 物理备份,热备份。比直接拷贝 MySQL 的数据目录更可靠。
  • 用 mysqldump 等命令导出、导入数据。
    • 优点:
      • 全量备份,热备份。
    • 缺点:
      • 导出时,默认给 MySQL 加只读锁,停止写入,以免遗漏数据。
      • 导入时,需要 MySQL 停止写入,以免数据冲突。
      • 导出、导入的速度慢。
  • 启用 binlog 。
    • 优点:
      • 增量备份,热备份,接近实时备份。
    • 缺点:
      • 恢复时比较麻烦,需要手动转换成 .sql 文件再导入。
  • 启用主从复制。
    • 优点:
      • 全量备份,热备份,接近实时备份。
    • 缺点:
      • 需要多部署一个 MySQL 实例。

# 导出

  • 在 shell 终端中,可用 mysqldump 命令导出数据库,保存为 sql 脚本。如下:

    mysqldump
              -h <ip> -u <user> -p
              -A            > dump.sql    # 导出所有数据库
              -B <db>...    > dump.sql    # 导出多个数据库
              <db> [tb]...  > dump.sql    # 导出某个数据库中的某个表
              --hex-blob                  # 以十六进制导出 BINARY、BLOB 类型的数据,避免其乱码
              --no-tablespaces            # 取消导出 CREATE TABLESPACE 语句。建议添加该选项,允许非 root 账号执行 myqsldump
              --skip-lock-tables          # 导出数据库时,默认会给所有数据表加上只读锁,可用该选项跳过加锁
              --skip-extended-insert      # 导出数据表时,默认会将几千个数据行合并成一条 INSERT 语句,可用该选项取消合并,为每个数据行导出一条 INSERT 语句
    
    • 导出的 sql 脚本的内容大致如下:
      CREATE DATABASE `db1`;
      USE `db1`;
      
      DROP TABLE IF EXISTS `tb1`;     -- 删除同名表
      CREATE TABLE `tb1` ...;         -- 创建表
      LOCK TABLES `tb1` WRITE;        -- 锁定表
      INSERT INTO `tb1` VALUES ...;   -- 逐行插入数据
      INSERT INTO `tb1` VALUES ...;
      INSERT INTO `tb1` VALUES ...;
      UNLOCK TABLES;                  -- 解锁表
      
    • 执行该 sql 脚本,即可重建数据库、数据表并导入数据。
    • 导出的 sql 脚本可以再用 zip 压缩,压缩率大概为 30% 。
  • 可用 mysql 命令进入 MySQL 客户端的终端,然后导出数据库,保存为文本文件。如下是导出 csv 格式的文件:

    USE db1;
    SELECT * FROM tb1 into outfile '/var/lib/mysql-files/db1-tb1.csv'
        fields terminated by ',' enclosed by '"'
        lines terminated by '\n';
    
    • MySQL 服务器限制了该导出目录,可以用 SHOW variables LIKE 'secure_file_priv'; 查看。

# 导入

  • 在 shell 终端,直接导入 sql 脚本:

    mysql -u <user> -p < /home/dump.sql
    
  • 用 mysql 命令进入 MySQL 客户端,然后用 source 命令导入 sql 脚本:

    source /home/dump.sql
    
    • 建议先禁用 binlog、事务,提高导入速度:
      SET session sql_log_bin = off;
      SET autocommit = 0 ;
      
  • 用 mysql 命令进入 MySQL 客户端,然后从文本文件导入数据。如下是导入 csv 格式的文件:

    load data infile '/var/lib/mysql-files/db1-tb1.csv' into table tb1
        fields terminated by ',' enclosed by '"'
        lines terminated by '\n';
    
    • 默认是导入 MySQL 服务器主机上的文件,改成 load data local infile 就是导入 MySQL 客户端主机上的文件。

# xtrbackup

:Percona 公司发布的一个 MySQL 备份工具。

  • 原理:
    • 备份时,给数据表加备份锁(backup lock),然后拷贝磁盘中的数据文件、 InnoDB 事务日志。
      • 默认是全量备份,但支持根据 LSN 进行增量备份。
    • 恢复时,解压数据目录,然后根据事务日志恢复最新的一些事务。
  • Percona Server 5.6 版本引入了备份锁(backup lock),它会禁止所有表的 DDL 、禁止非事务引擎的数据表的 DML 。

示例:

  • 运行 XtraBackup 的 Docker 镜像:

    docker run -it --rm -v /var/lib/mysql:/var/lib/mysql -v /tmp:/tmp percona/percona-xtrabackup:2.4 bash
    
    • v2 版本之后跳到发布 v8 版本,不兼容备份 MySQL 8.0 之前的数据库。
  • 生成备份文件:

    xtrabackup
        --backup
        --datadir /var/lib/mysql    # MySQL 的原数据目录
        --target-dir /tmp           # 保存备份文件的目标目录
        --compress                  # 将备份文件压缩
    
        -H <host>
        -P <port>
        -u <username>
        -p
    
        --databases         <name>  # 筛选要备份的数据库
        --tables            <name>  # 筛选要备份的数据表
        --databases-exclude <name>
        --tables-exclude    <name>
    
    • 压缩之后可用以下命令解压:
      xtrabackup --decompress --target-dir=/tmp
                --remove-original   # 解压之后删除压缩包
      
    • 也可以用 tar、zip 等命令压缩。
  • 应用事务日志,恢复数据目录:

    xtrabackup --prepare --target-dir=/tmp
    

# canal

:一个用于 MySQL 增量同步的工具,由阿里巴巴公司开源,采用 Java 语言开发。

  • 官方文档 (opens new window)
  • 原理:
    1. 让源 MySQL 开启 binlog ,并设置 binlog_format = row
    2. 运行 canal ,以 MySQL slave 的身份,从源 MySQL 获取 binlog ,解析成 JSON 格式的数据,然后将 binlog 写入 Kafka、ES 等数据端。
    3. 编写业务代码去处理 binlog ,实现增量同步等需求。
  • 例:从 MySQL 获取 binlog ,写入 Kafka
    docker run -d \
        -e JAVA_OPTS="-Xmx1G -Xms1G" \
    
        -e canal.instance.master.address=10.0.0.1:3306 \  # 连接 MySQL
        -e canal.instance.dbUsername=****** \
        -e canal.instance.dbPassword=****** \
        -e canal.instance.filter.regex=db1.tb1,db2\\.test.* \   # 获取 binlog 时,筛选 db1 数据库中的 tb1 表,再筛选 db2 数据库中,名称匹配 test.* 的所有表
        # -e canal.instance.master.timestamp=xx \   # 获取从某个 Unix 时间戳开始的 binlog ,单位为毫秒。如果不指定,则默认从最新一条 binlog 开始获取
    
        -e canal.serverMode=kafka \           # 写入 Kafka
        -e kafka.bootstrap.servers=10.0.0.1:9092 \
        -e kafka.compression.type=lz4 \
        -e canal.mq.dynamicTopic=.*\\..*  \   # 根据 MySQL 源 db、table 的名称,动态分配 topic 的名称,格式为 ${db}_${table}
        # -e canal.mq.topic=xx \              # 将全部数据都写入一个指定名称的 topic
        # -e canal.mq.partitionsNum=xx \      # 如果 topic 存在多个分区,则分散写入几个分区。默认写入所有分区
        # -e canal.mq.partitionHash=xx \      # 根据表中某个字段的值,计算哈希值,从而决定写入第几个分区
    
        --name canal-server \
        canal/canal-server:v1.1.6
    
    • 上例使用 \\ 是为了在 shell 命令中转义 \
    • 查看日志:
      docker exec -it canal-server tail -f canal-server/logs/example/*.log
      

# 主从集群

  • 架构:

    • 由一个 MySQL 实例作为 master ,负责写操作。
    • 由一个或多个 MySQL 实例作为 slave ,负责读操作,并自动复制 master 的数据。
      • 支持级联复制,让一个 slave 作为其它 slave 的 master 。
  • 优点:

    • 可以备份数据,每个节点都保存了数据库的一个副本。属于热备份。
    • 可实现读写分离,分散单个实例的负载压力。

# 原理

主从复制的流程:

  1. 让 master 将所有写操作记录到 binlog 中。
  2. slave 的 I/O 线程通过一个账号连接到 master ,获取 binlog 的内容,保存到自己的 relaylog(中继日志)中。
  3. slave 的 SQL 线程将 relaylog 转换成 SQL 命令并执行,从而与 master 保持一致。

主从复制的策略:

  • 异步复制
    • :master 一边执行事务,一边用另一个线程传递 binlog 给 slave 。
    • 复制速度最快,但备份数据不可靠。当 master 发生故障时,可能还来不及将 binlog 复制到 slave 。
    • MySQL 主从复制默认采用异步复制,采用半同步复制则需要安装插件,采用全同步复制则需要部署组复制集群。
  • 半同步复制
    • :master 每执行一个事务,只要将 binlog 传给至少一个 slave ,就可以执行下一个事务。
    • 复制速度较快,备份数据足够可靠。
  • (全)同步复制
    • :master 每执行一个事务,必须将 binlog 传给所有 slave ,然后才执行下一个事务。
    • 复制速度最慢,备份数据非常可靠。

# 部署

  1. 在 master 的配置文件中加入:
    [mysqld]
    server_id = 1
    log_bin   = mysql-bin
    
  2. 启动 master ,登录后执行以下命令:
    CREATE user slave@'%' identified by '******';
    grant replication slave on *.* to slave@'%';
    flush privileges;
    
    SELECT @@server_id;     -- 显示当前 mysql 的 id
    SHOW master status;     -- 显示 master 的状态,查询到当前的 binlog 位置
    SHOW slave hosts;       -- 显示所有 slave 的地址
    
  3. 在 slave 的配置文件中加入:
    [mysqld]
    server_id = 2
    log_bin   = mysql-bin
    relay_log = relay-bin       # 指定 relay_log 的名称,默认会包含随机数
    read_only = on              # 让服务器采用只读模式。此时依然允许修改 temporary 表、允许 root 用户修改任何表、允许作为 slave 节点复制执行 binlog
    # sync_relay_log    = 10000 # 每当 slave 收到 n 个事件,就将中继日志写入磁盘。取值为 0 ,则由操作系统自动 flush 到磁盘。如果 master、slave 同时异常终止,未写入磁盘的事件就会丢失
    # skip_slave_start  = off   # 启动时,是否跳过 start slave 。默认为 off ,因此即使执行了 stop slave ,重启服务器时也会继续复制
    # slave_skip_errors = off   # 如果 slave 执行复制的事务出错,是否跳过。取值如下:
            # off :默认值。会立即中断复制,需要用户修复问题,手动再执行 start slave
            # all :跳过所有错误,继续复制。此时 slave 不会打印报错日志,与 master 的差异可能越来越大
            # <error_codes> :跳过指定的错误,比如 1007,1008,1050
    
    # replicate-do-db=db1               # 让 slave 只复制指定的数据库,其它的默认不复制,但依然会接收全部 binlog 。该参数只能指定一个名称,但可以多次使用该参数
    # replicate-do-db=db2
    # replicate-ignore-db=db1           # 让 slave 不复制指定的数据库,其它的默认复制
    # replicate-ignore-db=db2
    # replicate-wild-do-table=db1.tb1%  # 只复制指定的数据表。支持模糊匹配,语法与 LIKE 相同
    # replicate-wild-ignore-table=test%.test%
    
  4. 启动 slave ,登录后执行以下命令:
    change master to                          -- 指定 master 节点
        master_host='10.0.0.1',
        master_port=3306,master_user='slave',
        master_password='******',
        master_log_file='mysql-bin.000003',   -- 从 master 的哪个 binlog 文件开始复制
        master_log_pos=576052,                -- 从 binlog 的哪个 position 开始复制
        master_deply=0;                       -- 复制延迟,让 slave 只执行 n 秒之前的事务
    start slave;                              -- 让 slave 开始复制
    SHOW slave status;                        -- 显示 slave 的状态
    
    -- stop slave;                            -- 停止复制
    -- reset slave;                           -- 删除所有中继日志,重新开始复制
    
    • 开始主从复制之前,应该先用 mysqldump 等工具全量复制一次数据到 slave ,避免主从差异过大。
    • 可以在全量复制期间,给 master 加全局只读锁,从而使 master_log_pos 保持不变,方便 slave 同步。
      FLUSH TABLES WITH READ LOCK;
      
      • 如果 slave 使用比全量复制更早的 master_log_pos 位置开始复制,则可能重复插入数据,导致复制失败。
      • 如果 slave 使用更加新的 master_log_pos ,则会遗漏事务。

# MHA

  • MHA(Master High Availability):一个实现高可用的工具。当主从集群的 master 故障时,自动进行主从切换。
  • 官方文档 (opens new window)
  • 优点:
    • 手动主从切换需要几十分钟,而自动主从切换只需要几十秒。
  • 缺点:
    • 主从切换之后,master 的 IP 变化,需要让客户端连接到新 IP 的 master 节点。解决办法是创建一个 VIP ,或部署一个 mysql proxy ,反向代理 MySQL 。

# 读写分离

  • 如果 master 节点的负载很大,接近瓶颈,则可考虑将客户端的读请求分散到 slave 节点。
  • 做法:如果某些客户端只需要读取 MySQL ,则让它们只连接 slave 的 IP 。如果某些客户端需要写 MySQL ,则让它们连接 master 的 IP 。
  • 不过,让客户端分别连接 master、slave 的 IP 存在以下问题:
    • 需要给不同客户端维护不同的配置参数,比较麻烦。
    • 发生主从切换之后,master、slave 的 IP 变化,需要更新客户端连接的 IP 地址,很麻烦。
  • 为了解决上述问题,可部署一个 mysql proxy 工具,一边供客户端连接,一边反向代理到当前的 master IP 或 slave IP 。

# 主主复制

  • 主主复制(Master Master Replication):一种 MySQL 集群方案。
  • 原理:部署两个 MySQL 节点,互相进行主从复制。两个节点都担任 master 角色,可供用户读写。
  • 缺点:MySQL 节点之间同步数据的方式只是复制事务,不能保证分布式集群的一致性。例如两个 MySQL 同时写入同一个表时容易主键重复、同时修改同一个表时容易同步失败。因此主主复制集群一般不可用。

# MGR 集群

  • MGR(MySQL Group Replication,组复制):是 MySQL 5.7 引入的插件,用于部署分布式的 MySQL 集群。

  • 官方文档 (opens new window)

  • 一个 Group 包含一组 MySQL 节点,通过 XCOM 组件进行通信。

    • XCOM 基于 Paxos 算法实现所有节点的共识。
      • 只需要超过半数的节点同意,一个事务就能成功提交。允许少部分节点故障。
      • 支持动态加入、删除节点,group 会自动调整。
    • 客户端发出一些事务请求时,XCOM 会将这些事务在所有节点上以相同的顺序执行,因此实现了顺序一致性。
  • 存在两种工作模式:

    • 单主模式(single primary mode)
      • :只有一个节点为 primary 角色,能处理客户端的读写请求。其它节点为 secondary 角色,只能处理客户端的读请求。
      • 如果 primary 节点故障,则集群会自动选出一个 secondary 节点,担任新的 primary 节点。
    • 多主模式(multi primary mode)
      • :所有节点都可处理客户端的读、写请求。
  • 通常在 MGR 集群之外再部署一个 mysql-router 代理,供客户端连接。

    • mysql-router 会连接到 MGR 集群,自动获取当前的 primary IP 和 secondary IP ,并将客户端的读写请求转发到这些 IP 。
  • 传统的 MySQL 集群大多采用主从方案,与之对比,MGR 集群的优缺点如下:

    • 优点:
      • 主从切换可能失败,可能丢失事务。而 MGR 基于 Paxos 算法控制分布式系统,更可靠。
    • 缺点:
      • 主从集群至少需要部署 2 个 MySQL 节点,而 MGR 集群至少需要部署 3 个 MySQL 节点,成本更高。
      • 存在一些功能限制。比如存储引擎必须采用 InnoDB ,每个表都必须定义主键,binlog_format 必须为 row 。

# Cluster 集群

  • :一种 MySQL 集群方案,由 MySQL 官方发布。
  • 官方文档 (opens new window)
  • 原理:
    • 运行多个 MySQL 节点,将数据表分片之后存储到不同节点。
    • 采用 NDB Cluster 存储引擎,将数据存储在内存中。
  • 优点:
    • 实现了高可用。
  • 缺点:
    • 占用很多内存。

# PXC 集群

  • :一种 MySQL 集群方案,全称为 Percona XtraDB Cluster ,由 Percona 公司发布。
  • 官方文档 (opens new window)
  • 原理:
    • 将 MySQL 部署至少 3 个独立工作的节点,每个节点存储完整的一份数据。
    • 客户端可以读写任一节点,但是每次写入数据时会同时写入到所有节点。
  • 优点:
    • 通过多节点,实现了高可用。
    • 实现了多个节点之间的无延迟同步、强一致性。
  • 缺点:
    • 只支持 InnoDB 引擎。
    • 需要部署多个节点,冗余大,成本高。
    • 写入数据时较慢。