3

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 ;).

Wrikken
  • 981
  • 9
  • 22

3 Answers3

2

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.

Warner
  • 23,756
  • 2
  • 59
  • 69
  • 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 01 '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 01 '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 01 '10 at 17:25
1

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.

vmfarms
  • 3,117
  • 20
  • 17
  • 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 01 '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 01 '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 01 '10 at 15:54
0

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

Kyle Buser
  • 246
  • 1
  • 9
  • I should note, definitely test this out first, especially by adding fields that have a default value. – Kyle Buser Sep 01 '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 01 '10 at 17:23