mysql structural replication from master to slave with slave data intact

4
votes

Here is what I'd like to achieve

  1. I want to set up a master Database which will hold only the table structures and no data.
  2. Slave databases will take in the structure from the master database. Only slave DB will contain the data
  3. Any structural changes (such as alter table, drop table etc) on the master should be updated to the slave databases without loosing data

How can I achieve this as now I need to manually run queries on each Database so as to keep the structure in sync which is not a good option. I would want to automate this process so anyone there who could help me in this. any suggestions or link to any tutorials which would allow me to achieve this is greatly appreciated

1
phpmysqlpdophpmyadmin
drop table without loosing data? Really? – GolezTrol
The loosing data part was for the alter command and not the drop.. I dont want to save the data if I wanna drop a table – Krish

1 Answers

1
votes

It can't really be done, but you can kind of hack your way through. Begin by just setting up normal slaves. Once synced they will have the same schema as the single master. Now the trick is to make queries on the master that propagate to the slaves but don't leave data on the master. First make queries exactly as you would normally, making sure that every table has an autoincrement key:

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

When you make the query, record the time, and save the last insert id in a queue at the application level. Intermittently make queries (every, say, 10 seconds, depending on your load) on the slaves:

SHOW SLAVE STATUS;

and subtract the Seconds_Behind_Master value from the current system time (call this time t1). Now iterate through the queue of old queries, removing the first element as long as the time on that element is greater than t1. Each time you remove an element in the queue, you want to remove that record from the master but leave it on the slaves (where you know it already is because they have been updated since you made said query). So now clear out the master db (which will have ~10 seconds worth of data at a given time) without wiping out 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() for the query to wipe out.