MySQL InnoDB – How to Copy Subset of Columns from One InnoDB Table to Another Without Locking

amazon-rdsinnodbMySQL

I have a MySQL instance running on Amazon's RDS. This means that I cannot change any of the binlog settings.

I have a table source, created like this, which contains around 2 billion rows:

CREATE TABLE `source` (
   id INT PRIMARY KEY AUTO_INCREMENT,
   value1 VARCHAR(256),
   value2 VARCHAR(256)
);

I have another table destination, with the same columns, but the id is a BIGINT:

CREATE TABLE `destination` (
   id BIGINT PRIMARY KEY AUTO_INCREMENT,
   value1 VARCHAR(256),
   value2 VARCHAR(256)
);

The source table has gaps in the ID that I want to compact. I'd like to copy all rows in source to destination, without the id column, something like this:

INSERT INTO `destination` (value1, value2)
SELECT value1, value2 FROM `source`;

How can I accomplish this, without locking the source table? The copy itself is going to take an extremely long time, due to the size of the table, and I can't have it locked for that long.

I essentially want to run the above statement with READ UNCOMMITTED isolation, but this isn't possible due to my inability to change any binary log settings.

Best Answer

IF you are not updating or deleting old rows, the way to do it with minimal locking is to do it chunk by chunk. See here for doing chunking for deleting; it can be adapted for your 'copy'. When you get to the end of the copying, stop writes long enough to copy the final chunk and rename:

Then, if your real goal was to change to BIGINT, finish with 

RENAME TABLE source TO old,
             destination TO source;
DROP TABLE old;
Related Question