-   Linux - General (
-   -   Need help with a script. MySQL and Sed (

nny0000 12-03-2007 08:37 PM

Need help with a script. MySQL and Sed
In an effort to ease some of my web development steps I plan on creating a script to automate everything. I run a drupal site and I need to set up a development environment quickly after I shut down the site for maintenance.

These are the steps that I need verified:

Steps to prepare the development environment
1. Drop all tables in my development Database (MySQL)
2. Remove all drupal files from development directory

Copying development site over to development
3. Copy main DB into dev DB
4. Copy drupal files to dev directory

Setting up the environment for testing
5. use sed (or a find and replace prog) to change $db_url = 'mysql://username: password@localhost/livedatabase'; to $db_url = 'mysql://username: password@localhost/devdatabase'; in drupal settings.php

6. Since the development site is accessible from the internet, I need to add some security to the new dev .htaccess file. I figured I could just add:

AuthUserFile /full/path/to/.htpasswd
AuthType Basic
AuthName "Development"
Require valid-user

to a .txt file and use cat to dump it at the end of .htaccess.

So this is what I have so far, any help would be greatly appreciated.

Also I have Dreamhost so localhost will not work with connecting to MySQL databases I have to use something like ''


mysqldump -u[USERNAME] -p[PASSWORD] -h [HOSTNAME] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]
cd ~/devsite // safety measure for next line
rm -Rf ./*
$ mysqldump -u[USER] -p[PASSWORD] -h [HOSTNAME][DATABASE] | ssh mysql [DATABASE]
cp ~/livesite/* ~/devsite/

I have no idea how to use sed need advice

cat ~/devsite/.htaccess << ~/htaccess-password.txt

Any advice would be greatly appreciated. Thanks!

jschiwal 12-03-2007 09:20 PM

For appending to ~/devsite/.htaccess, you could use

cat >> ~/devsite/.htaccess << EOF
AuthUserFile /full/path/to/.htpasswd
AuthType Basic
AuthName "Development"
Require valid-user

You don't need a separate file. With htaccess-password.txt, you could simply use:
cat htaccess-password.txt >> ~/devsite/.htaccess

For sed:
sed -i '/db_url = /s/livedatabase/devdatabase/' $file

You could have a list of files as well in the argument. This list might come from a find connand, and you could use xargs to add the files matching the search criteria to the end of the sed argument. The "-i" option to sed will cause each file to be edited in place instead of catenated together. Otherwise you would need to process each file in a loop and use a temporary file for the results, to prevent clobbering the original file. ( note: the -i option may not be available in some non-gnu versions of sed. )

nny0000 12-03-2007 09:42 PM

Now that I think of it, I could just just copy the live sites settings.php file into another folder, mark it up the way I want and copy it into the development site via script. I would just have to check it on software upgrades. Much easier than using sed.

Thanks for all the help

nny0000 12-03-2007 11:00 PM

Ok well that didnt work as planned. The only issue is emptying the devsite database; I get 'mysqldump:error 2002 (HY000)' and 'mysqldump:got errno 32 on write'. Everything else works to my knowledge except emptying the devsite database

Here is the command that isnt working:

mysqldump -u [USERNAME] -p[PASSWORD] -h [HOSTNAME] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

Does anyone have a solution to get mysqldump to enter the DB and delete everything in it, while retaining the database?


jschiwal 12-04-2007 09:10 AM

Are you trying to use the output of grep ^DROP to the third command? If so use xargs.

However, I don't know if mysql will take multiple lines of input after the command.

Would 'DROP db_name.*;' work if you logged in without specifying a database?

If not, you could use:
mysqldump -u [USERNAME] -p[PASSWORD] -h [HOSTNAME] --add-drop-table --no-data [DATABASE] | grep ^DROP >temp
mysql -u[USERNAME] -p[PASSWORD] [DATABASE] <temp

All times are GMT -5. The time now is 11:05 AM.