Editing a database with SED
#!/bin/bashAs 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.
mysqldump -h server -u root -ppassword \
--database olddatabasename \
| 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;
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.