Thursday, November 09, 2006

Editing a database with SED

As I had the occasion to take an existing database and make some wholesale changes to it and reload it under a new name, and particularly wanted to do this possibly numerous times while I tweaked the changes I'm making, the thought had crossed my mind to use sed or something similar to take a database dump, modify a few of its particulars, and load it under a new name. With that in mind I created a bash script rather like the following:

mysqldump -h server -u root -ppassword \
--database olddatabasename \
--routines \
--ignore-table=olddatabasename.page_content \
--add-drop-database \
--add-drop-table \
| sed -e "s/\`//g" \
-e "s/olddatabasename/newdatabasename/g" \
-e "s/Song/Work/g" \
-e "s/song/work/g" \
-e "s/artists_works/artist2work/g" \
| mysql -h server -u root -ppassword

mysql -h server -u root -ppassword newdatabasename << '$$'
grant select on newdatabasename.* to PhpUser@boblap;
alter table artists add column MiddleName varchar(20) after FirstName;
alter table artists add column Suffix varchar(10) after MiddleName;
As you can see I piped the output of mysqldump into sed, made a few blanket changes, then piped it into mysql to load the new database. Then I ran mysql again to make some additional changes. All this in the context of a bash script that I can run repeatedly as needed until I'm satisfied with the changes.

The thought also occurred to me, especially for large databases, of breaking the operating into several parts, e.g. first the schema only, then the data, table by table if necessary. Obviously this is meant more for development than production, but this could be used for some complex scenarios or blanket changes potentially tedious and time-consuming if done by hand, not to mention the likelihood of error.


Post a Comment

Links to this post:

Create a Link

<< Home