Mysql database structure replication

chrissy

Anyone know if it is possible to replicate only database structure not data accross multiple databases? Obviously, this can be done manually but I am looking for an automatic solution? Any ideas?

Pig

Most utilities have a dump feature and give you option of data and/or structure. MySQL Front, MySQL CC and phpMyAdmin all do that.

chrissy

Thx for the reply. I know that I can do a structure dump but my problem is....

I am going to have a massively huge database. To alleviate this I plan to separate the one large database into a few smaller databases. The new smaller databases will have the EXACT same structure but there is going to be different data in the databases. My only concern with this solution is that if I need to make a table change to one database, I want to have that change automatically made in the other databases as well. I could write a script to apply a single change to all of the databases but if there is a utility out there, I would rather use that. So I wanted to see if anyone had done or seen something similar. Thanks

Pig

That must be a pretty big db if you need to split it up. You probably already know, but most db's can handle millions of records without flinching.

Anyway, that's a pretty basic task. I don't know if you will find a script for it, but I would try hotscripts and phpfreaks.

chrissy

Yes it is a very big database (a few million records already) and steadily growing. We are starting to notice huge performance problems that were not there when there was substantially less data. Thus our move towards separating the databases. I am sure that we will probably end up writing something to make all of the changes for us. Thanks for your help!

LordShryku

Any possibilities of archiving? We have a couple databases that are +10 million records that we archive every week to a seperate machine.

chrissy

Believe it or not... All of the data is actively used. It is a data mining app with no historical data (yet). So... archiving won't help unfortunately.

LordShryku

Yikes....it it a full DB you want to copy or just a table?

chrissy

Tell me about it... Yikes might be too conservative... I just want to have it so if I make any structure changes (not data) to a master db that the changes are replicated in all of the other databases.

I am getting the feeling that I am just going to have to write something that will do it.

LordShryku

The bad news: You prolly will have to write something...

The good news: It won't be that hard. There's a MySQL statement that will generate the create table statement for you. All you'll have to do it change it a little bit to an ALTER TABLE statement. Take a look at the SHOW CREATE TABLE statement.
http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Database_Administration.html#SHOW