不做`mysqldump`的`mysql_upgrade`有什么风险? [英] What is the risk of `mysql_upgrade` without doing `mysqldump`?

问题描述

几个月前将mysql升级到5.7形式5.5后，忘记做mysql_upgrade.

After upgradig mysql to 5.7 form 5.5 few months ago, I forgot to do mysql_upgrade.

并且面临一些问题.. mysql、sys、performance_schema 数据库丢失，root 权限被破坏.当我尝试执行一些 mysql 用户权限操作时，会弹出很多 Access denied for user 'root'... 消息.

And facing some problems.. mysql, sys, performance_schema databases are missing and root privileges are broken. A lot of Access denied for user 'root'... messages pop up, when I try to do some mysql user privileges things.

这个堆栈答案必须解决我的问题.但我需要知道它不会影响任何模式、数据......等.

This stack answer will have to solve my problem. But I need to know it won't affect any of the schemas, data... ect.

因为我的数据库非常庞大.它总计 10 GB，由大约 50 个表组成.恐怕会发生一些不好的事情.我知道答案将是 mysqldump.但是完整备份会花费很长时间，也许一个小时.企业不会接受这种停机时间.

Because my database is pretty huge. It amounts to 10 GB and consists of about 50 tables. I'm afraid some bad things could happend. I know the answer will be the mysqldump. But the full backup will cost a long time, maybe an hour. And the business won't accept that downtime.

那么mysql_upgrade不做mysqldump有什么风险?

推荐答案

在没有备份的情况下对数据库进行任何操作管理的风险高得令人无法接受...不是因为 MySQL 的任何限制 本身，但因为我们谈论的是对您的业务至关重要的事情.您的备份频率不应低于您愿意丢失的数据时间间隔.

The risk of doing anything administrative to your database without backups is unacceptably high... not because of any limitations in MySQL per se, but because we're talking about something critical to your business. You should be backing it no less often than the time interval of data you are willing to lose.

如果您使用的是 InnoDB，则使用 mysqldump 的 --single-transaction 选项，并且应该没有锁定，因为 MVCC 处理一致性.如果您没有使用 InnoDB，那本身就是一个问题，但是使用 --skip-lock-tables 应该可以最小化锁定.

If you are using InnoDB, then use the --single-transaction option of mysqldump and there should be no locking, because MVCC handles the consistency. If you are not using InnoDB, that is a problem itself, but using --skip-lock-tables should mimimize locking.

请注意，如果您发现正在进行的 mysqldump 导致问题，那么杀死它应该是非常安全的 -- 使用 SHOW PROCESSLIST; 然后 KILL QUERY #; 其中 # 是进程列表中转储连接的 ID.

Note that it should be quite safe to kill a mysqldump in progress if you find it is causing issues -- find the thread-id of the dump using SHOW PROCESSLIST; and then KILL QUERY #; where # is the ID of the dump connection from the process list.

您引用的答案的潜在问题是 5.1 > 5.5 是受支持的升级路径，因为这两个版本是连续的.5.5 > 5.7 不是.您应该先升级到 5.6，然后再升级到 5.7，在每个步骤之前和之后运行 mysql_upgrade 的适当版本(适当意味着与服务器版本匹配的实用程序版本目前正在运行).

The potential problem with the answer you cited is that 5.1 > 5.5 is a supported upgrade path, because those two versions are sequential. 5.5 > 5.7 isn't. You should have upgraded to 5.6 and then 5.7, running the appropriate versions of mysql_upgrade both before and after each step (appropriate meaning the version of the utility matching the version of the server running at the moment).

您的处境可能比您想象的要微妙……或者您可能不会.

You may be in a more delicate situation than you imagine... or you may not.

鉴于类似的情况，除了完全停止服务器，通过将所有文件复制到新机器来克隆它，并针对克隆测试修复步骤之外，我不想做任何事情.

Given similar circumstances, I would not want to do anything less than stop the server completely, clone it by copying all the files to a new machine, and test the remediation steps against the clone.

如果这个系统是关键业务，它应该有一个实时运行的副本服务器，如果出现故障，它可以提升为主服务器并永久替换这台机器.在这种情况下，您可以将您的修复应用到副本并推广它.

If this system is business-critical, it should have a live, running replica server, that could be promoted to master and permanently replace this machine in the event of a failure. In a circumstance like this one, you would apply your fixes to the replica and promote it.

用户 'root' 的访问被拒绝... 可能与架构不兼容有关，也可能无关.

Access denied for user 'root'... may or may not be related to the schema incompatibilites.

这篇关于不做`mysqldump`的`mysql_upgrade`有什么风险?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！