Today I needed to clone a schema in MySQL: simply I upgraded my monitoring software Zabbix and thus I needed to “upgrade” also the DB schema where Zabbix saves data. As this is a destructive operation I wanted to have the zabbix schema cloned. But how do you clone a schema in MySQL?
The first thing to do is to create the new schema (or database in MySQL world) and grant all privileges to a user (even an existing one):
create database zabbix16;
grant all privileges on zabbix16.* to zabbix@”%”;
At this point we can use the mysqldump program to dump data directly in the mysql program:
mysqldump –add-drop-table –complete-insert -uzabbix -pPWD zabbix | mysql -uzabbix -pPWD zabbix16
After some time, when mysqldump finishes, you will see the zabbix16 schema as an exact copy of the original one!