MySQL renaming and create table at the same time

Question

I need to rename MySQL table and create a new MySQL table at the same time.

There is critical live table with large number of records. master_table is always inserted records from scripts.

Need to backup the master table and create a another master table with same name at the same time.

General SQL is is like this.

RENAME TABLE master_table TO backup_table;

Create table master_table (id,value) values ('1','5000');

Is there a possibility to record missing data during the execution of above queries?

Any way to avoid missing record? Lock the master table, etc...

@jhpratt original table needs to be empty and no need any data loss — Sathiska, Mar 13 '18 at 07:54

score 5 · Answer 1 · answered Mar 13 '18 at 17:12

What I do is the following. It results in no downtime, no data loss, and nearly instantaneous execution.

CREATE TABLE mytable_new LIKE mytable;

...possibly update the AUTO_INCREMENT of the new table...

RENAME TABLE mytable TO mytable_old, mytable_new TO mytable;

By renaming both tables in one statement, they are swapped atomically. There is no chance for any data to be written "in between" while there is no table to receive the write. If you don't do this atomically, some writes may fail.

RENAME TABLE is virtually instantaneous, no matter how large the table. You don't have to wait for data to be copied.

If the table has an auto-increment primary key, I like to make sure the new table starts with an id value greater than the current id in the old table. Do this before swapping the table names.

SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='mydatabase' AND TABLE_NAME='mytable';

I like to add some comfortable margin to that value. You want to make sure that the id values inserted to the old table won't exceed the value you queried from INFORMATION_SCHEMA.

Change the new table to use this new value for its next auto-increment:

ALTER TABLE mytable_new AUTO_INCREMENT=<increased value>;

Then promptly execute the RENAME TABLE to swap them. As soon as new rows are inserted to the new, empty table, it will use id values starting with the increased auto-increment value, which should still be greater than the last id inserted into the old table, if you did these steps promptly.

cdaiga · Answer 2 · 2018-03-13T09:20:44.463

Instead of renaming the master_backup table and recreating it, you could just create a backup_table with the data from the master_table for the first backup run.

CREATE TABLE backup_table AS
   SELECT * FROM master_table;

If you must add a primary key to the backup table then run this just once, that is for the first backup:

ALTER TABLE backup_table ADD CONSTRAINT pk_backup_table PRIMARY KEY(id);

For future backups do:

INSERT INTO backup_table 
  SELECT * FROM master_table;

Then you can delete all the data in the backup_table found in the master_table like:

DELETE FROM master_table A JOIN 
       backup_table B ON A.id=B.id;

Then you can add data to the master_table with this query:

INSERT INTO master_table (`value`) VALUES ('5000'); -- I assume the id field is auto_incrementable

I think this should work perfectly even without locking the master table, and with no missing executions.

INSERT INTO master_table (id,`value`) VALUES ('1','5000'); Isn't there a potential duplicate on the PK of the backup table if the exercise is ever repeated to the same table? — Paul Campbell, Mar 13 '18 at 09:11
Yeah, it is @PaulCampbell. I have updated that portion of my answer. — cdaiga, Mar 13 '18 at 09:19

MySQL renaming and create table at the same time

2 Answers2