4.4.5 mysql_upgrade — Check and Upgrade MySQL Tables检查和升级MySQL表

Note注意

As of MySQL 8.0.16, the MySQL server performs the upgrade tasks previously handled by mysql_upgrade (for details, see Section 2.11.3, “What the MySQL Upgrade Process Upgrades”). 从MySQL 8.0.16开始,MySQL服务器执行以前由mysql_upgrade处理的升级任务(有关详细信息,请参阅第2.11.3节,“MySQL升级过程升级的内容”)。Consequently, mysql_upgrade is unneeded and is deprecated as of that version; expect it to be removed in a future version of MySQL. 因此,mysql_upgrade是不必要的,从该版本开始就被弃用了;预计它将在MySQL的未来版本中被删除。Because mysql_upgrade no longer performs upgrade tasks, it exits with status 0 unconditionally.因为mysql_upgrade不再执行升级任务,所以它无条件退出,状态为0。

Each time you upgrade MySQL, you should execute mysql_upgrade, which looks for incompatibilities with the upgraded MySQL server:每次升级MySQL时,都应该执行mysql_upgrade,它会查找与升级后的MySQL服务器的不兼容性:

If mysql_upgrade finds that a table has a possible incompatibility, it performs a table check and, if problems are found, attempts a table repair. 如果mysql_upgrade发现某个表可能不兼容,它将执行表检查,如果发现问题,则尝试修复表。If the table cannot be repaired, see Section 2.11.13, “Rebuilding or Repairing Tables or Indexes” for manual table repair strategies.如果表无法修复,请参阅第2.11.13节,“重建或修复表或索引”,了解手动表修复策略。

mysql_upgrade communicates directly with the MySQL server, sending it the SQL statements required to perform an upgrade.mysql_upgrade直接与mysql服务器通信,向其发送执行升级所需的SQL语句。

Caution小心

You should always back up your current MySQL installation before performing an upgrade. See Section 7.2, “Database Backup Methods”.在执行升级之前,您应该始终备份当前的MySQL安装。请参阅第7.2节,“数据库备份方法”

Some upgrade incompatibilities may require special handling before upgrading your MySQL installation and running mysql_upgrade. 在升级MySQL安装并运行mysql_upgrade之前,某些升级不兼容可能需要特殊处理。See Section 2.11, “Upgrading MySQL”, for instructions on determining whether any such incompatibilities apply to your installation and how to handle them.请参阅第2.11节,“升级MySQL”,了解有关确定此类不兼容是否适用于您的安装以及如何处理它们的说明。

Use mysql_upgrade like this:像这样使用mysql_upgrade

  1. Ensure that the server is running.确保服务器正在运行。

  2. Invoke mysql_upgrade to upgrade the system tables in the mysql schema and check and repair tables in other schemas:调用mysql_upgrade来升级mysql模式中的系统表,并检查和修复其他模式中的表:

    mysql_upgrade [options]
  3. Stop the server and restart it so that any system table changes take effect.停止服务器并重新启动,以便任何系统表更改生效。

If you have multiple MySQL server instances to upgrade, invoke mysql_upgrade with connection parameters appropriate for connecting to each of the desired servers. 如果您有多个MySQL服务器实例需要升级,请使用适合连接到每个所需服务器的连接参数调mysql_upgradeFor example, with servers running on the local host on parts 3306 through 3308, upgrade each of them by connecting to the appropriate port:例如,对于在3306至3308部分的本地主机上运行的服务器,通过连接到适当的端口来升级每个服务器:

mysql_upgrade --protocol=tcp -P 3306 [other_options]
mysql_upgrade --protocol=tcp -P 3307 [other_options]
mysql_upgrade --protocol=tcp -P 3308 [other_options]

For local host connections on Unix, the --protocol=tcp option forces a connection using TCP/IP rather than the Unix socket file.对于Unix上的本地主机连接,--protocol=tcp选项强制使用tcp/IP而不是Unix套接字文件进行连接。

By default, mysql_upgrade runs as the MySQL root user. 默认情况下,mysql_upgrade以MySQL root用户身份运行。If the root password is expired when you run mysql_upgrade, it displays a message that your password is expired and that mysql_upgrade failed as a result. 如果在运行mysql_upgraderoot密码已过期,则会显示一条消息,说明您的密码已过期并且mysql_upgade因此失败。To correct this, reset the root password to unexpire it and run mysql_upgrade again. 要更正此问题,请重置root密码以取消激活,然后再次运行mysql_upgradeFirst, connect to the server as root:首先,以root身份连接到服务器:

shell> mysql -u root -p
Enter password: ****  <- enter root password here

Reset the password using ALTER USER:使用ALTER USER重置密码:

mysql> ALTER USER USER() IDENTIFIED BY 'root-password';

Then exit mysql and run mysql_upgrade again:然后退出mysql并再次运行mysql_upgrade

shell> mysql_upgrade [options]
Note注意

If you run the server with the disabled_storage_engines system variable set to disable certain storage engines (for example, MyISAM), mysql_upgrade might fail with an error like this:如果您在运行服务器时将disabled_storage_engines系统变量设置为禁用某些存储引擎(例如MyISAM),mysql_upgrade可能会失败,并出现以下错误:

mysql_upgrade: [ERROR] 3161: Storage engine MyISAM is disabled
(Table creation is disallowed).

To handle this, restart the server with disabled_storage_engines disabled. 要处理此问题,请在禁用disabled_storage_engines的情况下重新启动服务器。Then you should be able to run mysql_upgrade successfully. 然后,您应该能够成功运行mysql_upgradeAfter that, restart the server with disabled_storage_engines set to its original value.之后,重新启动服务器,并将disabled_storage_engines设置为其原始值。

Unless invoked with the --upgrade-system-tables option, mysql_upgrade processes all tables in all user schemas as necessary. 除非使用--upgrade-system-tables选项调用,否则mysql_upgrade会根据需要处理所有用户模式中的所有表。Table checking might take a long time to complete. Each table is locked and therefore unavailable to other sessions while it is being processed. 表检查可能需要很长时间才能完成。每个表都被锁定,因此在处理过程中对其他会话不可用。Check and repair operations can be time-consuming, particularly for large tables. 检查和维修操作可能很耗时,特别是对于大型表格。Table checking uses the FOR UPGRADE option of the CHECK TABLE statement.表检查使用CHECK TABLE语句的FOR UPGRADE选项。 For details about what this option entails, see Section 13.7.3.2, “CHECK TABLE Statement”.有关此选项的详细信息,请参阅第13.7.3.2节,“CHECK TABLE语句”

mysql_upgrade marks all checked and repaired tables with the current MySQL version number. mysql_upgrade用当前mysql版本号标记所有已检查和修复的表。This ensures that the next time you run mysql_upgrade with the same version of the server, it can be determined whether there is any need to check or repair a given table again.这确保了下次使用相同版本的服务器运行mysql_upgrade时,可以确定是否需要再次检查或修复给定的表。

mysql_upgrade saves the MySQL version number in a file named mysql_upgrade_info in the data directory. mysql_upgrade将MySQL版本号保存在数据目录中名为mysql_upgrad_info的文件中。This is used to quickly check whether all tables have been checked for this release so that table-checking can be skipped. 这用于快速检查是否已检查此版本的所有表,以便跳过表检查。To ignore this file and perform the check regardless, use the --force option.要忽略此文件并执行检查,请使用--force选项。

Note注意

The mysql_upgrade_info file is deprecated; expect it to be removed in a future version of MySQL.mysql_upgrade_info文件已弃用;预计它将在MySQL的未来版本中被删除。

mysql_upgrade checks mysql.user system table rows and, for any row with an empty plugin column, sets that column to 'mysql_native_password' if the credentials use a hash format compatible with that plugin. Rows with a pre-4.1 password hash must be upgraded manually.mysql_upgrade检查mysql.user系统表行,对于任何具有空plugin列的行,如果凭据使用与该插件兼容的哈希格式,则将该列设置为'mysql_native_password'。具有4.1之前密码哈希的行必须手动升级。

mysql_upgrade does not upgrade the contents of the time zone tables or help tables. For upgrade instructions, see Section 5.1.15, “MySQL Server Time Zone Support”, and Section 5.1.17, “Server-Side Help Support”.mysql_upgrade不会升级时区表或帮助表的内容。有关升级说明,请参阅第5.1.15节,“MySQL服务器时区支持”第5.1.17节,“服务器端帮助支持”

Unless invoked with the --skip-sys-schema option, mysql_upgrade installs the sys schema if it is not installed, and upgrades it to the current version otherwise. 除非使用--skip-sys-schema选项调用,否则mysql_upgrade会安装未安装的sys模式,否则会将其升级到当前版本。An error occurs if a sys schema exists but has no version view, on the assumption that its absence indicates a user-created schema:如果sys架构存在但没有version视图,则会发生错误,前提是它的缺失表示用户创建的架构:

A sys schema exists with no sys.version view. If you have a user created sys schema, this must be renamed for the upgrade to succeed.存在一个没有sysversion视图的sys架构。如果您有用户创建的sys架构,则必须重命名该架构才能成功升级。

To upgrade in this case, remove or rename the existing sys schema first.在这种情况下,要升级,请先删除或重命名现有的sys架构。

mysql_upgrade supports the following options, which can be specified on the command line or in the [mysql_upgrade] and [client] groups of an option file. mysql_upgrade支持以下选项,可以在命令行或选项文件的[mysql_upgrade][client]组中指定。For information about option files used by MySQL programs, see Section 4.2.2.2, “Using Option Files”.有关MySQL程序使用的选项文件的信息,请参阅第4.2.2.2节,“使用选项文件”

Table 4.10 mysql_upgrade Options选项

Option Name选项名称Description描述Introduced引入版本Deprecated弃用版本
--bind-addressUse specified network interface to connect to MySQL Server使用指定的网络接口连接MySQL服务器
--character-sets-dirDirectory where character sets are installed安装字符集的目录
--compressCompress all information sent between client and server压缩客户端和服务器之间发送的所有信息8.0.18
--compression-algorithmsPermitted compression algorithms for connections to server服务器连接的允许压缩算法8.0.18
--debugWrite debugging log写入调试日志
--debug-checkPrint debugging information when program exits程序退出时打印调试信息
--debug-infoPrint debugging information, memory, and CPU statistics when program exits程序退出时打印调试信息、内存和CPU统计信息
--default-authAuthentication plugin to use要使用的身份验证插件
--default-character-setSpecify default character set指定默认字符集
--defaults-extra-fileRead named option file in addition to usual option files除了常规选项文件外,还读取命名选项文件
--defaults-fileRead only named option file只读命名选项文件
--defaults-group-suffixOption group suffix value选项组后缀值
--forceForce execution even if mysql_upgrade has already been executed for current MySQL version强制执行,即使当前mysql版本已经执行了mysql_upgrade
--get-server-public-keyRequest RSA public key from server从服务器请求RSA公钥
--helpDisplay help message and exit显示帮助消息并退出
--hostHost on which MySQL server is locatedMySQL服务器所在的主机
--login-pathRead login path options from .mylogin.cnf从.mylogin.cnf读取登录路径选项
--max-allowed-packetMaximum packet length to send to or receive from server发送到服务器或从服务器接收的最大数据包长度
--net-buffer-lengthBuffer size for TCP/IP and socket communicationTCP/IP和套接字通信的缓冲区大小
--no-defaultsRead no option files不读取选项文件
--passwordPassword to use when connecting to server连接到服务器时使用的密码
--pipeConnect to server using named pipe (Windows only)使用命名管道连接到服务器(仅限Windows)
--plugin-dirDirectory where plugins are installed安装插件的目录
--portTCP/IP port number for connection用于连接的TCP/IP端口号
--print-defaultsPrint default options打印默认选项
--protocolTransport protocol to use要使用的传输协议
--server-public-key-pathPath name to file containing RSA public key包含RSA公钥的文件的路径名
--shared-memory-base-nameShared-memory name for shared-memory connections (Windows only)共享内存连接的共享内存名称(仅限Windows)
--skip-sys-schemaDo not install or upgrade sys schema不要安装或升级sys架构
--socketUnix socket file or Windows named pipe to use要使用的Unix套接字文件或Windows命名管道
--ssl-caFile that contains list of trusted SSL Certificate Authorities包含受信任SSL证书颁发机构列表的文件
--ssl-capathDirectory that contains trusted SSL Certificate Authority certificate files包含受信任的SSL证书颁发机构证书文件的目录
--ssl-certFile that contains X.509 certificate包含X.509证书的文件
--ssl-cipherPermissible ciphers for connection encryption连接加密的允许密码
--ssl-crlFile that contains certificate revocation lists包含证书吊销列表的文件
--ssl-crlpathDirectory that contains certificate revocation-list files包含证书吊销列表文件的目录
--ssl-fips-modeWhether to enable FIPS mode on client side是否在客户端启用FIPS模式
--ssl-keyFile that contains X.509 key包含X.509密钥的文件
--ssl-modeDesired security state of connection to server与服务器连接的所需安全状态
--tls-ciphersuitesPermissible TLSv1.3 ciphersuites for encrypted connections加密连接允许的TLSv1.3密码套件8.0.16
--tls-versionPermissible TLS protocols for encrypted connections加密连接的允许TLS协议
--upgrade-system-tablesUpdate only system tables, not user schemas仅更新系统表,不更新用户模式
--userMySQL user name to use when connecting to server连接到服务器时使用的MySQL用户名
--verboseVerbose mode详细模式
--version-checkCheck for proper server version检查服务器版本是否正确
--write-binlogWrite all statements to binary log将所有语句写入二进制日志
--zstd-compression-levelCompression level for connections to server that use zstd compression使用zstd压缩的服务器连接的压缩级别8.0.18