[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.

http://www.mysql.com/doc/en/Client-Side_Scripts.html 

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
/var/db/mysql. 

cd /var/db/mysql
cp -pR statdb stat1 

or 

cd /var/db/mysql
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?

http://www.mysql.com/doc/en/Adding_users.html
http://www.mysql.com/doc/en/GRANT.html 

You can create an automated script to do this: 

Just specify the right command sequence inside
grant_privileges.sh 

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
password requirement. 

For a really programmatic approach, Perl's DBI interface
to MySQL works quite well. 

Regards,
Scott 




More information about the linux-user mailing list