[kwlug-disc] how to duplicate a mysql database?

[Khalid Baheyeldin]

On Wed, Aug 26, 2009 at 3:44 AM, Robert P. J. Day <rpjday at crashcourse.ca>wrote:

> On Tue, 25 Aug 2009, Khalid Baheyeldin wrote:
>
> > It is actually difficult to do what you have seen. The hardest part
> > is assigning permissions for a user. You can skip that if you are
> > testing with root.
>
>   yes, i'll be testing as root, so i assume i can skip that "GRANT"
> step.
>

In that case, it is reduce to:

# mysqladmin create newdb
# mysqldump olddb | mysql newdb

And if the database was created in previous steps, then you either need to
DROP it, and then re-create it, or have a cleanup script that empties it for
you.

I use this script that I call dbclean.sh, which again assumes you are root,
with no passwords.

#!/bin/sh

case $# in
2) DB_NAME=$1
   DB_USER=$2
   CMD="mysql -u$DB_USER $DB_NAME"
   ;;
1) DB_NAME=$1
   CMD="mysql $DB_NAME"
   ;;
*) echo "Usage: `basename $0` database [user [password]]"
   exit 1
esac

echo "SHOW TABLES;" |
  $CMD |
  grep -v '^Tables_in_' | sed -e 's/\(^.*$\)/DROP TABLE \1;/' |
  $CMD



So, the work flow becomes:

Once only:
# mysqladmin create newdb

Every iteration of the test
# dbclean.sh newdb
# mysqldump olddb | mysql newdb


>   i'd seen a number of solutions that did that last part in two steps:
> dump the db to a file, then run mysql as a separate command.  so
> there's nothing wrong with using a pipe and driving the output to the
> input?  good.  i thought that was acceptable, i just wanted to make
> sure.


If the database is not too big, there is no downside. If it is big, it will
still work, but perhaps the pipe size will be slower? I have not measured
that. You still need the time for write and then the time for read. And
the database operations (e.g. indexing) may be slower than either.

I have used that many times and never had an issue.


>
>   of course, all will be backed up thoroughly. :-)
>
>


-- 
Khalid M. Baheyeldin
2bits.com, Inc.
http://2bits.com
Drupal optimization, development, customization and consulting.
Simplicity is prerequisite for reliability. --  Edsger W.Dijkstra
Simplicity is the ultimate sophistication. --   Leonardo da Vinci
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://kwlug.org/pipermail/kwlug-disc_kwlug.org/attachments/20090826/506ade11/attachment.htm>