Simple MySQL table backup & restore

SQL          Trackback

Here is simple MySQL table backup script. You can use it to easily create backup of your MySQL table, so you can play with that table without fear of losing your valuable data. First line will delete table named “tablename_backup” (if table with that name exists). Second line will create “tablename_backup” table using same structure as tablename is using. And third line will copy data from tablename to tablename_backup.

DROP TABLE IF EXISTS tablename_backup;
CREATE TABLE tablename_backup LIKE tablename;
INSERT tablename_backup SELECT * FROM tablename;

I already hear you asking, what about restoring of that backup. And here it is:

DROP TABLE IF EXISTS tablename_temp;
RENAME TABLE tablename TO tablename_temp,
tablename_backup TO tablename,
tablename_temp TO tablename_backup;

If there is table named “tablename_temp” we will delete it, and then we have to make some table name tumbling. We can use one command (renaming operations are done from left to right) to make 3 rename operations. First we are renaming table “tablename” to temporary table “tablename_temp”, then we are restoring our backup from “tablename_backup” to “tablename”, and at the end we are saving starting table as new backup “tablename_backup”…

Pretty simple and effective?

No Comments

Make A Comment

No comments yet.

Comments RSS Feed    TrackBack URL

Leave a comment