Easiest way to copy an entire MySQL database between two sources?

Is there an easy way to completely copy the structure and data from one database source to another through PhpStorm? Literally a clone... teardown the old, bring in the new.

I have a PROD db, a TEST db (same server but different location/db than PROD), and a local DEV db. I'm trying to figure out the fastest way to be able to wipe out the DEV/TEST dbs and import the live PROD data, so that my data's as fresh as possible when I start work. I read something in a JetBrains post about using a migration feature, but it doesn't seem to exist. I'm not sure if it was just odd wording on the page, or if the feature was removed in a previous version for some reason, but I can't figure out a way to do this.

It sounds like I may be able to accomplish something similar by exporting my PROD db to a DDL file and then importing that into the DEV/TEST dbs, but it doesn't seem like it's as fast as a few clicks. On my pre-PhpStorm setup I did all the work on my actual web server, and I had a bash script setup to do the database replicating in a matter of seconds, and if it's possible I would like to get something similar setup via PhpStorm.

PS - As a note to new users, don't select your entire post, click the font dropdown, and then hit the first letter of the font you want to use. Instead of going to that font on the list and updating your post, it replaces your entire post with the character you pressed, and unlike most other forms you can't use CTL-Z to recover it and you have to re-type your post.

5 comments
Comment actions Permalink

We use SQLyog for this process. Not, but I've yet to find a simpler way to do it.

0
Comment actions Permalink

SQLyog it is! Though they sure don't make it easy to find the free community edition. I looked around for and tried some alternatives before stumbling upon the community edition, because as a sole developer (for now) of an open source project, buying the enterprise version for this one feature just wasn't worth it. The community version is free and has the database copy feature though, so it's perfect for what I need.

For anyone else looking to copy databases like this, save yourself the time and just go download the community version of SQLyog:

https://code.google.com/p/sqlyog/downloads/list

And for reference...


Toad for MySQL - Allows you to import and export to files, but not to other databases.

Navicat - They have free versions if you qualify (ie. if you run an open source project), but it sounds like most people prefer SQLyog.


HeidiSQL - *sigh* The website lists the database copy as a feature, and it shows up in the UI, however the feature's been on hold for years with no plans to finish it. Trying to get this to work was a pretty frustrating endeavour.

0
Comment actions Permalink

We pay for SQLyog (have been for years), because of features like schema and data sync, but we actually like HeidiSQL better for most other operations (didn't know they even had plans for a copy feature.)

0
Comment actions Permalink

Oh, and didn't know SQLyog had a community edition. Nice to know so that I can pass that on.

0
Comment actions Permalink

For others who come here like I have, SQLyog has moved to github and can be found here: https://github.com/webyog/sqlyog-community

0

Please sign in to leave a comment.