在复制数据库表期间脚本卡住了 - Mysql - PHP

As a follow up question to my prev question, I decide to make this.

作为我的上一个问题的后续问题,我决定这样做。

I use the code below to duplicate database tables, but sometimes if the table size is large, the script hangs and the page just keeps waiting, and when I compare number of rows in both table I find something similar to this

我使用下面的代码来复制数据库表,但有时如果表大小很大,脚本会挂起,页面会一直等待,当我比较两个表中的行数时,我会发现类似于此的内容

Original table count: 855057
Backed up table count: 855022

原始表数:855057备份表数:855022

So why it got stuck during executing insertion? Keep in mind that in most cases both counts actually the same, still, it hangs even though it finished duplicating!

那么为什么它在执行插入时卡住了?请记住,在大多数情况下,两个计数实际上都相同,但即使它完成重复,它仍会挂起!

Here's my code:

这是我的代码:

//duplicate tables structure 
 $query = "CREATE TABLE $this->dbName.`$newTableName` LIKE $this->dbName.`$oldTable`";
    ..
    ..

//duplicate tables data
  $query = "INSERT INTO $this->dbName.`$newTableName` SELECT * FROM $this->dbName.`$oldTable`";
    ..
    ..

ps. I run the duplicating script from my localhost to backup db located in remote server.

PS。我从我的localhost运行复制脚本到位于远程服务器的备份数据库。

2 个解决方案

#1

Use LOCK TABLES

使用LOCK TABLES

// duplicate tables structure 
$query = "CREATE TABLE $this->dbName.`$newTableName` LIKE $this->dbName.`$oldTable`";

// lock source and target table
$query = "LOCK TABLES $this->dbName.`$newTableName` WRITE, $this->dbName.`$oldTable` AS source WRITE";

// duplicate tables data
$query = "INSERT INTO $this->dbName.`$newTableName` SELECT * FROM $this->dbName.`$oldTable` AS source";

// unlock
$query = "UNLOCK TABLES";

#2

You will want to check if the query is still running by running show full processlist. You will also have to keep in mind that if the tables engine is innodb an undo log is also created as in case of interruption the transaction will have to rollback.

您将需要通过运行show full processlist来检查查询是否仍在运行。您还必须记住,如果表引擎是innodb,则还会创建撤消日志,因为在中断的情况下,事务将不得不回滚。