将 Looker 后端数据库迁移到 MySQL

默认情况下，Looker 使用 HyperSQL 内存数据库来存储其配置、用户和其他数据。在繁忙的实例上，此数据库的大小可能会增长到数 GB，这可能会导致性能问题、Java 内存压力和启动时间过长。

在客户托管的实例上，当内部 HyperSQL 数据库的大小超过 600 MB 时，我们建议您将 HyperSQL 数据库替换为完整的 MySQL 数据库后端。如需检查 HyperSQL 数据库的大小，请查看 looker.script 文件的大小：

cd looker
cd .db
ls -lah

如果 looker.script 文件的大小超过 600 MB，请按照以下步骤迁移到外部 MySQL 数据库。

预配 MySQL 实例和用户

您可以预配 MySQL 8.4.X（推荐）或 MySQL 8.0.X 实例作为后端。不支持 8.0 之前的 MySQL 版本。

Looker 26.6 及更高版本支持将 MySQL 8.4.X 用作 Looker 后端数据库。对于使用 MySQL 8.0.X 作为 Looker 后端数据库的客户托管实例，建议您在将 Looker 实例更新到 26.6 版或更高版本后，立即更新到 MySQL 8.4.X。

在 AWS RDS 中，db.m5.large 类的实例可能足以作为单个 Looker 实例的后端。尽管数据库的实际用量可能在 5-10 GB 范围内，但最好预配 100-150 GB 的 SSD 存储空间，因为预配的 IOPS 取决于请求的存储空间量。

MySQL 8.4.X

从 Looker 26.6 开始，Looker 支持使用 MySQL 8.4.X 作为内部数据库。对于 MySQL 8.4.X，您可以使用以下任一 MySQL 身份验证插件，如以下部分所述：

使用 caching_sha2_password

在 MySQL 8.4.X 中，默认身份验证插件为 caching_sha2_password。Looker 提供了两种使用 caching_sha2_password 插件的方法：

  1. 为内部数据库使用 SSL 连接时：不需要 RSA 公钥。Looker 会使用提供的用户凭据安全地连接到数据库。请参阅本页面上的创建数据库凭证文件部分，设置与数据库的 SSL 连接。
  2. 如果未在启用 SSL 的情况下进行连接：当提供给 Looker 的用户账号使用 caching_sha2_password 插件时，Looker 默认会使用 --get-server-public-key 选项连接到内部数据库。如果未启用 SSL，请确保网络环境安全。

无论采用哪种方式使用 caching_sha2_password 插件，都请通过发出以下语句来设置用户：

CREATE USER 'DB_username' IDENTIFIED WITH caching_sha2_password BY 'password';

替换以下内容：

  • DB_username：用户名
  • password：唯一且安全的密码

使用 mysql_native_password

Looker 可与 mysql_native_password 插件搭配使用，通过 JDBC 驱动程序向 MySQL 数据库进行身份验证。如需让 MySQL 8.4.X 与 mysql_native_password 插件搭配使用，您必须执行以下额外步骤：

  1. 配置 MySQL 数据库以启用 mysql_native_password 插件。这可以通过多种方式完成，具体取决于 MySQL 数据库的部署方式以及您对配置的访问权限类型：

    1. 使用 --mysql-native-password=ON 启动 MySQL 服务器。

    2. 在 my.cnf 配置文件中设置该属性：

      [mysqld]
mysql_native_password=ON

    3. 如果 MySQL 实例托管在 Google Cloud、AWS 或 Azure 上，则应自动启用 mysql_native_password 插件。如需了解详细说明，请参阅提供商文档。

  2. 创建用户：

    CREATE USER 'DB_username' IDENTIFIED WITH mysql_native_password BY 'password';

    替换以下内容：

    • DB_username：用户名
    • password：唯一且安全的密码

MySQL 8.0.X

在 MySQL 8.0.X 中，默认身份验证插件为 caching_sha2_password。Looker 使用 mysql_native_password 插件尝试通过 JDBC 驱动程序向 MySQL 数据库进行身份验证。为使 MySQL 8.0.X 正常运行，您必须执行以下额外步骤：

  1. 将 MySQL 数据库配置为使用 mysql_native_password 插件。您可以通过多种方式完成此操作，具体取决于 MySQL 8 数据库的部署方式以及您对配置的访问权限类型：

    • 使用标志 --default-auth=mysql_native_password 启动进程

    • 在 my.cnf 配置文件中设置该属性：

      [mysqld]
default-authentication-plugin=mysql_native_password

    • 如果您的数据库实例通过 AWS RDS 托管，请通过应用于此数据库实例的 RDS 参数组设置 default_authentication_plugin 参数。

  2. 创建用户：

    CREATE USER 'DB_username' IDENTIFIED WITH mysql_native_password BY 'password';

    替换以下内容：

    • DB_username：用户名
    • password：唯一且安全的密码

调优 MySQL

在 MySQL 实例上调整以下设置。

增加数据包大小上限

MySQL 的默认 max_allowed_packet 大小对于数据库迁移来说太小，可能会导致迁移失败并出现 PACKET_TOO_LARGE 错误。将 max_allowed_packet 设置为允许的最大值 1073741824：

max_allowed_packet = 1073741824

设置临时表算法

MySQL 8 处理内部临时表的方式与之前的版本不同。默认设置可能会导致运行 Looker 所需的一些查询时出现问题，尤其是对于具有许多用户和项目的 Looker 实例。最佳做法是设置以下全局服务器设置：

internal_tmp_mem_storage_engine = MEMORY

配置字符集

设置以下默认参数以使用 UTF8mb4，该参数支持 UTF8 字符集。请参阅文章在 MySQL 中，切勿使用“utf8”。使用“utf8mb4”，了解我们为何建议在 MySQL 中使用 UTF8mb4 而非 UTF8。

character_set_client = utf8mb4
character_set_results = utf8mb4
character_set_connection = utf8mb4
character_set_database = utf8mb4
character_set_server = utf8mb4
collation_connection = utf8mb4_general_ci
collation_server = utf8mb4_general_ci

在 Amazon RDS 实例上，您可以通过创建或修改参数组并修改相应设置来应用此设置。我们建议您复制当前参数组，并在副本上进行更改，尤其是在多个 RDS 实例之间共享参数组时。保存参数组后，将其应用于 RDS 实例。可能需要重启。

设置副本方案

Looker 依赖于需要 mixed 或 row binlog 的功能。如果您托管自己的 MySQL 实例，请通过发出以下命令之一将 binlog_format 设置为 mixed 或 row：

SET GLOBAL binlog_format = 'MIXED';

或

SET GLOBAL binlog_format = 'ROW';

创建数据库并授予用户权限

在数据库实例上创建数据库：

create database DB_name default character set DB_charset default collate DB_collation;

然后，向您在预配 MySQL 实例和用户时创建的用户授予权限：

grant all on DB_name.* to 'DB_username'@'%';
grant all on looker_tmp.* to 'DB_username'@'%';

替换以下内容：

  • DB_name：数据库名称
  • DB_charset：与 MySQL 服务器参数设置匹配的字符集。使用 utf8mb4 可获得完整的 UTF-8 支持。
  • DB_collation：与 MySQL 服务器参数设置匹配的排序规则。Looker 需要 utf8mb4_general_ci。
  • DB_username：用户名

最后一行中的 looker_tmp 数据库不一定实际存在，但内部报告需要 grant 语句。

创建数据库凭证文件

Looker 需要知道要与哪个 MySQL 数据库通信，以及要使用哪些凭证。在 Looker 目录中，创建一个名为 looker-db.yml 的文件，其中包含以下内容，并将 DB_hostname、DB_username、DB_password 和 DB_name 替换为数据库的值：

dialect: mysql_8
host: DB_hostname
username: DB_username
password: DB_password
database: DB_name
port: 3306

如果您的 MySQL 数据库需要 SSL 连接，请将以下行添加到 looker-db.yml：

ssl: true

如果您还想启用 SSL 证书验证，请将以下行添加到 looker-db.yml：

verify_ssl: true

（可选）您还可以通过添加 jdbc_additional_params 来指定 MariaDB JDBC 驱动程序支持的任何其他 JDBC 参数。例如，如果您需要使用特定的受信任证书存储区文件，可以将以下参数添加到 MySQL JDBC 连接字符串中：

jdbc_additional_params: trustStore=/path/to/my/truststore.jks&keyStore=/path/to/my/keystore.jks

对于客户托管的安装，您可以选择添加 max_connections，以指定 Looker 可与您的数据库建立的最大连接数。例如，如需将数据库的并发连接数限制为 10，请添加以下内容：

max_connections: 10

在 Looker 的加密方案下，数据库中的所有敏感数据都会进行静态加密。即使有人获得了明文数据库凭据并访问了数据库，Looker 也会在存储之前对敏感数据进行加密或哈希处理。这适用于密码、分析数据库凭据和查询缓存等元素。不过，如果您不想将此配置的明文密码存储在磁盘上的 looker-db.yml 文件中，则可以配置环境变量 LOOKER_DB，使其包含 looker-db.yml 文件中每行的键和值列表。例如：

export LOOKER_DB="dialect=mysql_8&host=localhost&username=root&password=&database=looker&port=3306"

备份 .db 目录

备份 .db 目录，其中包含构建内存中 HyperSQL 数据库所需的文件，以防您需要恢复 HyperSQL：

cp -r .db .db-backup
tar -zcvf db-backup.tar.gz ./.db-backup

迁移数据库

在中型或大型实例上，将数据库迁移到 MySQL 可能需要数小时，尤其是当 HyperSQL 数据库大小达到 1 GB 或以上时。我们建议您在迁移期间暂时将 EC2 实例升级到 m5.2xlarge（具有 32 GB RAM，以允许执行步骤中指定的 26 GB 堆），这样可以将所需时间缩短到大约 10 分钟。

  1. 在 Looker 主机上：

    cd looker
./looker stop
vi looker

  2. 在 Looker 启动脚本中，在文件中新建第二行：

    exit

  3. 在 AWS 控制台中停止实例。停止后，将 EC2 实例大小更改为 m5.2xlarge。然后重新启动实例。

  4. 以 Looker 用户身份通过 SSH 连接到主机。首先，确保 Java 未运行；然后运行：

    cd looker
java -Xms26000m -Xmx26000m -jar looker.jar migrate_internal_data  looker-db.yml

    运行 migrate_internal_data 步骤时，可能找不到 libcrypt，并且会显示堆栈轨迹，如下所示：

    NotImplementedError: getppid unsupported or native support failed to load
ppid at org/jruby/RubyProcess.java:752
ppid at org/jruby/RubyProcess.java:749

    如果发生这种情况，请在执行 Java 命令之前手动设置 LD_LIBRARY_PATH：

    export LD_LIBRARY_PATH=$HOME/looker/.tmp/:$LD_LIBRARY_PATH

  5. 成功完成后，从 AWS 控制台停止该实例。

  6. 现在，您可以将实例恢复到原始大小。

  7. 再次启动实例。

启动 Looker

  1. 修改 Looker 启动脚本，并删除您之前添加的 exit 行。

  2. 确保启动脚本中的 LOOKERARGS 中未定义任何参数。而是应将所有参数移至 lookerstart.cfg 文件，以免被新版启动脚本覆盖。保存并退出启动脚本。

  3. 修改 lookerstart.cfg。修改后应如下所示：

    LOOKERARGS="-d looker-db.yml"

    如果 Looker 启动脚本中还有其他参数，请将其添加到 lookerstart.cfg 文件中。

  4. 归档 .db 目录（如果尚未归档）。

    mv .db .db-backup
tar -zcvf db-backup.tar.gz ./.db-backup
rm -rf ./.db-backup/

  5. 启动 Looker：

    ./looker start

验证 Looker 是否正在使用新数据库

如果 Looker 成功使用了后端 MySQL，您应该会看到 Looker 实例与新数据库实例之间的网络连接。如需检查此项，请在 Looker 实例上运行以下命令：

netstat -na | grep 3306

您应该会看到与数据库实例的一些连接。以下是输出示例，显示了 IP 地址为 10.0.3.155 的数据库实例：

looker@instance1:~$ netstat -na | grep 3306
tcp6       0      0 10.0.5.131:56583        10.0.3.155:3306         ESTABLISHED
tcp6       0      0 10.0.5.131:56506        10.0.3.155:3306         ESTABLISHED
tcp6       0      0 10.0.5.131:56582        10.0.3.155:3306         ESTABLISHED
tcp6       0      0 10.0.5.131:56508        10.0.3.155:3306         ESTABLISHED

备份 Looker

迁移到 MySQL 后端后，Looker 的自动 S3 备份将不再运行。我们建议至少每晚备份一次 MySQL 数据库，并每晚备份一次 Looker 工作目录的文件系统。looker/log/ 目录可能会从文件系统备份中排除。如需了解详情，请参阅创建备份文档页面。

问题排查

本部分介绍了如何解决将 MySQL 后端数据库与 Looker 搭配使用时可能出现的常见问题。

更新后出现共享文件夹为空或 Illegal mix of collations 错误

更新使用 MySQL 8.0.x 或 8.4.x 后端数据库的客户托管 Looker 实例后，Shared 文件夹在 Looker 界面中可能会显示为空。信息中心和 Look 也可能不会显示在搜索结果中，但您仍然可以使用其直接网址打开它们。在后端日志或浏览器网络响应中，内部 API 请求失败，并显示 HTTP 500 Internal Server Error 和 Illegal mix of collations SQL 异常。

如果 MySQL 后端数据库的默认排序规则 (@@collation_database) 设置为 MySQL 8 的默认 utf8mb4_0900_ai_ci 而不是 utf8mb4_general_ci，就会出现此问题。在每个 MySQL 连接上，Looker 都会将会话 collation_connection 变量设置为 @@collation_database。即使 collation_server 设置为 utf8mb4_general_ci 且现有表使用 utf8mb4_general_ci，如果将数据库默认排序规则设置为 utf8mb4_0900_ai_ci，MySQL 也会将 utf8mb4_0900_ai_ci 分配给更新期间创建的新表，以及在列出文件夹内容时在内存中生成的临时表。然后，MySQL 会拒绝这些表与现有 utf8mb4_general_ci 表之间的 JOIN 操作。

如需解决此问题，请将数据库和表排序规则与 utf8mb4_general_ci 保持一致：

  1. 在修改数据库或表排序规则之前，请先备份 MySQL 后端数据库。
  2. 验证 MySQL 配置或参数组中的 collation_connection 和 collation_server 是否已设置为 utf8mb4_general_ci，如配置字符集中所述。

  3. 在 Looker 后端数据库中设置默认字符集和排序规则：

    ALTER DATABASE DB_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

    将 DB_name 替换为 Looker 后端数据库的名称。

  4. 在 Looker 后端数据库中查找不使用 utf8mb4_general_ci 排序规则的所有永久表：

    SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'DB_name'
  AND TABLE_TYPE = 'BASE TABLE'
  AND TABLE_COLLATION != 'utf8mb4_general_ci';

  5. 对于查询返回的每个受影响的表，请将该表转换为使用 utf8mb4 和 utf8mb4_general_ci：

    ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

    将 table_name 替换为受影响表的名称。

  6. 重启 Looker 实例，或重启集群部署中的每个节点，以便新的数据库连接和临时表继承 utf8mb4_general_ci 排序规则。