Mysql structural replication from master to slave with slave data intact

Question

Here is what I would like to achieve

• I want to create a main database that will contain only a table of structures and no data.
• Slave databases will be used in the structure from the main database. Only a subordinate database will contain data
• Any structural changes (for example, a change table, a drag and drop table, etc.) to the master should be updated to subordinate databases without data loss.

How can I achieve this, since now I need to manually run queries in each database in order to synchronize the structure, which is not a good option. I would like to automate this process so that someone can help me with this. any suggestions or links to any textbooks that would allow me to achieve this were highly appreciated

Answer 1

It cannot be done, but you can hack your way. Begin with a simple setup of ordinary subordinates. After synchronization, they will have the same scheme as a single master. Now the trick is to make requests on the master, which apply to slaves but do not leave data on the master. First create queries in the same way as usual, make sure that each table has an auto-increment key:

INSERT INTO tbl (col1, col2, col3) VALUES (val1, val2, val3); SELECT LAST_INSERT_ID(); 

When executing the request, write down the time and save the last identifier in the queue at the application level. Periodically make requests (each, say, 10 seconds, depending on your load) on slaves:

 SHOW SLAVE STATUS; 

and subtract the value of Seconds_Behind_Master from the current system time (call this time t1). Now go to the queue of old queries by deleting the first element until the time on this element is longer than t1. Every time you delete an item in the queue, you want to delete this entry from the wizard, but leave it on the slaves (where you know this is because they have been updated since the request was created). So, now clear the db master (which will have data in 10 seconds at a certain point in time) without destroying the slaves:

 SET sql_log_bin=0; DELETE FROM tbl WHERE autoincrement_key=last_insert_id; SET sql_log_bin=1; 

Where last_insert_id is the stored LAST_INSERT_ID () to destroy the request.