MySQL – Automatically Replicate structure-only from DB1 to DB2 on the same host

MySQLreplication

I have two databases:

- production
- development

Is there a way I could set a replication schema in order to replicate any structure changes from production to development? Preferably, structure only, no data replication.

Thanks

Best Answer

This can be achieved by using either black-hole storage engines or by invoking some schedule script to truncate the tables periodically.

Both approaches have prons and cons so use which suits you best. You can use following method when you want to use black-hole storage engine

1. Take back-up using mysqldump using following command

mysqldump --login-path=cred  --port=$i  --master-data=2 --databases user-databases > mysql_backup.sql

2. Replace storage engines from innodb to blackhole using following command

sed -i.bak 's#InnoDB#blackhole#g'  mysql_backup.sql

3. Now proceed to restore this backup on to your target server using following command

mysql --login-path=<cred> -h <emote_host> --show-warnings <mysql_backup.sql

4. Issue change master on slave server getting co-ordinates from the backup file.

5. Issue show slave status\G to verify slave is working.

6. Confirm from some table to verify that no data is being replicated.

You need to change default storage engine to Blackhole on slave server so structure of tables created on master is replicated and engine needs to be overwritten.

The second approach involves truncating tables periodically, this way you can recover or switch-over in case of some disaster. Use following commands to print a truncate table for all user databases and then execute it in some script.

select concat('truncate ',table_schema,'.',table_name,';') from information_schema.tables where table_schema not in ('information_schema','mysql','performance_schema','sys','test');

Hope it helps

Related Question