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.

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?

share|improve this question

2 Answers 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.

share|improve this answer
    
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.

share|improve this answer

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.