How to make table schema changes and restore data after appropriate transformation

Introduction

In many cases we might have to encounter scenarios where we need to perform backup of data or perform a schema change like data type transformation etc.

This blog post illustrates the steps that can be used as a checklist to perform the operation at ease

Steps

The below sql query creates a new table with the same schema as given in the like field

create table <table_name>_old like <table_name>;

Now that we have the schema ready, we can copy the data for backup using the below command

insert into <table_name>_old select * from <table_name>;

Once the above command succeeds, we can drop the old table

drop table <table_name>;

Then we can create the new table with the same name as the one given in <table_name> so that we can allow applications to still use the same table name.

CREATE TABLE `<table_name>` (

  `id` bigint NOT NULL AUTO_INCREMENT,

...);

Now that the table with new schema is ready, we can migrate the data from the old table to the new table using below like query

insert into <table_name> select * from <table_name>_old;

Here, we are using a simple select * from  query, but in real time there might be joins or other ways to arrive at the transformed data.

This way we can ensure that the table data is transformed to the new schema and is ready to be used.

Verify the newly migrated data using a select query like given below

select * from <table_name>;

Once all the records are looking good, we can drop the old table as it might be occupying lot of memory due to the redundant data, which can be done using the below command.

drop table <table_name>_old;

Hope this helps :)