# 部署

MySQL 常见的部署架构:

  • 单实例
  • 主从集群
  • 主从+MHA 集群
  • 组复制
  • Cluster 集群
  • PXC 集群

# 单实例

  • 运行 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 [email protected]'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 类型的数据,避免其乱码
              -l                          # --lock-tables ,导出数据库时,给所有数据表加上只读锁
              --skip-lock-tables          # 默认启用了 -l 选项,可用该选项跳过加锁
    
    • 导出的 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
    

# 主从集群

  • 架构:

    • 由一个 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 发生故障时,自动进行主从切换。

# 组复制

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

  • 一个 Group 包含一组 MySQL 实例节点,通过 XCOM 组件进行通信。
    • XCOM 基于 Paxos 算法实现所有节点的共识。
      • 只需要大部分节点同意,一个事务就能成功提交。允许少部分节点故障。
      • 支持动态加入、删除节点,group 会自动调整。
    • 用户发出一些事务请求时,XCOM 会将这些事务在所有节点上以相同的顺序执行,因此实现了顺序一致性。
  • 存在两种工作模式:
    • 单主模式(single primary mode)
      • :只有一个节点为 PRIMARY 角色,允许处理用户的写请求。其它节点为 SECONDARY 角色,设置为 read_only 。
      • 如果 primary 节点故障,则会自动选举出一个新的 primary 节点。
      • MGR 本身不提供故障转移的功能,客户端可通过 MySQL Router 访问新的 primary 节点。
    • 多主模式(multi primary mode)
      • :所有节点都可处理用户的读、写请求。

# Cluster 集群

  • 由 MySQL 官方发布。
  • 原理:
    • 运行多个 MySQL 实例,将数据表分片之后存储到不同实例。
    • 采用 NDB Cluster 存储引擎,将数据存储在内存中。
  • 优点:
    • 实现了高可用。
  • 缺点:
    • 占用很多内存。

# PXC 集群

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