Tutorial :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?


Solution:1

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


Solution:2

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)


Solution:3

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
Previous
Next Post »