1

I have a simple MySQL replication system set up with a single master and a single slave. If I add a table to the master, will it show up on the slave? If I add a column, will it show up on the slave? How about deletes?

This question can be encapsulated in:

How exactly do schema changes work in simple MySQL replication systems?

Dexter
  • 117
  • 1
  • 6

2 Answers2

2

Yes, schema changes replicate as any other event. MySQL slaves replicate master's binary logs into a relay log and then executes the events. Of course, this is if replicate-do-db or another variable identifies the specific scope in question to execute the statements.

There was a bug in MySQL, where if you executed alterations by specifying the table as database.table, it would not replicate. It required a use statement to proceed the queries that matched a replicate-do variable to actually execute the queries. I believe this is addressed in the current version but is still something to be aware of.

This functionality is well documented on the MySQL Web site.

Warner
  • 23,756
  • 2
  • 59
  • 69
  • I ran into this bug yesterday myself. At least I assume that is the issue. Our replication, according to master and slave status, is working properly. However, there is a data discrepancy. Basically I did this: update ss_central.styles set style_css = (select style_css from `central_bak_7-14-10-2`.styles where style_number = $style_number) where style_number = $style_number – Kyle Buser Sep 16 '10 at 18:19
0

If you add a table to the master, it will show up on the slave. If you add a column, it will show up on the slave. If you delete a column, it will delete from the slave.

Without replication filters, MySQL replicates all data and schema changes. Creation and deletion of databases too.

Having said that, I strongly advocate against replication filters.

See MySQL says replication is fine but data is not copied

For most applications, leaving it wide open is perfectly fine.

Community
  • 1
Riedsio
  • 283
  • 4
  • 7