[GLLUG] Re: MySql: Database inside of a Database
Scott Henry Harrison
harris41 at msu.edu
Wed Feb 11 08:06:23 EST 2004
> Is it possible to have a database inside of a database?
Anything is possible, but I am not sure that I recognize what
you are suggesting as a conventional solution.
> Or maybe some one can offer a better solution:
> I would like to do this so that when the stat1's user no longer wants
> the stats, I can just delete the database and not have to go through
> deleting 5 to 10 tables.
To replicate a database, you can dump out its tables and the
load into new database:
mysqldump -u delta statdb -p > statdb.copy.dump
mysqladmin create stat1
mysql -u root mysql -p < grant_privileges_SEE_BELOW_FOR_HOWTO
mysql -u delta stat1 -p < statdb.copy.dump
To delete a database:
mysqladmin drop stat1
Alternatively, you can replicate and delete databases
underneath DB_DIR, where DB_DIR is something like
cp -pR statdb stat1
rm -Rf stat1
> If this is possible, how can I grant a user name and password so that
> they can do every thing on statdb and included databases but leave the
> rest of the databases alone?
You can create an automated script to do this:
Just specify the right command sequence inside
mysql -u root mysql -p < grant_privileges.sh
mysqladmin may require a password (and you would need
to then also have -p, like "mysqladmin create somedb -p").
Of course, if you directly copy directories underneath
/var/db/mysql or whatever DB_DIR is (and "violate"
expected usage of mysqladmin), then you can avoid this
For a really programmatic approach, Perl's DBI interface
to MySQL works quite well.
More information about the linux-user