部署

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 。
  • MySQL 官方开源的 mysql-router (opens new window) ，能自动路由客户端的读写请求，分别转发到 master 节点、slave 节点。
  • Persona 公司开源的 proxysql (opens new window)
  • 阿里巴巴公司开源的 MyCat (opens new window)

主主复制

• 主主复制（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 引擎。
  • 需要部署多个节点，冗余大，成本高。
  • 写入数据时较慢。