Home / server / Questions / 194462
Accepted
Continuation
Asked: 2010-10-26 03:09:49 +0800 CST

Any way to copy the schema from one MySQL database to another?

  • 772

I have a MySQL database that contains almost 100 tables.

I want to set up N additional MySQL databases on the same server, each running on a different port. And I want each additional database to have the same schema/table structures as the original database.

Is there any way to automatically make N duplicates of the original database and set them up on N different ports?

Thanks

mysql database database-administration

2 Answers

  1. Best Answer

    Have a look at mysqldump and it's --no-data option to get a copy of the schema. Once you have that you will have to make a script to source in mysql

    e.g.

    mysqldump --no-data -u user -p database >database-schema.sql

    will generate a copy of the schema for database database.

    If you had a script like below in a file called for example makedbs.sql

    create database N;

    set permissions here

    use N;

    source database-schema.sql;

    create database N1;

    set permissions here

    use N1;

    source database-schema.sql;

    Then you could run mysql and source makedbs.sql

    mysql -u user -p

mysql> source makedbs.sql

    which would create 2 databases N and N1 which have the same schema as your original.

    • 10

  2. You can dump the schema of an existing database using mysqldump with the --no-data option and then use this to create new databases using a simple script that creates each database and then populates it from the output of mysqldump.

    • 1