MySQL table backup for live data manipulation

Feb 7, 2017

Sometimes a DB admin has to access a live DB to perform some investigation and/or generate custom reports. It'd be a bad practice to directly manipulate the data on the production tables. A good practice would be to make a copy of the existing table (structure, indices and data) and perform data manipulation on that one.

CREATE TABLE newtable LIKE oldtable; 
INSERT newtable SELECT * FROM oldtable;

Once done, drop the temporary table before exiting the MySQL shell.

DROP TABLE newtable;