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

Question

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

Solution 1

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.

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