没有停机时间和潜在的数据丢失,就不能简单地停止回滚。
建议等待回滚自行完成。要估计回滚需要多长时间,请登录mysql并执行几次以监控其进度:
<code style="text-align: left;">mysql> select now(),trx_state,trx_operation_state,trx_weight,trx_rows_locked,trx_rows_modified from information_schema.innodb_trx where trx_state='ROLLING BACK';do sleep(10);
+---------------------+--------------+---------------------+------------+-----------------+-------------------+
| now() | trx_state | trx_operation_state | trx_weight | trx_rows_locked | trx_rows_modified |
+---------------------+--------------+---------------------+------------+-----------------+-------------------+
| 2017-11-15 15:20:19 | ROLLING BACK | rollback | 17028388 | 64342541 | 16672912 |
+---------------------+--------------+---------------------+------------+-----------------+-------------------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (10.00 sec)
mysql> select now(),trx_state,trx_operation_state,trx_weight,trx_rows_locked,trx_rows_modified from information_schema.innodb_trx where trx_state='ROLLING BACK';do sleep(10);
+---------------------+--------------+---------------------+------------+-----------------+-------------------+
| now() | trx_state | trx_operation_state | trx_weight | trx_rows_locked | trx_rows_modified |
+---------------------+--------------+---------------------+------------+-----------------+-------------------+
| 2017-11-15 15:20:29 | ROLLING BACK | rollback | 16245596 | 64342541 | 15906465 |
+---------------------+--------------+---------------------+------------+-----------------+-------------------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (10.00 sec)
mysql> select now(),trx_state,trx_operation_state,trx_weight,trx_rows_locked,trx_rows_modified from information_schema.innodb_trx where trx_state='ROLLING BACK';do sleep(10);
+---------------------+--------------+---------------------+------------+-----------------+-------------------+
| now() | trx_state | trx_operation_state | trx_weight | trx_rows_locked | trx_rows_modified |
+---------------------+--------------+---------------------+------------+-----------------+-------------------+
| 2017-11-15 15:20:39 | ROLLING BACK | rollback | 15463244 | 64342541 | 15140438 |
+---------------------+--------------+---------------------+------------+-----------------+-------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (10.00 sec)
在上面的输出中,我们可以看到回滚正在以如下速度进行:
16672912 - 15906465 = 766447 rows per 10 seconds, or 76644 rows per second.
因此,预计回滚可能需要16672912 / 76644 = 218秒才能完成。
注意:这只是一个估计,它取决于数据大小、当前服务器负载和配置