Tutorial :Mysql: how do I make an exact working copy of a table?


I am working on a restructuring of a legacy database and its associated applications. To keep old and new working in parallel I have created a development database and copied the tables of interest to it, e.g.

create database devdb;    drop table if exists devdb.tab1;  CREATE TABLE devdb.tab1 like working.tab1;  insert into devdb.tab1 select * from working.tab1;  

Having done this I notice that triggers affecting tab1 have not been copied over. Is there any way in which I can produce a working copy of tab1, i.e. data, permissions, triggers, everything?


Hmm, kind of obvious in hindsight but it would appear that dumping the tables via mysqldump and loading those dumps in the new database restores triggers (and I would hope any other relevant information).

Shame as I had wanted to do the whole process via the DbVisualizer database manager. You learn something everything day ...


Note that the "create table x select * from y" syntax will not create an exact working copy of the table, even if you're not concerned about triggers an whatnot. The new table will:

  • Use the default MyISAM table type (even if the parent table is InnoDB)
  • Default to the Latin1 character set (even if the parent table is UTF-8)
  • May wipe out the values of auto_incremented fields and replace them with 0 (depends on table structure but really nasty if it happens to you)


There is a quicker way to both recreate the structure and import datas (albeit you lose your indexes ;)):

 create table devdb.tab1 select * from working.tab1;  

For the triggers and friends, you will have to query in information_schemas

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »