Mysql – Adding tables on the master – thesql database replication

MySQLmysql-replication

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?

Best Answer

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.

Related Solutions

Mysql – Master and Slave MySQL databases with different schemas

Yes. Keep in mind, the queries will replicate and execute identically on the slave as with the master. If the column is on the end of the table, it could simply use the default value. If it is in the middle, you could introduce data type conflicts or potentially even replication failure if the insert failed.

Mysql – Restart thesql replication after sql_error

You can use the following commands (on mysql prompt):

mysql> STOP SLAVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS;

The value 1 represents the number of statements to skip. You can do it repeatedly until the replication is fixed. You can see this page.

Best Answer

Related Solutions

Mysql – Master and Slave MySQL databases with different schemas

Mysql – Restart thesql replication after sql_error

Related Topic