Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.