MySql - Creating Prepopulated Table

Discussion in 'MySQL' started by juicytuna, Oct 20, 2008.

0
  1. #1
    I have a table with over 100 rows which I'd like to be able to recreate on other servers. Using create_table and manually entering each row as an argument will take a long time as you can imagine. Is there a quick way for generating a prepopulated create_table statement for on an already existing table?

    Thanks.
     
    juicytuna, Oct 20, 2008 IP
  2. JEET

    JEET Notable Member

    Messages:
    3,832
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #2
    This may not be the best idea, but this is what I do.
    Take a backup of mysql. Copy the "insert" query in a mysql_query() statement and paste in installer.
    regards :)
     
    JEET, Oct 20, 2008 IP
  3. Krnl

    Krnl Peon

    Messages:
    60
    Likes Received:
    3
    Best Answers:
    1
    Trophy Points:
    0
    #3
    **BACKUP YOUR DATABASES** (mysqldump yourdatabase > yourdatabase.sql)

    To get a table from one server to another...

    On server A:
    mysqldump yourdatabase yourtable > yourtable.sql
    Code (markup):
    on server B:
    mysql -uroot -p yourdatabasename < yourtable.sql
    Code (markup):
    If the table doesn't exist in 'yourdatabasename' on server B, it will be created. If the table already exists on server B, you can edit the yourtable.sql file and remove the lines for table creation and just leave the lines with the INSERTs, i.e. something like:

    grep INSERT yourtable.sql > yourtable1.sql && mv yourtable1.sql yourtable.sql

mysql -uroot -p yourdatabasename < yourtable.sql
    Code (markup):
    It would be a good idea to familiarize yourself with the file 'yourtable.sql' before you actually execute anything anyway since it might have a "DROP yourtable IF EXISTS" statement in it, etc...

    HTH
     
    Krnl, Oct 21, 2008 IP