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

Question

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?

Answer 1

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

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

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

Answer 2

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 ...

Answer 3

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)