How to clone a mysql database

Here’s an easy way to copy or clone a database either on the same server or to a remote server and at the same time search/replace some data in the tables if needed..

All you need to do to clone the db is use mysqldump and instead of writing to a file, just pipe it straight back into mysql like so..

mysqldump –user=username –password=password –host=localhost OldDB  | mysql –user=otheruser –password=otherpass –host=wherever NewDB

Now what if there is some data in the tables somewhere that needs to be replaced? The solution is simple, just pipe it through sed before piping back to mysql, like so..

mysqldump –user=username –password=password –host=localhost OldDB  | sed -e ‘s/foo/bar/g’ | mysql –user=otheruser –password=otherpass –host=wherever NewDB

You can even run it through multiple substitutions in the same command or include all manner of regex funkiness – see the sed docs for details.

In the above case NewDB is assumed to exist already, but you could also include the create statement in the full command like so..

mysqladmin –user=otheruser –password=otherpass –host=wherever create NewDB && mysqldump –user=username –password=password –host=localhost OldDB  | sed -e ‘s/foo/bar/g’ | mysql –user=otheruser –password=otherpass –host=wherever NewDB

Of course you’d need to ensure you can actually access the remote server on the mysql port unless only cloning locally.

Needless to say, when doing things like this with your db it is wise to double check everything for example if NewDB already exists on the host your are cloning to (which may or may not be the one you intended to clone to – check it!) with data that you want to keep then you will have a very bad day if you import the db dump into it since it will have a load of drop table statements and then it’s bye bye data.  Also if you are piping the dump through sed you need to make sure it will only replace what you want it to replace so be careful and make sure you have a working backup!