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?


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.

  • 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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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