如何复制锁定的mySQL表?

时间:2020-12-14 15:04:11

I am trying to rotate out an innoDB table which has a high number of transaction, so I need to lock all the tables involved.

我试图旋转出一个具有大量事务的innoDB表,所以我需要锁定所有涉及的表。

With the tables locked I cannot use RENAME to move data around using auxiliary tables.

锁定表后,我无法使用RENAME使用辅助表移动数据。

And if I do an INSERT, then I have to worry about the integrity of the ids.

如果我执行INSERT,那么我必须担心ID的完整性。

Any suggestions? Is this even possible

有什么建议?这甚至是可能的吗?

1 个解决方案

#1


Can you do the rotation in the log? Have 7 tables - one for each day of the week - and have your queries to take the current day into account, or even better, wrap the insert with a stored procedure. Same goes for 24 tables for hourly rotation, 12 for monthly rotation, etc.

你能在日志中进行轮换吗?有7个表 - 一周中的每一天一个 - 并且让您的查询考虑当前的一天,甚至更好,用存储过程包装插入。同样适用于每小时轮换24个表,每月轮换12个等。

#1


Can you do the rotation in the log? Have 7 tables - one for each day of the week - and have your queries to take the current day into account, or even better, wrap the insert with a stored procedure. Same goes for 24 tables for hourly rotation, 12 for monthly rotation, etc.

你能在日志中进行轮换吗?有7个表 - 一周中的每一天一个 - 并且让您的查询考虑当前的一天,甚至更好,用存储过程包装插入。同样适用于每小时轮换24个表,每月轮换12个等。