Brucalipto.org

Keep your door open!

How to Clone a Schema in MySQL

| Comments

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):

Creating a new schema
1
2
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:

1
2
$ 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!

Comments