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.

Can I set up MySQL as a slave only replicating structure changes? (CREATE / ALTER TABLE etc.). I've got 2 reasons for this:

  1. Multiple developers with development branches in code which should always work with a 'fresh' datastructure, fetched from a 'main' development database.
  2. Multiple projects / clients which share (part of) the same code/scripts (think of a symlinked 'core' directory), but with different databases (1 per client), for which structure should be updated simultaneously with code changes in the shared portion of the code, with a minimum amount of downtime.

If this isn't possible, what are other good ways to sync mysql-datastructures without the actual data across sites / databases / servers? And no, structional changes don't happen all that often, but with enough different databases hanging around it's quite some time spend syncing all instances, and I'd like to both limit downtime and spend my time on more interesting jobs ;).

share|improve this question
add comment

3 Answers 3

up vote 2 down vote accepted

I typically dump the schema using mysqldump. However, I believe the blackhole engine could be used as you describe.

You would ALTER all tables to BLACKHOLE and INSERT, UPDATE, and DELETE records would not affect the data, as blackhole does not store the data.

You can skip-innodb and set default-storage-engine=BLACKHOLE and all tables created will default to BLACKHOLE except those that are created as MyISAM, as MyISAM cannot be disabled. You need to be aware of this as you create tables on the master, as you may need to later ALTER back to BLACKHOLE on the slave replicant.

Nevertheless, what you describe is an administrative issue and not a technical issue. I recommend configuring at least two development databases, which would be maintained by the infrastructure team.

The first, would be a development database. The developers have more access to this database and it would be regularly refreshed with scrubbed data.

The second database would be a QA or testing environment, which developers would not have RW access to. This allows proper staging.

Both of these databases would be verified by the IT person making production changes to the database and would be included as part of the staging process to production, which insures consistent schemas across environments.

If you want developers to have databases all over the place, they simply cannot be maintained and it will have to be their responsibility to insure that their dataset is current enough for their needs. You could programmatically provide a schema dump on a shared drive or perhaps a blackhole replication slave with RO access.

share|improve this answer
    
Hmmm, blackhole's (expecially the 'nothing happens to auto_increment id's) would be nice indeed. Alterations in data would of course still be logged to the binary log. However, I think I'm beginning to realize the question probably can be answered with a simple: 'just create a database without any write-rights, and make that the ultimate master'. The most simple answer to: 'I only want to log structure changes and no data changes' is of course 'Well, just don't change any data on the master'. Combined with no alteration rights on structure except for the slave processes that could work. –  Wrikken Sep 1 '10 at 14:47
    
You're right on. When I re-read your question, I realized what was actually involved. Check out my update. –  Warner Sep 1 '10 at 14:55
    
Went with this solution (although I dropped the blackhole tables: the envisioned master is now just a structure with the proper engines without any data), with a replicate-same-server-id & replicate-rewrite-db to propagate structures to other databases on the same server (although I have yet to test that last 'database-to-database-replication on the same server...) –  Wrikken Sep 1 '10 at 17:25
add comment

Unfortunately, MySQL replication does not allow that. What you can do is regularly run mysqldump -d which will dump the database and table structure, but leave out the data. You can then import this structure to a new DB.

From man mysqldump:

--no-data, -d

          Do not write any row information for the table. This is very useful if
          you want to dump only the CREATE TABLE statement for the table.

We do this where I work currently. As part of any changes to the DB, we run this command and save it to a file and push it into source control. Devs simply pull down the file and apply the changes.

share|improve this answer
    
I'm aware of the mysqldump -d option, but this would still require manual intervention (after all, we don't want to lose existing data, so most diffs in structure before<>after should be manually rewritten to ALTER TABLE statements (although it would work for keeping stored procedures / functions in sync). If you know a tool which can create those ALTER TABLE statements from a diff in structures that could help, I can write something basically working, but there are always possible edge-cases / bugs lurking there.... –  Wrikken Sep 1 '10 at 14:37
    
What you're describing is a table migration. Frameworks like Django and Rails have support for this natively, but it's usually based off the models defined in the source. It builds a set of "migration" files with these alter statements within them and versions them to allow you to migrate up along all the changes. This is possibly what you're looking for, but will require you to place your DB schema in one of those frameworks. Hope this helps. –  vmfarms Sep 1 '10 at 14:51
    
Being locked into a framework is unfortunately not a solution. as projects can differ in both framework and even language. Querying information_schema for precise structure & importing it into a framework like that could work, but would still require manual intervention, which I'd like to avoid. Thank you for the suggestion though. –  Wrikken Sep 1 '10 at 15:54
add comment

Since you mentioned symlinking, I'll assume the instances of MySQL, or multiple databases, are either on the same server or have access to the same drives.

Assuming this I would try symlinking the master's .frm to the slave's .frm. This way whenever the master is updated with structural changes the slave is automatically updated, but they won't share data. I haven't actually tried this, but it should work in theory.

"Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension."

share|improve this answer
    
I should note, definitely test this out first, especially by adding fields that have a default value. –  Kyle Buser Sep 1 '10 at 16:04
    
Access to the same drives is often possible (multiple clients of the same project per server), but not always (different local development machines, clients spread out over multiple servers etc.). I might test this for arguments sake in future, but I have very bad experiences syncing files of MySQL instances instead of using the proper mechanisms MySQL provides. –  Wrikken Sep 1 '10 at 17:23
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.