How to copy data from another table without lock the table in MYSQL 6.2?

user_4326231 0
Viewed 5409

I have a table that has all history data in Mysql server and it is very huge (about 700 million rows). I am creating a new table with the same columns but with partitioning, then I need to copy all data from the old table into the new partitioned table. I have already got the right script to do that but I think it might lock the table. I don't want that happen because it is on production server. What should I do to avoid locking the table?

innodb mysql
4 Answers

To reduce the disadvantages of using OFFSET, this article describes a possible approach to use a JOIN when a numeric primary key id is available to force the use of the proper index. Note that to keep track of process, a "procedure_log" table is created and gradually updated after processing a batch:

For MySQL:

DROP PROCEDURE IF EXISTS copyTable;

DELIMITER |
CREATE PROCEDURE copyTable()
BEGIN

    DECLARE batchSize INT DEFAULT 100;
    DECLARE i INT DEFAULT 0;
    DECLARE rowCount INT;

    # Note that we use a WHERE clause to prevent a full table scan / use the index properly
    SET rowCount = (SELECT COUNT(id) FROM my_table WHERE id IS NOT NULL);

    CREATE TABLE IF NOT EXISTS my_table_copy LIKE my_table;
    CREATE TABLE IF NOT EXISTS procedure_log ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, entry TEXT, timestamp DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) );

    WHILE i <= rowCount DO
        INSERT IGNORE INTO my_table_copy (
          SELECT source.* FROM (
            SELECT id FROM my_table ORDER BY id LIMIT i, batchSize
          ) tmp
          JOIN my_table source ON source.id = tmp.id
          ORDER BY source.id
        );
        SET i = i + batchSize;

        INSERT INTO procedure_log (entry) VALUES (CONCAT('Copied batch from my_table => my_table_copy, batch: ', batchSize, ', offset: ', i, ', rowCount: ', rowCount));
    END WHILE;
END |
DELIMITER ;
user_1876590
Related