<br><br><div class="gmail_quote">On Wed, Aug 26, 2009 at 3:44 AM, Robert P. J. Day <span dir="ltr"><<a href="mailto:rpjday@crashcourse.ca">rpjday@crashcourse.ca</a>></span> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div class="im">On Tue, 25 Aug 2009, Khalid Baheyeldin wrote:<br>
<br>
> It is actually difficult to do what you have seen. The hardest part<br>
> is assigning permissions for a user. You can skip that if you are<br>
> testing with root.<br>
<br>
</div> yes, i'll be testing as root, so i assume i can skip that "GRANT"<br>
step.<br>
<div class="im"></div></blockquote><div><br>In that case, it is reduce to:<br><br><span style="font-family: courier new,monospace;"># mysqladmin create newdb</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;"># mysqldump olddb | mysql newdb</span><br style="font-family: courier new,monospace;"><br>And if the database was created in previous steps, then you either need to<br>DROP it, and then re-create it, or have a cleanup script that empties it for<br>
you.<br><br>I use this script that I call dbclean.sh, which again assumes you are root,<br>with no passwords.<br><br><font size="1"><span style="font-family: courier new,monospace;">#!/bin/sh</span><br style="font-family: courier new,monospace;">
<br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">case $# in</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">2) DB_NAME=$1</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;"> DB_USER=$2</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> CMD="mysql -u$DB_USER $DB_NAME"</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;"> ;;</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">1) DB_NAME=$1</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;"> CMD="mysql $DB_NAME"</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> ;;</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">*) echo "Usage: `basename $0` database [user [password]]"</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> exit 1</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">esac</span><br style="font-family: courier new,monospace;"><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">echo "SHOW TABLES;" |</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;"> $CMD |</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> grep -v '^Tables_in_' | sed -e 's/\(^.*$\)/DROP TABLE \1;/' |</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;"> $CMD</span><br style="font-family: courier new,monospace;"></font>
<br><br><br>So, the work flow becomes:<br><br>Once only:<br><span style="font-family: courier new,monospace;"># mysqladmin create newdb</span><br><br>Every iteration of the test<br># dbclean.sh newdb<br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;"># mysqldump olddb | mysql newdb</span><br style="font-family: courier new,monospace;">
<br></div><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class="im"><br>
</div> i'd seen a number of solutions that did that last part in two steps:<br>
dump the db to a file, then run mysql as a separate command. so<br>
there's nothing wrong with using a pipe and driving the output to the<br>
input? good. i thought that was acceptable, i just wanted to make<br>
sure.</blockquote><div><br>If the database is not too big, there is no downside. If it is big, it will<br>still work, but perhaps the pipe size will be slower? I have not measured<br>that. You still need the time for write and then the time for read. And<br>
the database operations (e.g. indexing) may be slower than either.<br><br>I have used that many times and never had an issue.<br> <br></div><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<br>
of course, all will be backed up thoroughly. :-)<br>
<br></blockquote></div><br><br clear="all"><br>-- <br>Khalid M. Baheyeldin<br><a href="http://2bits.com">2bits.com</a>, Inc.<br><a href="http://2bits.com">http://2bits.com</a><br>Drupal optimization, development, customization and consulting.<br>
Simplicity is prerequisite for reliability. -- Edsger W.Dijkstra<br>Simplicity is the ultimate sophistication. -- Leonardo da Vinci<br>